Home > Forum Home > Developing Business Administration Solutions > Importing Data from PDF Files Share

Importing Data from PDF Files

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


Forum TopicPost Reply Login

Importing Data From Pdf Files

Rate this:
(4.2/5 from 24 votes)
Happy 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 on
 
Replies - Displaying 51 to 60 of 88Order Replies By: Most Recent | Chronological | Highest Rated
Confused
Confused
Rate this:
(3/5 from 1 vote)
In my opinion that is what its not doing.

This excel VBA code or the pdftotext.exe doesnt know how to not overwrite cells that have data on it. It doesnt matter if the code to delete the data is commented out because every time the program simply starts from the beginning, it reads every PDF file from the beginning, that is intended, but at the same time it overwrites the old data because it was not programmed to resume writing from the first empty cell.

While running the extraction command it knows how to copy data of each pdf file to a new row, but after the extraction process is over it forgets where it left. Next time you run the extraction, it starts from the beginning of the sheet overwriting the old data. That is how it runs because it was intended that way.

I have tried to change the mrow variable VBA code so that it would find the first empty cell, but every time I have run errors on the following command line and that is where I drop off.

VBA Code:
Call Run_Replacements(CStr(Cells(mrow, (j + 3)).Address), CStr(arrTmp(j, 2)))
 Posted by on
Confused
Thanks!
Rate this:
(3/5 from 1 vote)
Hello, thanks!

This seems to be working! Highly appreciated! 
 Posted by on
Confused
Confused
Rate this:
(3/5 from 1 vote)
Hi, It isnt still working as I want it to, if I delete the pdf files it has already extracted it overwrites the old data again because it returns to write from the beginning. How it can be made to find the next free cell?
 Posted by on
Confused
Removing tabs and spaces
Rate this:
(3/5 from 1 vote)
This is a great tool if i can figure out how to do a couple of things.  Each month I have sales reports for every day the Bar was open.  These reports are PDF files.  Below is a excert from the txt file that was created.  

101 - System Tracking
 Food                            27              216.29        Emp Disc 50%                 0                0.00                                     0              0.00
 Liquor                         123              452.89        Manager Comp Open            0                0.00                                     0              0.00
 Beer                            59              178.81        Open Food Disc               0                0.00      Gift Cert Redeemed             0              0.00
 Daquiri                        131              690.99        Mgr Comp 100%                1              -93.08      Emp Charge                     0              0.00
 T-Shirt                          0                0.00        Mgr Comp 50%                 0                0.00      Total Other Payments                          0.00

I need the following in a spreadsheet.
Columns       Results
Food            216.29
Liquor          452.89
Beer            178.81
Daquiri        690.99
T-Shirt            0.00

Please let me know the best way to get these results.  THank you for your help and for creating a great tool. 
 Andy
 Posted by on
Confused
Need help on pulling multiple instances
Rate this:
(3/5 from 1 vote)
This is excellent utility - thanks for sharing this in open forum. really lot of value. The issue I have is on pulling multiple instances across pages of pdf. I did read thru different upgrades / responses from 2012 but not able to pinpoint on how exactly to do this. This is not in tabular format but has clear qualifiers "contractor name", "rate"

Can you plz help?

 Posted by on
Confused
How to extract I didn't understand
Rate this:
(3/5 from 1 vote)
Sir,
I have pdf files, the fields are in rowwise. I am a layman.
Will you explain in detail how to extract the data.
I read all posts in this site but I could not able to understand. 
I read the readme file in the folder, but I didn't understand the procedure.

So, please explain very easily. You can reply to my mail also, because the explation would be lengthly.

Thanks,
BMVLU 
 Posted by on
Confused
Importing Two rows
Rate this:
(3/5 from 1 vote)
Thank you very much for this tool I need to get data from 5000 invoices and have one bit that I cant work out. My data appears on two lines the PDF as follows:

 (line 1)Invoice for
(line2)Partial Private Circuit Charges (123456789)

I need to extract line 2 and it always occurs on the line that follows "Invoice for" but the ending text is volatile. Please help
 Posted by on
Fedup
Email sample
Rate this:
(3/5 from 1 vote)
You can attach a sample PDF by replying to the notification email with details of desired extraction of this post and we can look into it.
 Excel Business Forums Administrator
 Posted by on
Oops
Formatting issues and spaces
Rate this:
(3/5 from 1 vote)
There are several issues to address here so let's cover them individually.
  1. The date formatting from number ranges.  One way is to retain some common preceding text such as "Range:". If this is not possible then we can add some replacement pairs from 1 to 9 with adding an apostrophe to stop Excel converting the text to a date.
  2. Extra spaces. We can remove multiple spaces by using replacement pairs specifying a double space to be found and a single space to be the replacement.
  3. The name of the school.  Sometimes we can get the initial text in a row by specifying a common text which occurs at the end of the row above. Sometimes this can be further above with wildcard replacement pairs to remove everything in between.  Text formatting is irrelevant as the conversion creates plain text before being interrogated.
  4. Personnel. It is usually best to first extract as much text as needed and then clean using rules defined in the replacement pairs.  Sometimes we need to be quite creative here.
I hope that this helps.
 Excel Business Forums Administrator
 Posted by on
Shocked
Data overwriting issue
Rate this:
(3/5 from 1 vote)
The output data is emptied before each extraction process simply under the assumption that the data has already been used. This can either be turned off by commenting out the VBA code that does this, or data can be copied out to a separate workbook for saving after each process is run. 
 Excel Business Forums Administrator
 Posted by on
 Displaying page 6 of 9 

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

Solutions: Export MapPoint Waypoints Survey Data Analysis

© Business Spreadsheets   Legal   Follow us: