Missing data in csv import when backslash used in the text string containing double quotes

[Kamil_edv]

Hi Community,

during csv file import from datalake I miss some data if in a column there is text string containing double quotes.

Values with this quotes use backslash as an indicator.

Example string: Street name "additional name" number, number 2 - addendum"

in csv file looks like this: "Street name \"additional name\" number, number 2 - addendum", next column value

Colums are delimited by comma.

As a result I get in column: Street name \additional name\" number

there is a missing part in that column: , number 2 - addendum

and also further colums are affected as values in columns move and due to additional commas in text string threated as delimeter some values were not imported as there is declared number of columns during import.

Any hint how to properly import data from csv or prepare csv file to import?

here is a function used: = Csv.Document(#"https://****/datalake/***/*** csv",[Delimiter=",", Columns=426, Encoding=65001, QuoteStyle=QuoteStyle.Csv])

[spinfuzer · Accepted solution]

Replace the backslash \ with a " then you can use the Csv.Document

Csv.Document(
    Text.Replace(
        Text.FromBinary(File.Contents("C:\path\to.csv"))
        , "\",""""),
    [Delimiter=",", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.Csv]
)

[Kamil_edv]

Hello Spinfuzer, that's resolving my issue - thank you!