This is an extremely loose tutorial for the project I was requested — to export the 3D Printers in Libraries Map to a spreadsheet. Unfortunately, doing so loses a lot of data which is on the Google My Maps application. So this tutorial goes through how to correct it…with a lot of steps.
Export My Maps
You will then need to unpack the KMZ file. Use 7-zip for this. Just right click and unpack.
Download this program and install it.
Create a folder on your computer and drag your KML file into it.
Set it up according to the following tutorial. In configuration settings, have the watched folder be the one you created in the previous step.
Click the magnifying glass in the top right corner of the Watched Folder column on the left. You should now see your KML file in the leftmost column under KML Files. Double click on the KML file. Then click on the Create File button in the middle of the screen.
Your new CSV file will be in the folder you created earlier.
Issues: you will only see the lat, long, name of place, and notes field. You will need to add an additional column to fill in the information from the layers in your My Maps. Check your My Maps layers to see what are the first and last items of that layer. You will then:
Type in your layer name in the first blank column’s first cell. Then click on that cell. In the bottom right will be a little square. Click on that and then move your mouse downwards to drag that layer name through the rest of the rows within that layer. Repeat for each new layer.
Next, you need to convert the lat/long to addresses. First, you have to create an extra column to the left of column A. Do this by right clicking on A and then clicking on Insert. A new column will appear to the left of it. Then follow these instructions on how to move the data from one column to the next: http://www.gcflearnfree.org/excel2013/8.5
Delete column B.
You then need to use CONCATENATE to merge column A and B together with a comma between them. Read instructions here: http://superuser.com/a/405436
You want to put the formula in the first cell of the first blank column you have.
Highlight the new column and copy it. Then paste it into this website: http://www.findlatitudeandlongitude.com/batch-reverse-geocode/ For the Batch Reverse Geocode Output Fields field, make sure only address is checked.
In Excel, add a new column in front of column C. We’re going to copy the address from the previous step into this column.
Hopping ahead, I’ve copied the first batch out of that reverse geocode site. Fortunately, it drops the quotation marks when pasted into Excel. So I paste that into a new column. THEN I need to create 5 blank columns to the right of the one I just put data into. I then use Data > Text to Columns to separate by the delimiter of a comma. This will break the data up. Then I repeat so I can break up the State/Zip Code pairing. Use the delimiter of a space. You may notice weird spaces in front of your items.
To remove those weird spaces, you’ll need to follow these steps: http://www.extendoffice.com/documents/excel/667-excel-remove-first-space.html
Make sure to create a new column to the right of the data you want to extract. Put in this formula in the top cell: =TRIM(A2) This formula assumes that the first row is your headers. If it’s not, change the number to whatever. Then drag the bottom right corner of the cell all the way down. Then copy this cell’s data. Click the letter of the column to the left. You will then paste as Values.
Now I’m trying to find a bulk tool that matches up zip codes or City/State to congressional districts. More to come later if needed.