Write multiple excel files to multiple CSV files

Hi all, I’m quite new to KNIME and I need some help with this.

I have multiple excel files and I would like to write each of them to each CSV file without uploading one by one.

This means, for example, I have 50 excel files and I want them to be converted 50 csv files where I can also create names for them.

Any recommendations?

Thank you so much!

What names do you want to create for these exported files?

Are all your Excel files in the same folder? And do they all have the same columns?

If so, then you can use the Excel Reader node to read all the files in that folder

If not, you can go old-school with the List Files/Folders node and an Excel Reader to CSV Writer loop that loads each file in turn and exports as a CSV file.

image

You’ll need to add some extra nodes in between the Excel Reader and the CSV Writer to handle the file name creation.

Are you familiar with flow variables and loops?

4 Likes

For all what @elsamuel said, there’s also an assumption that each of these Excel files contains only 1 sheet. You can’t write multiple sheets to 1 csv file.

3 Likes

All my Excels files are in the same folder and all have the same column. Each of excel file contains only 1 sheet.

I try to use the workflow that you showed, but the result comes only 1 csv file which combines all my excel files data. I am not sure for the setting in CSV writer node.

But what I want is as follow
In Folder 1

  • File ABC001.xlsx → ABC001.txt
  • File ABC002.xlsx → ABC002.txt
  • File ABC003.xlsx → ABC003.txt

Any suggestions? Really appreciate it. Thank you.

Hi @kpornbut , as per @elsamuel instructions “You’ll need to add some extra nodes in between the Excel Reader and the CSV Writer to handle the file name creation.”

If you don’t do that, then it will write everything to the same file, which you found out.

1 Like

You can manipulate the Path flow variable for each Excel file (extract the name, add a .txt or .csv extension, save that as another flow variable), and use that new flow variable to create a path and file name for the csv files.

You can access this demo workflow here.
There are probably more elegant ways to do it, but this is something I just threw together.

4 Likes

@elsamuel Thank you so much. I couldn’t have done it without you! :grinning:

1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.