Forum Discussion

Bhanu Prakash's avatar
Bhanu Prakash
Copper Contributor
Nov 11, 2017

How to exclude data from a text file which is already imported

Hi, I am trying to import a text file, basically its a XYZ file, having rows of 9403352. When I import data in a worksheet, it imports the data till 1048576 rows. A warning message also comes that "....You can import the rest of data by repeating the import operation on another worksheet and using the Text Import Wizard to exclude data already imported". 

 

But I am unable to find a option to exclude the data already imported. 

Anyone please help.

BI & Data Analytics
Need Help
  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Nov 11, 2017

    Hi Bhanu,

     

    If you use legacy Text Import Wizard on the first step you see an option from which row to start the import - put here one next to already imported.

    Other words, if you import into one worksheet 1,048,576 rows (which is the size limit, see https://support.office.com/en-us/article/Power-Query-specifications-and-limits-5fb2807c-1b16-4257-aa5b-6793f051a9f4), on next import start from 1,048,577

     

    Alternatively you may load entire file to data model using PowerPivot/PowerQuery connectors (not into the sheet, into the data model). They also have some formal limits, but actually that's limited only by your PC memory size.

    • Bhanu Prakash's avatar
      Bhanu Prakash
      Copper Contributor
      Nov 13, 2017

      Dear Sergei,

      Thanks for your valuable reply. I had tried the same. However I again tried but I am not getting the data imported into my excel worksheet.

      While running text wizard, if I put the value as 1048577, data is not shown in the preview window and on completion of the wizard, only the column 1 value of 1048577 row is getting imported.

       

      Thanks,

      Bhanu