Need support in creating a workflow read excel, Filter and create new format excel? Can someone guide or support

Questions
,
1

Dear Friends,

I need the following use case workflow for my project

  1. Trigger when local file uploaded in particular folder. File is .els.
  2. I need to read the .xls attached sheet 1 Bills and convert it
  3. Create a Teacher wise sheet along with a unique bill number for each teacher
  4. Create a pdf in the local folder for each teacher with the bill number and generate an email trigger to a marketing person and teacher email id

Can you help.I have no clue how to move forward

2

Hey @Priya_Kumar, welcome to community :3

So you can use Excel file from the hard drive like read binary file, but since it’s a bit complicated (for me at least) you may consider

If you need to read the file from the drive, check this node

I am using google node, and they have please easy method to manipulate data (I believe other does too)

About PDF I would wait for answer from more experience people

3

Just to add to what @Shirobachi already said, in case you’re adding your files to a folder on your disk, you could use the Local File Trigger node to start a new workflow when a file has been added to a folder.

That said, your file format seems a little tricky. n8n works with key-value pairs for each item. In a spreadsheet, the key would be the column header while the value would be the content of each cell when using the Spreadsheet File node to read your file.

So the empty “Bill Data” Sheet from your example would not return any items in n8n when you read it. Instead, you would need to add some data to at least one row to read it.

An example node reading the Bill Data sheet from your file could then look like so:

image
image1257×452 28.7 KB

Creating the new sheet in the format you have shared will however not work through the Spreadsheet File node:

image
image1006×452 16.9 KB

That’s because n8n would append each item it has into a single row and the Spreadsheet File node wouldn’t attach empty rows as in your example. It would also not apply formatting through the Spreadsheet File node.

For generating PDF files you could use a service like APITemplate.io.

4

Thanks. I don’t need an empty row. but require generating the unique bill number for each teacher bill.
Can you guide me is there any way I can generate a unique bill number through n8n.

5

Could you please share what you have by now? Also explain please how it’s generate, like you struggling with generating unique number or sth else?
In addition, if it’s possible, maybe share your spreadsheet to better understand

6

I thought of breaking down a requirement into multiple pieces so we can figure a solution step by step

I have a Rest API to download a file from the server folder (It is not in the same server so can’t use local file trigger)

I tried downloading the file using the postman. I am getting the response as
content-type →application/vnd.openxmlformats-officedocument.spreadsheetml.sheet

Step 1: I need to download this spreadsheet file through REST API
Step 2: Store the data to DB through REST API

can you guide how to do this with n8n ?

7
  1. You can download file via request node into sever where is n8n
  2. Redirect this downloaded file to spreadsheet file node to get what you need
  3. use request node to send those data to REST API (step 2)

Hope it’s helpful

8

Yes, it is very helpful.

Please check the flow. Logic is able to cross till fetching file from REST API.

image
image969×428 64.3 KB

I am stuck on the following points

  1. how to convert a spreadsheet output as a JSON request in the following format

[
{
“key1”: “value1”,
“key2”: “value2”
},
{
“key1”: “value1”,
“key2”: “value2”
}
]

Can you guide please? Please check the excel file format here