How to read all sheets from an Excel file (.xls)?

Questions
1

Hello everyone !
Im running n8n locally on my laptop via Docker.
I want to build a workflow that read all the data inside every sheets from an excel file (.xls). I successfully developed a version that work for .xlsx file and i try to use the same technique for my .xls file but it didnt work.
THE PROBLEM:

  • the xls file is too large (36MB) so i need to compress it and get it from the email sent to me. After getting the zipped file, i decompress it and execute like below and get the error.
    Can anyone help me with the current problem i get. Thank you so much!

Describe the problem/error/question

What is the error message (if any)?

image
image1864Ă—669 71.1 KB

Please share your workflow

Share the output returned by the last node

Information on your n8n setup

  • n8n version: 1.112.5
  • Database (default: SQLite):
  • n8n EXECUTIONS_PROCESS setting (default: own, main):
  • Running n8n via (Docker, npm, n8n cloud, desktop app): docker
  • Operating system: windows
2

The old .xls format is a binary format and cannot be unzipped like xlsx.

However, you don’t need to use the command node and XML to JSON node at all, since there is a built-in conversion node, which you are using at the end of your workflow already.

Search for the “Extract from File” node, set it to “Extract from XLS/XLSX” and provide your binary input.
This will output valid JSON immediately.

3

thank you for your answer but the bult-in Extract from file node just return the data for the first sheet of my excel file; otherwise, i want to extract the data from all sheets of an excel(.xls) file.

4

Oh I’m sorry! Did not get that in your first post.
This makes it more complex indeed. Why is it more complex?
Because XLS is a proprietary format and cannot be handled like XLSX where you just extract the workbook and use it to iterate over the sheets.

If you don’t know the names of the sheets, you need to use a library or API for this usecase.

Either:

  • add a python library like pandas to your instance and use it in code node
  • add a javascript library like SheetJS (xlsx) to your instance via npm and use it in code node
  • use one of many (paid) APIs like https://www.convertapi.com/xls-to-xlsx to convert XLS to XLSX and process it afterwards
1 Like
5

Use the SplitOut node to split the binaries into an array of items.

This will allow you to have as many rows as there are binary files in your node.

Afterwards, you can add the node to extract from XLS files.

1 Like
6

thank you but split out just work for json and my .xls file cannot convert to json yet.

7

Can you save the XLS as a Google Sheet to get more options to handle the data?

1 Like
8

Thank you for your answer. Do you mean i should send in Google Sheet file or i get the file in .xls format then convert into Google Sheet form ? The first option i cannot do that because this file format is decided by the sender and i just listen to the email, receive the file and handle that.

9

Try like that:

10

@YannickDa this will not work for the need of @TrinhNhatHuy, because it’s not about multiple binary XLS files, but one XLS file with many sheets inside it. To extract the information from other than the first sheet, one of the libraries I mentioned will be necessary.

3 Likes
11

I apologize, I hadn’t paid attention to that point. Thank you for the clarification. My solution indeed won’t work.

3 Likes
12

@salmansolutions thank you so much, let me try to use one of your recommended library.

1 Like