fast way of reading excel files from sharepoint

[Alter_Shivi]

Hi All,

I have around 30+ files to be read from sharepoint. Currently i am using batch macro to read and append the data. However, this is taking like 30 mins to just do this task. I see that sharepoint authenticates for every file here.

Is there a way to reduce this time?

[abacon]

@Alter_Shivi Another option is to use a traditional Input Data tool to read the files in. However, this would mean giving the gallery access to the location on the machine it sits on which may be tricky. But I would imagine that would run it much quicker than using the SharePoint Input tool.

Bacon

[caltang]

I second what @abacon says here. I would sync the access to your local and run it via the input data tool for a faster run. 

Setting up the config on Server helps too.

[aatalai · Accepted solution]

Another way would be to download the files and use the green tools to open it; that can work on the server.

NB the default is file location is %TEMP%/SharePoint, however, it was suggested to use %Engine.TempFilePath% as that can resolve to a different location to where the Alteryx server has been configured to use as its temp drive.

Updating the paths with the batch macro, that could help speed up the proccess

[Alter_Shivi]

Thank you @aatalai. This really helps, especially in future state i plan to place this process in alteryx server. 

Just a follow-up, is there a way to also free up the temp space later once the whole process has run?

[aatalai · Accepted solution]

@Alter_Shivi my understanding is that it will be wiped when the next run starts, however, I could be incorrect

[Gaurav_Dhama_ · Accepted solution]

If you are placing the files in staging layer, it will get wiped once your workflow finishes running. I generally keep the temp files in staging layer.

[Alter_Shivi]

@aatalai and @Gaurav_Dhama_ , Your suggestion definitely saved me alot of time in my workflow, but apart from reading different files, i have multiple inputs which read multiple files from different folders of same SharePoint.So basically multiple sharepoint input tools in a workflow which read from same sharepoint, and i am getting this error "

SharePoint Input (183) API response: Error code 503: The server cannot process the request due to a high load." Do you know what can i do in such case?

[Gaurav_Dhama_]

That might be because a lot of SharePoint inputs are running at same time, If you are on Alteryx version 23.1 + then you can use the control container to sequence these inputs. This will prevent all of them running at the same time.

Alternatively, if you do not have that, put those SharePoint inputs inside a multiple standard macro, and add a dummy input and SharePoint output, now add these macros in sequence. This will work as control flow as well.

So, if you are using 9 SP inputs, you can put 3 each in one standard macro, thus you will have 3 standard macros running.

Just in case you are using amp, make sure to append your dummy input to your SharePoint output, then drop it using select tool.