Convert PDF table to Google Sheets workaround

hello everyone this is Ian Lamont author of Google Drive and Doc's in 30 minutes and recently I received a question about Google Drive and Docs and actually specifically Google sheets and I've created some videos in the past where you can create a PDF from a Google sheets but someone asks is it possible to create a Google sheets from a PDF that is if you have a PDF with some information on it can you import that data into Google sheets and the the short answer is yes but you have to do a couple extra steps to make it work and this is what this video will show you hopefully in a short period of time so let's take a look at a PDF here's a very simple PDF that I have it basically just shows the daily highs temperatures in various cities in the Northeast US on a weekly basis it's a very simple chart and what I want to do is take this data it's all text data in the PDF I can't edit it but I want to bring it into Google sheets and the idea is once that data is in there then I can manipulate it I can create charts I can export it as something else and how would you do that and the answer is it's pretty hard to do it directly and in fact I haven't been able to figure out a way to do it directly but there's a workaround you can do to make it work one caveat the PDF that you have it has to be a really simple charter table it can't be other like you can't have text over on the left side here that's outside of the table or you know to two tables next to each other and split them up or anything like that it won't work it really has to be a simple chart that looks something like this maybe it could even be longer or could have more rows and columns but it has to be just a basic you know rectangular or square table no extra stuff next to it to make it work so here we have the weekly high temperatures sheet PDF now let's go to Google Drive first of all to import that and as I've shown on many other videos here's Google Drive and blow it up so you can see things a little bit better you press the new button to import something new file upload and then you find the the item the PDF that you want to convert so here here it is weekly high temperatures PDF so let's take that in open so what it will do is it will upload the PDF into Google Drive and if you want to you can just take a quick look at it click on it and it will preview it but we don't want to do that we actually want to convert it to something else and here's how you do it so we've just uploaded this thing here it is right here and you can see that it's highlighted but I'm not opening it I'm just clicking it once and then so it's highlighted then you go up to this more actions icon if you hover over it it will say more actions it looks like three dots click on that then select open with Google Docs notice it's Google Docs not Google sheets and I'll explain why in a minute so here is Google Docs and it's converting the PDF the very simple PDF into text alright so here is the Google Docs and right now the text is that a editable so I can delete Boston or I can change the numbers here but this is actually kind of useless because it's just like a string of text there's no there's no you know returns it doesn't look like a table anymore here's the original one for your reference okay here's what it looks like the raw text in Google Docs okay so we want it now we want to put this into Google sheets and you have to do a couple steps before you can before you can get into sheets but here's how go to file download as and choose the option that says text okay so it's just downloaded it into the into a text file and here it is alright let's open that up with the text editor and by the way this is the Mac text editor but if you're using Windows just use notepad or whatever and all of these steps will work in Windows as well alright so here's the raw text that we have now if I wanted to I could import this into Google sheets and actually let's just take a look and see what that looks like when we do that hold on here didn't get that small here okay so I'm gonna switch over to sheets just type sheets google.com in the address bar okay now to import text you would just click on the Open File picker then select upload then you drag the file there so let's do that right now the text file right here it is the text file I'm just gonna get rid of the PDF there so that doesn't confuse people all right so let's drag it in there so just took the text and here it is up it now a thought that now if thought that it was just another text file so it open use it opened it in Google Docs but we wanted in Google sheets so this is not what we want and the reason why is because Google Docs works with text files whereas Google sheets you can import text but it has to be in a different format you have to call it comma separated value or text or a tab separated value which has a different ending so let's um let's just let's just take CSV and add that and we'll say you sure you want to use CSV yeah we do all right so let's try that again so you go to sheets and I'm sorry this if this seems a little bit convoluted but unfortunately it's just a the process here is not if there's no automatic way to do this you have to follow these steps alright so now we're gonna take what I've called a comma separated value file it's actually a text file but I'm calling it comma separated value and and I'm just gonna try to import it now all right let's open up the sheets all right here it is so it opened up the text in Google sheets but this is not what we want because everything is in a single cell in the cell a1 see that this shows all the information that's in there and it's just all crammed in there at once what what we want is something that looks like this we want the table and all the data in it but it just couldn't parse that and so it assumed that all the text is appearing in the single cell all right so that's not what we want so let's let's get out of there and what you have to do is you actually have to do a little bit of fixing of the file so let's open that up in a text editor all right so this is the text file with the CSV comma separated value I'm going to replace all of these spaces with commas and let's see what happens then all right so first you do find and then you select a blank space and then you over here you click for place and you replace the space with a comma and then you select all okay so now all of this data is separated by a comma let's see how Google sheets handles that now reporting it it's gonna crunch through it okay this is a little bit better but there's still a problem so it basically put each value in one cell but it's only in one row going all the way down all right so I still can't use it the way that I want to so let's try it again and what you have to do basically is you have to work on that text file and edit it in a way where Google sheets will import it as a as a table and so what what you need to do here is you have to basically first of all you have to split this into the rows that you want so the cities is on the first row and then Sunday's on the second row Monday is on the third row so I'm going through and I'm basically deleting the extra comma at the end of each row and then just hitting the return key okay so here we go let's try that now yes I know this is painful but it will help you in the end all right so we're gonna import this this new reconfigured comma separated value file and let's see what Google sheets does with it all right that's a little bit better here but there's still some problems so it basically got all the dates all the days right with all the temperatures but for some reason Boston is in this in this cell a1 it should actually be in b1 and then New York you can see is split into separate cells and the reason why that happened let's get out of there the reason why that happened is when I was editing the sheet and replacing commas and stuff like that it put up you can see it put a comma in between new and New York and we don't want that because New York is actually supposed to be the one word all right and then the other thing is is that the first cell is actually blank so what you have to do is you have to add a comma before Boston and then a space so this space will correspond with Sunday Monday Tuesday with each with each word in the in the column below it alright so let's try that now let's see what happens go to file picker again in Google sheets upload let's find our here it is here's the edited fixed up file put it in there tada alright here we go now we have all the data in Google sheets from that PDF it took a little work to do it but what we can do is we can start to do things like create a chart let's do that in Google sheets alright here's like a simple line chart you could also create other types of charts like a bar chart or whatever so this is this is really how you can how you can do a lot of neat stuff with PDFs that have tabut table data and then convert it into Google sheets it takes a while because you have to fix the data you have to clean up the data and lots of people do this every day in their jobs you know they're cleaning up data to bring into Excel or to Google sheets but once you have that then you can start to do really neat things with it so that's how I changed a simple PDF table into Google sheets and then of course now I can change the data I can export it if I want to download as Microsoft Excel and actually even re-export it as a PDF for more information on how to get the most out of Google sheets and Google Drive and all the other applications in G suite check out my book available from in 30 minutes com thanks for watching

Channel: In 30 Minutes

Views: 153,024

Keywords: how-to, help, video, dummies, for dummies, guide, book, step-by-step, tutorial, google sheets, excel, spreadsheet, table, pdf, conversion

Id: 75UkU_w-yMU

Length: 11min 17sec (677 seconds)

Published: Mon Nov 13 2017