CSV mapping failure

Highlighted
Six Stars

CSV mapping failure

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

 

 


Accepted Solutions
Six Stars

Re: CSV mapping failure

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

 

Thanks for replies.

Regards

Fred

View solution in original post


All Replies
Six Stars

Re: CSV mapping failure

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

 

Sixteen Stars TRF
Sixteen Stars

Re: CSV mapping failure

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


TRF
Six Stars

Re: CSV mapping failure

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

Sixteen Stars TRF
Sixteen Stars

Re: CSV mapping failure

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


TRF
Six Stars

Re: CSV mapping failure

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!

 

 

Six Stars

Re: CSV mapping failure

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 

Six Stars

Re: CSV mapping failure

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

 

Thanks for replies.

Regards

Fred

View solution in original post

2019 GARTNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog