Forum Discussion

GFJ_BR's avatar
GFJ_BR
New Member
2 years ago
Solved

Parcial data missing from CSV/Text imported with double delimiter

Hello all.   I´m having some trouble with a file that was generated with ££ as the delimiter. This delimiter is common here in my team and we never had this problem before. The problem is that aft...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi GFJ_BR 

     

    Maybe this can be an alternative. First bring data into only one column without splitting, then use Split column By Delimiter feature to split it into multiple columns by "££". 

    let
    Source = Table.FromColumns({Lines.FromBinary(File.Contents("C:\xxxxxxxxxxxxxxxxxxxx.csv"), null, null, 65001)})
in
    Source

     

    Best Regards,
    Jing
    If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

HotChilli's avatar
HotChilli
Community Champion
2 years ago

How many columns are there? The first one is looking for 37 columns, the second one is looking for 73.

--

"receiving an error message" - this is because if an 'options' record is passed as a parameter (this is the style of the second query, with the options inside '[  .... ]') i believe it only works with a single character delimiter. 

GFJ_BR's avatar
GFJ_BR
New Member
2 years ago

In fact there are only 37 columns, but the query builder "adjusts" the amount for second code because it is inserting the empty columns between the real data columns.

 

For the second code I "talked" with ChatGpt and receveid a suggestion to use this option below, but my version of PowerQuery didn't recognized the value DelimiterStyle.Text.

Changed code suggestion from ChatGpt:
[Delimiter="££", DelimiterStyle=DelimiterStyle.Txt, Columns=37, Encoding=1252, QuoteStyle=QuoteStyle.None]