topic Re: CSV mapping failure in Talend Studio

CSV mapping failure

Anonymous — Fri, 03 May 2019 09:25:06 GMT

Hi Talends,

I have an issue with loading a correct mapped csv file.

Input CSV (with header) is like:

"field1";"field2";"field3";"field4"

"ABC";"123";"DEF\r\n

456\r\n

GHI";"JKL789"

Output (in Salesforce Text fields) should be like:

field1: ABC

field2: 123

field3:

DEF

456

GHI

field4: JKL789

But it is actually like:

First data set:

field1: ABC

field2: 123

field3: DEF

field4: NULL

Second data set:

field1: 456

field2: NULL

field3: NULL

field4: NULL

etc.

Rough configuration is like tFileInputDelimited-->tMap-->tSalesforceBulkExec

tFileInputDelimited settings are:

CSV Row Seperator: CRLF("\r\n")

Field Seperator: ";"

Escape Char: """

Text enclosure: """

Header: 1

Skip empty rows: true

Right now I have no clue what is possibly wrong.

For any help I would be very grateful.

Thanks!

Regards

Fred

Re: CSV mapping failure

Anonymous — Fri, 03 May 2019 11:56:50 GMT

Hi again,

I recognized an possible issue.

Not all dataset have that problem. So the CRLF might not be the problem.

The datasets, which aren't mapped properly has "" within the field3

(like:

"DEF\r\n

"456"\r\n

GHI")

That might cause the problem. Nevertheless I would think the marked field seperator ";" indicates the individual fields and is not influenced by the text enclosure.

How can I mitigate that problem?

Regards Fred

Re: CSV mapping failure

TRF — Fri, 03 May 2019 13:15:43 GMT

Try to replace " by \" when it appears in data not just as a field enclosure

Re: CSV mapping failure

Anonymous — Fri, 03 May 2019 14:15:43 GMT

Hi TRF

thanks for reply!

I configured the field in the tMap out expression for the mentioned field like:

rowX,field3.replace(""", "\"")

Now I get a compile Error ( The method replace(chat,char) in the type String is not applicable for the arguments (String) )

Did I understand you right?

Regards

Fred

Re: CSV mapping failure

TRF — Fri, 03 May 2019 14:54:33 GMT

replace is for char, not for string.

As soon as you have more than 1 char you need to use replaceAll such as

rowX,field3.replaceAll("\"", "\\\"")

You need to try, I can't actually on my side

Re: CSV mapping failure

Anonymous — Fri, 03 May 2019 15:28:53 GMT

Ok, thanks for the hint!

I did it like you suggested:

rowX,field3.replaceAll("\"", "\\\"")

Unfortunatly I get now a NullPointerExeption in the tMap component.

(java.lang.NullPointerException:null1)

Any ideas left?

Thank you!

Re: CSV mapping failure

Anonymous — Mon, 06 May 2019 11:26:00 GMT

Hello, I have still no success.

I tried several combination of configurations in the tFileDelimited compenent (Escape char, Text enclosure, etc.) and with the replace funtion.

Is there any other solution? E.g. is it possible to use more than one char for the field seperator (In this case: "";"" instead of ";").

This would maybe solve it.

Thanks for your suggestions.

Regrads

Re: CSV mapping failure

Anonymous — Mon, 27 May 2019 16:06:20 GMT

The only solution that works for me was to remove the " frome the inputfile.

Thanks for replies.

Regards

Fred