Being able to convert PDF data to a spreadsheet is an important way to streamline business operations.
This automation saves time and reduces manual data entry errors. It's particularly useful for businesses and individuals who receive data in PDF format but need to use it in a spreadsheet for analysis and operations.
We'll cover three different ways Lido can extract data from a PDF to a spreadsheet.
We will use Lido, a new spreadsheet built to automate repetitive tasks, to extract our tabular data from a PDF into a spreadsheet. There is a free tier with paid plans as well.
You can create a new account here: https://www.lido.app/go/signup
From the Lido Files page, click New File
This method will extract all data that Lido can identify as tabular data in a file. This can be multiple tables of different formats.
For example, all of the tables in the following PDF will be extracted into a Lido spreadsheet, but the two stand-alone cell of data will not be extracted:
Pick any cell (lets say A1) and start typing the EXTRACTTABLESFROMPDF formula. The formula format is:
=EXTRACTTABLESFROMPDF(<drive credential>,<file path to pdf>,<output cell>)
If this is your first time using Lido, you will likely need to add a credential to allow Lido access your Google Drive.
In the first argument of the formula for EXTRACTTABLESFROMPDF, choose “Add Credential” and follow the prompts.
For the 2nd argument of the formula, use the file picker to choose the PDF file that is on your drive. Make sure that the credential you used has access to the PDF file.
For the last argument in the formula, pick a cell in the spreadsheet (lets say A3) where you would like Lido to put the extracted data from your PDF. Make sure there is no data in your spreadsheet below A3 that might be overwritten by the new data.
So the full formula for the example we're using looks like:
```=EXTRACTTABLESFROMPDF(<drive-credential>,"https://drive.google.com/file/d/1As7PgjeKApwIqv16GCBsV3lT3-XqTEKb/view?usp=drive_web",A3)
Choose “Run Action” from the cell menu for the cell that has EXTRACTTABLESFROMPDF in it. This will run the formula and extract your data.
^^^
info
Action Formula
Action formulas are a special kind of formula in Lido. Unlike regular spreadsheet formulas which evaluate as soon as you type them, action formulas need to be "run" explicitly because they change external information such as sending emails, extracting data, and posting to APIs. Examples of action formulas include SENDGMAIL, EXTRACTTABLESFROMPDF, SENDSMS, and CALLURL.
^^^
You should now see your data from your PDF in your Lido spreadsheet table starting at the cell you specified as the output cell.
The second method of converting a PDF to a spreadsheet in Lido will take all data, tabular or not.
For example, all data in the following PDF will be extracted into a Lido spreadsheet:
Pick any cell (lets say A1) and start typing the IMPORTPDF formula. The formula format is:
=IMPORTPDF(<drive credential>,<file path to pdf>,<output cell>)
If this is your first time using Lido, you will likely need to add a credential to allow Lido access your Google Drive.
In the first argument of the formula for IMPORTPDF, choose “Add Credential” and follow the prompts.
For the 2nd argument of the formula, use the file picker to choose the PDF file that is on your drive. Make sure that the credential you used has access to the PDF file.
For the last argument in the formula, pick a cell in the spreadsheet (lets say A3) where you would like Lido to put the extracted data from your PDF. Make sure there is no data in your spreadsheet below A3 that might be overwritten by the new data.
So the full formula for the example we're using looks like:
```=IMPORTPDF(<drive-credential>,"https://drive.google.com/file/d/1As7PgjeKApwIqv16GCBsV3lT3-XqTEKb/view?usp=drive_web",A3)
Choose “Run Action” from the cell menu for the cell that has IMPORTPDF in it. This will run the formula and extract your data.
The third method of converting a PDF to a spreadsheet in Lido is to visually select a range of cells to import as data into Lido.
Click on the File menu, choose "Import from PDF" and select your PDF file.
Use the visual rectangle tool to select the range of the spreadsheet that you want to import. Click on "Extract Data"
Confirm that the data is correct and choose "Insert at Active Cell"
Your data will now be added to the Lido spreadsheet at the active cell.
We've shown you how you can use three methods to convert a PDF to a spreadsheet.
