This page explains how to use an excel file read PDF to excel with VBA code that allows you to convert many PDF files to Excel. To use the read PDF to Excel and format a PDF file into a usable excel file you should open the file below that a number of different various macros.  The read PDF to excel macro sets deal with different types of problems that can arise when copying from PDF to Excel using alternative macros. Unlike other PDF to excel programs that you can purchase, all of the macros are provided on an open source basis. The conversions from PDF to Excel are geared to financial modelling applications where you want the data in excel. When using the excel model with macros to convert, you should first copy and paste special the stuff you want from the PDF file (this can be straight from google chrome or from adobe).  When you paste, paste special as unicode text. You do not have to download a PDF file, but you can take it directly from the internet (not with the internet explorer). A step-by-step example of how to use the read pdf program is included in the case studies page.

Please note that when you run the read pdf to excel program, it does not work perfectly every time because of the vagaries in the way data comes into excel from pdf programs.  I hope you do not give up when this happens. I think I have been able to solve very many of the practical problems, but not every single one.  If you are having problems, please do not hesitate to send me an e-mail at edwardbodmer@gmail.com.  An example of a more complex example is included at on a subsequent page. https://edbodmer.com/complex-a-z-example/. To re-format pdf’s, click on the button below and then download the file.  Make sure that the status bar at the bottom of your excel file has the Shift, CNTL A instructions.  This status bar should show up in other excel files that you move to.

Read PDF to Excel File that Allows you to Format Data After Copying from PDF File (Press Shift, Cntl, A)

Asian Version of Excel File the Reads PDF (or Internet) to Excel with Macros that are Implemented with SHIFT CNTL Afor

File with Macros that Format Data Copied into and Excel File from the U.S.Securities and Exchange Website

On this page I walk through different problems and issues that arise when copying data from a PDF file and pasting it to an Excel file.

1. The first case is the most common situation where the PDF file is compressed into rows of data with multiple values. The data appear in excel without separation into different columns.
2. The second case is the situation where each piece of data is in a separate row. In this case there is one data value and the data is not compressed into a single column.
3. The third situation describes correcting the situation where data has an elegant European format with spaces instead of commas and a comma instead of decimals.
4. Other issues are addressed include fixing titles after you have re-formatted the data.

Basic Read PDF to Excel – Multiple Data and Words Compressed into One Column for Each Line

In any of the cases for formatting data, the excel file “read_pdf_to_excel.xls” must be open and make sure you enable macros.  Then, with the file open you copy and paste data from a PDF file to your blank excel sheet (best into cell A1).  After the data is there, you press SHIFT, CNTL, A. Normally, the data should be re-formatted and you should be happy.

Example PDF file with Normal Conversion Format (Sun Edison 2013 Report -- Numbers on Page 17 or F1)

Sometimes, you may get repetitive rows after you press the green button. In this case you can re-do the SHIFT, CNTL, A from the raw data page — make sure you go back to the page with the messy stuff.  But before pressing the green button, press the little yellow button named CONVERSIONS AND ADJUSTMENTS.  Then, click the COMBINE ROWS check box.  After doing this, press exit and the press the green button again.  The output should the be cleaned.

.

.

Videos Illustrating the Read PDF Process in the Basic Case

.

Note: If the read PDF is not working from Firefox, it may work better using Google Chrome and then pasting with unicode text (sorry about this).

Cleaning Up PDF Data in Excel When each Data Item is in a Separate Row in a Single Column

Sometimes when you copy data into excel it appears in a format where each data item is in a separate row as shown in the screenshot below. As long as the number of rows are consistent, then the copied data can be cleaned up.  You can do this by pressing the second rather than the first green button after you press the Shift, CNTL, A sequence that has a title of single pieces of data in separate rows.  After you press this button, a second screen that looks something like the screenshot below will appear (I say something like, because I change the program more often than I change the website).

– You have the problem walk down the rows.  As soon as there is text, the program will begin a new row and then put the data after the text. This works if there is text, then data, then text etc. like the example below.  In this case, DO NOT check any of the boxes in the screen that appears.

– You can have the program walk down a fixed number of rows.  This works when the data is not interrupted by text.  For example if the PDF has 31 rows (like in the complex example on the next page), you can enter the number 31 and after the number of rows is hit, the data is put into a second column.  In this case use the first check box in the screen above.

– You can have the program automatically start a new column of data as soon as a year (like 2018) appears in the data. In this case, check the box named “Break with Years”

In all of these cases,  after you run the read pdf program using this method, the data is transposed from rows to columns.

' Here define the start year and end year for testing
' careful with this
' add a test with mod cells(row,1) mod 1 = 0
' Note if you are using the year and you have more historic years you can adjust this


If use_year = True And Cells(Row, 1) > 2000 And Cells(Row, 1) < 2040 Then ' this tests if year -- if year make a string
year_text = "'" & Cells(Row, 1)
Cells(Row, 1) = year_text
End If

.

Read PDF to Excel with Titles Adjustments

I have struggled with getting the macros to correctly adjust the titles in the read pdf to excel file after the you process the information.  The video below explains how you can use the SHIFT, CNTL, T short-cut to access a menu that allows you to fix the titles.  The video also illustrates how to get data from excel into Power BI after you have read in the data.

Reading PDF Files that are Locked with Passwords

The PDF to Excel videos are summarised below. To watch the video, just click on the link