I receive a CSV file from a colleague that containing sales data. This data I then need to read in to a data table using Powershell.

The format of the file looks like this:

“,””””,””””,””””,””””,””””,””””,””Measures””,””Sale in local currency”
,Sale in amount ,Sale in kilo ,Sale in liter ”
Year””,””EAN””,””Retailer number””,””Product name””,””Brand
name””,””Segment””,””Segment2″”,””Chain””,””””,””””,””””,”””””
“202102,””[EAN]””,””[rn]””,””[NAME]””,””[BRAND]””,””[SEGMENT]””,””[SEGMENT2]””,””CHAIN””,””[LOCAL
CURRENCY]””,””[SALE AMOUNT]””,””[SALE KILO]””,””[SALE LITER]”””

The Sale in local currency, Sale in amount, Sale in kilo, Sale in liter and Segment2 can contain commas.

I have tried importing the CSV file where I skip the first 6 rows and create my own headers using -header @(…). However, due to the first row of data starting with a quotation mark and finale value ending in a third quotation mark, all values are read into the “year” column.

Replacing the quotation marks using -replace ‘”‘, ‘ ‘ (or exporting using -usequotes never) does not work either as some of the values have commas in them, and thus create extra values that need to be together. Like a number fx. 1.834,23

Replacing the double quotation marks with a single, creates a lot of empty fields at the end of the final value in each row. Something like: “202102,””[EAN]””,””[rn]””,””[NAME]””,””[BRAND]””,””[SEGMENT]””,””[SEGMENT2]””,””CHAIN””,””[LOCAL CURRENCY]””,””[SALE AMOUNT]””,””[SALE KILO]””,””[SALE LITER]”””, “”,””,””,””,””,””,””