How to Combine Rows and Columns from Multiple CSV Files

Task description

Below are three CSV files book1.csv, book2.csvand book3.csvopen in Excel:

We want to combine their rows and columns to generate a new file in the following structure:

Directions

1. Start esProc

Download esProc installation package and free DSK edition license HERE. You will be prompted to load the license file when you run esProc for the first time.

2. Write script in esProc:

We’llwrite the code separately for easy viewing:

A1 Define the path variable dirthat contains the source CSV files.

A2 Import data from book1.csv; @t option reads in the first row as column headers; @c option separates column values with comma.

A5 Rename A2, A4 and A6’s tables as c1, c2 and c3 respectively and perform full join over them through Id field. @f option enables a full join. Use null if no matching Id can be found.

A6 Create a new data set based on A5’s table. [c1.Id,c2.Id,c3.Id].nvl():Id gets nonnull Id values from the three tables and name the new column Id. Same explanations about the similar expressions.

A7 Export A6’s result to merged.csv; @t option enables reading the first row as column headers; @c option separates column values with comma.

3. Press F9 to execute the program. Then you can open merged.csvto view the target table.

【Reference】 mergecsv.zip