CSV Inputs into one Excel document

Hi All,

I have a number of CSV files that are held in one folder and I would like to consolidate all of the data into one Excel spreadsheet.

E.g. File1 -> Tab 1, File2 -> Tab 2 etc. of the newly created Excel spreadsheet.

I have used the directory tool so can now see the full path of each file, the directory and filename but I am not sure where to go next.

Any ideas?

Many thanks,
Scott

Datasets

Accepted answers

BenMoss

Okay, so can I ask you to delete that tool.

Then right click on the canvas and hit 'Insert > Macro'

Then navigate to the file 'Batch Macro.yxmc' which should exist in your unzipped folder.

Next step is to connect the directory tool to this macro.

Then, configure the macro, so select the full path field 'Full Path'. Specify an output location (where you want to write the .xlsx file too), and then the name of the .xlsx file you wish too write.

Then you should be able to run it.

Ben

All comments

BenMoss

Does each .csv file have the same format?

If so you can use a wildcard input, that is, use one input tool and first point it at just one of the files, then replace the filename part of the string with an *

c:\Desktop\FileOne.csv

becomes

c:\Desktop\*.csv

And thus alteryx will read in all files in that directory.

Note the files must have the same schema to work. It would also be worth checking the option to 'output filename as field'.

Then you can use one output tool, write the name of your xlsx file with a sample sheet name.

Then, at the bottom you have the ability to 'change file/table name using field'. Here you would check the option and choose 'change table name', then select the filename field. This will then, with an xlsx file, create a tab for each group of records for the specific values that exist in the field you select.

Ben

slinky123

Hi Ben,

Sorry should have said...all the files will contain different information so that solution would not work.

Many thanks,
Scott

Hello BenMoss,

I need help to reach one sheet from a .csv file

I get error if i use a formula tool to pass the file path of .csv to my batch macro.

[FullPath] + "/" + "Sheet1$" works file with .xlsx

this does not work for .csv file. Do you happen to know the input path for one sheet in .csv to be passed to a batch macro.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703