Forum Discussion

LucAppelmans's avatar
LucAppelmans
Frequent Visitor
2 years ago
Solved

Not all rows imported by Power Query from a fixed-width text file

I have a customer which has an issue related to Power Query. Let me explain the background:

Today they import a fixed-width text file into Access. Then they import the data from Access into Power BI using Power Query. Of course, this doesn’t make sense. Therefore the customer wants to directly import the fixed-width text file in Power BI. Amazingly when they import the fixed-width file not all rows are imported in Power BI. What is also very annoying is that there is no message. Just by checking the number of rows this was noticed.

Technical information:

  • Text File Size: 591MB
  • Number of rows: 929434
  • Number of columns: 77

In Power BI only 470208 rows are imported. I have done several tests:

  • I converted the fixed-width text file into a CSV file, and then all rows did import without any problem.
  • I modified the fixed-width text file by only keeping 25 columns, then 50 columns, 60 columns etc. In all these cases all rows were imported. But when I have 64 columns then less rows are imported.
  • I also tried to import all rows as one column and then use split column. Same result not all rows imported.

Is there any possibility to enable the import of the fixed-width file. What is surprising for me is that Access (a pretty old product) the file correctly imports (without any problem and fast), and that the “modern” Power Query cannot. Any ideas?

Here is the very simple M-code:

let
    Source = Csv.Document(File.Contents("C:\...\FILE.TXT"),77,{0, 3, 11, 19, 23, 26, 28, 29, 34, 52, 67, 82, 83, 86, 87, 88, 99, 110, 111, 118, 136, 141, 156, 158, 161, 178, 182, 186, 191, 196, 197, 215, 226, 227, 228, 229, 242, 245, 247, 253, 271, 277, 295, 301, 319, 325, 343, 349, 367, 373, 375, 378, 379, 394, 409, 411, 414, 427, 433, 445, 446, 447, 450, 455, 458, 484, 515, 530, 545, 560, 575, 605, 635, 636, 637, 640, 641},ExtraValues.Ignore,1252),
in
    Source

 

  • AlienSx's avatar
    AlienSx
    2 years ago

    Anonymous looks like positions in fixed-width text. But this parameter should be

    • delimiter can be a single character, a list of characters, or the value "", which indicates rows should be split by consecutive whitespace characters. Default: ",".

    LucAppelmans i would try to use Lines.FromBinary instead of Csv.Document and then split text by positions. Just my 2 cents.

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    What's that list of numbers after the columns parameter (77)? Is that a list of column names, or delimiters or something? Seems wrong.

     

    --Nate
     

    • AlienSx's avatar
      AlienSx
      Super User

      Anonymous looks like positions in fixed-width text. But this parameter should be

      • delimiter can be a single character, a list of characters, or the value "", which indicates rows should be split by consecutive whitespace characters. Default: ",".

      LucAppelmans i would try to use Lines.FromBinary instead of Csv.Document and then split text by positions. Just my 2 cents.

      • LucAppelmans's avatar
        LucAppelmans
        Frequent Visitor

        Hi AlienSx,
        I tried your suggestion to use Lines.FromBinary, and later split the column.
        I got all my rows. Great thanks.
        Do you have any explanation why this did work?
        Anyway Great Thanks