File delimited from CSV not working correctly

Anonymous — Sat, 16 Nov 2024 12:14:28 GMT

As I was finding out how to import directly from CSV data to a Drupal database (MySQL via phpMyAdmin), I came across a bug of Talend.
In the CSV files we use, the field separator often is ';' (semicolon) and rarely is ',' (comma). So, because in our corporation the use of the semicolon as field separator is common, I created a File delimited from a CSV where the semicolon is used as field separator and the corresponding result was:

"id;Achternaam;Voornaam;Voorletters;Tussenvoegsel;Geslacht;Geboortedatum;Straatnaam;Huisnummer;Toevoeging;Postcode;Woonplaats;Land;Telefoon;Mobiel;Email;BSN"
"1;Hoogenband;Pieter;P.C.M.;van den;M;1978-03-14;Floretruwe;3;;6218TL;Maastricht;Nederland;0433-479992;0618273645;pvdhoogenband@gmail.com;109231654"
"2;Gesink;Robert;R.;;M;1986-05-31;De Zicht;13;;7051VL;Varsseveld;Nederland;0315-242180;0627183645;rgesink@gmail.com;110456321"
"3;Vaart;Rafael;R.F.;van der;M;1983-02-11;Mary Zeldenruststraat;8;;1963EC;Heemskerk;Nederland;0251-822506;0636187245;rafavdvaart@gmail.com;109745368"
"4;Zonderland;Epke;E.J.;;M;1986-04-16;Vormt;2;;8531MH;Lemmer;Nederland;0514-563347;0612348765;epkezonderland@gmail.com;110873625"
"5;Haase;Robin;R.;;M;1987-04-06;Oude Haagweg;503;;2552GG;Den Haag;Nederland;0703-250207;0625368479;rhaase@gmail.com;112864753"

Notice the quotes " both before and after each row. This causes the import from CSV to the Drupal database to fail. So, I looked over and over it again and debugged it, but unfortunately to no avail. Then, I thought, well, let's change the field separator to a comma and check if that also fails. So, I created a File delimited from another CSV (result below), which uses commas instead of semicolons, and to my astonishment the quotes " both before and after each row are gone and the import from this CSV to the Drupal database succeeds! Corresponding result:

id,Achternaam,Voornaam,Voorletters,Tussenvoegsel,Geslacht,Geboortedatum,Straatnaam,Huisnummer,Toevoeging,Postcode,Woonplaats,Land,Telefoon,Mobiel,Email,BSN
1,Hoogenband,Pieter,P.C.M.,van den,M,1978-03-14,Floretruwe,3,,6218TL,Maastricht,Nederland,0433-479992,0618273645,pvdhoogenband@gmail.com,109231654
2,Gesink,Robert,R.,,M,1986-05-31,De Zicht,13,,7051VL,Varsseveld,Nederland,0315-242180,0627183645,rgesink@gmail.com,110456321
3,Vaart,Rafael,R.F.,van der,M,1983-02-11,Mary Zeldenruststraat,8,,1963EC,Heemskerk,Nederland,0251-822506,0636187245,rafavdvaart@gmail.com,109745368
4,Zonderland,Epke,E.J.,,M,1986-04-16,Vormt,2,,8531MH,Lemmer,Nederland,0514-563347,0612348765,epkezonderland@gmail.com,110873625
5,Haase,Robin,R.,,M,1987-04-06,Oude Haagweg,503,,2552GG,Den Haag,Nederland,0703-250207,0625368479,rhaase@gmail.com,112864753

Now, I know, it is not that much work to find and replace all occurences of ';' by ',', but I was wondering if somebody knows a workaround for this problem or if anyone else has seen this before and found a solution?
And let's say I work with the comma as field separator and I want to update a Drupal database table by importing a new CSV file with updated fields (and/or added/deleted rows), such that in the updated table all fields are updated and eventually added or deleted fields are respectively added and deleted. How can I accomplish this? Help is really appreciated!
Thanks in advance!

PS: my environment is as follows
Windows 7 Enterprise SP1
"Talend Open Studio for Data Integration" Version: 5.1.1 Build id: r84309-20120530-0540
Generated language Java

Re: File delimited from CSV not working correctly

janhess — Fri, 13 Jul 2012 17:03:41 GMT

Never had a problem with other versions. Must be a bug in 5.1.1. Can you post a picture of your job and the tFileOutputDelimited parameters.

Re: File delimited from CSV not working correctly

Anonymous — Tue, 17 Jul 2012 08:03:12 GMT

I'm not working with a tFileOutputDelimited, but with tMysqlOutput object. The tFileInputDelimited is used as main input, from a CSV file, I use a tMap to process the import from CSV to database.
My import job schema is as in the attached picture. Same for the parameters of the tFileInputDelimited for both cases of ';' and ','.
I think it's a bug, because while using ',' as field separator it works fluently, but ';' is not giving the desired result.
I also attached a picture of the error I get while using ';' as field separator. In one attached picture, where you can edit the tFileInputDelimited, as preview you will see "attribute;...;attribute;...;attribute", whereas in case of ',' you only see attribute,...,attribute,...,attribute.
Hopefully this is useful in resolving this, but I think it's a bug of TOS.
Thanks in advance!

Re: File delimited from CSV not working correctly

alevy — Mon, 30 Jul 2012 01:55:39 GMT

It looks correct to me. You are not comparing like with like. Your first file sample with semi-colons has, as you say, quotes " both before and after each row. But the second file sample with commas does not. That is the only reason the second file works while the first does not.

topic File delimited from CSV not working correctly in Talend Studio

File delimited from CSV not working correctly

Re: File delimited from CSV not working correctly

Re: File delimited from CSV not working correctly

Re: File delimited from CSV not working correctly