I have a really large (>1M rows) 70 MB CSV file that cannot be open in Excel due to it's size.
I'm trying to use new CDS Get Data feature to import via Power Query. The file is stored in my OneDrive for Business.
No matter what I tried (anonymous or corporate account), I keep getting credential error.
Any tip? Is the file too big? Perhaps, it doesn't work with brand new CDS for App entities yet?
Is the path problematic? It's a copied link from the "share" menu
You have to set-up on-premise gateway
But if its a 70mb file may be there might be an issue even if it runs.
Hi @VFXPro1 - it definitely won't work using 'Anonymous' so you need to use account credentials for the account where the file is stored in OneDrive for Business.
The file path could also be an issue. In my experience, it needs to end in the file extension type e.g. ".csv"
Give those a try and let us know how you get on.
Perhaps, this might sound stupid, but how do I get a path that ends with ".csv" when the link provided by OneDrive for Business is just an ID??
Also, since the file resides in O365, do I still need a data gateway (I didn't think so).
@VFXPro1 - you need to get the file URL by selecting the file and clicking the "Copy link" button. This should give you something like this:
And you will need to delete anything after the file extension, so for the above URL remove "?csf=1&e=4eMMLy" that appears after ".txt."
And you're right, if your OneDrive for Business is in O365 cloud-hosted no gateway will be required.
Great tip @lewishm, the copy link does provide the name of the file.
Sadly, I'm still getting an error... See the screenshot. The CSV file is 70 MB and has a little more than 1M row, I'll try with a smaller file just to test my theory. I wish the error message would provide more value.
@VFXPro1 - can you try opening the CSV file in Excel Desktop on your PC, and get the link using the method described in this article : https://docs.microsoft.com/en-us/power-bi/desktop-use-onedrive-business-links and let me know if that works.
Thanks for your assistance. Sadly, if the CSV file is larger than 1M rows, then it won't open in Excel, thus the suggestion won't work as described.
Check out new user group experience and if you are a leader please create your group
Did you know that you can visit the Power Query Forum in Power BI and now Power Apps
Participate in the Power Virtual Agents Community Challenge
Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.