Shared Excel connect to shared CSV?

Off Topic
1

I am not trying to link to Epicor - this is a straight Excel question.

I want to link a CSV file to Excel as a data connection only. The CSV file is 200MB and about 1.2 million rows, so it can’t be actually in Excel. It works fine on my desktop.

But now I put the two files (Excel and CSV) on OneDrive. (I could put it in SharePoint if it helps; I think it’s basically the same idea, right?)

I made a shareable link to the file. But if I try to use that as the data source in Excel, it gives an error.

  • If I use the wizard it says “You can’t open this location using this program…”
  • If I try to edit it in PowerQuery, it says “Access to the resource is forbidden.”

Any thoughts?

2

Is a local copy kept on your PC? I know that Google Drive can let you choose whether to keep the file on your device, or make it “on demand” (downloading it only when needed, then deleting the download after done with it) - or at least it used to.

Does “One Drive” need to be added in Excel’s Trust Center?

edit

And just because I’m curious… Does the problem exist if just one of the files is on OneDrive?

3

Yes, and it works if I reference that path (which is C:/something, of course).

But I want this to be available to co-workers, so that won’t work for them, right?

4

One system I use with E10 has our “My Documents” use our OneDrive. And when I try to use the recent files list in Report Builder, like:

image
image869×143 9.58 KB

I get:

image

That path and file name is only 134 chars.

5

And I don’t expect the C:/ path to work, but why not a shared path like this?

image

I mean, I’m totally cool if someone says, “This is a stupid way to do this, Jason. Why don’t you just use…”

I just thought this would be pretty straightforward. Link a OneDrive file to a OneDrive file…

6

If the IP address of the URL of that link is outside your LAN, that might be an issue. Some firewalls (the box at your companies internet connection) are setup to not allow references to the outside address from behind the firewall.

For example, Say my company’s website (say meepmeep.com) was hosted on our servers - which are “inside” our firewalls perimiter - and DNS for meepmeep.com resolves to 23.34.56.67. I couldn’t access our website using meepmeep.com, nor the IP 23.34.56.67. I’d have to know the internal IP Add of the server hosting our website.

7

In Excel I am able to use Get Data - Launch Power Query Editor and add a new source - Web.

Set it up for Organizational Account credentials with my O365 username and password.

Then put the link to the CSV from my OneDrive account into the web url and it pulls the csv data right into Excel. It sounds like the error you are getting with Power Query is because you aren’t using the Organizational Account.

8

Sorry, I’m not understanding this part. I don’t know where to set up credentials… you mean in Excel/PQ, or in OneDrive?

Must be the key step, since I did the rest and am still denied.

image
image707×463 12.6 KB

9

Thanks to @John_Mitchell I think we determined my issue is getting around 2FA.

Time to explore other options.