Read CSV only delimit top row

Help
#1

Hi guys,

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.

Any help would be appreciated.

Thanks.

csvTest.zip (19.6 KB)

#2

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”

#3

Hi,

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.

#4

Hi,

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.

csvTestv2.zip (16.2 KB)

Hope this helps you.

Regards,

#5

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.

Thanks for your help both!

#6

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.