Forum Discussion

iftakhar20's avatar
iftakhar20
Frequent Visitor
1 year ago

Power Query taking long time to import text file

Hi All,

So I have 4 quarterly sales reports for the year in text file that require minimal amount of transformation (mostly to shift some text to another column due to line break and fill down). I'm using Excel 2019 power query to import these files into data model. Total rows in a single file is 1.3 Million with average file size of 120Mb.

 

But the problem is, It is taking such a long time that I have to abandon the load. I am only importing one file at a time still the load gets stuck at 120 Mb (I think it loads the full file but does not finish). I had made an Excel macro 10+ years ago that works better than Power Query. 

 

I'm attaching screenshot of the source file. Can somebody help me with what I'm doing wrong or any strategy to load the file efficiently?

 

Appreciate your support.

 

Need Help

2 Replies

  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    1 year ago

    Hi iftakhar20 

    If I had access to the file, I would be able to determine whether the issue lies within the file itself, or with the ETL or with Power Query in Excel, given that Power BI Desktop tends to perform faster. Additionally, I'm intrigued by how your macro from 10 years ago managed to handle such a large number of rows, considering Excel's row limit is just over 1.048 million.

    • iftakhar20's avatar
      iftakhar20
      Frequent Visitor
      1 year ago

      Hi danextian , Thanks for the message. 

       

      The VBA Macro usese FileSystemObject to read a text file line-by-line so virtually, it can read millions of lines (of course it will be slow). If you see my sample file, there is an alternating empty line that needs to be ignored + there are page headers, so the processed number of lines is much smaller.

       

      I think my logic for shifting the column starting with 00000 is causing this issue. I add a new conditional column starting with 00000 and fill up. This is where things go south.