Too many rows

**IknowNOt** · 06-19-2023, 08:01 AM

hey guys,

I have a very huge file, actually it is a notepad, but wont even open on notepad, it is more than 500MB, the only way I know to open it is notepad++, it does open, I want the same info on excel, the thing is the lines are more than what excel can handle, is there a way to import it into excel, or how to have it multiple sheets? I tried importing via get data, notepad, into pivot table, it did work but it said the file has more rows than excel, any idea how to get the data into excel?

Thank you a lot.

**TMS** · 06-19-2023, 08:17 AM

Cut and paste sections of the file in Notepad++ and paste into smaller Notepad ++ files or directly into separate worksheets in Excel.

Then you can massage the subset files/sheets and potentially merge them together.

**sandy666** · 06-19-2023, 09:30 AM

try Data / From Text/CSV into Power Query then you can manage data as you wish

**IknowNOt** · 06-20-2023, 07:23 AM

Thank you all

**sandy666** · 06-20-2023, 07:27 AM

You are welcome

**TMS** · 06-20-2023, 08:17 AM

You're welcome.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

**IknowNOt** · 07-04-2023, 04:43 AM

hi again, so I was able to get my data into power query, and I did something, and it made the output on excel.... the results I got were amazing. My data is huge, so somehow, I was able to have more than 160 excel sheets. I am not sure how I load it from powerquery to excel that gave me this amazing result. I have no idea how to do it again.

any help please? I am asking about something I already did. I have a huge data file, was able to load it into power query, but how to get it back into excel into many sheets? I did it last time, so it can be done. when I load now I only get one sheet with max limit.

thx again

**sandy666** · 07-04-2023, 05:16 AM

Originally Posted by IknowNOt

My data is huge

what does that mean?

with XL sheet you are limited to 1,048,576 rows by 16,384 columns
with Power Query you are limited by RAM

**IknowNOt** · 07-04-2023, 05:56 AM

right, like I said, already tried it and it worked, just forgot how. my file is around 3gb. ram I have around 32gb i712gh gen

**sandy666** · 07-04-2023, 06:02 AM

if your csv files have the same structure try FROM FOLDER then TRANSFORM to a single table filter out to get only the data you need
it doesn't make sense to me to load ca. 160 sheets with similar data

edit:
each new table try to Load not Load to. It should load each table to the new sheet, if not try Load to and choose New Sheet

**IknowNOt** · 07-04-2023, 06:13 AM

let me add this, maybe you can help me more..I have TXT file around 3gb. I was able to load it into the powerquery. it has 12 millions rows and 2 columns.
how to load it into many sheets?

**sandy666** · 07-04-2023, 06:17 AM

CSV is a text file with delimiter (Comma Separated Values) so it doesn't make a big difference with txt file with delimiter
so, load all txt file into power query then load each table to the sheet
sorry for the question but why do you do that when you can load any txt file directly into the spreadsheet?

**IknowNOt** · 07-04-2023, 06:20 AM

well the txt file 3 gb i cant load it into excel! it has 12 millions rows

**IknowNOt** · 07-04-2023, 06:21 AM

how can I load each table? how to divide the file within power querry? can this be done? let us say I have 3 million row can I have each sheet with 1 million rows?

**sandy666** · 07-04-2023, 06:23 AM

your 3GB txt file is a SINGLE file?
so load it to PQ the split by one million rows to few table then load to the sheets
or use
keeprows.png
use first option then second or second option each time

I forgot to say: remove duplicates first

**IknowNOt** · 07-04-2023, 06:42 AM

yes !! thank you a lot!!!

**sandy666** · 07-04-2023, 06:50 AM

You are welcome

**sandy666** · 07-04-2023, 07:39 AM

After load your txt file to PQ you can use Table.Split(Source, 1000000) then on each table in the list right click and create new query (if you know what are you doing

)

**IknowNOt** · 07-04-2023, 08:32 AM

That is even better!!

**sandy666** · 07-04-2023, 08:40 AM

less work only

usage is the same, the advantage is that you have all the tables at once

out of curiosity: what kind of data you have in these two columns?

**IknowNOt** · 07-04-2023, 08:59 AM

barcode of item and 2nd column code of the same item.

**sandy666** · 07-04-2023, 09:04 AM

maybe try to group by column (choose one) will be less rows
hard to say something without knowing what is there exist

**IknowNOt** · 07-04-2023, 09:06 AM

the data is 3gb! that is why I can't upload here... I am sure if you saw the data, it would be easier for you to manipulate and tell me how to go around it. But the solution that you gave is perfect as well!

**sandy666** · 07-04-2023, 09:13 AM

I'm glad it works for you
good luck

Too many rows

Re: Too many rows

Re: Too many rows

Re: Too many rows

Re: Too many rows

Re: Too many rows

Re: Too many rows

Re: Too many rows

Re: Too many rows

Re: Too many rows

Re: Too many rows

Re: Too many rows

Re: Too many rows

Re: Too many rows

Re: Too many rows

Re: Too many rows

Re: Too many rows

Re: Too many rows

Re: Too many rows

Re: Too many rows

Re: Too many rows

Re: Too many rows

Re: Too many rows

Re: Too many rows

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] List consecutive hidden rows as range of rows vs single rows, in a listbox

[SOLVED] Delete blank rows between data rows, shift rows up, then repeat

[SOLVED] 30K Rows: How Do I Identify Duplicate Rows (across 5 Columns) and Delete BOTH Rows?

Bookmarks

Bookmarks

Posting Permissions