Import correctly CSV into power query

[PierreL69]

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 crazier is that Excel opens it without any problems in a very clean way, but it is impossible to see the parameters used.
So I am turning to you for help.

It is important to note that this file contains many hidden characters such as LF CR and others that complicate the import process. The character that delimits the columns is “;”.

Of course, before contacting you, I did a lot of research and testing, but to no avail, including all the methods described here:

https://community.fabric.microsoft.com/t5/Power-Query/Csv-Document-Delimiter-amp-quot-cr-amp-quot-ha...

https://stackoverflow.com/questions/79533537/power-query-does-not-recognise-crlf-in-text-string-func...

and many others

It frustrates me that I cannot do it, and I would really like to understand the right method...
And it's very annoying not to see the steps performed automatically by Excel to import the CSV file, which is so clean when opened by Excel.

I have therefore attached a very small part of the CSV file, in which I have replaced many characters in order to keep the data confidential.
Many thanks to you.

here the csv file

[AndreasKiller · Accepted solution]

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?

[AndreasKiller · Accepted solution]

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]

Thanks and I did not see that the final answer was given by you thanks a lot.

If just someone could explain me how to select the right encoding code ?

Thanks a gain

[PierreL69]

Nono it works with this encoding code 1201 but my question is how did you select this code ? because in the list proposed by power query you have lot of possibility and I always selected :

1252 (my regions) or 65001(and i suppose a kind of global code)

[wardy912]

Hero! that works for me

let
    Source = Csv.Document(File.Contents("C:\Users\Alex.Ward\Downloads\test\test.csv"),[Delimiter=";", Encoding=1201, QuoteStyle=QuoteStyle.Csv]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Bbcln bznbTT", Int64.Type}, {"blbTltTn bp", Int64.Type}, {"pTcBbT cBBpzbt bp", Int64.Type}, {"cBBpzbt bcnT", type text}, {"BzstpnTT TypT", type text}, {"STTvlBT GTpzp", type text}, {"ScbTs pXXlBT", type text}, {"TTXTTTbBT bp", type text}, {"Xcnlby GTpzp", type text}, {"npdTb bp", type text}, {"PTpdzBt GTpzp", type text}, {"PpwTT Szppby", type text}, {"zblt STTlcb bp", type text}, {"TpldTnlB", type text}, {"BpnnTTBlcb PTTBcztlpb", type text}, {"XcBtpTy BpdT", type text}, {"DctT DTspctBhTd", type text}, {"DTspctBhTd Tp", Int64.Type}, {"WcTThpzsT DTspctBhTd XTpn", type text}, {"DctT pX DTspctBh XTpn WcTThpzsT", type text}, {"DTspctBhTd Tp_1", type text}, {"pTdTT TTXTTTbBT", Int64.Type}, {"StpTcgT bpBctlpb", type text}, {"Bzy Tptcb VcbzT", type number}, {"BzstpnTT Tptcb VcbzT", Int64.Type}, {"XcBtpTy npdTb BpdT", type text}, {"cdnlb cppTpvcb DctT", type text}, {"TbglbTTT cppTpvcb DctT", type text}, {"XcBtpTy cppTpvcb DctT", type text}, {"TSn cppTpvcb DctT", type text}, {"BpnpTTsspT npdTb", type text}, {"BpnpTTsspT STTlcb bp", type text}, {"zblt PzTBhcsTd XTpn", type text}, {"lbvplBT DctT", type text}, {"lbstcbbctlpb DctT", type text}, {"XclbzTT DctT", type text}, {"TxtTbdTd WcTTcbty", type text}, {"bcbpzT Bpst lbBbzdTd", type text}, {"Dcys slbBT lbst.", Int64.Type}, {"Dcys slbBT Xclb", Int64.Type}, {"XclbzTT BctTgpTy", type text}, {"DTtclbs pX XclbzTT", type text}, {"Tbgblsh TTcbsbctlpb", type text}, {"TbtTy DctT", type text}, {"Szbnlsslpb DctT", type text}, {"Szbnlsslpb TlnT", type text}, {"SzbnlttTd By", type text}, {"Stctzs", type text}, {"TTcspb BpdT", type text}, {"BpnnTbts", type text}, {"npdlXlTd By", type text}, {"npdlXlTd DctT", type text}, {"TTXTT Tp", type text}, {"PcTt bznbTT", type text}, {"PcTt DTsBTlptlpb", type text}, {"PcTt DTsBTlptlpb pthTT", type text}, {"PcTt Qzcbtlty", Int64.Type}, {"ScbTs BpbXlTnctlpb bznbTT", Int64.Type}, {"HcPT tp DTspctBh?", type text}, {"bcbpzT cbbpwcbBT", type number}, {"zblt Bzy PTlBT", type number}, {"blbT Bzy PTlBT", type number}, {"zblt BzstpnTT PTlBT", type number}, {"blbT BzstpnTT PTlBT", type number}, {"Bzst BpbtcBt", type text}, {"Bzst Ph", type text}, {"Bzst bcnT", type text}, {"Bzst cdd 4", type text}, {"Bzst cdd 1", type text}, {"Bzst Blty", type text}, {"Bzst TTg", type text}, {"Bzst Ppst BpdT", type text}, {"Bzst BpzbtTy", type text}, {"lbst BpbtcBt", type text}, {"lbst Ph", type text}, {"lbst bcnT", type text}, {"lbst cdd 4", type text}, {"lbst cdd 1", type text}, {"lbst Blty", type text}, {"lbst TTg", type text}, {"lbst Ppst BpdT", type text}, {"lbst BpzbtTy", type text}, {"XcBtpTy DTspctBh DctT", type text}, {"DlXXTTTbBT bTtwTTb XcBtpTy DTspctBh DctT tp XclbzTT DctT", Int64.Type}, {"WcTThpzsT DTspctBh DctT", type text}, {"DlXXTTTbBT bTtwTTb WcTThpzsT DTspctBh DctT tp XclbzTT DctT", Int64.Type}, {"TxBTTdTd WcTTcbty", type text}, {"ScXTty lsszT", type text}, {"", type text}})
in
    #"Changed Type1"

[PierreL69 · Accepted solution]

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" ?

[wardy912]

It's possible we are,

 I tried to change the encoding in Power query

let
    Source = Csv.Document(File.Contents("C:\Users\Alex.Ward\Downloads\test2\test.csv"),[Delimiter=";", Columns=1, Encoding=65001, QuoteStyle=QuoteStyle.None]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}})
in
    #"Changed Type"

I now have a result similar to yours

Hopefully someone else can help

[PierreL69]

Thanks a lot to have tried to help me, I am strugglin with this since some days now !

I hope someone will find the solution.

Thanks a again

[wardy912]

Try this:

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)""#(0000)4#(0000)3#(0000)1#(0000)0#(0000)4#(0000)0#(0000)""#(0000)" or [#"#(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)""#(0000)4#(0000)3#(0000)1#(0000)0#(0000)4#(0000)4#(0000)""#(0000)" or [#"#(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)""#(0000)4#(0000)3#(0000)4#(0000)0#(0000)3#(0000)3#(0000)""#(0000)" or [#"#(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)""#(0000)4#(0000)3#(0000)4#(0000)0#(0000)7#(0000)8#(0000)""#(0000)" or [#"#(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)""#(0000)4#(0000)3#(0000)4#(0000)0#(0000)8#(0000)0#(0000)""#(0000)" or [#"#(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)""#(0000)4#(0000)3#(0000)4#(0000)0#(0000)8#(0000)1#(0000)""#(0000)" or [#"#(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)""#(0000)4#(0000)3#(0000)4#(0000)0#(0000)8#(0000)3#(0000)""#(0000)" or [#"#(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)""#(0000)4#(0000)3#(0000)4#(0000)0#(0000)8#(0000)4#(0000)""#(0000)")),
    #"Replaced Value1" = Table.ReplaceValue(#"Filtered Rows","""","",Replacer.ReplaceText,{"#(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)b#(0000)l#(0000)b#(0000)T#(0000)l#(0000)t#(0000)T#(0000)n#(0000) #(0000)b#(0000)p#(0000)", "#(0000)p#(0000)T#(0000)c#(0000)B#(0000)b#(0000)T#(0000) #(0000)c#(0000)B#(0000)B#(0000)p#(0000)z#(0000)b#(0000)t#(0000) #(0000)b#(0000)p#(0000)", "#(0000)c#(0000)B#(0000)B#(0000)p#(0000)z#(0000)b#(0000)t#(0000) #(0000)b#(0000)c#(0000)n#(0000)T#(0000)", "#(0000)B#(0000)z#(0000)s#(0000)t#(0000)p#(0000)n#(0000)T#(0000)T#(0000) #(0000)T#(0000)y#(0000)p#(0000)T#(0000)", "#(0000)S#(0000)T#(0000)T#(0000)v#(0000)l#(0000)B#(0000)T#(0000) #(0000)G#(0000)T#(0000)p#(0000)z#(0000)p#(0000)", "#(0000)S#(0000)c#(0000)b#(0000)T#(0000)s#(0000) #(0000)p#(0000)X#(0000)X#(0000)l#(0000)B#(0000)T#(0000)", "#(0000)T#(0000)T#(0000)X#(0000)T#(0000)T#(0000)T#(0000)b#(0000)B#(0000)T#(0000) #(0000)b#(0000)p#(0000)", "#(0000)X#(0000)c#(0000)n#(0000)l#(0000)b#(0000)y#(0000) #(0000)G#(0000)T#(0000)p#(0000)z#(0000)p#(0000)", "#(0000)n#(0000)p#(0000)d#(0000)T#(0000)b#(0000) #(0000)b#(0000)p#(0000)", "#(0000)P#(0000)T#(0000)p#(0000)d#(0000)z#(0000)B#(0000)t#(0000) #(0000)G#(0000)T#(0000)p#(0000)z#(0000)p#(0000)", "#(0000)P#(0000)p#(0000)w#(0000)T#(0000)T#(0000) #(0000)S#(0000)z#(0000)p#(0000)p#(0000)b#(0000)y#(0000)", "#(0000)z#(0000)b#(0000)l#(0000)t#(0000) #(0000)S#(0000)T#(0000)T#(0000)l#(0000)c#(0000)b#(0000) #(0000)b#(0000)p#(0000)", "#(0000)T#(0000)p#(0000)l#(0000)d#(0000)T#(0000)n#(0000)l#(0000)B#(0000)", "#(0000)B#(0000)p#(0000)n#(0000)n#(0000)T#(0000)T#(0000)B#(0000)l#(0000)c#(0000)b#(0000) #(0000)P#(0000)T#(0000)T#(0000)B#(0000)c#(0000)z#(0000)t#(0000)l#(0000)p#(0000)b#(0000)", "#(0000)X#(0000)c#(0000)B#(0000)t#(0000)p#(0000)T#(0000)y#(0000) #(0000)B#(0000)p#(0000)d#(0000)T#(0000)", "#(0000)D#(0000)c#(0000)t#(0000)T#(0000) #(0000)D#(0000)T#(0000)s#(0000)p#(0000)c#(0000)t#(0000)B#(0000)h#(0000)T#(0000)d#(0000)", "#(0000)D#(0000)T#(0000)s#(0000)p#(0000)c#(0000)t#(0000)B#(0000)h#(0000)T#(0000)d#(0000) #(0000)T#(0000)p#(0000)", "#(0000)W#(0000)c#(0000)T#(0000)T#(0000)h#(0000)p#(0000)z#(0000)s#(0000)T#(0000) #(0000)D#(0000)T#(0000)s#(0000)p#(0000)c#(0000)t#(0000)B#(0000)h#(0000)T#(0000)d#(0000) #(0000)X#(0000)T#(0000)p#(0000)n#(0000)", "#(0000)D#(0000)c#(0000)t#(0000)T#(0000) #(0000)p#(0000)X#(0000) #(0000)D#(0000)T#(0000)s#(0000)p#(0000)c#(0000)t#(0000)B#(0000)h#(0000) #(0000)X#(0000)T#(0000)p#(0000)n#(0000) #(0000)W#(0000)c#(0000)T#(0000)T#(0000)h#(0000)p#(0000)z#(0000)s#(0000)T#(0000)", "#(0000)D#(0000)T#(0000)s#(0000)p#(0000)c#(0000)t#(0000)B#(0000)h#(0000)T#(0000)d#(0000) #(0000)T#(0000)p#(0000)_1", "#(0000)p#(0000)T#(0000)d#(0000)T#(0000)T#(0000) #(0000)T#(0000)T#(0000)X#(0000)T#(0000)T#(0000)T#(0000)b#(0000)B#(0000)T#(0000)", "#(0000)S#(0000)t#(0000)p#(0000)T#(0000)c#(0000)g#(0000)T#(0000) #(0000)b#(0000)p#(0000)B#(0000)c#(0000)t#(0000)l#(0000)p#(0000)b#(0000)", "#(0000)B#(0000)z#(0000)y#(0000) #(0000)T#(0000)p#(0000)t#(0000)c#(0000)b#(0000) #(0000)V#(0000)c#(0000)b#(0000)z#(0000)T#(0000)", "#(0000)B#(0000)z#(0000)s#(0000)t#(0000)p#(0000)n#(0000)T#(0000)T#(0000) #(0000)T#(0000)p#(0000)t#(0000)c#(0000)b#(0000) #(0000)V#(0000)c#(0000)b#(0000)z#(0000)T#(0000)", "#(0000)X#(0000)c#(0000)B#(0000)t#(0000)p#(0000)T#(0000)y#(0000) #(0000)n#(0000)p#(0000)d#(0000)T#(0000)b#(0000) #(0000)B#(0000)p#(0000)d#(0000)T#(0000)", "#(0000)c#(0000)d#(0000)n#(0000)l#(0000)b#(0000) #(0000)c#(0000)p#(0000)p#(0000)T#(0000)p#(0000)v#(0000)c#(0000)b#(0000) #(0000)D#(0000)c#(0000)t#(0000)T#(0000)", "#(0000)T#(0000)b#(0000)g#(0000)l#(0000)b#(0000)T#(0000)T#(0000)T#(0000) #(0000)c#(0000)p#(0000)p#(0000)T#(0000)p#(0000)v#(0000)c#(0000)b#(0000) #(0000)D#(0000)c#(0000)t#(0000)T#(0000)", "#(0000)X#(0000)c#(0000)B#(0000)t#(0000)p#(0000)T#(0000)y#(0000) #(0000)c#(0000)p#(0000)p#(0000)T#(0000)p#(0000)v#(0000)c#(0000)b#(0000) #(0000)D#(0000)c#(0000)t#(0000)T#(0000)", "#(0000)T#(0000)S#(0000)n#(0000) #(0000)c#(0000)p#(0000)p#(0000)T#(0000)p#(0000)v#(0000)c#(0000)b#(0000) #(0000)D#(0000)c#(0000)t#(0000)T#(0000)", "#(0000)B#(0000)p#(0000)n#(0000)p#(0000)T#(0000)T#(0000)s#(0000)s#(0000)p#(0000)T#(0000) #(0000)n#(0000)p#(0000)d#(0000)T#(0000)b#(0000)", "#(0000)B#(0000)p#(0000)n#(0000)p#(0000)T#(0000)T#(0000)s#(0000)s#(0000)p#(0000)T#(0000) #(0000)S#(0000)T#(0000)T#(0000)l#(0000)c#(0000)b#(0000) #(0000)b#(0000)p#(0000)", "#(0000)z#(0000)b#(0000)l#(0000)t#(0000) #(0000)P#(0000)z#(0000)T#(0000)B#(0000)h#(0000)c#(0000)s#(0000)T#(0000)d#(0000) #(0000)X#(0000)T#(0000)p#(0000)n#(0000)", "#(0000)l#(0000)b#(0000)v#(0000)p#(0000)l#(0000)B#(0000)T#(0000) #(0000)D#(0000)c#(0000)t#(0000)T#(0000)", "#(0000)l#(0000)b#(0000)s#(0000)t#(0000)c#(0000)b#(0000)b#(0000)c#(0000)t#(0000)l#(0000)p#(0000)b#(0000) #(0000)D#(0000)c#(0000)t#(0000)T#(0000)", "#(0000)X#(0000)c#(0000)l#(0000)b#(0000)z#(0000)T#(0000)T#(0000) #(0000)D#(0000)c#(0000)t#(0000)T#(0000)", "#(0000)T#(0000)x#(0000)t#(0000)T#(0000)b#(0000)d#(0000)T#(0000)d#(0000) #(0000)W#(0000)c#(0000)T#(0000)T#(0000)c#(0000)b#(0000)t#(0000)y#(0000)", "#(0000)b#(0000)c#(0000)b#(0000)p#(0000)z#(0000)T#(0000) #(0000)B#(0000)p#(0000)s#(0000)t#(0000) #(0000)l#(0000)b#(0000)B#(0000)b#(0000)z#(0000)d#(0000)T#(0000)d#(0000)", "#(0000)D#(0000)c#(0000)y#(0000)s#(0000) #(0000)s#(0000)l#(0000)b#(0000)B#(0000)T#(0000) #(0000)l#(0000)b#(0000)s#(0000)t#(0000).#(0000)", "#(0000)D#(0000)c#(0000)y#(0000)s#(0000) #(0000)s#(0000)l#(0000)b#(0000)B#(0000)T#(0000) #(0000)X#(0000)c#(0000)l#(0000)b#(0000)", "#(0000)X#(0000)c#(0000)l#(0000)b#(0000)z#(0000)T#(0000)T#(0000) #(0000)B#(0000)c#(0000)t#(0000)T#(0000)g#(0000)p#(0000)T#(0000)y#(0000)", "#(0000)D#(0000)T#(0000)t#(0000)c#(0000)l#(0000)b#(0000)s#(0000) #(0000)p#(0000)X#(0000) #(0000)X#(0000)c#(0000)l#(0000)b#(0000)z#(0000)T#(0000)T#(0000)", "#(0000)T#(0000)b#(0000)g#(0000)b#(0000)l#(0000)s#(0000)h#(0000) #(0000)T#(0000)T#(0000)c#(0000)b#(0000)s#(0000)b#(0000)c#(0000)t#(0000)l#(0000)p#(0000)b#(0000)", "#(0000)T#(0000)b#(0000)t#(0000)T#(0000)y#(0000) #(0000)D#(0000)c#(0000)t#(0000)T#(0000)", "#(0000)S#(0000)z#(0000)b#(0000)n#(0000)l#(0000)s#(0000)s#(0000)l#(0000)p#(0000)b#(0000) #(0000)D#(0000)c#(0000)t#(0000)T#(0000)", "#(0000)S#(0000)z#(0000)b#(0000)n#(0000)l#(0000)s#(0000)s#(0000)l#(0000)p#(0000)b#(0000) #(0000)T#(0000)l#(0000)n#(0000)T#(0000)", "#(0000)S#(0000)z#(0000)b#(0000)n#(0000)l#(0000)t#(0000)t#(0000)T#(0000)d#(0000) #(0000)B#(0000)y#(0000)", "#(0000)S#(0000)t#(0000)c#(0000)t#(0000)z#(0000)s#(0000)", "#(0000)T#(0000)T#(0000)c#(0000)s#(0000)p#(0000)b#(0000) #(0000)B#(0000)p#(0000)d#(0000)T#(0000)", "#(0000)B#(0000)p#(0000)n#(0000)n#(0000)T#(0000)b#(0000)t#(0000)s#(0000)", "#(0000)n#(0000)p#(0000)d#(0000)l#(0000)X#(0000)l#(0000)T#(0000)d#(0000) #(0000)B#(0000)y#(0000)", "#(0000)n#(0000)p#(0000)d#(0000)l#(0000)X#(0000)l#(0000)T#(0000)d#(0000) #(0000)D#(0000)c#(0000)t#(0000)T#(0000)", "#(0000)T#(0000)T#(0000)X#(0000)T#(0000)T#(0000) #(0000)T#(0000)p#(0000)", "#(0000)P#(0000)c#(0000)T#(0000)t#(0000) #(0000)b#(0000)z#(0000)n#(0000)b#(0000)T#(0000)T#(0000)", "#(0000)P#(0000)c#(0000)T#(0000)t#(0000) #(0000)D#(0000)T#(0000)s#(0000)B#(0000)T#(0000)l#(0000)p#(0000)t#(0000)l#(0000)p#(0000)b#(0000)", "#(0000)P#(0000)c#(0000)T#(0000)t#(0000) #(0000)D#(0000)T#(0000)s#(0000)B#(0000)T#(0000)l#(0000)p#(0000)t#(0000)l#(0000)p#(0000)b#(0000) #(0000)p#(0000)t#(0000)h#(0000)T#(0000)T#(0000)", "#(0000)P#(0000)c#(0000)T#(0000)t#(0000) #(0000)Q#(0000)z#(0000)c#(0000)b#(0000)t#(0000)l#(0000)t#(0000)y#(0000)", "#(0000)S#(0000)c#(0000)b#(0000)T#(0000)s#(0000) #(0000)B#(0000)p#(0000)b#(0000)X#(0000)l#(0000)T#(0000)n#(0000)c#(0000)t#(0000)l#(0000)p#(0000)b#(0000) #(0000)b#(0000)z#(0000)n#(0000)b#(0000)T#(0000)T#(0000)", "#(0000)H#(0000)c#(0000)P#(0000)T#(0000) #(0000)t#(0000)p#(0000) #(0000)D#(0000)T#(0000)s#(0000)p#(0000)c#(0000)t#(0000)B#(0000)h#(0000)?#(0000)", "#(0000)b#(0000)c#(0000)b#(0000)p#(0000)z#(0000)T#(0000) #(0000)c#(0000)b#(0000)b#(0000)p#(0000)w#(0000)c#(0000)b#(0000)B#(0000)T#(0000)", "#(0000)z#(0000)b#(0000)l#(0000)t#(0000) #(0000)B#(0000)z#(0000)y#(0000) #(0000)P#(0000)T#(0000)l#(0000)B#(0000)T#(0000)", "#(0000)b#(0000)l#(0000)b#(0000)T#(0000) #(0000)B#(0000)z#(0000)y#(0000) #(0000)P#(0000)T#(0000)l#(0000)B#(0000)T#(0000)", "#(0000)z#(0000)b#(0000)l#(0000)t#(0000) #(0000)B#(0000)z#(0000)s#(0000)t#(0000)p#(0000)n#(0000)T#(0000)T#(0000) #(0000)P#(0000)T#(0000)l#(0000)B#(0000)T#(0000)", "#(0000)b#(0000)l#(0000)b#(0000)T#(0000) #(0000)B#(0000)z#(0000)s#(0000)t#(0000)p#(0000)n#(0000)T#(0000)T#(0000) #(0000)P#(0000)T#(0000)l#(0000)B#(0000)T#(0000)", "#(0000)B#(0000)z#(0000)s#(0000)t#(0000) #(0000)B#(0000)p#(0000)b#(0000)t#(0000)c#(0000)B#(0000)t#(0000)", "#(0000)B#(0000)z#(0000)s#(0000)t#(0000) #(0000)P#(0000)h#(0000)", "#(0000)B#(0000)z#(0000)s#(0000)t#(0000) #(0000)b#(0000)c#(0000)n#(0000)T#(0000)", "#(0000)B#(0000)z#(0000)s#(0000)t#(0000) #(0000)c#(0000)d#(0000)d#(0000) #(0000)4#(0000)", "#(0000)B#(0000)z#(0000)s#(0000)t#(0000) #(0000)c#(0000)d#(0000)d#(0000) #(0000)1#(0000)", "#(0000)B#(0000)z#(0000)s#(0000)t#(0000) #(0000)B#(0000)l#(0000)t#(0000)y#(0000)", "#(0000)B#(0000)z#(0000)s#(0000)t#(0000) #(0000)T#(0000)T#(0000)g#(0000)", "#(0000)B#(0000)z#(0000)s#(0000)t#(0000) #(0000)P#(0000)p#(0000)s#(0000)t#(0000) #(0000)B#(0000)p#(0000)d#(0000)T#(0000)", "#(0000)B#(0000)z#(0000)s#(0000)t#(0000) #(0000)B#(0000)p#(0000)z#(0000)b#(0000)t#(0000)T#(0000)y#(0000)", "#(0000)l#(0000)b#(0000)s#(0000)t#(0000)�#(0000)B#(0000)p#(0000)b#(0000)t#(0000)c#(0000)B#(0000)t#(0000)", "#(0000)l#(0000)b#(0000)s#(0000)t#(0000) #(0000)P#(0000)h#(0000)", "#(0000)l#(0000)b#(0000)s#(0000)t#(0000) #(0000)b#(0000)c#(0000)n#(0000)T#(0000)", "#(0000)l#(0000)b#(0000)s#(0000)t#(0000) #(0000)c#(0000)d#(0000)d#(0000) #(0000)4#(0000)", "#(0000)l#(0000)b#(0000)s#(0000)t#(0000) #(0000)c#(0000)d#(0000)d#(0000) #(0000)1#(0000)", "#(0000)l#(0000)b#(0000)s#(0000)t#(0000) #(0000)B#(0000)l#(0000)t#(0000)y#(0000)", "#(0000)l#(0000)b#(0000)s#(0000)t#(0000) #(0000)T#(0000)T#(0000)g#(0000)", "#(0000)l#(0000)b#(0000)s#(0000)t#(0000) #(0000)P#(0000)p#(0000)s#(0000)t#(0000) #(0000)B#(0000)p#(0000)d#(0000)T#(0000)", "#(0000)l#(0000)b#(0000)s#(0000)t#(0000) #(0000)B#(0000)p#(0000)z#(0000)b#(0000)t#(0000)T#(0000)y#(0000)", "#(0000)X#(0000)c#(0000)B#(0000)t#(0000)p#(0000)T#(0000)y#(0000) #(0000)D#(0000)T#(0000)s#(0000)p#(0000)c#(0000)t#(0000)B#(0000)h#(0000) #(0000)D#(0000)c#(0000)t#(0000)T#(0000)", "#(0000)D#(0000)l#(0000)X#(0000)X#(0000)T#(0000)T#(0000)T#(0000)b#(0000)B#(0000)T#(0000) #(0000)b#(0000)T#(0000)t#(0000)w#(0000)T#(0000)T#(0000)b#(0000) #(0000)X#(0000)c#(0000)B#(0000)t#(0000)p#(0000)T#(0000)y#(0000) #(0000)D#(0000)T#(0000)s#(0000)p#(0000)c#(0000)t#(0000)B#(0000)h#(0000) #(0000)D#(0000)c#(0000)t#(0000)T#(0000) #(0000)t#(0000)p#(0000) #(0000)X#(0000)c#(0000)l#(0000)b#(0000)z#(0000)T#(0000)T#(0000) #(0000)D#(0000)c#(0000)t#(0000)T#(0000)", "#(0000)W#(0000)c#(0000)T#(0000)T#(0000)h#(0000)p#(0000)z#(0000)s#(0000)T#(0000) #(0000)D#(0000)T#(0000)s#(0000)p#(0000)c#(0000)t#(0000)B#(0000)h#(0000) #(0000)D#(0000)c#(0000)t#(0000)T#(0000)", "#(0000)D#(0000)l#(0000)X#(0000)X#(0000)T#(0000)T#(0000)T#(0000)b#(0000)B#(0000)T#(0000) #(0000)b#(0000)T#(0000)t#(0000)w#(0000)T#(0000)T#(0000)b#(0000) #(0000)W#(0000)c#(0000)T#(0000)T#(0000)h#(0000)p#(0000)z#(0000)s#(0000)T#(0000) #(0000)D#(0000)T#(0000)s#(0000)p#(0000)c#(0000)t#(0000)B#(0000)h#(0000) #(0000)D#(0000)c#(0000)t#(0000)T#(0000) #(0000)t#(0000)p#(0000) #(0000)X#(0000)c#(0000)l#(0000)b#(0000)z#(0000)T#(0000)T#(0000) #(0000)D#(0000)c#(0000)t#(0000)T#(0000)", "#(0000)T#(0000)x#(0000)B#(0000)T#(0000)T#(0000)d#(0000)T#(0000)d#(0000) #(0000)W#(0000)c#(0000)T#(0000)T#(0000)c#(0000)b#(0000)t#(0000)y#(0000)", "#(0000)S#(0000)c#(0000)X#(0000)T#(0000)t#(0000)y#(0000) #(0000)l#(0000)s#(0000)s#(0000)z#(0000)T#(0000)", "#(0000)", "", "_2", "_3", "_4", "_5", "_6", "_7", "_8", "_9", "_10", "_11"})
in
    #"Replaced Value1"

Seems like too much work to be the right way to do things though, but it looks better!

[PierreL69]

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

in PBI

[wardy912]

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.

[PierreL69]

What a shame I do not understand why excel can do it automatically and us with power query we should pass through a manual conversion.

Are we not missing something somewhere?

[PierreL69]

And for additionnal info here a screenshot of the CSV file transformed automatically by excel in a clean way :

And what I have with power query :

We can see that CR LF are assimiled as a new raw 😞 even with the QuoteStyle = QuoteStyle.Csv  parameter 

Please note that csv file will be hosted on a online SharePoint

Thanks again

[wardy912]

Hi @PierreL69 

 I managed to import after changing the encoding in VS code

In VS code, open the file.
Click the encoding label in the bottom-right corner (e.g., "UTF-32 BE").
Choose "Save with Encoding" → UTF-8.
Save and re-import the csv to Power BI.

It still looks strange, but I assume that's because of the encryption you added?

This can also be done in notepad++

Open the CSV file in Notepad++.
Go to Encoding in the top menu.
Select Convert to UTF-8.
Save the file.

I hope this helps, please give a thumbs up and mark as solved if it does, thanks!

[PierreL69]

Huum, I haven't tried your solution yet.

The CSV file will be processed by someone else, someone who is not very good at this kind of thing/manipulation...

For info, I have not added any encoders or anything else, this CSV file is exported as isit is from a website with a MySQL database in the background.

What I want is for this person to upload the CSV file as is it is to a SharePoint, and then for me to import it automatically via Power Query.

Could what you mentioned be done via Power Query (automatically)?

I would like there to be as little manual handling as possible, especially for the person who will have to extract the CSV file every time an update is needed.

[wardy912]

No, unfortunately it can't. Notepad++ will be your easiest option.