Talend community forum / Problem with field separator

Piero001 · 2012-05-21 12:04:50

Tags: [CSV file, delimited file, error, field, schema, separator]

Hello there,

I'm in trouble with creating a file delimited metadata from a *csv file.
An other type than "string" causes an error message.
Maybe a wrong field or row separator causes the problem. Can you help me to find out where the mistake is?

The *csv file has the following schema:

"Column1";"Column2";"Column3"
"Column4";"Column5";"Column6"
"Column7";"Column8";"Column9"

Uploaded Images

Last edited by Piero001 (2012-05-21 12:05:32)

pedro · 2012-05-21 12:11:28

Hi

The root reason is because there is one integer column in the schema which reads String insert data "205616".
You'd better notice double quotation.

Regards,
Pedro

Piero001 · 2012-05-21 12:16:14

Hi Pedro,

many thanks for your answer!

Can't Talend Ignore the double quotations from the insert data? I want to set the column as an Integer because i want to filter the input.

Regards,

Piero

avdbrink · 2012-05-21 12:52:18

Hi,

I suggest you read the value as a String, just because it is a String as the quotes tell us. Than, afterwards you can use a tMap or nother component to convert the type of the variable from String to Integer.

Hope this helps,
Arno

Piero001 · 2012-05-21 14:23:01

Hi Arno,

thanks for your answer.

Im confused with the fact that talend automaticly suggests Integer for the "Transaktionsummer" Column as you can see in screenshot 4, but when I run the job it says Error for string: " "123123" "

why can't Talend read the numbers without the double quotation? I think it uses the wrong field seperator.

I don't know how to convert columns with tMap into the right typ. Is there no other solution for the problem, like changing to the right corresponding charakter? (Screenshot 3)

thanks for your help,

Piero

avdbrink · 2012-05-21 16:17:49

Hi Piero,

I'm sorry, I didn't look at your screens as good as I should have.

You are absolutly right: Talend can read your data without you needing to convert the type of the field. You should just check the CSV Options checkbox (or the CSV radio button under the Escape Char Settings in the wizard).

This will make Talend read the integer without quotes and make them valid.

Hope this helps.

Regards,
Arno

Piero001 · 2012-05-21 16:38:10

Hi Arno,

thank you again! That's exactly what I'm looking for.

But your tipp causes some new troubles: Changing the Escape Char Settings to CSV raises the number of Columns up to 255 (!) - now Talend even involved the empty cells of the *csv file.

Second Question: Which Escape Char and which Text Enclosure should i choose for my Data? I can only choose between

Empty
"/""
"'"
"//"

but shouldn't the right escape char for my file type not looking like this """" ?

thanks for all your help,

Piero

Uploaded Images

janhess · 2012-05-21 16:45:14

If you've got empty cells they should be included in the schema.

avdbrink · 2012-05-21 16:50:57

Hi Piero,

The one you should use is "\"" This is an escaped version of " (the double quote)

Assuming you choose this for text enclosure and you're still getting this much columns you might want to check your input file. Does this one (when opening in excel for example) show this much rows? I once saw someone who accidentally put some formatting on column XE, this cause his CSV export to export all columns up until col XE...

Hope this helps!

Regards,
Arno

Piero001 · 2012-05-21 18:00:12

Hi everyone!

Thanks to you i can now choose every typ I want: Integer, Char.

The new problem is definitly caused by Talend! I created the following example file with MS notepad and tried to generate metadata in Talend.

You can try it by yourself, copy and paste the following lines to the MS Notepad and save it as an .csv file

"column-a-1";"column-a-2";"column-a-3";"column-a-4";"column-a-5"
"column-b-1";"column-b-2";"column-b-3";"column-b-4";"column-b-5"

If you generate metadata from that source, checking the CSV box in step 3 and click the refresh button, the columns will raise up to 255...

Here are my current settings:

";" = Field Seperator
"\n" =Row Seperator
"\"" = Escape Char
"\"" = Text Enclosur

Does someone know how to deal with that???

Last edited by Piero001 (2012-05-21 18:01:24)

avdbrink · 2012-05-21 22:16:25

Hi,

I don't have a Windows machine at hand right now, but will try your setup tomorrow. I'll let you know my findings.

Regards,
Arno

avdbrink · 2012-05-22 07:46:02

Hi Piero,

I've tried your suggested csv file, but couldn't reproduce your column issue. This might be due to the fact I'm using another version of Talend. Currently I'm on 4.2.3. What's yours?

Regards,
Arno

Piero001 · 2012-05-22 09:14:44

Good morning Arno,

thanks for your efforts. I'm currently on a Talend 5.1.0 version running it on a 64-bit-mode under windows 7.
I'll try the setup with an earlier version soon.

Regards,

Piero

avdbrink · 2012-05-22 09:47:24

Good morning to you too Piero,

Well, looks like we've actually found a bug here if it turns out 5.1.0 generates more columns than needed.

I might try a 5.1.0 version here too today if I can find the time. Maybe on some other platform as well. I'm developing on a Windows 7 64 just like you, but I have some Linux at hand on a VM.

Maybe Pedro or Shong can shine a light on this as well. Can't you guys?

I'll keep you all posted.

Regards,
Arno

Piero001 · 2012-05-22 11:15:18

Hi all,

I finally found out that the column problem only appears in the preview window. Everything works fine if you just ignore the preview window not to much columns when executing a job.

think it is a bug in version 5.1.0, it's not a big deal but it's just curious.

thanks for all your help!!

avdbrink · 2012-05-22 14:09:22

Hi Piero,

Good to know it's just a wizard issue.

I won't spend any more time on it then

Good luck!

Best regards,
Arno

Announcement

Unanswered posts

#1 2012-05-21 12:04:50

Problem with field separator

#2 2012-05-21 12:11:28

Re: Problem with field separator

#3 2012-05-21 12:16:14

Re: Problem with field separator

#4 2012-05-21 12:52:18

Re: Problem with field separator

#5 2012-05-21 14:23:01

Re: Problem with field separator

#6 2012-05-21 16:17:49

Re: Problem with field separator

#7 2012-05-21 16:38:10

Re: Problem with field separator

#8 2012-05-21 16:45:14

Re: Problem with field separator

#9 2012-05-21 16:50:57

Re: Problem with field separator

#10 2012-05-21 18:00:12

Re: Problem with field separator

#11 2012-05-21 22:16:25

Re: Problem with field separator

#12 2012-05-22 07:46:02

Re: Problem with field separator

#13 2012-05-22 09:14:44

Re: Problem with field separator

#14 2012-05-22 09:47:24

Re: Problem with field separator

#15 2012-05-22 11:15:18

Re: Problem with field separator

#16 2012-05-22 14:09:22

Re: Problem with field separator

Board footer