In one of the fields/columns in my csv file has quotes, and it messes up the following field. Is there a way to remove the quotes before importing the file to Easy File Transfer?
In delimited text, quotes within data should be escaped, so they aren’t confused with quotes around data. If this hasn’t been done, then the CSV is badly formed.
To remove quotes you could
- input your file as Plain text rather than Delimited text
- strip off the quotes using Replace
- write back out to plain text
But this might not be the right thing to do. Can you post as few lines of your CSV as plain text here, so I can see what it looks like?
Thank you. Below is an example of the quote, which mess up with import.
I follow your instructions. After replacing the quote, how do I convert it back to Delimited text import to keep transforming?
This is a valid CSV file with a data value:
“test quoted”
So, in this case, input it as CSV and then use the Replace transform to replace " (double quote) with nothing.
input.csv (30 Bytes)
replace-csv-quotes.transform (1.5 KB)
Thank you.
I think I know where the issue is. When I opened the file by Excel and save it. Excel change the csv quoting format.
Excel is famous for mangling data.
Can you take a look at my data file below?
It looks like the return carriage does play a role in the mess. The [Production] should be another column, but when Import to Easy Data Transform, it is not split.
I can’t upload file so I share my the files using Google Drive URL.
https://drive.google.com/drive/folders/1IVGpL-dYtBdfcCYvGrBns4srfb5ENNUe?usp=sharing
[Production] should appear in a new column. But the carriage return after the two double quotes seems to be incorrectly handled. It might be a bug in Easy Data Transform CSV parser. I will investigate further.
1 Like
Thank you. Please keep me posted!
1 Like
Can confirm it is a bug. When an escaped quote is followed by a LF character (line feed) it is not parsed correctly. Hope to have a fix soon. Apologies for any inconvenience.
Hope to get you a fix today.
The CSV was being incorrectly parsed when an escaped quote was followed by a linefeed.
I believe this snapshot release fixes the problem:
Windows .exe installer: https://www.easydatatransform.com/downloads/EasyDataTransform_1_46_4_snapshot_1.exe
Windows .zip installer: https://www.easydatatransform.com/downloads/EasyDataTransform_Windows_1_46_4_snapshot_1.zip
Mac .dmg installer: https://www.easydatatransform.com/downloads/EasyDataTransform_1_46_4_snapshot_1.dmg
Please try it and let me know.
Thanks for reporting this issue.
1 Like
It works perfectly.
Thank you for fixing it.
Will this also be updated in the next update? I afraid that when we have the next update release, it will no longer work.
It will be in the next release.
Also I have added tests for this bug in our suite of regression tests. So if the bug recurrs, we should pick it up straight away in the automated regression tests (before release).