topic Re: CSV file with LF in columns. in Talend Studio

CSV file with LF in columns.

JSadasivam1614976104 — Sat, 16 Nov 2024 00:07:37 GMT

I have a CSV file as in the attached image. One of the columns has LF characters in it. The row seaparator is a LF as well. I have tried using tfileinputdelimited with CSV OPtions checked and the the Text Enclosure as """. tfilenputdelimited seems to ignore the text enclosure property.

Any suggestions on how to parse the file ?

Re: CSV file with LF in columns.

Anonymous — Tue, 25 May 2021 05:01:04 GMT

I think you need to set the escape char to LF rather than ".

Re: CSV file with LF in columns.

gjeremy1617088143 — Tue, 25 May 2021 07:00:26 GMT

You can use regex expression to replace LF if you could identify the LF for separate row from the rest

Re: CSV file with LF in columns.

JSadasivam1614976104 — Tue, 25 May 2021 14:14:31 GMT

Thank you for the suggestion. But that does not work.

Re: CSV file with LF in columns.

gjeremy1617088143 — Tue, 25 May 2021 15:04:10 GMT

ok I think you can replace LF not preceded by a double quote or a coma with regex : you read your file as one line as a string (see tFileInputRAw for example) then

(your string).replaceAll("(?<![\",])\\n","the caracter you want instead of LF") in a tjavarow and you save it with tFileOutputRaw.

Then you can read it with LF as row separator.

Re: CSV file with LF in columns.

JSadasivam1614976104 — Tue, 25 May 2021 15:06:05 GMT

Thank you ! I was just tryng that manually using notepad++.

I will try this out in Talend.

Re: CSV file with LF in columns.

gjeremy1617088143 — Tue, 25 May 2021 15:14:45 GMT

it could work if your last field is between double quote or empty

Re: CSV file with LF in columns.

gjeremy1617088143 — Tue, 25 May 2021 15:21:07 GMT

you can test it in notepad++ :

ctrl+h

then in find what : (?<![",])\n

Replace with : the caracter you want

search mode : regular expression

then you click on replace All and you see if it's ok

Re: CSV file with LF in columns.

JSadasivam1614976104 — Tue, 25 May 2021 15:23:05 GMT

Thank you trying it out. Let you know how it goes.

Re: CSV file with LF in columns.

JSadasivam1614976104 — Tue, 25 May 2021 16:42:36 GMT

@gjeremy1617088143 thank you. This worked trying it out with notepad++. Will try it out in Talend.

Re: CSV file with LF in columns.

JSadasivam1614976104 — Tue, 25 May 2021 20:39:46 GMT

@gjeremy1617088143 i am getting a Java Heap error while reading the file . The file size is around 500 MB .

How do I resolve the Heap Error. I increased the JVM max to about 4 GB. Still getting the error.

Re: CSV file with LF in columns.

gjeremy1617088143 — Tue, 25 May 2021 21:52:20 GMT

Cut it in as many csv you can (5to10) and iterate the job on all of them. It will reduce the heap state (500 mo begin to be huge for csv, i already Work with 10go csv it consume all the memory if you don’t partition the data in smaller files).