topic Re: Convert String to Double in Data Management Questions

Convert String to Double

TimHayes — Tue, 22 May 2012 18:22:59 GMT

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?

Re: Convert String to Double

TimHopper — Tue, 22 May 2012 18:43:27 GMT

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

Re: Convert String to Double

TimHayes — Thu, 24 May 2012 02:02:41 GMT

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.

Re: Convert String to Double

GoldenJiang — Wed, 06 Jan 2016 03:55:14 GMT

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.

Re: Convert String to Double

MichaelRobb — Wed, 06 Jan 2016 19:08:34 GMT

Alternatively, you can use the "Show Codeblock" in the field calculator and then use

Output = CDbl( [lFIELDNAME])

[Field] = Output

CDbl converts the string to double.

You can then add more items such as Replace as well to remove items such as periods or decimals if need be

e.g.

Output = CDbl( Replace([FIELDNAME],"."," "))

Re: Convert String to Double

MichaelRobb — Wed, 06 Jan 2016 19:32:06 GMT

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.

Re: Convert String to Double

GoldenJiang — Fri, 08 Jan 2016 00:51:08 GMT

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.

Re: Convert String to Double

MichaelRobb — Fri, 08 Jan 2016 02:57:12 GMT

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.

Re: Convert String to Double

GoldenJiang — Sat, 09 Jan 2016 06:57:47 GMT

Thanks Michael,

It seems that there is something wrong with the code:

Please show me how to resolve this problem.

Re: Convert String to Double

MichaelRobb — Sat, 09 Jan 2016 19:54:40 GMT

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.

Re: Convert String to Double

GoldenJiang — Sun, 10 Jan 2016 21:03:20 GMT

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.

Re: Convert String to Double

MichaelRobb — Sun, 10 Jan 2016 21:52:04 GMT

It should work fine if the featurelcass is in a FileGeodatabase. A Shapefile will not work for what you are looking for as I have mentioned.

Id Import the shapefile into a FileGeodatabase and then your desired <null> will work... and so would the script.

Re: Convert String to Double

GoldenJiang — Mon, 11 Jan 2016 02:16:38 GMT

What's the difference if I save a featureclass in a folder or save it in a FileGeodatabase?

I have been saving data to folders all the time. Hope that doesn't affect my yield data.

Re: Convert String to Double

curtvprice — Sun, 29 Dec 2019 15:26:03 GMT

What's the difference if I save a featureclass in a folder or save it in a FileGeodatabase?
I have been saving data to folders all the time. Hope that doesn't affect my yield data.

Datasets saved to a folder from ArcGIS tool output are saved in shapefile (feature classes) and .dbf table format.

Geoprocessing considerations for shapefile output—Help | ArcGIS Desktop

Geoprocessing considerations for shapefile output—Appendices | ArcGIS Desktop (Pro)

Re: Convert String to Double

MichaelRobb2 — Tue, 21 Nov 2023 11:32:33 GMT

Well this is weird. For a minute I thought I had forgotten how to code in the last 7 years, but quickly realised that my name sake had just helped me out, thanks!!