We often have to work with tables & datasets shared as a PDF file.
I’m talking about those data tables, invoices, receipts, financial statements, bills and more that you often receive as email attachments.
PDFs are great for sharing data. But you just can’t edit, modify or process any data shared in a PDF file. Spreadsheet programs like Google Sheets are ideal for viewing & organizing such data (especially tabular data).
But sadly, Google Sheets doesn’t readily support PDF files – there’s just no direct way to open a PDF on Google Sheets!
So how do you convert a PDF file to a format that can be read on Google Sheets?
In this article we look at 6 approaches to export data stored in PDF to Google Sheets.
If you’re looking to convert business PDF documents in bulk to Google Sheets without loss of format, skip to the last method – we’ve saved the best for last – and it’s completely automated!
Want to convert PDF files to Google Sheets ? Check out Nanonets' free PDF to CSV converter. Find out how to automate your entire PDF to Google Sheets workflow with Nanonets.
Table of Contents
- Automated PDF to Google Sheets Conversion (HIGHLY RECOMMENDED)
- PDF to Google Sheets using the CSV format
- PDF to Google Sheets using Adobe & Microsoft applications
- Conclusion
Want to convert PDF files to Google Sheets ? Check out Nanonets' free PDF to CSV converter. Find out how to automate your entire PDF to Google Sheets workflow with Nanonets.
Open PDF on Google Docs and copy to Google Sheets
Here's a quick way to convert PDF to Google Sheets, assuming the table/data is basic.
Let's assume you have a simple/plain PDF like the one below:
We'll also check how this method fares with PDFs that contain more complex tabular data.
- Open your Google Drive
- Click on "New" and then "File upload"
- Select and upload your PDF file
- Open the PDF file in Google Drive, once the upload is complete
- Click on "Open with" and then "Google Docs"
This is what it would look like on Google Docs:
- Select the table and copy it: Cmd/Ctrl + C
- Open a new Google Sheets file
- Select a cell and paste the copied table: Cmd/Ctrl + V
And you're done.
Here's the table from the PDF now neatly displayed in a Google Sheets file.
But let's try a more realistic PDF file.
A PDF with a more complex table structure – something that you're more likely to come across in the real world.
Like for example, an invoice from Walter White to "Los Pollos Hermanos" for services rendered.
As you can see above, this simple copy paste approach via Google Docs doesn't always work quite well.
When dealing with more complex table data in PDFs, the conversion to Google Sheets can get messy.
This approach to converting PDFs to Google Sheets is ideal for PDFs with simple tabular data. Complex table structures or borderless tables with plain text will not be neatly converted in this method.
The best way to deal with plain text or borderless tables is to first convert the plain text into CSV. Let's look at that next.
Want to convert PDF files to Google Sheets ? Check out Nanonets' free PDF to CSV converter. Find out how to automate your entire PDF to Google Sheets workflow with Nanonets like shown below.
Convert PDF to CSV manually on Google Docs and import to Google Sheets
This method is going to require much more manual effort than the previous approach.
We will manually convert the PDF file into a CSV file and then import the same to Google Sheets. CSV is a delimited text format that can represent tabular data using plain text.
In CSV files, all text is organized into rows and columns that are separated by commas – hence the name comma-separated values (CSV).
Here are the steps to convert PDF to CSV and then to Google Sheets:
- Open your Google Drive
- Click on "New" and then "File upload"
- Select and upload your PDF file
- Open the PDF file in Google Drive, once the upload is complete
- Click on "Open with" and then "Google Docs"
If you already see data in a comma-separated format at this stage (like the image below), then please skip ahead.
If not, you will need to manually add commas to organize the data.
Here's how to convert plain textual data to a CSV format:
- On the Google Doc press Ctrl + F
- Click on the 3 vertical dots on the small window that pops up
- Enter a space in “Find”
- Enter a comma in the “Replace with”
- Click on "Replace all" – this will replace all spaces with commas
The data is now represented in a CSV format, let's now save the data as a CSV file.
- Click "File" and "Download" and "Plaintext(.txt)
- Open this .txt file in a plain text editor
- Click "File" and "Save as" and type ".csv" in the end of the file name
- Click "Save"
You now have your PDF data saved as a CSV file.
- Open a new Google Sheets file
- Click "File" and "Import"
- Click "Upload" and select the CSV file that you just saved
- The Google Sheets file will now show the tabular data correctly
Here are screenshots of some of the steps mentioned above:
The table has been captured pretty well on Google Sheets.
Some things to note:
- This process needs to be done for one file at a time
- Considerable manual effort might be required to clean up data
- Adding commas when no space is provided between two distinct values
- Removing excess spaces, characters & other formatting issues/errors
- Dealing with existing commas within values, here are some examples
- 950k represented as 950,000
- decimal commas – values represented as 0,1111 (esp. in European countries)
- commas in addresses – no. 23, ABC street, Hamburg, Germany
While the manual CSV approach maintains the table structure while converting PDFs to Google Sheets, it is time-consuming and difficult to scale. Complex table structures with split columns and common headers might require more manual effort to reconcile with the CSV format.
One way to avoid all this effort in converting a PDF to CSV is to check out online converters that can do it for you.
Want to convert PDF files to Google Sheets ? Check out Nanonets' free PDF to CSV converter. Find out how to automate your entire PDF to Google Sheets workflow with Nanonets by clicking below:
Use online PDF to CSV converters and import to Google Sheets
Online converters can convert PDF to CSV or PDF to Excel in seconds.
This is really convenient when you need to convert many PDF files (or multi page PDF files) to Google Sheets.
There are many online converters like Zamzar, Convertio, PDFTables, Aconvert & even Nanonets' own online PDF to CSV tool. You can check out any of these.
Here's how you can convert PDF files to CSV using online converters:
- Select one of the many online PDF to CSV converters
- Add or upload your PDF files
- Click "Convert" to convert the PDFs to CSV
- Then download or export the converted files
That's it.
Here's a quick video demo using the Nanonets PDF to CSV converter:
Online converters give mixed results while converting PDF to CSV.
Some are great, others not so much.
Some can only handle native PDFs, while others also support scanned PDFs. (Nanonets' converter can handle both native & scanned PDFs)
Some support batch conversion while others don't. And only few offer APIs.
While online PDF to CSV converters are a great improvement over manual conversion, they are still not quite perfect. You'd still need to do minor cleanups after converting your PDF to Google Sheets!
Another alternative to online converters is to use popular document processing applications like Adobe or Microsoft.
Want to convert PDF files to Google Sheets ? Check out Nanonets' free PDF to CSV converter. Find out how to automate your entire PDF to Google Sheets workflow with Nanonets by clicking below:
Export PDF as Excel on Adobe Acrobat DC and import to Google Sheets
Adobe Acrobat DC allows users to export PDF files as multiple other file types including Excel.
And Google Sheets supports the Excel file type.
And since Adobe is a native PDF reading application, it just might give better results with respect maintaining formatting and table structures.
Here are the steps:
- Open your PDF on Adobe Acrobat Pro
- Click "File" and "Export To" and "Spreadsheet" and "Microsoft Excel Workbook"
- Import this Excel file to Google Sheets
Here's a very basic demo:
While this method might help maintain original formatting in most cases, it doesn't support batch conversion. And complex table structures might not be easily captured in this method.
Let's now see how Microsoft Excel handles PDFs.
Want to convert PDF files to Google Sheets ? Check out Nanonets' free PDF to CSV converter. Find out how to automate your entire PDF to Google Sheets workflow with Nanonets by clicking below:
Import PDF as Excel on Microsoft Excel and import to Google Sheets
You already know that Google Sheets support Excel files.
Microsoft Excel allows users to import/open PDF files within its spreadsheet application.
- Open a blank/new Microsoft Excel Workbook
- Click "Data" and "Get Data" and "From File" and "From PDF"
- Select your PDF file to import it to Excel
- Select the table(s) you want to extract and click "Load"
You will now see the tabular data on your Excel file.
- Finally import this Excel file to Google Sheets
Here's a quick basic demo from Microsoft expert Kevin Stratvert:
While this is a pretty neat method might help maintain original formatting in most cases, it doesn't support batch conversion. And complex table structures might not be easily captured in this method.
Want to convert PDF files to Google Sheets ? Check out Nanonets' free PDF to CSV converter. Find out how to automate your entire PDF to Google Sheets workflow with Nanonets by clicking below:
Automated PDF to Google Sheets Conversion
All the methods we've seen so far to convert PDF to Google Sheets have required varying levels of manual intervention.
Not to mention that different methods give varying results with respect to data capture and formatting.
Also, none of the previous 5 methods can handle bulk conversion!
That's where Nanonets can help!
Nanonets is an AI-based OCR software that helps businesses optimize & automate their workflows. (Check out this neat example of how Nanonets automated a PDF renaming workflow)
Hook up Nanonets OCR to Google Sheets using Zapier and you're all set with an automated PDF to Google Sheets workflow. Nanonets can handle complex table structures, bulk uploads, validations, ERP integrations and much more.
Follow the steps below:
- Sign up on Nanonets
- Select a pre-trained model or build a custom OCR model
- Set up this Zap correctly
And you're good to go
If you'd like to talk to our automation experts, set up a call today, and see Nanonets live in action:
Nanonets has advanced table extraction capabilities. Check out the video below:
Conclusion
In this article, we have looked at different ways to convert PDF to Google Sheets.
We also looked at how Nanonets can help automate any PDF to Google Sheets workflow.
- &
- 11
- About
- across
- Action
- advanced
- All
- already
- APIs
- Application
- applications
- approach
- article
- Automated
- Automation
- background
- BEST
- Bills
- border
- build
- business
- businesses
- call
- Can Get
- capabilities
- cases
- Common
- complex
- Convenient
- Conversion
- countries
- data
- dc
- deal
- dealing
- different
- documents
- easily
- especially
- European
- example
- Excel
- experts
- financial
- First
- format
- Free
- going
- good
- great
- help
- helps
- here
- highly
- How
- How To
- HTTPS
- image
- Including
- integrations
- IT
- looked
- looking
- manual
- manually
- Microsoft
- mixed
- model
- most
- offer
- online
- open
- organizing
- Other
- Popular
- press
- pretty
- process
- Programs
- RE
- Reading
- real world
- receive
- require
- required
- Results
- Scale
- Services
- set
- shared
- Simple
- small
- So
- Software
- something
- Space
- spaces
- split
- Stage
- started
- statements
- street
- support
- Supports
- Talk
- talking
- time-consuming
- today
- use
- users
- Video
- What
- within
- without
- Work
- world
- youtube
