DEV Community

Judy
Judy

Posted on

2 1 1 1 1

Align Merge Two CSV Files according to Namesake Fields #eg64

Problem description & analysis

We have a txt file data1.txt, as shown below:

ID,Name,LastName,Department

12345,John,Samon,Computer Science

14524,David,Souza,Electric and Electronic

And another txt file data2.txt, as shown below:

ID,Q1,Q2,Q3,Midterm,Final

12345,100,90,75,89,100

14524,80,70,65,15,90

The two files associate through their IDs in a one-to-one relationship. We are trying to generate a new text file as follows:

ID,Name,avgQ_Average,Midterm,Final,Department

12345,John,88.33,89,100,Computer Science

14524,David,71.67,15,90,Electric and Electronic

Solution

Write the following script p1.dfx in esProc:

Image description
Explanation

A1  Import data1.txt.

A2  Import data2.txt.

A3  Join A1 and A2 through ID field in a one-to-one relationship.

A4  Create the result table sequence according to the specific structure.

A5  Export the result table sequence to mergedFile.txt.

Find how to integrate the script code with a Java program in How to Call an SPL Script in Java.

SPL open source address

Download

Image of Timescale

Timescale – the developer's data platform for modern apps, built on PostgreSQL

Timescale Cloud is PostgreSQL optimized for speed, scale, and performance. Over 3 million IoT, AI, crypto, and dev tool apps are powered by Timescale. Try it free today! No credit card required.

Try free

Top comments (0)

Billboard image

The Next Generation Developer Platform

Coherence is the first Platform-as-a-Service you can control. Unlike "black-box" platforms that are opinionated about the infra you can deploy, Coherence is powered by CNC, the open-source IaC framework, which offers limitless customization.

Learn more

👋 Kindness is contagious

Please leave a ❤️ or a friendly comment on this post if you found it helpful!

Okay