Forum Discussion
How to open an Excel with more than 30 millions lines ?
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.
This is the last things I can see before the 4 hours load. Can you juste help me about what should I do when im here to create the sub-sets and do the cleaning etc ?? Or maybe do you have good sources for me to learn that ? Video, etc... ?
Thank you so much
