Table of Contents
We all know the benefits of using a spreadsheet program over simply placing data on a text document. Turning that said data into a spreadsheet helps us organize and easily do work with that data.
You might have recently downloaded a PDF file online or from a friend and it’s got a lot of datasets on it. Now you’re wondering if there’s an easy way to convert it into a Google Sheets file.
Well, you’re in luck! You can convert PDF files into Google Sheets files. However, there’s no direct way to do it. What you’ll need to do first is convert that PDF file into a file that is supported by Google Sheets. Then you can use this new file and convert it into a Google Sheets file.
In this article, I liked to show you exactly how to do that. We’re going to discuss 3 different methods to convert data from your PDF file into Google Sheets.
Method 1: Converting PDF to Google Docs and then to Google Sheets
I would like to add that your PDF file should have data in somewhat of a table fashion. This will make the conversion process easier. If it’s not in any particular table format, this tutorial might not work for you.
If you do have a PDF file with data in it that is in a table format, then follow the steps below:
- Go to your Google Drive account.
- Click on New > File upload.
- Locate and upload that PDF file you are trying to convert.
- Once the upload is complete, open the PDF file (now in Google Drive).
- Click on Open with > Google Docs.
- Select the table and copy it using Ctrl + c on your keyboard (or right-click on your mouse and select copy).
- Go back to your Google Drive and open a new Google Sheets file.
- Select an initial cell in the Google Sheets file.
- Paste your table by using Ctrl + v (or right-click your mouse and press paste).
At this point, all your data from your table originally from the PDF should show up on your Google Sheets.
This process simply makes the dataset found in a PDF file selectable for copying and pasting. Fortunately, converting from PDF to a word editor like Google Docs is an available feature.
Once we were able to highlight, select, and copy the Google Docs data, we would eventually transfer that dataset over to Google Sheets.
One issue that needs to be mentioned is that sometimes the conversion from PDF file to Google Docs has formatting issues. The major issue occurs when you paste the table into a Google Doc but Google Doc fails to recognize it as a table and thus puts all that data into a single line.
If that’s the case, check out the next method.
Method 2: Convert PDF to CSV and then to Google Sheets
Another method allows for the same outcome but requires that you convert the PDF file with the table into a CSV file.
The list of Google Sheets file extensions supports CSV, TXT, XLS, and JPG. All the data in a CSV file is organized into columns and rows that are comma-separate.
No characters have to be escaped in the CSV file. Each row in a table has the same number of columns as the table headings that appear at the top of each column.
Your goal is to convert your PDF file into a text-based spreadsheet format (like .txt) first before you convert it into a Google Sheets file, so all your important data will still be available for use after the conversion processes have been completed.
Follow the steps below for this method.
- Go to your Google Drive account.
- Click on New > File upload.
- Search for the PDF table file in your local storage and upload it to Google Drive.
- Open the PDF file in Google Docs.
- You should see all the data lined up without the table. You’ll need to create commas for every individual item listed.
- On your keyboard, press Ctrl + f to open up the search/find function.
- Click on the kebab menu (vertical three-dot menu).
- Add a space in the “Find” textbox using your keyboard’s spacebar. Please note: You’ll notice that a number indicator will show up to the right of the textbox, letting you know that it’s found that many spaces in the document.
- Type a comma in the “Replace with” textbox.
- Click on Replace all to replace all the spaces with commas. Please note: These commas are going to separate the texts into their own cells, so if there are any words you’d like to stay together in one cell, remove the comma from them. This rule also applies to large numbers that have commas in them. It’s best to remove these commas from the large numbers to keep them intact when they are converted over to Google Sheets.
- Exit the find tool, and then go to File > Download > Plain text(.txt) to save this file as a txt.
- With your mouse, right-click this txt file > Open with > Notepad for PC or TextEdit for Mac.
- Make further edits here. The values that were originally in each row in Google Sheets should have their own new line in txt as shown in the picture. You may need to manually do that yourself so that the conversion to Google Sheets will come out looking properly.
- Go to File > Save as.
- Type the name of the file with the ending .csv.
- Then click Save.
- Go back to Google Drive and open up a blank Google Sheet file.
- In this blank Google Sheet, go to File > Open > Upload.
- Select the recent csv file you saved and click Open.
- It will now open with all the data in the appropriate cells.
We have to be careful here.
Commas usually signal to CSV files that these keywords belong to different cells on a table. If, for example, your data has 1,000,000, you might be in trouble.
The number 1,000,000 has 2 pairs of commas in it. Google Sheets will interpret this as a separate cell for 1, then 000, and 000. We don’t want that.
Again, Google Sheets will see commas as the end of one cell and the beginning of the next.
What I recommend is replacing the comma with space. It should look something like this: 1 000 000 and will ultimately stay together when exported into a Google Sheet file.
However, Google Sheets is getting smarter every day. You can simply provide it with $1000000 and it will naturally convert it to $1,000,000.00. Or at the end of this process, you can simply go to your Google Sheets format option tab and automatically make all numbers in currency format.
One other thing to remember is that while you format your data (specifically) in your txt file document. Make sure you enter a new line for every row in your dataset before you actually convert it to a spreadsheet.
If you don’t, the conversion to Google Sheet will likely be a single row with the data dispersed into each column. It’ll basically be a long horizontal line across the screen full of all your data.
I would also like to add that this method does come with a few issues like the extra blanks we’ll have to reconcile, some weird extra characters sometimes, formatting issues, and more.
But overall, I think this is the best method for anyone who doesn’t want to download anything extra or use a third-party program.
But if you’re looking for an even simpler approach, there are online tools available.
Method 3: Use an online tool to convert PDF to Google Sheets
There are a number of online tools that can help you convert PDFs to Google Sheets. The best part about these online tools is that they are free to use.
I like the tool I found here at altoconvertpdftoexcel.
It’s easy to use and does not require any extra software downloads. All you need is a browser, an internet connection, and a PDF file in order to convert it into a Google Sheets file. Let’s take a look at how it works.
- Go to the altoconvertpdftoexcel website.
- Click on Choose File.
- Select your PDF file.
- Click on Convert Now!
- Download the file which is going to be in Excel form.
- Upload that Excel file to Google Drive.
- Open that Excel file and switch it to Google Sheets.
This method is extremely simple and free. What we did here is use a tool that essentially converts PDF files to Excel spreadsheets.
Google Sheets supports Excel and all we have to do is convert it to a Google Sheets file with Google Sheets.
This method outputs pretty good formatting and little issues that I’m aware of.
The takeaway
When looking at converting PDF files to Google Sheets, it’s hard to find a direct way.
I’m sure Google recognizes the need for a simple and direct means of converting a PDF document into a spreadsheet. However, it’s just a little bit more complicated than that. What if your PDF had blocks of text? What if it didn’t have any tables at all? How would Google Sheets interpret this and convert it properly?
My guess is that this task is probably going to be on Google’s back burner for a long time. The safest approach is to follow any of these three methods.
I hope this article helped you understand your options when looking at converting PDFs into Google Sheets by providing some examples and resources on how they’re executed.
Other interesting articles:
