Parcial data missing from CSV/Text imported with double delimiter

[GFJ_BR]

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 after data import near half of the data don't show inside the PowerBi or inside Excel.

If I set the delimiter to only one £, then the file is imported correctly, but create an empty column between every data column (and it not helps 🙄).

When I edit the query I noticed that PowerQuery generate different codes for double delimiter and single delimiter.

Double delimiter (with has data missing after import) uses this code:

= Csv.Document(File.Contents("C:\Users\UserX\Folder_1\Folder_2\Folder_3\Folder_4\extracted_data.txt"),37,"££",ExtraValues.Ignore,1252)

Single delimiter use this code:

= Csv.Document(File.Contents("C:\Users\UserX\Folder_1\Folder_2\Folder_3\Folder_4\extracted_data.txt"),[Delimiter="£", Columns=73, Encoding=1252, QuoteStyle=QuoteStyle.None])

There is something that I can do to import all data correctly maintaining the double delimiter? I really don't want to change this setting due to many other data extractions that dont't getting the same import error.

I've tried to use the second code line with double delimiter, but receiving an error message saying that parameter Delimiter just accepts one single character.

Thanks in advance.

[HotChilli]

As per my previous response, "££" will not work if you put the parameters in an 'options' record.  The 'DelimiterStyle error is just going to confuse things further so I think you should throw that away.

--

We know that ££ is a legitimate delimiter so I think you should focus your investigation elsewhere. Possibly a different encoding, possibly investigate how the file was produced in the first place (different language/ regional settings/source software?)

[GFJ_BR]

The file is generated with TERADATA SQL ASSISTANT, and I use the same aplication to create other files from other databases and had no errors during importing the files.
I noticed that for this database in specific the text file generated is slight different from the other ones, but have no idea why this is happening. I'm not changing nothing in configurations. 
The difference is exactly in the delimiter.

The data in file usually appear like this:

NRC££ID_PARQUE_PCC££INSTÂN./DESIG.££CNL££CIDADE££ÚLT.ATUAL.££DT_RETIRADA££ID_STATUS_PC££CLASSE££SEG

For this database the text inside the file shows this:

TR. CHAVE＃RAMAL＃ID FIXA＃DOC＃TITULAR＃LOGIN COTA플O＃N. PEDIDO＃DT. PEDIDO＃VENC. PEDIDO＃ST. PEDIDO

Based on some special characters that in first scenario exporting OK and in the second NOT OK, probably this is realy and encoding problem, but I really not changing nothing in config before file exporting. And to make the picture more weird, both MS EXCEL and also MS POWER BI recognizes the ££ as the delimiter in the second file. 

Both examples were opened in NOTEPAD++ as plain text files.

[v-jingzhan-msft · Accepted solution]

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]

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]

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]