Hi, I need to bring some correction to a CSV file. The picture on top shows the rows (highlighted) that need correction. The one in the bottom shows the row in corrected form. Would this be possible in FME workbench 2023.1? In my case several ti

It looks like you have erroneous new line characters in the input csv which is causing it to break over multiple lines.

Unfortunately its not quite as simple as removing all new line characters as that would break the csv even further.

Can you share the raw CSV file here?

Note that the ";" character also need to be chenged to ",".

I think you have a number of issues with the csv, probably the least of which is the line breaks.

The problematic line has 53 fields, but the header line (and other lines) has 50

You also have double quotes starting the line which in a csv will subsequently escape any commas. This means that row 4, is actually only parsed as one field



I know, been busy looking for solution since monday. Hope you can bring me closer to one.

Of course this is possible with FME 😉

Steps:

• Read the whole file into one feature (Text File, read whole file at once)
• Split on ""
• In the odd-numbered features (which contain the data originally between "") replace ";" with ",", and CR/LF with a space
• Restore the order of the features, and concatenate
• Split on CR/LF, remove all "
• Export to a Text File

The resulting CSV file is read correctly by Excel.

Note: This solution works on the data you provided, it may not work if there are other problems in your data. In that case it may be better to go back to your source and obtain a better CSV file.

Percfect, It works Geomancer.

Thanks👍

Thanks for the feedback, glad to know it works!