Preserving Alteryx data type when output to excel

[annlago]

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!

[Bren_Spill2]

I'm sure there's a better approach than this but, using a formula tool, try adding a single quote (') to the beginning of the field that you want to maintain the formatting for. That should force it to stay as vstring and keep the white space once it hits excel.

Formula = " ' "+[stringfield] 

If you can attach your workflow may be able to give a better answer. 

[OTrieger]

@annlago 

This is relating to Excel and not to Alteryx. You might write out numbers and then Excel will convert them into dates etc.

If you will write the data out to csv format and then will open the output with Notepad you will have all the data in the correct format. So Alteryx doing everything as intended.

If you will need to use excel after that the data is been output, then you can cheat excel by adding an apostrophe (') to the data and then excel will regard all the data as text and will be as is.

[cjaneczko]

As @OTrieger mentioned this isnt Alteryx, this is Excel. If you dont want to have the data types altered you can edit the settings in Excel to prevent the data from being converted to numbers or the trailing spaces from being removed. Take a look at your settings in excel as shown below.

[annlago]

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. 

[cristiane_dsc]

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:

Name	Age	Phone Number
Clara	23	44 5 7894 2345
Nancy	42	31 5 6789 0999
Peter	45	12 4 7345 6678
John	56	55 6 9278 9887
Florence	32	55 8 0292 8876
Christpher	33	76 9 0987 6566
Bruno	21	98 9 7356 8887
Jack	60	55 6 9284 6665
Barbara	33	44 5 7364 2234
Karen	40	98 6 8367 7836

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

[cjaneczko]

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))