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

--

--

--

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

puts, prints and more — printing to the console in Ruby…

Effective Ginkgo/Gomega

5 topic-based Project Ideas in Flutter

Logging in Cloud Environments

Pattern Matching

Using Cloud Build with a multi-stage Docker build file and a private repo

Automate Processes Faster with Low-code | Zoho Creator

Our journey with Reactor

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
chuxin huo

chuxin huo

hello:)

More from Medium

From <h1>Hello World</h1> To Consuming an API

Week 6: Menu del Día

How To Make A Custom QR Code Generator with Logo: A Complete Guide!

Working with Time in SuiteScript (Part 1)