Run Command (batch script) to Open Excel File (.xlsx), Save file, Close Excel

[taxguy33]

Hello,

I am running into issues reading .xlsx files automatically using an Input Tool (the tabs are always going to be named "Sheet1" - because the file is saved to a network drive by a UiPath robot with the results of a robot's run in a format with like 12 columns). However, when I try to drag one of the completed .xlsx files to my Designer canvas without opening the Excel file and hitting save - I get the below error "The Excel archive is corrupted: shared strings root=x:sst".

I saw some discussion about that sometimes when files are saved down they are missing some sort of "XML" data that allows Alteryx' s input tools from reading in the Sheet data without manual intervention.

Because of this limitation (open to if anyone has any idea on how to fix this issue), I am wondering if a workaround is writing some sort of Batch script that can be fed through the Run Command tool in Alteryx that would open up the file (knowing it has Sheet1 in there, but not visible to Alteryx), save the file as is, then close Excel.  After this Run Command is complete - I can then actually try to read in the results of those files into a Dynamic input tool or an Input Tool with 0 issues. 

I don't want to have to go out to these Excel files each day and open / save/ close them manually when Alteryx has to be able to complete this somehow.

Thanks,

@taxguy33 

[oly]

Hi @taxguy33 ,

you can try RunCommand. The problem you might be able to open Excel through CMD command but you can't control Excel functions.

1. Maybe UiPath can save CSV file that you will have no problems reading (easiest technically but you might have other people involved to solve)

2. You can look into independent tools to convert XLSX to CSV, you would be able to use them inside RunCommand then.

Cygwin https://answers.microsoft.com/en-us/msoffice/forum/all/how-to-convert-xls-to-csv-from-with-th...

or https://www.softinterface.com/Convert-XLS/Features/Convert-XLS-To-CSV.htm 

In general, try to separate network and file format issues to narrow down the problem.

Best of luck, 

Oly

[IraWatt]

Hey @taxguy33,

Instead of running a batch command you can use the python tool and open and save excel files that way. I've attached an example workflow below.

All the best,

Ira

[edwin_melo]

Hi @taxguy33,

Have you tried running your UIPath robot in Alteryx?

https://www.alteryx.com/-/media/project/alteryx/pdfs/files/alteryxuipath-solution-briefv2-final.pdf?... 

[EW]

I got the same "The Excel archive is corrupted: shared strings root=x:sst" error on a bunch of XLSX spreadsheets, not sure exactly why.  I didn't have time to do a lot of troubleshooting or exploring in this case.  I wrote a simple VBA macro to open each file in a folder, and resave.  That seems to have fixed whatever issue was causing Alteryx not to like the files.  I know it's not the cleanest Alteryx-y solution, but it was quick in my case!  

I'm not sure if it's related or not, but I did just recently update to 2022.1 and there've been a few weird things happening for me.  

[flying008]

Hi, @EW 

Can you upload 1 xlsx file template that can't be input normally of  Alteryx? Maybe we can analyze the internal structure of the file to find the cause and solution.

[EW]

Unfortunately I don't think I can.  The spreadsheets are proprietary data I don't think I'm allowed to share, and I can't replicate the issue in other files or remove content and still get the same error.