CSV mapping failure

[Anonymous]

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

[Anonymous]

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

[TRF]

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

[Anonymous]

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

[TRF]

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

[Anonymous]

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!

[Anonymous]

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 

[Anonymous · Accepted solution]

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

Thanks for replies.

Regards

Fred