Read Multiple CSV file (multiple criteria)

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.

Common Use Cases

Accepted answers

All comments

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-Files-with-Different-Field/ta-p/13370

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,

mceleavey multi file csv.yxzp

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

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.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703