Forum Discussion

PierreL69's avatar
PierreL69
Helper IV
1 year ago
Solved

Import correctly CSV into power query

Hello everyone, I am contacting you because I am having trouble importing a CSV file into Power Query, but without success... I can't seem to find the right method to do this. What drives me even c...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    I can import the file using this MCode:

    = Csv.Document(File.Contents("Z:\test.csv"),[Delimiter=";", Encoding=1201, QuoteStyle=QuoteStyle.Csv])

     

    Or do I miss something?

  • PierreL69's avatar
    PierreL69
    1 year ago

    Victory it works, so what differ between what I did and what you did is the encoding number you used "1201"

    Could you explain me how you selected this "encoding system" ?

PierreL69's avatar
PierreL69
Helper IV
1 year ago

For info I did what you asked but it did not work same results :

 

 

in PBI

 

 

wardy912's avatar
wardy912
Super User
1 year ago

I change the column number in the query to allow 100 and get a nearly workable result:

 

let
    Source = Csv.Document(File.Contents("C:\Users\Alex.Ward\Downloads\test2\test.csv"),[Delimiter=";", Columns=100, Encoding=65001, QuoteStyle=QuoteStyle.None]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"#(0000)B#(0000)b#(0000)c#(0000)l#(0000)n#(0000) #(0000)b#(0000)z#(0000)n#(0000)b#(0000)T#(0000)T#(0000)", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type1", each ([#"#(0000)B#(0000)b#(0000)c#(0000)l#(0000)n#(0000) #(0000)b#(0000)z#(0000)n#(0000)b#(0000)T#(0000)T#(0000)"] <> "#(0000)"))
in
    #"Filtered Rows"

With your knowledge of the data, you may be able to apply some transformations and get it working, i'm stuck unfortunately.