File delimited from CSV not working correctly

[Anonymous]

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

[janhess]

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.

[Anonymous]

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!

[alevy]

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.