Read all csv files in a folder, and modify individual files based on the filenames

[qiaoqiao77]

I was trying to input all the csv files saved in a folder, but they don't have the same format.

These csv files have filenames that contain country names, since the columns(field) are not the same for different country's csv file (Different country's file format is fixed, some country's csv files lack one column, some countries lack two columns, or two columns), I will need to format each csv file based on the country (for example, I need to insert one column before specific column for US file, insert 2 columns for AUS file... so that all files have the same format) before I can combine all the data from different csv files.

Can any one kindly advise how can I do this? Many thanks in advance!!

[Qiu]

@qiaoqiao77 
To read in the data from CSVs with different schema, we can use a Batch Macro and use the feature of below.
And for the modification part, I think we may have to use a lot of filter to branching the processing.

Maybe you can provide a few sample input and desired output?

[Raj]

@qiaoqiao77 
find the macro attached for reference.

[qiaoqiao77]

Thank you @Qiu for your quick rep

I attached two sample csv files. The columns in different country's is not the same, some have more columns, so I need to add columns for those countries with missing columns. 

For example, if the csv filename contains “China”， I will insert a column before column A ; and put country name in the first column

if the csv filename contains "Indonesia",I will insert a column before column A and column D, and put country name in the first column.

There are many other country's csv files as well, will insert columns before different column.

Hope this clarifies my question, thank you again for your help!

[Raj]

@qiaoqiao77 
find the workflow attached
mark done if solved.