topic Re: problem in loading excel having values in degree minute second in Geoprocessing Questions

problem in loading excel having values in degree minute second

sakshimittal — Mon, 07 Mar 2016 07:30:43 GMT

Hi.. I have an excel in which the values are in degree minute second format. I want to convert all this in decimal degrees by a single tool but the tool display xy data is not highlighting even i can't add a new field in the excel to calculate it again. please tell me what should i do?

Re: problem in loading excel having values in degree minute second

JayantaPoddar — Mon, 07 Mar 2016 08:16:51 GMT

Hi Sakshi,

Could you attach a sample excel sheet of the existing data available with you?

Re: problem in loading excel having values in degree minute second

DanPatterson_Retired — Mon, 07 Mar 2016 09:01:18 GMT

I suspect that your data are not in numeric format. In order for it to be converted, your degrees, minutes and seconds would need to be in separate columns. If they are in one column, then the data need to be parsed in excel and decimal degrees calculated from it

Re: problem in loading excel having values in degree minute second

sakshimittal — Mon, 07 Mar 2016 09:24:17 GMT

Thanks Jayanta.. PFA excel sheet.

Re: problem in loading excel having values in degree minute second

sakshimittal — Mon, 07 Mar 2016 09:29:17 GMT

sir this is the excel.

Re: problem in loading excel having values in degree minute second

JayantaPoddar — Mon, 07 Mar 2016 09:44:26 GMT

I have used an equation in excel to convert DMS to DD. Since the Longitude value is from West, I have introduced the negative sign.

The cursor position of a few values were modified, to extract the correct value.

Please find attachment below.

Re: problem in loading excel having values in degree minute second

NeilAyres — Mon, 07 Mar 2016 09:48:51 GMT

Those dms text strings look well formatted so the convert coordinate notation tool would work on this data.

Re: problem in loading excel having values in degree minute second

sakshimittal — Mon, 07 Mar 2016 09:56:33 GMT

Thanks a lot .. can you please tell me about that equation??

Re: problem in loading excel having values in degree minute second

sakshimittal — Mon, 07 Mar 2016 10:07:45 GMT

I have tried this tool but the output creating blank shape file with two blank columns of lat long.

Re: problem in loading excel having values in degree minute second

JayantaPoddar — Mon, 07 Mar 2016 10:22:53 GMT

Let me consider the DMS value has 02 digits each with the symbol of Deg. Min. and Sec. in between (e.g. 23d45m30sN).

The expression I have used is LAT_dd = VALUE(LEFT(<LAT_DMS>,<No. of characters> ) + MID(<LAT_DMS>,<Cursor Position>,<No. of characters>)/60 +MID (<LAT_DMS>,<Cursor Position>,<No. of characters>)/3600)

LEFT (23d45m30sN, 2) = 23

MID (23d45m30sN, 4, 2) = 45 ----> converted to deg. 45/60

MID(23d45m30sN, 7, 2) = 30 ----> Converted to deg. 30/3600

So the value would be 23+45/60+30/3600 = 23.758333

Ultimately, the VALUE function converts the result of the above expression into numeric.

NOTE: Few values had a different format. I had to change the cursor position/No. of Characters for these values

Re: problem in loading excel having values in degree minute second

NeilAyres — Tue, 08 Mar 2016 06:10:09 GMT

The output from the tool is a feature, not just a table, with the desired coordinates, in whatever format added to the attribute table.

Did you choose the correct input options?

DMS Long / Lat in 2 separate columns?