One Star

File delimited from CSV not working correctly

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:
"1;Hoogenband;Pieter;P.C.M.;van den;M;1978-03-14;Floretruwe;3;;6218TL;Maastricht;Nederland;0433-479992;0618273645;;109231654"
"2;Gesink;Robert;R.;;M;1986-05-31;De Zicht;13;;7051VL;Varsseveld;Nederland;0315-242180;0627183645;;110456321"
"3;Vaart;Rafael;R.F.;van der;M;1983-02-11;Mary Zeldenruststraat;8;;1963EC;Heemskerk;Nederland;0251-822506;0636187245;;109745368"
"5;Haase;Robin;R.;;M;1987-04-06;Oude Haagweg;503;;2552GG;Den Haag;Nederland;0703-250207;0625368479;;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:
1,Hoogenband,Pieter,P.C.M.,van den,M,1978-03-14,Floretruwe,3,,6218TL,Maastricht,Nederland,0433-479992,0618273645,,109231654
2,Gesink,Robert,R.,,M,1986-05-31,De Zicht,13,,7051VL,Varsseveld,Nederland,0315-242180,0627183645,,110456321
3,Vaart,Rafael,R.F.,van der,M,1983-02-11,Mary Zeldenruststraat,8,,1963EC,Heemskerk,Nederland,0251-822506,0636187245,,109745368
5,Haase,Robin,R.,,M,1987-04-06,Oude Haagweg,503,,2552GG,Den Haag,Nederland,0703-250207,0625368479,,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
One Star

Re: File delimited from CSV not working correctly

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.
One Star

Re: File delimited from CSV not working correctly

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!
Seven Stars

Re: File delimited from CSV not working correctly

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.