Convert String to Double

[TimHayes]

In a Feature Class Attribute Table, I have data in a column that is of string data type and need to convert it to a double data type. I have already added an empty column with a double data type on which I can use the Field Calculator.

What is the best way to go about this?

[TimHopper · Accepted solution]

Timothy,

You've got the correct workflow.  As long as the current string field contains only numbers, you can create a new field (type double) and use the field calculator to populate it with the values from your string field.

Your only other option would be to create a new feature class with the proper field schema and load your data into it (using field mapping to make sure your data ends up in the proper fields).

[TimHayes]

Timothy,

You've got the correct workflow.  As long as the current string field contains only numbers, you can create a new field (type double) and use the field calculator to populate it with the values from your string field.

Your only other option would be to create a new feature class with the proper field schema and load your data into it (using field mapping to make sure your data ends up in the proper fields).

Yes, it worked. It has been a few years since I tried this. The problem was that my string column had one empty value/blank in it. I must have missed this when I checked the data before I ran the Field Calc. In order for this to work there can be no blanks in the string field.

[GoldenJiang]

Hi Tim,

Thanks for the answer.

What if my string column contains no only numbers but also blanks (#DIV/0!)?

Is there a way to convert the blanks (#DIV/0!) to 'Null's instead of '0's at the same time?

Cheers.

[MichaelRobb]

If you want anything that is NOT a numeric number in the text field to show up as NULL in the double field, This works:

Id do a check to make sure ISNumeric, if not then output Null.

Dim String, Check

String = [STRINGFIELD]

Check = IsNumeric(String)

If Check = True then

Output = CDbl(String)

else

Output = null

End if

[FIELD] = Output

<null> will be what you see in the double field.

[GoldenJiang]

Thanks Michael,

I am not really familiar with VB Script. I followed your method but it turns out 'failure during processing'.

Please tell me how to resolve this problem.

[MichaelRobb]

hi Golden,

First you need to Check "Show Codeblock"

that entire block of code you have there you put in Block #1 as seen above

then you need to type Output in Block #2.

[GoldenJiang]

Thanks Michael,

It seems that there is something wrong with the code:

Please show me how to resolve this problem.

[MichaelRobb]

Hi Golden.

There is nothing wrong with the code.

The issue here is not understanding data in general and/or the code....

In this case, let me try to provide a 'crash course' on some items.

Null means one thing and is handled differently for SQL and is different for data in a FileGeoDatabase and again different in a dbf standalone and yet again different for xlsx etc...

What does this all mean?  It means, an understanding of data and its storage media is needed.  Below I have made some case examples.

Follow:

Create a table in a FileGeoDatabase.

Catalog >> New .. >> Table  (in the file geodatabase)

Create two fields,  STRINGS - with a TEXT data type and DOUBLES using Double

Add a few sample Strings...

Right click on FIELD  >> Calculate

(Show Codeblock) (VBScript)

Pre-Logic:

Dim String, Check

String = [STRINGS]

Check = IsNumeric(String)

If Check = True then

Output = CDbl(String)

else

Output = null

End if

Doubles=  Output

so should look like this:

Once you run it...

Anything NOT a numeric is now <Null>.

Looks great!  You could from here export to xlsx for example, without issue.

Ok, lets try a Shapefile or dBASE standalone table.

Here it what happens if it runs on a Shapefile:

You can see the results as well, warning that there was an Empty value ( meaning <null>).  We know that this is on the Double field because we understand that <null> is not possible in a shapefile environment. (I wont get into details as to why) but you can look and you will see via any means (SQL manager or ArcCatalog or in .accdb that on a double field, there are options to Allow NULL Values.

So the warning means, empty value was inserted with what is only possible with a double field in a shapefile... a Zero.

we could write more VBScript to alleviate the warning message when it runs (what is called error encapsulation / handling) if we wanted.

Now what I didn't notice when I quickly replied is that it appears that the featureclass is JOINED to an xlsx? (not entirely sure)

in which case, <null> is not supported.

If you are working directly off a xlsx in ArcMap.

Doubles should show <null>

Of course then there is no field calculations...

So then this becomes a question of 'what are you trying to accomplish'.

if you want <null> for a featureclass, then the featureclass needs to be in an Enterprise Geodatabase or a FileGeodatabase (and a few other data container types).

It doesn't end here either, I could keep assuming (which isn't good) (e.g are you calculating a Joined Featureclass to export to xlsx? ) ... so Ill stop here, hopefully this makes sense and works for whatever you are trying to accomplish.

[GoldenJiang]

Great thanks Michael,

I am processing spatial and temporal yield maps. I put the normalized yields (grids) of different years into a number of columns in Excel and calculate the average, coefficient of variance (average/STD) and performance classes. '#DIV/0!' shows up because some data are missing.   

Yes, I imported csv. files with the processed results into ArcGIS but the program won't let me add fields for the csv. files. Then I Joined this file to one of my existing shape files and added a field as the Double type.

I have been modifying the original excel files and changing '#DIV/0!' to '-1' then using definition query to filter these values out. That works fine but I am still interested in coding to solve this problem because it might streamline things a bit.