Forum Discussion
More than 1,048,576 rows
I have been sent a 4.4GB Excel .CSV file. It has 16columns, but several million rows. When I try to open it, it only opens the first 1,048,576 rows. Is there a way to open the whole file? I don't mind it opening across multiple worksheets, but
8 Replies
- Split the CSV file and import it into separate worksheets or combine queries using Power Query to manage large datasets in Excel, but be cautious of potential performance issues.
Consider using databases or specialized tools for handling big data if needed. To my knowledge - no. You may split csv on several files and open them one by one. Or import into the Excel using legacy wizard, it has an option from which row to start importing. Something like this.
Could you please provide me steps with screenshots for more clarity?SergeiBaklan
Sorry, I didn't catch, steps for what, how to load csv file into Excel data model?
SergeiBaklan How do you think the author saved an excel file of this size?
Author saved not an Excel file, author saved "comma separated values" file which could be opened in Excel and on most systems Excel is set as default application to open such kind of files.
Using of csv files is very usual for the exchanging of information between different information systems. If you in your online bank will ask for the transaction in some period, most probably csv will be within the options how to provide such information (on screen, csv file, pdf file, etc). Believe me, such csv files are generating not by Excel.
You may generate large csv file yourself manually - save about million of rows from excel as csv, open such csv in Notepad, copy and add or add manually another half millions of rows or so, close the file. Now try to open in Excel - it will show only first million. Try to open in Notepad++ , it will show entire file.
Yes, exactly, that could add some logic for splitting rather than simple batch file which splits based on number of rows.
And perhaps it's not necessary to return data back to Excel sheet, load to data model and work with it aggregating data.
