Align Merge Two CSV Files according to Namesake Fields
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:
A |
|
1 |
=file("data1.txt").import@ct() |
2 |
=file("data2.txt").import@ct() |
3 |
=join(A1:data1,ID;A2:data2,ID) |
4 |
=A3.new(data1.ID,data1.Name,round(avg([data2.Q1,data2.Q2,data2.Q3]),2):avgQ_Average,data2.Midterm,data2.Final,data1.Department) |
5 |
=file("mergedFile.txt").export@ct(A4) |
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.
https://stackoverflow.com/questions/56187795/how-can-i-take-the-length-from-the-text-files-in-java
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc_SPL
SPL Learning Material 👉 https://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProc_SPL