Importing Data from PDF Files

Excel Help for Importing Data From Pdf Files in Developing Business Administration Solutions

Forum Topic

Post Reply Login

Importing Data From Pdf Files

Rate this:

(4.2/5 from 24 votes)

	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. [Download the free PDF data import Excel program here] 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.
	Excel Business Forums Administrator
	Posted by Excel Helper on 17 Jan 2011

Replies - Displaying 71 to 80 of 88

Order Replies By: Most Recent | Chronological | Highest Rated

	Another New Version Rate this: (3/5 from 1 vote) Thanks again for the feedback. There is another new version from the same link which should now list multiple instances for text found in each PDF file without the need for a separate table. This was actually quite some change to the underlying code with the upside that it should now also be faster by processing each PDF file text only once and holding the patterns and replacements in memory. One thing to note id that the beginning text for each replicated pattern should be first in the list of patterns. This has been specified in the cell comment for Start Text. This all worked in the tests but you feedback will again be greatly appreciated.
	Excel Business Forums Administrator
	Posted by Excel Helper on 22 Feb 2012

	Combined results separator Rate this: (3/5 from 1 vote) Thank you oteacher for this feedback. The pipe character was used to separate combined results in memory during the process. This obviously does not work when there are pipe characters in the content. We changed this separator to three characters ^^^ in the now updated download. This is under the assumptions that this string would be highly unlikely in the content. We look forward to your testing results.
	Excel Business Forums Administrator
	Posted by Excel Helper on 22 Feb 2012

	Getting row below headings Rate this: (3/5 from 1 vote) 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.
	Excel Business Forums Administrator
	Posted by Excel Helper on 05 Dec 2012

	Download version Rate this: (3/5 from 1 vote) I just checked the download and it is the latest version. What seems to be missing? The Xpdf program is unmodified and used only to convert PDF conent to text format before the VBA code analyzes and extracts from it.
	Excel Business Forums Administrator
	Posted by Excel Helper on 22 Feb 2013

	Start Text and new line as end text Rate this: (3/5 from 1 vote) In this case it looks like "Total Due" should be the start text and the end text a new line. Otherwise, if you want the invoice number then use that as a pattern and then multiple wild card replacements to remove everything between that and the amount.
	Excel Business Forums Administrator
	Posted by Excel Helper on 01 Jun 2014

	Multiple column content extraction Rate this: (3/5 from 1 vote) We can extract multiple columns of data in one text block and then use the Text to Columns feature in Excel to separate the data into cells.
	Excel Business Forums Administrator
	Posted by Excel Helper on 21 Apr 2015

	Importing file location Rate this: (3/5 from 1 vote) The importing routine processes all PDF files in the same folder for which the Excel file and extraction tool reside. This was to keep the solution simple but of course the code can be altered to change the target directory. The workaround is to create a new folder and move/copy all PDF files as well as Excel and .exe to it for processing.
	Excel Business Forums Administrator
	Posted by Excel Helper on 03 Nov 2015

	Multiple Page PDFs Rate this: (3/5 from 1 vote) The PDF files can be multiple pages and the resulting text extracted and analyzed includes the entire PDF content. If your data is on the last page, you'll need to make sure that the start and end text for extraction is unique to that page so that isolates it in the extraction.
	Excel Business Forums Administrator
	Posted by Excel Helper on 15 Nov 2015

	Modified version for appending results Rate this: (3/5 from 1 vote) To be sure, we have modified the Excel file for importing the PDF data to include an option for appending results. We have tested this working with the test PDF files in the zip file. The new version can be downloaded from the same link in the original post above.
	Excel Business Forums Administrator
	Posted by Excel Helper on 19 Aug 2013

	Using the tabs to align data Rate this: (3/5 from 1 vote) In the example for the text file, it seems you want the third column of data for each product type. In this case, you can specify the start texts as the items required (e.g. 'Food'). The end text can be a new line. When the data comes in as one column, we can use the text to columns command in Excel to split the data out with the tab delimiter and then get all data lined up next to the items.
	Excel Business Forums Administrator
	Posted by Excel Helper on 27 Nov 2013

Displaying page 8 of 9

Excel templates and solutions matched for Importing Data from PDF Files:

Solutions: Export MapPoint Waypoints Survey Data Analysis