How do I have a single column with more than one data type?

[SergeantJack]

Thank you for taking the time to help.  I have a workflow that writes a single column of data into a worksheet of an existing macro-enabled Excel file.  The data consists of survey questions, with both numerical and text data.  My problem is that Alteryx seems to be able to support one data type per data column, so it's writing every cell as a V_string.  When written to the Excel file, this breaks the macros that point to cells that are supposed to be in a numeric format.

Can anybody give me some guidance?  My instinct is that this problem needs to be addressed within the Alteryx workflow, and fixed before it's written to Excel, but I don't know how to fix it.

[csmith11]

Top of my Head Answers:

Feasible Solution:

Its a Macro Enable Workbook. Is it your file or is it something you receive from an upstream process?

Can you write a Macro Script to Convert these values back to Number?

Another possible solution:

Are the Cells that you are writing to in a fixed position? 

For example, are the string values in the first 10 rows, and all the numeric values in the next 10 rows? Or is it a mix bag of both?

If the results are fixed positions you may be able to write to each position separately.

I'll look into this a little more and see if there's another solution that might help.

[csmith11]

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Is-multiple-formats-possible-in-a-sing...

Unfortunately, Alteryx said its not possible. 

So the best solution I can recommend is either a VBA script that Alteryx Kicks off for you.

Or Leverage a python package that can Change the Excel File after it has already been written. 

[Samanthaj_hughes]

I recommend this too @csmith11 . Another way is also writing data into a back sheet that is looked up in a way using formulas on your specific sheet. Depending on the size of your data

[csmith11]

Here is a sample workflow using a Python Script that can help you fix your numeric fields in Excel:

#Please note the sample workflow is configured for column A only. You can modify the script as needed.

#I'm working to convert this to a simple Dynamic Macro

[csmith11]

I built a quick macro that should be easy to deploy. You'll need to pass the full output file path with the sheet name into the Macro as a field.

Let me know if you are able to deploy it successfully on your end.