Preserving Alteryx data type when output to excel

Question

Hi Everyone,

I'm having trouble getting my Alteryx output to maintain the Alteryx data types when the output is opened in excel.  I've tried numerous - outputting to excel directly, outputting to an excel template that has been formatted to match the data types in Alteryx (I've done this both directly and through the Blob tools).  The closest I've come is by choosing CSV in the Alteryx ouput tool.  However, excel is still changing fields designated as V-String in Alteryx into numbers if the field contains all numbers and excel is also removing trailing spaces that I actually need to stay intact.

Any help would be appreciated!

cjaneczko · Answer

The other thing you can try is a Formula or Multi-Field formula tool to replace the standard space with the non-breaking space. I don't know how this will impact how excel sees the space in your instance.

Replace([Field 1], " ", CharFromInt(160))

cristiane_dsc · Answer

Can you share a mock sample of the data you're using and your workflow? I've tried to replicate the situation, but in my case Excel kept the original data format,  not even after removing the spaces in between the numbers. The data was still text after saving to Excel. Leading and trailing spaces were removed, though. Here is what I've used as a test:

NameAgePhone NumberClara2344 5 7894 2345   Nancy4231 5 6789 0999  Peter4512 4 7345 6678 John56   55 6 9278 9887Florence32  55 8 0292 8876Christpher33 76 9 0987 6566Bruno2198 9 7356 8887   Jack6055 6 9284 6665  Barbara3344 5 7364 2234 Karen40   98 6 8367 7836

The third column is the one with V_String data and trailing and leading spaces.

annlago · Answer

Thank you for all of your quick responses!  I had previously tried adjusting the data settings in excel but it wasn't accomplishing what I was looking to do, great suggestion though.  I added an apostrophe to all of the text fields using the Multi-Field formula tool and reran the workflow.  When I opened excel the apostrophe was visible which I wasn't expecting (I'm used to only seeing the apostrophe in the formula bar when it's used to have excel treat the entry as text).  It also still removed the trailing spaces.  The workflow I'm building is part of a process where the output from the workflow is ingested into another application that requires an excel spreadsheet and is very inflexible about data types and is also validating against data that contains trailing spaces, it's proven to be a bit challenging for certain things.

Thanks again, I really appreciate the help and suggestions.