Alteryx Designer trims characters when saved to Excel xlsx

[jzamora]

Alteryx Admin Designer
Version: 2021.1.4.26400

I'm outputting data to Excel as xlsx and not all of the characters are being written to Excel.

I have the following text:

"Consumo "

"CONSUMO"

"Consumo"

And Excel is writing:

"Consumo"

"CONSUMO"

"Consumo"

It is removing the trailing space in the first text.

View result in Excel

[Maskell_Rascal]

Hi @jzamora 

So this is a weird quirk with Alteryx when it writes to an Excel file or table. You can get around it by replacing the standard spaces in your strings with non-breaking spaces ASC#160. In Alteryx the command would be CharFromInt(160). These new spaces created won't be removed from your output. 

You can see from the example above that Field1 has a trailing space in Row 2 and a length of 8. Field2 shows the same length, but doesn't have a warning about trailing space since it was replaced with the ASC#160 character space. 

Attached is a workflow for you to try out. 

If this solves the problem please mark answer as correct, if not let me know!

Cheers!

Phil

[apathetichell]

Or use render...

@Maskell_Rascal- Awesome little trick!

 

[DawnDuong]

hi @jzamora 

If you do not need to retain Excel-specific feature, you can write to .csv file, and the spaces will be kept.

Dawn.

[jzamora]

Thanks, but I need to output to a specific Excel Sheet. And Render does not allow this. @apathetichell 

[jzamora]

Unfortunately the alternative provided does not allow to join back to the original file since:

"Consumo " is not the same as "Consumo"+CharFromInt(160), so both texts are not equivalent @Maskell_Rascal 

[Maskell_Rascal]

@jzamora Can you explain more what you are trying to accomplish?

[jzamora]

I need to output each original distinct value to Excel, so a business user can then complete in Excel the correct value if there is one so I can then replace in the original file. So I need the values as is, some values don't have trailing spaces and need to be replaced by a new value, so its not only corrected trailing spaces.

So far what has worked is the render but I lose the functionality to write to a specific Excel Sheet. Saving to a csv is the best option so far ( @DawnDuong ), because I can make a data connection to a sheet and refresh the file in a specific location

Thanks for the follow-up @Maskell_Rascal 

[apathetichell]

Here's info on writing to sheets in render - note I do believe render will overwrite your file though so you can have a specific sheet name but your file will be rendered anew each time...

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Naming-Tabs-in-an-Excel-Doc-Using-Rend...

[Maskell_Rascal]

@apathetichell is correct about the Render Tool. While this is a good solution, it will overwrite your entire file each time it is ran. So if there are additional sheets in the file that are not part of your outputs, those sheets would disappear. 

The only other option I can think of for this would be to use my original solution, and create a simple vba macro that finds/replaces all ASC#160 spaces with regular ones. Something like this would work:

Sub ReplaceChar160()
    For Each Worksheet In Worksheets
    Worksheet.Cells.Replace Chr(160), " "
    Next
End Sub

You can create a simple vbs script to open your Excel file and run the macro after the Alteryx workflow is finished running. Alteryx can even be used to run the script for you. The process can get pretty complicated, so unless you foresee the need to do this in the future, I'd probably just find another workaround.