ETL one to many relationship from CSV to XML

cralin, on 02 martie 2024 - 14:16, said:

1,John,RO,The complete address 1
2,David,AT,The complete address 2
3,Helen,DE,The complete address 3
222,Gigel,RO,The complete address 222

1,Dacia,2021,Cancelled,2c4c231b-f612-4725-876d-8a32c674d2eb
3,Porche,2024,Completed,c3e59825-81d2-47bd-b385-d0f587c2c434
2,Mercedes,2020,Clearing,e65752276a19d7fabfab98f71514cb77
1,Audi,2023,Completed,8a63217e-d019-46b9-ae23-1e52c4e57ffe
2,Ford,2021,Execution,e6ac636eb7c1a8fc1c460d5ad4add487
222,Lastun,1960,In approval,e1c76251-1721-4454-b35c-6657e42b8552

cralin, on 02 martie 2024 - 18:34, said:

Edited by dani.user, 02 March 2024 - 19:11.

dani.user, on 02 martie 2024 - 18:59, said:

Id,Name,Country,Address,Model,Year
1,John,RO,address1,,
,,,,Dacia,2021
,,,,Ford,2023

Edited by fabian23, 02 March 2024 - 19:26.

fabian23, on 02 martie 2024 - 19:15, said:

dani.user, on 02 martie 2024 - 23:53, said:

E vorba de un singur if. Mai simplu si mult mai rapid decat cautarea in alt fisier.

Untitled.png

Edited by fabian23, 03 March 2024 - 10:13.

fabian23, on 03 martie 2024 - 10:12, said:

fabian23, on 03 martie 2024 - 10:12, said:

Edited by cralin, 03 March 2024 - 14:20.

dani.user, on 03 martie 2024 - 13:11, said:

dani.user, on 03 martie 2024 - 13:11, said:

Edited by fabian23, 03 March 2024 - 16:33.

$ wc -l *synthetic.csv
  250000 main_synthetic.csv
1374909 reportedCarsData_synthetic.csv

$ time ./etl.sh main_synthetic.csv reportedCarsData_synthetic.csv > result.xml
real 143m23.160s
user 113m33.760s
sys 80m48.694s

ls -lh
total 232M
-rwxr-xr-x 1 cralin cralin 979 Mar 2 19:54 etl.sh
-rwxr-xr-x 1 cralin cralin 1003 Mar 3 21:16 etl_v2.sh
-rw-r--r-- 1 cralin cralin 139 Mar 1 21:23 main.csv
-rw-r--r-- 1 cralin cralin 15M Mar 3 19:04 main_synthetic.csv
-rw-r--r-- 1 cralin cralin 358 Mar 2 13:03 reportedCarsData.csv
-rw-r--r-- 1 cralin cralin 29M Mar 3 19:36 reportedCarsData_synthetic.csv
-rw-r--r-- 1 cralin cralin 189M Mar 3 22:02 result.xml

Edited by cralin, 04 March 2024 - 01:20.

#!/bin/bash
echo '<?xml version="1.0" encoding="UTF-8"?>'
echo '<dataBody xmlns="urn:ro:test:car:report:v1" xmlns:ns2="urn:ro:test:car:report:commontypes:v1" version="4.0">'
FIRST_ELEMENT=1
while IFS="," read -r _ID_ _NAME_ _COUNTRY_ _ADDRESS_ _CARDATA_MODEL_ _CARDATA_YEAR_
do
if [ -n "$_ID_" ]
	 then
	 if [ $FIRST_ELEMENT -ne 1 ]
		 then
		 echo '</report>'
	 fi
	 FIRST_ELEMENT=0
	 echo '<report Id="'$_ID_'">'
	 echo '<Name>'$_NAME_'</Name>'
	 echo '<Address>'
	 echo '<Country>'$_COUNTRY_'</Country>'
	 echo '<AddressFree>'$_ADDRESS_'</AddressFree>'
	 echo '</Address>'
	 else
	 echo '<reportedCar>'
		 echo '<model>'$_CARDATA_MODEL_'</model>'
		 echo '<year>'$_CARDATA_YEAR_'</year>'
		 #echo '<reportedCaseId type="'$_CARDATA_REPORTED_CASEID_TYPE_'">'$_CARDATA_REPORTED_CASEID_UUID_'</reportedCaseId>'
		 echo '</reportedCar>'
fi
done < $1
echo '</report>'
echo '</dataBody>'

1,Name_1,Country_1,The complete address 1,,
,,,,Model_1,Year_1
,,,,Model_2,Year_2
,,,,Model_3,Year_3
,,,,Model_4,Year_4
,,,,Model_5,Year_5
,,,,Model_6,Year_6
,,,,Model_7,Year_7
2,Name_2,Country_2,The complete address 2,,
,,,,Model_1,Year_1
,,,,Model_2,Year_2
,,,,Model_3,Year_3
,,,,Model_4,Year_4
,,,,Model_5,Year_5
,,,,Model_6,Year_6
,,,,Model_7,Year_7
,,,,Model_8,Year_8
3,Name_3,Country_3,The complete address 3,,
,,,,Model_1,Year_1
,,,,Model_2,Year_2
,,,,Model_3,Year_3
,,,,Model_4,Year_4
,,,,Model_5,Year_5
,,,,Model_6,Year_6
4,Name_4,Country_4,The complete address 4,,
,,,,Model_1,Year_1
,,,,Model_2,Year_2
,,,,Model_3,Year_3
,,,,Model_4,Year_4

$ ls -lh *dani.user*.csv
-rw-r--r-- 1 cralin cralin 40M Mar 4 00:55 dani.user__reportedCarsData_synthetic.csv

$ wc -l dani.user__reportedCarsData_synthetic.csv
1623658 dani.user__reportedCarsData_synthetic.csv

$ time ./etl_dani.user.sh dani.user__reportedCarsData_synthetic.csv > result.xml
real 1m34.322s
user 1m9.695s
sys	 0m24.192s

Edited by cralin, 04 March 2024 - 02:42.

cralin, on 04 martie 2024 - 02:39, said:

$ ls -lh *dani.user*.csv
-rw-r--r-- 1 cralin cralin 40M Mar 4 00:55 dani.user__reportedCarsData_synthetic.csv

$ wc -l dani.user__reportedCarsData_synthetic.csv
1623658 dani.user__reportedCarsData_synthetic.csv

$ time ./etl_dani.user.sh dani.user__reportedCarsData_synthetic.csv > result.xml
real 1m34.322s
user 1m9.695s
sys	 0m24.192s

Edited by fabian23, 04 March 2024 - 17:50.

fabian23, on 04 martie 2024 - 17:50, said:

for i in $(seq 1 250000); do echo "${i},Name_${i},Country_$i,The complete address ${i},,"; for j in $(seq 1 $(echo $((1 + RANDOM % 10)))); do echo ",,,,Model_${j},Year_${j}"; done; done | tee dani.user__reportedCarsData.csv

Edited by cralin, 04 March 2024 - 19:13.