Importing about 2.5 million rows of data from TXT file

[Im_Offset]

Hello everyone....

The title says it all. I have a text file with about 2.5 million rows of data. Obviously this is more than can be on one worksheet. The TXT file is so large, I cannot open in notepad to manually break the data up into smaller chunks.

I'm not very good at using power pivot, so I need help from the community here. I am using Excel 365.

I would be very grateful if some can explain to me how I can either:
1) import all the data across three different worksheets where I can then filter it and manually move data into different spreadsheets. Right now, I'm stuck at just being able to see the first 1,048,576 rows. How can I start a new import that starts on row 1,048,577 on a second worksheet and goes from there?
2) I have created a data model in power pivot with all of the rows. But I do not know how to create the pivot table in excel to show the data I want. I can use the initial filter and then select the data for rows, but then I cannot get it to show additional rows of data from the data set. How can I use power pivot to filter the data on one metric and then get all the data (Which is way less than 100,000 rows) into a workbook?

Thank you in advance, because obviously.
Im_Offset

 

[Logit]

Can't say with certainty but perhaps WORDPAD can handle the 2.5 million rows ? If not, you'll need to search the internet for another word processor than can. I ran into a similar issue a few years back and had to search the internet myself. I don't recall where I located software that
would work.

After finding the software that functions for your needs I would recommend breaking the TXT file data into maybe 500,000 - 700,000 rows ... then process from there. Excel gets really loaded down with large volumes of data. Oftentimes it becomes unmanageable.

Sorry, I can't help you with the pivot table.

 

[AlanSidman]

A sample workbook with 10-15 rows of data would certainly help. Then show us what you would like those 10-15 rows to look like in a PT. Can't help what we cannot see.

 

[Debaser]

If you just want to load filtered sets of data into worksheets you could use power query rather than powerpivot, although if you already have the data in the data model you can also use DAX to load a table.

 

[Marc L]

The title says it all. I have a text file with about 2.5 million rows of data. Obviously this is more than can be on one worksheet

Hello, I've done the same for around 5 millions rows with few columns under VBA - not this forum section - within a single worksheet,​

just leaving two blank columns between the data once filled the last worksheet row.​

But once done the guy was lost, how to manage the data, and so on … Just wasted my time.​

​

To manually break the text file if really necessary just download Notepad++ …​

​

If you already know which is the criteria to filter then rather than import all just import only the necessary data,​

can be achieved in differents ways under VBA or with Power Query - not this forum section again - …​

​

 

[p45cal]

I have created a data model in power pivot with all of the rows. But I do not know how to create the pivot table in excel to show the data I want.

So if you've managed to get the data into the data model, it's only the design of the pivot you need.
Supply (a) a workbook with the data-acquiring query/whatever in it, (b) a small sample of the of the text file (sensitive data removed if necessary, but still realistic) for us to experiment with and (c) the sort of thing you want to see in the pivot.

 

[GraH - Guido]

Load that text file via Power Query (very similar to text import in this case, but better) and load as pivot.

You could load to data model too, and actually you can use the implicit measures like you would with an ordinary pivot. Even though I'm cursing in church now. No direct need to learn about DAX.