CSV Inputs into one Excel document

[slinky123]

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

[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 *

So

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

[BenMoss]

Okay then we require something a bit more complex, let me develop a solution and I will post it soon.

Ben

[BenMoss]

Solution attached.

The issue, as you have identified, which makes the problem complex is the fact that the file structures are not the same, thus we have to create a batch macro, to process through one .csv file at a time and write that to an xlsx tab, before then reading in the 2nd, and writing a 2nd tab and so on.

Check out the 'Sample Workflow' and you can look to test it based on the sample data I have used.

Ben

[slinky123]

Hi Ben,

Apologies but I am unable to open this file. Any chance you can send me the yxmd file please or just the code?

Many thanks,
Scott

[BenMoss]

Unfortunately not as it uses the directory tool and the wildcard to identify the files to select, therefor you cannot package automatically the input files that it pulls through. I can share the file as a standard .zip file accessible by the below link which you can unzip.

https://drive.google.com/file/d/12lRo3bPn5-hMjHXZZTMd7TsnsWL0tvdH/view?usp=sharing

Ben

[slinky123]

Thanks Ben. I have managed to extract all of the files, but can you explain how I use the workflow with the batch macro please?

You will have to forgive my ignorance, but it's the first time I have ever used macros in Alteryx.

Cheers,

Scott

[slinky123]

I've changed all the file locations and files to my files. I get a "The Entry Point is invalid" message on the configuration tool...

[BenMoss]

So you should open 'Sample Workflow' and it should look something like this...

In order to get the macro to run successfully you will need to adjust the directory tool to look at the folder you have unzipped.

Some additional tips would be that the little + icon indicates that this is a macro. You can turn this on by going to 'options > User Settings > Edit User Settings > Canvas' > Display Macro Indicators on Tools.

If it is a macro you can right click on the tool and hit 'Open macro' where you can make edits.

I suggest if you have no experience working with macro's you start by looking some of the sample workflows under the help menu. I'd also consider looking at some training material provided on the community. 

A good post regards the technique I have applied can be found here...

https://community.alteryx.com/t5/Alteryx-Knowledge-Base/CS-Macro-Dev-Reading-in-multiple-files-with-...

Although it's performing a different task, the idea is the same, we read one file at a time, the only difference is we are also outputting the file to a specific tab too.

Let me know if you can't get the sample workflow to run as expected.

Ben