...

How to Combine Rows and Columns of Multiple CSV Files

 
file merge(2)

Task description

Below are three CSV files: book1.csv, book2.csv and book3.csv respectively. Open them in Excel as follows:

..

..

..

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’ll write the code separately for easy viewing:

A

1

>dir="E:/csv/"

2

=file(dir+"book1.csv").import@tc()

3

=file(dir+"book2.csv").import@tc()

4

=file(dir+"book3.csv").import@tc()

5

=join@f(A2:c1,Id;A3:c2,Id;A4:c3,Id)

6

=A5.new([c1.Id,c2.Id,c3.Id].nvl():Id,[c1.one,c2.one,c3.one].nvl(),[c1.two,c2.two,c3.two].nvl(),[c1.three,c2.three].nvl(),c2.four:four,c3.yes:yes,c3.no:no,c3.maybe:maybe)

7

=file(dir+"merged.csv").export@tc(A6)

A1   Define a variable dir to store the path where the source CSV files locate. 

A2   Import data from book1.csv; @t option enables to read the first row as column headers; @c option is used to separate column values with commas.

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 means to get non-null Id values from the three tables and name the new column as Id. So are the explanations for other expressions.

A7   Export A6’s result to merged.csv; @t option enables to read the first row as column headers; @c option is used to separate column values with commas.

 

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

 

 

【Attachment】 mergecsv.zip

 

 


file merge(2)
 
 •  300 View  •