Convert string to Double error

Question

Hi All,

I have data in some 34,000 columns. 99% of these columns have numeric data but are converted as string while creating an output file. For this I have used "tonumber([_CurrentField_],1,1)" in Multi-field tool and checked "Change Output type" to Double.

However, some of the columns have string data which is also important, and I would like them to continue to provide the string output.

Specimen data is as below:

Field1Field2Field3Field412abc  1xyzc12 ckjdschdsac010 11dadiasj  1 clksahcjacsa1   11 100021csaiciodccasjhcsa11 ckcjs

I would all numbers to get converted to number format, while columns with text inputs should output the exact same text. Since the number of columns is very large, its almost impossible to manually identify such columns.

Has anyone encountered such a problem, or can help me solve this?

Thanks,
Kaushik

skeen503979 · Accepted Answer

@kaushik_birmiwal

That's an impressive column count.  Have you tried using the Multi-Field Formula tool?  It looks like you should be able to use it to select only numeric fields and change the output type to whatever you're wanting.  This way you wouldn't have to manually click each one in the Select tool (which obviously is unreasonable in your situation).  Also, the Auto Field tool may be of some use to you as well.

estherb47 · Accepted Answer

Hi @kaushik_birmiwal

Each column can contain only one data type, so if there are mixed numbers and string, they will be stored as string.

You could create new columns with the Multi-Field formula tool, though that would be a tremendous number of columns to handle.

Let me know if that helps, or if I can further clarify

Cheers!

Esther