Forum Discussion
How to open an Excel with more than 30 millions lines ?
Hi everyone, (im Mac user)
I've upload an .csv file with is 6GO. There is over 30 millions lines and more than 40 column but I've just learn that excel can not open a file who is over 1 million line. Someone have a solution for me ?
Sorry for my English, im French ahah. Ive set up my microsoft office into beta channel to use Power Query, but that didn't change anything, except if I miss use it 🙂
Thanks for your help!
Julxoo Yes, Power Query will be the tool to use. On a Mac, though, that will be a challenge if you have never used it before. Although, Excel for the Mac (Beta) has full functionality when it comes to connecting to a CSV file, the look and feel is different from what you find in on-line tutorials based on the Windows version.
When you say, "but that didn't change anything", do you mean that you don't see the Power Query button in Data ribbon? If not, you may have to wait until your beta version gets it. New features aren't rolled out to Insiders/Beta users all at once.
- First of all, thanks for your answer.
When I say "that didn't change anything", this is the following step I do :
I click on "Get Data(Power Query)" > Text/CSV > Browse.
Then I reach m file, I click on Get Data > Next > Load.
When they ask me "data type selection", I click on "all data" and finally Load.
That take 4 hours to load, but then, they only show me 1'050'000 lines and not all! this is where im block...Julxoo But, when you have so many rows you should NOT load the data into Excel as you will still have the 1 million row limit. Just connect to CSV. Do the necessary clean-up, transformations, groupings etc. and then create sub-sets of relevant data to be analyzed that do not exceed the 1 million row limit.
Having said that, you would actually need to use the Data Model (a.k.a. Power Pivot/DAX) or Power BI to deal with such enormous data sets. All of these are, unfortunately, NOT supported on the Mac (yet). Get your hands on a powerful PC or install a virtual Windows machine with Excel/Power BI on your Mac (Bootcamp or Parallels). Can't guarantee peak performance though.
