Importing a folder of .xlsx and saving them as seperate .csv

Hi folks,

I have a (I assume simple) problem that I couldn't find the answer for yet.

I have a folder filled with 28 excelfiles. Their templates are identical (some have a few "empty" columns I couldn't find yet that prevent them from loading in with a single wildcard input tool) and each contains several hundred thousand rows of data. I managed to input and join them using a batch macro but the precess is painfully slow (more then 1:20 hour just to load the data, no calculations yet).

I have observed that Alteryx is way faster with .csv files. I just loaded in 17 million rows of data in .csv files and it took uner 3 minutes. I tried saving the .xlsx as .csv in Excel, but if I do that I get an "too many input fields" error when inputting the .csv in Alteryx.

I found a quick workaround: Input the .xlsx file and directly output it as .csv (see attached picture).

Screenshot 2021-09-08 172146.png

Of course I could to this one by one 28 times, but I feel like there must be a way to do this automatically since it takes a few minutes for each run. I know how to load all 28 files at once via a batch macro and save as a single .csv, but for documentary reasons I would like to keep them in their original format and get 28 .csv as well.

Is it possible to load in all 28 Excelfiles and output them as 28 .csv? It would be ideal if the filename of the .csv would match that of the input Excel (like seen in the example).

Thank you so much in advance!

Workflow

Datasets

Batch Macro

Accepted answers

Finnn

I somewhat got it to work using a batch macro. But my problem is, that the macro stops after the first iteration. Only one file is read and only one file put out. Could anyone have a quick look at my workflow and see if the error can be found? Thanks!

Edit: I found my error. As it turns out (I wonder how I could miss that), that a batch macro needs a directory as input. Now that I did that everything is working fine.

batchMacro.yxzp

All comments

Garabujo7

Hello @Finnn ,

For an even faster data format I recommend you to export the data to Alteryx db, yxdb, that one is wa faster than csv and has a smaller file size.

If you got the "too many input fields" you may use \0 for no delimiters and then use a text to columns to get all the columns and filter out the empty or null fields with a data cleansing tool.

Also, make sure you modify the field length to something like 10000 to not getting errors for the field length.

Finnn

Hey @garabujo7,

Thank you for the tip with Alteryx db, I'll give that one a try! I still need to figure out how to automate the process but at least know I know which format is best!

Finnn

Edit: I found my error. As it turns out (I wonder how I could miss that), that a batch macro needs a directory as input. Now that I did that everything is working fine.

batchMacro.yxzp

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15880

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703