Read Multiple CSV file (multiple criteria)

[SH_94]

Dear Community,

I would like to ask if anyone has the workflow that consist of following criteria to read the CSV file:

1. Read multiple csv file in the multiple subfolder

2. Read the file name for each CSV file

3. Read the different schema of the CSV file as some of the files are having different column and header name.

I try to search the other discussion and noted that it is quite hard to find the batch marco that able to perform the step above.

Many thanks for your help in advance.

[ArtApa]

Hi @SH_94 - I think this is the one that you need: https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/CS-Macro-Dev-Reading-in-Multiple-Fi... 

Just need to make a change to the Directory and Input Data tools to be able to read in subfolders and add a column with a file name.

[mceleavey]

Hi @SH_94 ,

I've attached a package which does this for you.

The directory tool reads in the list of files, so you would set the folder here, change the naming convention of the files and select if you need a sub-folder search.

This will then load them all in and create a unioned output. This means you will get null columns for those files that don't have the column of another file.

You can then split them all out using filters and use the cleansing tool to drop null columns.

If they are all basically the same schema but not exact then you won't need to do this as you will want them unioned together.

Hope this helps,

M.

[SH_94]

Dear @mceleavey 

Good day to you.

I was trying to follow the steps and it still does not achieve the desired result that i want. I have attached all my working files: including macro and dummy files as follow.

Basically i would like to achieve the following result:

1. Extract the file name for each file (CSV file)

2. Consolidated all the csv file (including all those subfolder)

3. Read all the csv file (different schema involved)

Can you assist me on the following file attached?

Many thanks for the help

[apathetichell]

Hi... try my macro - but just an fyi - .csv files don't have tabs or sheets.

https://gallery.alteryx.com/#!app/Excel-lent-adventure-2-0/60fd8af7826fd3167c292e3f

It takes in a full path from a directory tool and will combine any .csv, .xls or .xlsx in that directory...

[SH_94]

Dear @apathetichell ,

Can they extract file name and read different schema of csv files?

Many thanks in advance.

[apathetichell]

If it doesn't do both let me know and I'll update it - Directory tool and full path is the control parameter for it. Please post any error messages or other problems/suggestions you have as it's a work in progress. Note - there are two macros in the package.

[SH_94]

Dear @apathetichell ,

I have the following error as below :

May i know how to fix the error ?

Thank you.

[apathetichell]

can you put your .csvs in a directory and use it on that directory? right now it's encountering a file with a bad name for processing ("0.0000") I'd either have to go in and my macro to specifically exclude bad filenames or you can move the file/move the other files to a different directory.... Not sure if it's R or Alteryx which i shaving a problem with that file but you should change the name or dump it.