none
How to read .csv files that have leading and trailing double quotes RRS feed

  • Question

  • I am trying to read an entire folder with the option "Get & Transform Data" from excel, but there are some .csv files that have leading and reading double quotes. Hence, excel do not detect the delimiters inside the double quotes.

    This is how the .csv file looks like including double quotes:

    "Period;Product Group;EAN Code;material no;article no;model no;sales units;sales value with VAT;GT Channel"

    How can i read this specific files with the normal csv files together in a massive way?

    Thursday, July 19, 2018 3:50 AM

Answers

  • Hello.

    This could help:

    let
        Source = Text.FromBinary(File.Contents("your_file_path")),
        ClearSideQuotes = Text.Combine(Csv.Document(Source)[Column1],"#(cr)#(lf)"),
        ToCSV = Csv.Document(ClearSideQuotes, [Delimiter=";"])
    in
        ToCSV


    Maxim Zelensky Excel Inside

    • Marked as answer by Guillercarc Sunday, July 22, 2018 10:18 AM
    Thursday, July 19, 2018 2:49 PM
  • Thanks for your help,

    I found a very simple way (not sure if the most efficiente one), by reading the source as a txt in the sample tranform query and then removing the doubles quotes.

    • Marked as answer by Guillercarc Sunday, July 22, 2018 10:18 AM
    Sunday, July 22, 2018 10:18 AM

All replies

  • Hello.

    This could help:

    let
        Source = Text.FromBinary(File.Contents("your_file_path")),
        ClearSideQuotes = Text.Combine(Csv.Document(Source)[Column1],"#(cr)#(lf)"),
        ToCSV = Csv.Document(ClearSideQuotes, [Delimiter=";"])
    in
        ToCSV


    Maxim Zelensky Excel Inside

    • Marked as answer by Guillercarc Sunday, July 22, 2018 10:18 AM
    Thursday, July 19, 2018 2:49 PM
  • Thanks for your help,

    I found a very simple way (not sure if the most efficiente one), by reading the source as a txt in the sample tranform query and then removing the doubles quotes.

    • Marked as answer by Guillercarc Sunday, July 22, 2018 10:18 AM
    Sunday, July 22, 2018 10:18 AM