I have this csv file which I’m not able to delimit correctly using Read CSV activity. When I read the CSV only the top row (column names) are delimited, while all other rows are not. The correct delimiter is comma, and this is also used in Read CSV activity.
I have tried to delimit the file in Excel Power Query with the same results. However if I use the text to columns function in Excel the data delimits correctly.
I have attached a dummy workflow with a selection of the data.
Seems to have something to do with the quotes, however if set “Ignore Quotes” to True, I get an error saying: “Read CSV: The CSV file format for Data\Test.csv is invalid”
This is because your lines are encapsulated within double quotes therefore treated as one string.
But if you remove the double quotes at each extremity, the csv is broken because now your lines have more columns than your header.
It seems there is an unnecessary double quote at the beginning / end of each line.
We can achieve it using Read Text File Activity, Replace activity and Generate DataTable activity as the following sample.
Thanks both. Turns out my colleague had sent me some sample data of the csv in a xlsx file, which I then converted back to csv, which caused the double quoting. So indeed the file was broken.