ALTERYX INSPIRE | Join us this May for for a multi-day virtual analytics + data science experience like no other! Register Now

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Writing to Excel files and sheets dynamically: Error Win32 Error 1175

chrisha
10 - Fireball

Hey,

 

I try to write my data to different excel files and different sheets:Screenshot 2019-01-03 at 18.38.10.png

 

 

In my data there is a column that changes the file name and a different column that changes the sheet name. In a formula tool I write the filename by

 

"Z:\...\Tables_" + [Country] + "_Unrelated_Main.xlsx|||" + [Question Number] + ""

In my Output tool, I have selected "Overwrite Sheet" (problem same for "Create Sheet") and the option to replace the full path by a field:Screenshot 2019-01-03 at 18.40.20.png

When I run the workflow, I sometimes get the error

Output Data (20) Unable to make final write to Z:\...\Tables_BRA_Unrelated_Main.xlsx, Error Win32 Error 1175

 

The error occurs randomly. It's always a different file/sheet Alteryx is trying to write and it occurs only sometimes. I cannot see a logic to when the error is occurring and writing up a toy example won't show you the error message. Z: is a network Windows-share, but the problem also occurs when writing locally.

 

Previous posts on this error have either used different Outputs writing to the same file (not the case here, just one Output tool), recommended to use a Block Until Done tool (already included, but rather useless anyway if I understand correctly how the tool works), or that an update solves the problem. My Alteryx Designer is already at the latest version 2018.4.

 

Since there is a lot of data involved in this project and the workflow takes quite some time to run, it is really annoying if it fails at the last minute.

 

Any idea how to solve this issue? Or is this a yet unreported bug?

 

Best 

Christopher

Thableaus
17 - Castor
17 - Castor

Hey @chrisha,

 

It seems to me this error is something related to File Path. Maybe size, or a rule that's being broken. 

Have you tried different combinations of File Paths?

 

https://docs.microsoft.com/en-us/windows/desktop/fileio/naming-a-file

This article explains some rules for naming a file, I'd try to go this way.

 

Cheers,

chrisha
10 - Fireball

Length and special characters are unlikely to be an issue (no special characters, folder and file names are short enough). Furthermore, if it really is the file path, it should occur consistently, no?

danilang
17 - Castor
17 - Castor

Hi @chrisha

 

Is the data in your output ordered by filename/sheetname?  What you're experiencing could be caused by Alteryx opening file A, writing to it, closing it and then repeating the process with file B and then coming back to file A before the OS has had time to actually close it. From what I can see of your workflow, the last sort is by country, etc.  Making sure that your top level order is by file/sheetname should remove this problem.

 

Dan

 

chrisha
10 - Fireball

@danilang, Good idea! I moved the Sort tool behind the Formula tool and added the filename as the first item in the list of variables to sort by. For writing to a local drive, this seems to have resolved the issue. When writing to a network share, it's still happening.

 

So, if I understand correctly, this is indeed an issue of Alteryx trying to write to a file that is still locked by the writer module. Any other way to circumvent this and making sure Alteryx is internally waiting for the file/sheet to be closed correctly?

danilang
17 - Castor
17 - Castor

@chrisha

 

One thing you could look at is moving the writing component to a batch macro, passing in the list of file names as the control parameter.  Within the macro, use a filter tool that's linked to control parameter. It's my understanding(I may be wrong but hopefully someone can correct me) that a batch macro actually waits for file handles to be released before looping.  Of course, with this method you'll need a file name field and it will be slower than the in-line method because of the batch overhead.  But if it works...

 

Dan

Inactive User
Not applicable

Hi @danilang 

 

I chanced upon this thread and believe I have the same problem (refer to screenshot). This error occurs rather randomly, the sheet names are not sorted as well - do you have any advice on this? Thanks! 

@mutama, fyi!

 

Kenny
Error Message.png

gtaramasso
7 - Meteor

My team is also experiencing this error **intermittently** -- The user tried again 5 minutes later and the error did not occur. 

 

We are on Server version 2018.4. The users are running the workflow via Gallery.

mohit9garg
8 - Asteroid

@danilang @Thableaus 

 

I am also having this same problem intermittently. Is there any solution?

Sleeper
5 - Atom

@mohit9garg I've been having the problem intermittently too. I am writing to a network location. When I temporarily write to the local drive the problem goes away. I think what @danilang mentioned as the potential cause in this thread is correct.

Labels