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

Question

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!!

Raj · Answer

@qiaoqiao77 
find the workflow attached
mark done if solved.

help.yxzp

qiaoqiao77 · Answer

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!

BookIndonesia.csv

BookChina.csv

Raj · Answer

@qiaoqiao77 
find the macro attached for reference.

batch macro to read multiple sheets.yxmc