Business Spreadsheets has developed a free Excel program to extract and import PDF data into Excel which can be downloaded and used without restriction.
There is a common need to extract and import specific data from PDF files into Excel. Since Excel does not natively support the reading of PDF content, utilities are needed to convert the PDF file content for the Excel format. Several commercial applications accomplish this; however it is often the case where only specific data is required to be imported from multiple PDF files into one structured format.
We created such an application by using VBA code in conjunction with an open source PDF to Text conversion utility, which can be found at Foolabs.
Update: 19-Feb-2012 A new version also extracts multiple instances of the same data matching pattern from one or more PDF files.
The program relies on the conversion utility (included in the download) and all PDF files to reside in the same directory as the Excel application. Text or data to extract are defined in the Control sheet by specifying start text, end text and multiple replacements routines with wildcard support. This enables flexibility to obtain comparable data from multiple PDF files based on patterns independent of different PDF file structures.
As many extraction rules as required can be set in order to create a table of information imported by extraction rule and PDF file name. Information on how to set up rules is available within the Excel application with a help icon and cell comments. The VBA code is commented and open for modification.
Any improvements or new features to the code are welcome to be posted here so that we can update the download version to the benefit of everyone.
The handling of differing column widths depends a lot on the PDF content.
If you're able to get the data into the Excel output cleanly, then the best option would be to tidy it thereafter. Options for this include the Text to Columns feature in Excel using spaces or tabs as the delimiter, and the TRIM function to remove white space.
Really great tool. My case is a little bit different. I need to rename lots of pdf files based on the first line. How should I modify the code to have as START point the beginning of the pdf as it is not always the same? Thank you.
OK. The separation chaacter string used in the code to build the arrays of matching text is the '^^^' on the assumption that this is unlikely to be found within the content itself.
The pipe character '|' is still used to specify replacement pairs as this is independent of the source text extraction and used only to define find and replace patterns in order to clean the output as necessary.
I hope that this clarifies and if you have any ideas for improvement then we can look to implement.
Thanks for your reply! I was looking through the comments, and it has been mentioned that the pipe character has been replaced with ^^^. Which does not seem to be the case for the version mentioned above. (Eventhough this is not very difficult to change..).
So I thought it was not the updated version, and was afraid it might be missing some other functions.
Hello! Thanks for this program, it runs pretty great. But the downloadable version doesn't seem to be the newest version as describe in the comments. Could anybody verify this? And if not, is the newest version still available?
Also one more question: How much has been changed in the Xpdf program? or the program reliace on the original unedited version?
Since the text search relies on the starting text and ending text, a row of data underneath column headings can be extracted by specifying the last heading as the start text and the first text below the row required as the end text.
Rows of extracted data can be then transformed to columns via the 'Text to Columns' command in Excel.
I love the file and it works for the pdf's that you gave as an example. I am trying to use it for pdf's of bank statements. I have another file with macros that reconciles bank activity to GL ledger activity and marks with an x anything that reconciles leaving your reconciling items. The issue I have is we only get bank statement activity in PDF format. Your file and code would work perfectly if I could use the control text tab and place the column headings as text to search for in the pdf and it drops in all the text details on the pdf below that column heading? I am not sure if it is possible since it is searching for text and this is on the lines below the text. Any suggestions?