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 "....
BI & Data Analytics
Need Help
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
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

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Nov 13, 2017

      Did you import into the new clean workbook?

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

        Hi,

        Earlier I was trying to import into new worksheet but today i tried the same into the new clean workbook. The result is same as earlier.

         

        Thanks.

Resources