How to convert a PDF calling list into a Google Docs Spreadsheet
So, you’ve got a call list and you want to split it up with a team of volunteers so you can work together to call through it. And more than that, you’d like to be able to collaborate on just one copy using Google Docs, so that you can tell who’s calling and how things are progressing, while avoiding duplicating each other’s efforts unnecessarily. Plus, some of your volunteers use Google Voice to call with (so they don’t have to pay for long distance charges) and they like to use the click-to-call method (Google Voice + Chrome w/ Voice Extension + Gmail) because it’s faster and free. So, you want to set the list up as a Google Docs Spreadsheet so that you can share it with volunteers, track progress, and make it easy for your click-to-call volunteers to use. But, of course, you’ve got a problem: the list is in a PDF file. Well, it’ll take a little working with the file, but we can make that work!
Here’s what you need to do it:
1 – A free Google Docs account (www.google.com/docs)
2 – The free Open Office software (www.openoffice.org) or any other office suite that can handle Microsoft Excel (.xls) files.
This tutorial assumes you can muddle your way through both, but also assumes you may not know much about either. Also you should note that while there are a lot of steps they’re really very easy.
- First, you’ll need to convert the PDF file into something easy to cut and paste from. Fortunately, the Google Docs uploader does a terrific job of that! So, step 1 is to upload the PDF file into your Google Docs account, being sure to uncheck the “Convert documents, presentations, and spreadsheets to the corresponding Google Docs formats” checkbox, and then check the “Convert text from PDF or image files to Google Docs documents” checkbox. Or, to make this easier, there are two checkboxes, and you only want a check in the bottom box. Once the file is uploaded and converted the name will convert to a link. Just click that to open the converted file up.
- Now that you’ve got the converted file open you’ll notice that it’s in a text document type format where the text that’s been converted is under the PDF pages it was converted from. For example, in a multi-page PDF, Page 1 is embedded as an image, then the text from page one, then Page 2 as an image, then the text from page 2, and so on. You’ll also notice that in some cases where there should be a space ( ) there is an exclamation (!) mark instead, and where there should be a dash or minus sign (-) there’s a quotation mark (“) instead. Don’t worry about that, we can easily get rid of that. But first, we need to get rid of those images in the converted file. All you have to do in order to remove the images is right click on each one and select “Delete Table”. You’ll want to do the same to get rid of the instructions box Google inserts at the top.
- Once you’ve deleted all the image tables and the instructions box in the converted file, then you simply select all the text (the easiest way is to just hit ctrl+a on your keyboard, but you can go to Edit > Select All in the drop down menu as well – either way works). Then copy it to your clipboard (either ctrl+c or Edit > Copy.
- Now you need to open up your office software to create a new spreadsheet. Once your new spreadsheet is open, click in the right uppermost cell (that’s cell A1) and then paste in what you just put on the clipboard (ctrl+v should work in all software, Edit > Paste in most). This should move all of the data from the text document on Google Docs (which began life as a PDF file) into your spreadsheet, getting it into the format we want it in. This should put each person in the list on a different row. Of course, it’s not pretty yet. Taking care of that is the next step.
- OK, now we need to clean up the text and get it nicely organized. Exactly how you’re going to do that will depend somewhat on how you want to use it, but some things you should do regardless…
- The first thing you’ll want to do to clean up the file is to fix that conversion artifact that turns some spaces ( ) into ! marks and some dashes (-), such as in phone numbers, into quotation marks (“). The easiest way to do that is to use the find & replace tool (ctrl+f or Edit > Find & Replace). For example, to turn the ! mark into the space it should be, just put the ! mark in the find box and the space (just hit the spacebar once) in the replace box. Then click the “Replace All” button. Do the same with the ” and – marks. Voila! so much for that problem!
- Now, chances are that the call sheet you got as a PDF had a bunch of check boxes and text descriptions for tracking the calling. That was because more than likely it was meant to be printed out so you could track your work on paper. Now, you want to use the same tracking titles, but since this is a spreadsheet you’ll handle it a little differently. What you want is to have the tracking codes across the top of the spreadsheet, in the first row, so you can drop x’s in the boxes (like checking a box). You’ll want to abbreviate these to keep things nice and neat, and you should make a key so you can check what they mean.
- To create the key just create a new sheet in the spreadsheet. See those tabs near the bottom that say “sheet 1″, “sheet 2″, etc.? Hover your mouse next to them, right click, and select “Insert Sheet”. Choose “after current sheet” (usually) and then OK. If you click before when you meant after or vice versa, don’t worry, you can re-arrange the tabs easily by drag and drop. The new sheet should open right up. Now just put the abbreviations down one column, and their meanings down the next, starting in the second row down. Hint: to make a later step easier, go from top to bottom for left to right (left-most code on the top, right-most code on the bottom). Title each column “Abbreviation” and “Meaning” in the top row. You might want to bold the titles, too. For example, “Left Message” could be LM, “Already Voted” could be AV, “Wrong Number” could be WN, and so on. You just want a key in there in case any of your volunteers need to know what the abbreviations mean. Now rename the sheet “Key” by right clicking on the tab, and selecting “rename sheet”, typing in the new name and then clicking OK.
- Now that you’ve got the key made and don’t need to know the tracking codes anymore, you’ll want to get the un-needed tracking code clutter out of the rows. The easiest way to do this is to use the Find & Replace tool again. This time, select the entire string of unwanted text, and enter it into the Find box, and enter nothing into the replace box (make sure nothing is in it) This will effectively delete all the unwanted text in one fell swoop.
- Now, chances are all you have left in each row are the names and phone numbers, and these should all be in column A. You’ll want to get the first and last names, and the phone numbers into their own columns. You don’t really HAVE to do this at all, but if you do it will make re-ordering and sorting the list easier should you need to. For example, if you wanted to sort by last name, or first, or by area code. To seperate them easily, first select the entire A column (or whichever column they are in), then (in Open Office) go to the drop down menu, select “Data > Text to columns and then checking the “space” box and unchecking the “tabs” box (which is checked by default). This should put each block of text into it’s own column.
- The next thing you’ll want to do is put the header row with the tracking code abbreviations on the top. Please note that Google Docs won’t support fancy angled text or partial bolding of cell contents, and that sort of thing so don’t bother with any of that. To start just right click in cell A1 and select “insert…” and then check the “Entire row” radio button (like a round checkbox). Then click OK. Put the titles “First Name”, “Last Name”, and “Phone Number” where you’d expect them to be (probably in the first three columns). Then put each of your abbreviations for your call tracking codes in a column (try to use the same order as they were in the original PDF). Now, you can just make each column just wide enough for their contents (just highlight the column, and move the borders).
- At this point your spreadsheet should be looking pretty good. Make any other little adjustments you feel like making, and save it to your hard-drive one last time. Now that your spreadsheet is basically done, you’re going to want to upload it to your Google Docs account. You’ll do this the same way you did before with the PDF file, except this time instead of having the bottom box checked you’ll have the top box, the one that says “Convert documents, presentations, and spreadsheets to the corresponding Google Docs formats” checked.You may be wondering why you even bothered with using Open Office in the first place, instead of just making it directly in a new Google Docs spreadsheet. There are a few reasons. First, in my experience Google Docs spreadsheets sometimes hang up when you’re trying to paste in large amounts of data, even from another Google Document. Secondly, it’s beneficial to have a local version as a backup. Third, and this is really the kicker, there are some things you can do in Open Office (such as opening extra sheets) that you can’t do in Google Docs. However, when you upload a file that had those features, they work perfectly. So, you’re better off for several reasons to make your file in Open Office, then upload it to Google Docs than to try and make it in Google Docs to start with.
- Now, open up your Google Docs spreadsheet once it’s uploaded and converted. It should be pretty much exactly the same as it was in Open Office. With, of course, the important differences that this file can be shared collaboratively, and it’s progress can be tracked. And, if you’re all set up with Google Voice (and, optionally, Gmail – if you want to take your calls in Gmail as a softphone) you can open it in Google’s Chrome browser (with the Google Voice extension installed) and just click the phone numbers to dial.
- One last step I recommend, which just makes things easier as you call. In the Google Docs spreadsheet drop down menu, select Tools > Freeze rows > Freeze 1 row. This will freeze row one so it doesn’t scroll, which helps you remember which box is which and which number you’re on.
That’s it! Your file is ready, and you can share it with your volunteers!
Does your campaign need a kick in it’s technological pants? Contact me at firstname.lastname@example.org for help.