Read an excel from Sharepoint onedrive location using input data tool

[annamalaimoorthy]

Hi All, 

I am trying to read an excel file that I have in my sharepoint on Onedrive. 

The link is like below

https://company.sharepoint.com/:x:/r/personal/my_name/Documents/A.xlsx?d=w0669a13420274b19b48bf2b75feea73c&csf=1&web=1&e=2SgYtt

When I paste this link in the web browser the file downloads automatically. 

Can someone guide on how to modify this link and use it in Input Data tool to read that excel file. 

Thanks

Annamalai

[DawnDuong]

hi @annamalaimoorthy 

This was addressed by @T_Willins in this thread 

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Connect-Alteryx-Designer-to-Excel-in-S...

Need to make sure that you are signed in to One Drive for it to work.

Dawn.

[annamalaimoorthy]

@DawnDuong , 

Thanks for your help. It works when I run it in my local designer. 

But if I publish it to the server or schedule it , it is again giving me the same error. Please find the attached error message. 

Can you advise hoe to overcome this 

Thanks

Annamalai 

[DawnDuong]

hi @annamalaimoorthy 

I looks to me that the issue is the file access path currently points to "personal" folder.

Can you use another laptop (or ask your colleague) to type the URL and see if the file can be accessed with the URL? If the answer is no, then the setting is that the "asset" that needs to be included for the work flow to work cannot be accessed remotely.

Depending on the use case, in the past I have seen a couple of method that works:

1) save inputs, outputs, and workflows on your local folder with meaningful sub-directory structure and save as relative path. Then upload the entire analysis folder to sharepoint.

2) export the workflow to include all necessary assets before publish. Note that this way the input files will be included - make sure that this is allowed.

3) If the data is not to be shared (or everyone has their own data of exactly the same format), you can publish the app in gallery and let the users select their own file.

Dawn.

[annamalaimoorthy]

Hi @DawnDuong ,

If I grant access to my colleague for the file and share the URL with her, her is able to see the file. 

If I package the assets at the time of publish, then the relative path will not be set. Tomorrow when the file in the SharePoint is modified the workflow will not picking up the new file from the SharePoint location. 

Kindly suggest. 

Thanks

Anna

[DawnDuong]

Hi @annamalaimoorthy 

Sorry I missed your question. Am on leave now and do not check the community everyday 🙂

I mentioned 3 methods in the earlier reply. If you need to maintain the live linkage to the file, I suggest that you use the first method:

1) Save inputs, outputs, and workflows on your local folder with meaningful sub-directory structure and save as relative path. Then upload the entire analysis folder to sharepoint.

This way, as long as the required input file(s) is(are) updated, then the workflow will pull from the most update data. Do note that this way, anyone who has access to that particular folder can access all your input, output and workflow flows. 

More about relative path is discussed here:https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/How-To-Use-Relative-Paths/ta-p/1160

If you do you have access to private server, do give method 3 (using the analytic app) a try.

Cheers,

Dawn.