Convert My Maps KML to Spreadsheet

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

https://support.google.com/mymaps/answer/3109452?hl=en

You will then need to unpack the KMZ file. Use 7-zip for this. Just right click and unpack.

http://www.7-zip.org/

Download this program and install it.

http://sourceforge.net/projects/kmlcsv/?source=typ_redirect

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.

http://choonchernlim.com/kmlcsv/tutorials/using-kmlcsv-converter-with-google-maps/

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.

Emerging Leader Appearance in American Libraries

EL_group_full

Here are the photos from the American Libraries issue which feature this year’s crop of Emerging Leaders — a group that I am lucky to be a part of. I haven’t written much about this yet, mostly because my part of the project (building a website) won’t come up until May. Right now, we’re conducting a survey to see who reads the New Member Round Table’s newsletter, Footnotes. Confession: I wasn’t aware NMRT even had a newsletter or a journal.

My group has joined up with the journal group for the purposes of the survey. Both groups have more or less come to the same conclusion which we discussed at the all-day Emerging Leader workshop on Friday, January 30th. However, the survey results will reveal if we were right. We will then give advice as to what we think the future of both publications should be, if they should move to a new platform (we’re favoring WordPress), and how to increase readership with publicity efforts.

EL_2015_Group

Now to the thing which I’ve disappointed in: my name appears wrong in the magazine. I repeatedly told the magazine people the day of the photoshoot that my name was wrong on their paperwork. I then reached out through official channels to ensure that my name appeared correctly as Amanda L. Goodman. So when it came to my attention that the issue was released, I was very unhappy that my insistence on getting my name correct went unheeded. Other members also had their names appear incorrectly (or whole names were dropped out). However, I’m not sure if they want to bring public attention to this fact, so I won’t name them.

I’m frustrated. I feel disrespected. In stories, people believe that names hold power and so hide away their True Name. The naming of a thing has meaning. My name and its appearance is how I choose to appear before the world. I do not like being misrepresented.

Another confession: I actually prefer my middle name, but since I’ve never been called it (and thus feel that it’s far too late to change now that you all know me by Amanda), I instead honor the beautiful swooping shape of the cursive L and immoralize that instead in my signature.

Digital Sign: Marie Curie

Property of Darien Library
Property of Darien Library

The Children’s Library is celebrating Women’s History Month by dressing up as their favorite real life heroines. Here we have Amy (@AmySaurusRex) as photographed by Krishna (@DarcyEyre). The original photo was one of the most beautiful shots I’ve seen.

I’m pleased with this piece because my spouse just asked if Amy had actually been in a laboratory(!).

Flyer: The Doctor is You

Property of Darien Library
Property of Darien Library

Let me be honest: I designed this flyer with a sense of competition.

My colleague caught me ASAP after returning from ALA Midwinter in Chicago and asked me to produce this flyer by the end of the day. She gave me another library’s flyer for inspiration. I saw the layout of that one and thought, I can do better. Nothing like a little competition to get the blood moving…

The doctor image was created in Inkscape on my work Windows computer. According to that monitor, the doctor’s race is much more ambiguous. I’m annoyed that it came out the way it appears here on other computers. However, I think I did an alright job of trying not to present a specific gender or age to the illustration. My spouse pointed out that the image does portray someone of a certain weight. The model for the crossed arms was a buff guy, so there’s that.

The original version had a red background instead of blue. Unfortunately our printer turned the doctor’s skin tone orange and thus there was no contrast. So I changed out the color. I’m pretty happy with the way this came out though the amount of text makes me cringe. I’m used to designing digital signs which are skimpy with the text.

Poster: Recharge Your Book Group

Property of Darien Library
Property of Darien Library

Becky Spratford is a guest speaker coming to our library in a couple weeks. From my understanding, this program is her bread-and-butter. I was hoping to capitalize on any design element she already had going on, but found nothing. So I drew up a few concepts (lightning, batteries, power outlet) and let the book group coordinators decide. They choose the lightning! I then did the campiest Photoshop job to the images to merge them together.

Don’t let this poster deceive you: the initial book + lightning strike was done in about 45 minutes. It was trying to add Becky’s photo to it that took houuuurs. Eventually I gave up because the photo made the poster too busy.

Presentation: A/B Testing in Library Emails

Email is not dead — in fact, your library’s emails are delivered to your patrons’ inboxes more than 90% of the time while Facebook continues to diminish how many of your followers even see your content. The trick then is to get your patrons to open your emails and then interact with the content in a way to drive value for your library. At Darien Library, we are using A/B testing to discover how to design email newsletters that get opened and acted upon. Our most recent test of changing a subject line generated 10% additional opens. Then by adding a digital service to an email, we increased usage of the service by 151%. In this short session, Amanda will go over some of the metrics you can test for in your email campaigns. She will be focusing on MailChimp, but other email campaigns also offer insights on how your users interact with your library’s emails.

~User Experience Interest Group at Midwinter 2015

I was fortunate enough to present these slides to the LITA UX IG on Sunday, February 1st in Chicago. The main takeaways are that:

  • It’s difficult to know why people click on things from A/B testing alone
  • Multiple tests are needed to try and narrow down results
  • A/B testing is fun

My favorite question from the audience was how does email analytics work — how can you know someone opened your email? I got to explain about the invisible pixel that is downloaded when an email is open. However, Apple and now Gmail skews the data since they auto-load images while Outlook does not.

Darien Library Does Frozen

The fantastic ladies of the Children’s Library dressed up as Frozen characters for the December 30th Sing Along. They dressed up, took the photos, and sent me the link. I then selected these 5 photos out of 456 shots and created these images from them. Elsa took the longest to create. I believe the total time was probably around 5 hours.

These photos are the property of Darien Library. Frozen belongs to Disney.

The Road to Being Debt Free

Today I pulled the switch and paid off the remainder of my student loan debt. My education costs were paid for through federal student loans, scholarships, grants, and a graduate assistantship. I was ineligible for public loan forgiveness because my debt would have been paid off within ten years of standard payments. While the confirmation message shows that I owe $0, the feeling has not sunk in yet.

What I’m most thankful for is that I paid off every penny by myself based upon my own efforts. Of course, I could not have done so if I had not been gainfully employed full-time for the last 41 months. When I started college in 2004, I wanted to be a Disney animator. My dad had forbidden me from applying for Randolph-Macon Women’s College (since integrated) in Virginia so I had to go to school across town. I was miserable there. As high school was wrapping up, my classmates said that they had just known they were at the right school when they stepped onto campus. I never felt that during the four miserable years in my undergraduate program. I was there because I had no money to defy my dad and go to school elsewhere. So local I stayed.

I supported my dad, my three cats, and myself through college by working part-time in various retail and restaurant jobs. Pay was extremely low as you may imagine. I needed my student loans to make ends meet. I went to school full-time. My average day was me arriving for class at 8 a.m. Then I’d be at work at 4 p.m. till 9:30 p.m. I’d sleep in my car between classes and pull all-nighters to get projects done. I was exhausted all the time and deeply unhappy.

Graduation rolled around four years later in 2008 — just as the economy sank. I was fortunate to have my crappy retail job to continue to go to. I freelanced on a video documentary and graphic design job. Put in endless applications. I then went to a job fair on campus and ended up on the local news. My coworkers saw me talk about having put in 79 applications as a college graduate and having no leads. I then looked into grad school.

My first choice was Eastern Carolina University to become a nurse-midwife. However to get there, I needed to pick up four biology classes for enrollment. I sat for hours at the local community college to talk to them. They told me that I couldn’t take the classes without enrolling as a freshman and starting college all over again. Very angry and disappointed, I decided to take up what those “what should I do with my career?” tests said — library school. I took the GRE a week later (did as best as can be expected for such a short turnaround) and visited UNC and UNCG. At UNC, I did not talk to the library school but the journalism department (!). Dad and I almost didn’t bother going to UNCG, but we swung by last minute anyways. I’m so glad I did — I found my school.

Work punished me. Everyone knew I had been on the news talking about finding a non-retail job. So at the height of Christmas shopping and despite being on the customer service desk — they cut my hours to zero. For two long weeks I had no pay. This was the only income for my household. When I came to check the schedule, I found out that I had been hired to do claims (AKA returns, hazardous waste management, and recalls) full-time. The guy who worked back there said he wanted me to come work with him. Thus Chris saved my household. I worked in claims until June 2009. I moved to Greensboro for library school on August 1, 2009.

I had transferred from my retail store to one near my new apartment. It was extremely hateful. Feeling overwhelmed, anxious, and homesick, I went to the school’s counselors. The guy convinced me that retail was killing me so I quit in September. I had worked for the company for 37 months. Things went okay for awhile, but my darling Dollbaby was sick from complications of diabetes and needed to visit the vet quite frequently. So in January I started to bite the bullet and try to go back to that hellhole. Then another call from above happened and I was awarded a graduate assistantship as webmaster. My education was now paid for and I had a small income that allowed me to take care of my baby.

Librarianship was something I choose out of desperation. My dream since the age of 8 to work as a Disney animator was too far away and impossible to achieve (you needed to have money to have a long unpaid internship to even get your foot in the door). So I improvised. This blog has been tracking me since the time I graduated college in 2008. I spent the first 26 years of my life in poverty. I was so heavy with sorrow for taking on student loan debt. Now that it’s gone and I’ve repaid the government/society, I am finally free. I can focus on saving for retirement.

Thus the end of this story. I obtained my driver’s license and the freedom to drive when I was twenty years old. Since my work and school consumed all my time, I was rarely able to make the trips to visit my grandparents who lived exactly 56 miles away from my house. In January 2011, in my final semester of library school, I got the call that the end was coming for Grandpa. I managed three trips between then till he died. The second to last trip, Grandma had left the room and I knelt down beside grandpa’s wheelchair. I was crying, holding onto his hand. And I asked him to please come to my graduation in May. Please. I am his only descendent to finish college let alone graduate school. I asked him if he was proud of me. I wanted to make him proud. He had worked so hard his whole life and had been sick with Parkinsons for 9 years. I wanted him to come see that his hard work had led to at least one of his descendents finally having the chance to get out of poverty. He died on Tuesday, February 15th at 9 p.m.

So grandpa, I paid off the last of my debt today in your honor. Your birthday was on Friday. I want to show you how far we still have to go.