Data Science CSV file with 43 millions rows, how to open?

Hi Mad380

Excel has two built-in but separate grid systems. The grid everyone sees when they open Excel has 1,048,576 rows and 16,384 columns. This grid supports all of the built-in Excel formulas, data connections, links, etc.

There is a separate grid that is 1.5 billion rows by 2 billion columns. This is called the Data Model.

Your data won't fit into the standard grid, but will fit into the data model.

Rohnski2 explained how to add data to the Data Model, but the steps provided work only in Excel for Windows.

Unfortunately, you can not add data to the Data Model grid in Excel for Mac. On Mac, you can not do anything with data that is within a Data Model that was generated by Excel for Windows. If there is a Data Model grid that was made in a Excel for Windows and you open such a workbook on Mac, no damage will be done to the Data Model, but the Data Model is completely unusable on Mac. PivotTables based on the Data Model will not be refreshable or editable. PivotCharts based on the Data Model are not supported at all.

Right now, your only Excel options for working with a large data set are to use Excel for Windows (you can run Excel for Windows in Parallels desktop), or use a query to whittle the data down to a result set that fits onto the smaller regular grid.

You can let Microsoft know that this arrangement is not suiting you by using the Help menu in Excel and choosing a Feedback option.

PowerQuery is the right place to be looking to handle data this large.

But there is one more tool you need to make it work, Data Manager.

Yes, you use PowerQuery to connect to the file to import it, BUT in addition, you only LOAD the data into Data Manager. Unfortunately I don't know if Data manager is included with Excel Mac. You'll have to look or ask someone else to look.

.

This article tells how to do in in Windows, it doesn't mention Excel Mac

https://support.microsoft.com/en-us/office/create-load-or-edit-a-query-in-excel-power-query-ca69e0f0-3db1-4493-900c-6279bef08df4 

Specifically look in the "Load to" Section.

.

The 2 elements you need for this to work is turn on "Only Create Connection" and

"Add this data to the Data Model"

As you already know, Excel is limited to 1million rows. Your data is substantially bigger.

But data manager employs data compression, so I've read of examples where it has been used to import 100's of millions of data rows.

.

After the data is in Data manager you can access it from Excel or PivotTables.

Good luck.

I would be curious if anyone used Excel's Power query with that much data...

Click: Power Query Specifications

( from what little I understand of Gravitational waves, my guess is that Excel may not be the best tool to use )

Dear,

Thank you for posting in Microsoft Community.

I understand that you want to know how to open big CSV file in Excel.

We will check it on our side and get back to you.

Thank you for your kind understanding. Stay safe and healthy always.

Sincerely,

Zee | Microsoft Community Moderator

Re: click: Power Query Specifications

FYI...

When I click, I get:

"uBlock Origin has prevented the following page from loading:"

Nothing Left to Lose