Convert string to Double error

[kaushik_birmiwal]

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: 

Field1	Field2	Field3	Field4
1	2	abc
	1	xyz	c
1	2		ckjdschdsac
0	1	0
1	1	dadiasj
	1		clksahcjacsa
1
1	1		1000
2	1	csaiciodc	casjhcsa
1	1		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 solution]

@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 solution]

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

[kaushik_birmiwal]

@skeen503979 The solution worked well for me. Since I wanted to change data type of few columns, I segregated the required columns using Select tool and then use Join tool to consolidate the data back.  

@estherb47 your solution also worked, but the @skeen503979's solution worked better since there were were just too many columns in my data.

Thanks!!