How to Convert PDF to Google Sheets

We often need to work with data received in the form of a PDF file.

You might have a scanned copy of important data that you need to process in Google Sheets, or you may want to work on a dataset received from someone or downloaded from a website in PDF form.

Unfortunately, there is no way to directly open a PDF in Google Sheets, or convert one to a format that can be processed in a Google worksheet.

There are, however, ways to indirectly get your data into a Google Sheet.
In this tutorial, we will discuss different ways to convert PDF files to Google Sheets format.

How to Convert PDF to Google Sheets

At this point, there are largely two ways to convert PDF files to Google Sheets format:

• By using other Google applications like Google Drive, Google Docs.
• By Using third party applications or online tools.

Let us look at each of these methods one by one.

Related reading: How to Convert PDF to Word on Mac [5 Easy Ways]

Using Google Docs to Convert PDF to Google Sheets

Let’s say you have the following PDF file saved somewhere on your computer hard disk.

If you want to open this file in Google Sheets, here’s what you have to do:

1. Go to your Google Drive (drive.google.com).
2. Navigate to New->File Upload.
3. You should now see a File Upload dialog box from where you can select the folder you want to fetch your PDF file from.
4. Double click the name of your pdf file.
5. Your file should start uploading and you can see this from a box at the bottom right corner of your browser window.
6. Once the file is done uploading, the box will say ‘1 upload complete’.
7. Click on the name of your PDF file.
8. The file will open in a popup within the same tab.
9. Click on Open With, followed by Google Docs.
10. This will open the PDF in Google Docs.
11. Select the part of the pdf file that you want to work with and copy it (by pressing CTRL+C).
12. Open a Google Sheets file, select the cell where you want the contents of the PDF file to be placed, and press CTRL+V to paste.

Your PDF file’s table contents should now be visible in Google Sheets, and you can subsequently work on this data as required.

When using this method, there are a few important points to note though:

• If the data in a PDF file is stored as plaintext rather than in the form of a table with rows and columns, this method might not work.
• When you paste data from a PDF into Google Sheets, you might end up bringing in hidden table cells or characters as well.

If the data is in plain text, then you might need to first convert the data into CSV format.

Converting PDF to CSV and Then Opening with Google Sheets

This might be a little bit of a long process and might require a little more effort on your part.

Here are the steps that you need to follow in case you come across such a PDF file:

Use Google Drive to upload the File:

1. Go to your Google Drive
2. Navigate to New->File Upload
3. You should now see a File Upload dialog box from where you can select the folder you want to fetch your PDF file from.
4. Double click the name of your PDF file.
5. Your file should start uploading and you can see this from a box at the bottom right corner of your browser window.
6. Once the file is done uploading, the box will say ‘1 upload complete’.
7. Click on the name of your PDF file.
8. The file will open in a popup in the same browser tab.

Use Google Sheets to Save the File as a Plaintext File (.txt)

1. Click on Open With, followed by Google Docs.
2. This will open the PDF in Google Docs.
3. If you see the contents of the tables in the form of plaintext, then you will need to convert the contents into a comma-separated format. Notice that the contents of each cell are separated by a space. We need to take advantage of this and convert all space characters to commas.
4. Press CTRL+H to open the Find and Replace Dialog box.
5. In the input box next to Find, type a space character (simply by pressing the space bar on the keyboard).
6. In the input box next to Replace with, type a comma character ‘,’.
7. Press the Replace all button. This will replace all the space characters with commas.
8. Close the dialog box.
9. We now have a comma-separated file.
10. In our sample file, we might need to make a few adjustments, since the file already contained values separated by commas in the last column of the table. This might get treated as a separate cell when converted in Google Sheets. So for our convenience, we removed the commas between the last three values (all belonging to the last column) of each row. We replaced the commas with space, as shown below:
11. Download the file as a Plaintext file (.txt) by navigating to File —> Download —> Plaintext (.txt)

Use Notepad to Save the File as a Comma Separated Value File (.csv)

1. Once the plaintext version of the file is downloaded, open it in notepad.
2. Navigate to File->Save As.
3. In the Save As dialog box, type the name of the file, followed by .csv, since we want to save the file in comma-separated value format.
4. Click Save.

Now all that’s left to do is open the CSV file in Google Sheets

Use Google Sheets to Open the CSV File

1. In your browser, open Google Sheets by typing sheets.google.com in the browser location bar.
2. Navigate to File->Open (or just press the shortcut CTRL+O).
3. This will open the ‘Open a file’ dialog box.
4. Select the Upload tab and press the button that says ‘Select a file from your device’.
5. Double-click on the CSV version of your file from its appropriate folder.
6. This will now load the contents of your table into Google Sheets.

You will notice every value that you separated with commas now occupies individual cells in Google Sheets. You can now work on the contents of the file and process the data as you need to.

Note: You might need to do a little more work to clean up the data by removing extra blank cells, removing hidden or illegible characters, formatting date values, etc.

Using Third-Party Applications / Online Tools to Convert PDF to Google Sheets

Even though Google Sheets does not provide an easy way to convert PDF files to a format that Google Sheets understands, there are a number of applications and online tools available to get this done quickly and easily.

In this section, we will discuss some of these tools.

Using Google Workspace Marketplace (PDF Tables Extractor) to Convert PDF to Google Sheets

The PDF Tables Extractor is a Google application designed to extract tables from a PDF file. It extracts all tables in the file, making sure it retains the row and column structure of the tables.

It then converts the file into a CSV format that you can directly edit. It also displays the extracted (and edited) table in the form of an HTML table within the browser. You can easily copy this table into Google Sheets directly.

This app can be used to extract tables from PDF files both on your Google drive and well as your local hard disk.

Here are the steps that you need to follow in order to use the PDF Tables Extractor to convert your PDF file to Google Sheets:

1. Open the PDF file in Google Drive
2. Click on the button on top that says ‘Open with’.
3. In the dropdown menu that appears, select the ‘+Connect more apps’ option.
4. This will open the Google Workspace Marketplace, from where you can find the apps you need.
5. In the search bar on top, type ‘pdf table extractor’.
6. Select the PDF Table ExtractorApp from the app options that appear.
7. Click on the Install button.
8. You will be asked for permission to install PDF Tables Extractor. Click Continue.
9. You will then be asked to confirm if you want to allow PDF Extractor to add itself to Google Drive. Click Allow.
10. You will now be asked to confirm if you want to view and manage Google Drive files and folders that you have opened or created with this app. Click Allow.
11. Click Allow once more.
12. You should now see a message at the bottom left of the browser window that says ‘PDF Tables Extractor has been connected’.
13. Once again click on the button on top that says ‘Open with’.
14. From the dropdown menu that appears, this time select PDF table extractor.
15. The application should now open.
16. On the left side of the window, click on the button that says ‘Open file from Google drive’.
17. Select the PDF file that you need to convert, press Select and wait for the file to get loaded into the application.
18. You will see the name of the file on top of the application window when it is done loading. The ‘Stop’ button will also get disabled.
19. Once the file has been loaded into the application, click on the button that says ‘Start, Extract Tables’.
20. This will convert your PDF file to CSV format. When processing is done, the Stop button will again become disabled and you will see the word ‘Completed’ appear in green next to the Stop button.
21. You will also see the HTML version of the file in an HTML table format below.
22. You can now go ahead and copy this table, after which you can paste it into Google Sheets directly.

Using Online2pdf to Convert PDF to Google Sheets

Online2pdf is a free online tool that lets you edit, convert, and compress PDF files. The great thing about this tool is that it can work with both native as well as scanned PDF files.

This means whether the contents are in table format or in plaintext, the tool will help you quickly extract, convert and export it into Google Sheets.

Here are the steps that you need to follow in order to use Online2pdf to convert your PDF file to Google Sheets:

1. From your browser, go to https://online2pdf.com/
2. Click on the Select Files button.
3. Navigate to the folder on your hard disk that contains the PDF file you want to convert.
4. Double click the name of the file.
5. This will import the file into the web application.
6. Now from the dropdown next to ‘convert to:’, select ‘Excel 2007-2019(*.xlsx)’.
7. Click on the Convert button.
8. Wait for the file to be uploaded and processed.
9. Once the processing is done, the download of the file will start automatically.
10. Save the file to your hard disk.
11. Once you are done downloading the .xlsx file, you can go ahead and upload it into Google Sheets.

Besides this, there are a number of other online tools available to help you quickly convert your PDF files to a format usable and editable in Google Sheets.

Some of these other sites include Convertpdf2excel (https://pdfchef.com/pdf-to-excel.html), Altoconvertpdftoexcel (https://altoconvertpdftoexcel.com/) and many more.

A quick Google search will give you a number of other options. You can select the platform that you are most comfortable with and get your job done in a jiffy.

We hope this tutorial was helpful for you.

Source: https://productivityspot.com/convert-pdf-to-google-sheets/

Honorable Mentions:

PDF Table Importer


https://workspace.google.com/marketplace/app/pdf_table_importer/646940040599