Forum Discussion
Missing data in csv import when backslash used in the text string containing double quotes
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])
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] )
2 Replies
spinfuzerSolution Sage
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] )Hello Spinfuzer, that's resolving my issue - thank you!
