Oct 21 2022 05:20 AM
Oct 21 2022 05:20 AM
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!
Oct 21 2022 05:38 AM
@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.
Oct 21 2022 05:45 AM
Oct 21 2022 05:57 AM
@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.
Oct 21 2022 06:04 AM
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
Oct 21 2022 06:26 AM
@Julxoo Here is where I took my first steps into PQ, running Windows and Excel on my Mac via Parallels.
Chapter 5 deals with Close & Load options that you have on a PC. The Data Model is not available on the Mac.
And, now that I think of it, PQ on my Mac (Insider Bata) still defaults to loading every query back to Excel. It doesn't allow me to choose "Connection only", although I read that it has been introduced.
It's exciting to see that PQ has finally come to the Mac. But, as said, it's probably not yet a suitable platform to handle such massive data sets. Unless I've missed a recent development, of course.
Oct 21 2022 06:35 AM