10

I've converted a shapefile with attributes to a .kml for a client. The .kml and attributes were then edited within google earth. I received the updated .kml and now I need to get it back to .shp. The problem is: all of my attributes are now in html format, which makes the popup info box in google earth. When I try to convert back to .shp my attributes get lumped into a (google created) "Description" field. And ALL of my attributes and information is lumped into a single cell. Is there some way to extract the data from the "Description" field. Even if I do that, is there an easy way to get rid of the html format and make it usable?

This is what I get using the "Fusion Table" method I found on some forums. You can see the html in the left, it continues on further than it shows.

http://s6.postimg.org/uf7jeuksh/problems.png

I am using ArcGIS Desktop 10.2.

  • What do you use to convert between the formats? Can you try ogr2ogr or QGIS instead? – bugmenot123 Jul 24 '15 at 8:45
3

I've just confirmed that a Spatial ETL tool created using the Data Interoperability extension will bring the attributes from your KML/KMZ file back to a shapefile with the schema in tact. Just select the Dynamic Schema option in the tool creation wizard:

Spatial ETL step


a work around would be to parse the description field for the values of the various attribute fields you need and copy them into the appropriate fields. And next time, get your client to add attributes into an excel spread sheet or something so that you can just join it back to your shapefile for updating.

  • 1
    I think that's my only solution at this point. But I will definitely include an excel spread sheet next time! – Katie B. Jul 24 '15 at 11:52
  • Do you have access to the Data Interoperability extension for ArcGIS? I don't have experience with it for this issue, but there may be writers in there which can remap your attirbutes – Adam Jul 29 '15 at 22:57
  • FME from Safe Software (which Data Interoperability is based on) will also do what you need, but it's expensive too. – Adam Aug 31 '15 at 5:31
3

I had to open the kml in QGIS first and save as a shapefile and it maintained the attributes. In QGIS, Add vector data and under browse, set files of type to "Keyhole Markup Language (KML)" Browse to the KML that you want to import, then click open. Save the objects imported from KML to a Shapefile. On importing the shapefile in ArcMap, you'll see all the attributes maintained.

We're looking for long answers that provide some explanation and context. Don't just give a one-line answer; explain why your answer is right, ideally with citations. Answers that don't include explanations may be removed.

2

This tool, Export to KML developed by Kevin Martin does the work.

There are some issues with the styles but at least colours and attributes are correct, and it´s plenty of options to play with the attributes, labels, etc...

2

I only needed one of the fields, which contained a date string. Using arcMap, I was able to solve this by using the field calculator to extract it. If you only need to extract a couple fields, this may work for you, albeit a bit tedious:

The following steps will help you build up a line of VB to use in the field calculator to cut a sub-string from the description field based on the HTML tags and the legth of data stored in your table.

  1. In GoogleEarth, open your KML/KMZ, click on a feature to open the popup. Identify the exact name of the field you want to retrive. Mine is called "DATE"
  2. In ArcMap, open the attribute table of the layer that you created when imported the KML/KMZ.
  3. Identify the description field. In my data it is called "PopupInfo"
  4. In the first record, right-click in the "PopupInfo" cell, and click "copy"
  5. Open Notepad and paste the contents of the cell. You will now see the HTML for the pop-up.
  6. Use ctrl-F to find the field name from the KML/KMZ (in my case, "DATE"). Here is a screenshot showing just the line of the HTML I care about. enter image description here
  7. Count the number of characters, including spaces from the beginning of the field name until the start of your data. In my case there are 15 characters.
  8. Determine how many characters you want to extract. For my substring, I need 19. For example if you just need state codes like AZ and WY, you may only need 2. If your data doesn't have consistent length entries in this field, err on the side of the longest. Edit: you can remove the extra characters later manually or by using the instr() and rtrim() VB functions. Post or message me if you need help with that.
  9. In arcMap, add a new TEXT field your layer, and be sure to give it at least as many characters as you'll need for your data. The default is 50.

  10. Then, open the field calculator for your new field. The start of the data is found using the InStr() function, and the data is extracted using the Mid() function. Type the following VB command:

    Mid([PopupInfo],InStr([PopupInfo],"DATE")+15,19)

Of course, swap out my field names for yours (from steps 3 and 6), and change the length of the numbers 15 and 19 to the length of your substrings (found in steps 7 and 8).

In the VB line above:

  • [PopupInfo] is the name of the annoying field that contains the HTML for the KML pop-up.
  • "DATE" is the KML field name and HTML tag that contains data that I want to extract.
  • 15 is the number of characters from the start of the date tag to the start of the actual date data.
  • 19 is the number of characters in thee date string that we want to retain.
1

Here is a guide that I found that walks you through some steps using ArcMap, Google Drive Fusion Tables, and MS Excel to convert KML files to .shp files while preserving the attributes.

Link to site that has the guide.

  • Yes, but that doesn't solve my problem. My attributes still get lumped into the "Description" field in the html format. The KML to Layer tool does not work for my situation. I've even tried other arcscripts. – Katie B. Jul 23 '15 at 19:33
  • See my edited answer. – Cameron Sloan Jul 23 '15 at 19:38
  • Yet again, the image I linked in my original question is my result when using the fusion table method. It does not work for my situation. – Katie B. Jul 23 '15 at 19:42
  • Did you bring it into excel? – Cameron Sloan Jul 23 '15 at 19:43
  • Yes, same issue. I just found another forum site where someone else is having the same issue. It seems I will have to go through the 1000 lines of script and delete the code or try doing a "find and replace" I appreciate your help. – Katie B. Jul 23 '15 at 19:44
1

I've been able to make this work using cursors and lists to split the PopupInfo xml field into useful values

  1. use the arcpy KML to layer tool and Project tools to get into to the desired coordinate system (i ran into problems adding fields to the original conversion output, which may be due to the layer file associated with it)

  2. Use .da.SearchCursor to get the PopupInfo string from the first row. Then split it into a list based on '<', delete the first two values (the label field from Google Earth) and put remaining values that have the 'td>' tag but not the 'td>' closing tag into a new list of field names (even indexes) and field values (odd indexes)

  3. Loop through field names list with arcpy.AddField_management to add all fields (skip if they already exist)

  4. Use .da.UpdateCursor to get PopupInfo's for all rows, then split and create new lists just like with search cursor

  5. this time, use all the odd index values to update rows ( if i%2 <> 0: row[(i-1)/2] = list[i] ) and then cursor.updateRow(row)

1

bugmenot123 already mentioned it, but ogr2ogr can convert between shapefiles and kml.

Converting between KML and shapefile (SHP) format? covers how to convert between the two.

0

UPDATE: if you come across this thread with the same problem, there is a tool out there for you! Check out https://mygeodata.cloud/

I works really well. I was able to upload my KML and the exported shapefile had all of my attributes correctly in their own fields. The only downside is that there is a limited number of "free" conversions.

-3

There is still no simple solution to this conversion issue. It has been asked here multiple times in the last several years:

kml-in-qgis-with-additional-data

preserving-attributes-during-kml2shp-conversion-in-arcgis-for-desktop

convert-kml-to-shapefile-without-losing-attribute-data

esri's explanation for their KmltoLayer conversion tool:

"As the 'owner' of the KML tool at Esri I can say: The ExtendedData elements inside a KML will not translate into field attributes when using the KML to Layer tool with ArcGIS at any version (9.3-10.3). This includes ArcMap on Windows, or ArcGIS Server on Linux. There is an enhancement request in to support this which we're considering for a future release."

  • 2
    Hey - I take personal offence to your answer. If you're going to copy and paste my comments and put them in quotes, certainty DO NOT add your own commentary about buying an extension. – KHibma Aug 6 '15 at 15:06
  • @sirgeo there is definitely a solution to this issue, in the Data Interoperability extension. It just seems that this is not a solution that you like. – Adam Aug 31 '15 at 5:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.