nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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?

Batch Macro

Sharepoint

Accepted answers

aatalai

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

aatalai

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

Gaurav_Dhama_

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.

All comments

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

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

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

Alter_Shivi

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

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

Gaurav_Dhama_

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.

Quick Links

Popular Tags

Child Item

This months top contributors

atcodedog05 19598

Qiu 15958

binu_acs 15783

MarqueeCrew 13710

apathetichell 13703