Problems reading/writing Excel files on Z-drive with Alteryx 2020.4

[clmc9601]

Hi Alteryx community,

I’m getting several different errors when reading and writing excel files in Alteryx 2020.4. I never had these problems prior to 2020.4. I’m using a virtual machine running Windows (owned by my organization) to access Alteryx on a Mac laptop, so many of the files I reference are located on a Z-drive. Z-drive is how the virtual machine accesses files stored on my Mac’s hard drive. Files stored on the virtual machine’s C-drive don’t have these same problems, but given that any files on the C-drive are erased each time I log off, I prefer not to use that method. I’m not familiar with the differences between C- and Z-drives or why that would be problematic. I’m very grateful for any insights!

I’ve been generating sample data from weekly challenge 38 to test these problems, so I have attached my workflow at the end. I listed the problems below in order of priority.

1. Must input all Z-drive xlsx files as “legacy xlsx” or else error (31)

Does anyone know why this might be happening or what the difference between regular xlsx and legacy xlsx is (especially since they have the same file extension)?

C-drive xlsx files read in normally with no errors and no legacy needed. Xls, csv, yxdb files do not have this problem.

Here is the error I get before changing the input file type to legacy. The file is not open on my mac or virtual machine. It gives me this error even when I have never opened the file on my computer or create a brand new file. As soon as I change it to "legacy," the error disappears.

Error message when reading xlsx file from Z:\

2. Alteryx will not write any xlsx to Z-drive (and xls is very slow)

I’m struggling to address these errors since they are slightly different each time.

Writing as a regular xlsx file results in the same error as reading a regular xlsx file. When I change it to legacy, I get a different error. Alteryx will write .xls files to the Z-drive but extremely slowly.

Here is the regular xlsx error-- it's the same as trying to read a .xlsx file.

Here is the legacy xlsx error:

One file from each group will usually show up on my Mac where it was supposed to write them, and here is the error that results from attempting to open it. It's probably corrupted in some way since Alteryx never finished writing the files.

The same errors occurred when I switched and only attempted to write one file at a time. I had it previously set to write multiple files taking filename from field. 

Xls will write multiple files to Z-drive but very slowly. It took 50 seconds whereas for csv it took 8 seconds.

3. Sometimes strings >255 are truncated without warning when reading Z-drive xlsx as legacy

I have not been able to replicate this problem with sample data. I have a personal file it consistently happens with, which I have duplicated and altered and it still truncates. When I isolated the longest strings into a separate file, it no longer truncated them. I’m not talking about the red triangle in the corner in the results window. The string is actually truncated— when I attach a browse and view the cell, the last few characters are just gone, and there was no warning. I used this same file during 2020.2 and did not have the truncation problem. I wonder if it has to do with the average string length per field. In my original file, out of 99 rows, the average string length was 68 whereas the two problem strings had lengths of 259 (before they were truncated to 255).

On my problem file, it gave each string 255 characters, which wasn't enough.

4. Cannot read in multiple Z-drive files with wildcard

I tried using the wildcard character with xlsx, xls, and csv files on my Z-drive, and all of them fail. I can read in a single file successfully then change part of the filename to * and it will fail. C-drive files work normally with the wildcard.

 

Below is the workflow I used to generate the sample data. Thank you for your time! I really appreciate any insights you have!

[BrandonB]

@clmc9601 a C drive is generally referencing the physical hard drive on a machine. Other drives like a Z drive, N drive, S drive, etc, are normally referring to mapped network drives. These letters are like an alias or name of sorts that is kind of like a shortcut to an actual location. I would highly suggest using the full UNC path. Luckily there is an easy way to do this. You can update all of your paths in a workflow to UNC by going to Options->Advanced Options-> Workflow Dependencies and clicking "All UNC". It should change the paths that say Z to the full location and may fix your issue. 

You can see this being discussed in the following link: https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Scheduled-Workflow-not-able-to-read-fi...

[clmc9601]

Thank you for your reply, @BrandonB!

Unfortunately, I get the same error messages for reading and writing even when using the UNC path.

error reading wildcard

error writing xlsx legacy

(I know it says .csv but I overwrote the entire path so it did end up .xlsx)

[BrandonB]

Rather than modifying via the options, can you go through the drop down menu process? I have found that this is the best way to ensure the settings are correct and that you choose xlsx in the pop up window. 

[smoskowitz]

Is the huge gap in the path typical? I would think that \\TSCLIENT\                                                                \\test.csv would be an issue (unless you hid it where I would have to tell you well done!)

Otherwise, do you have access to that share?

Thanks,

Seth

[clmc9601]

When I put an unconfigured input data tool on the canvas and follow the drop down menus to input an excel file, I get stuck pretty early on. It's the same error message displayed in full in the pictures above. All the options grey out and I have to click cancel, then go back and configure it to legacy add the sheet query separately. There isn't even an option for legacy xlsx in the first input data options. Is this the drop down menu you meant, @BrandonB?

 

input data file type options

Yes, @smoskowitz, I just hid the rest of the filepath in those pictures. It normally shows the entire path. 🙂 What "share" are you referencing?

Thank you both again for your suggestions! I really appreciate it.