I have a pdf file which which I can copy data manually.
My question is it possible to extract specific data from a pdf with vba .
e.g box 26 the right part ,27,28 to 42 and so on.the data is around 300 pages
the headers will be item no, commodity code and so on at row 1 and row 2 will be the data 002 ,15091000 under their respective columns .
Attached sample results expected
Last edited by JEAN1972; 10-27-2024 at 02:41 PM. Reason: files
You could open the PDF in Word, then process it there. The problem is that the PDF>Word conversion process generates two different table layouts for your data, none of which line up correctly. Accordingly, you'd need to code the extraction process separately for each data layout. Here is some code to get you started:
The above code looks for the 'Marks & Numbers' lines and splits the content into separate tables at that point. As coded, the macro stops after creating 10 tables - that's just so you can see what the issues are. If you want to turn all of the reports into tables for further processing, you could move the line:
Please Login or Register to view this content.
If t = 10 Then GoTo ErrExit
so that it follows:
End Select
This will allow you to test whatever code you develop for extracting the data from the odd & even tables the code creates. Once, you're satisfied, you can delete that line and uncomment the:
'.Close
and:
'Quit
lines.
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
This exctracts all 150 pages into 1 sheet in Excel....need adobe installed though...Re: extract specific data from pdf file to excel using vba
Then your next step would be to loop all the"25 Marks & Numbers" areas and extract the required data via some super duper code...
Edit...
@ John...Tx for your contribution...
@Jean...So this is the complete code...
Please Login or Register to view this content.Please Login or Register to view this content.
Just one question...Your result for Last Column first entry shows both rows as 4529 but the data shows 4529 & below DVPlease Login or Register to view this content.
Last edited by Sintek; 10-29-2024 at 03:03 AM.
Good Luck...
I don't presume to know what I am doing, however, just like you, I too started somewhere...
One-day, One-problem at a time!!!
If you feel I have helped, please click on the [★ Add Reputation] to left of post window...
Also....Add a comment if you like!!!!
And remember...Mark Thread as Solved...
Excel Forum Rocks!!!
Building on Sintek's code (which I am sure he will refine:
Please Login or Register to view this content.
Last edited by JohnTopley; 10-28-2024 at 10:38 AM.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
@Sintex: under "Other Costs" I see there are the "odd" text values in the data.
@John...please clarify...
See Sheet1 row 1950 : POWDER 2*30*400G 0|
I didn't extract that far to test further...Will look into it...Tx John...
I see there is a irregular entry of the PDF not same as others...
There are a couple of entries like this...BEVERAGE PREPARATIONS 37 Other Costs CC 38 Supp U1 39 Supp U2
POWDER 2*30*400G 0|
I assume that that snippet prior to the value must become part of the Product description...Above amended hopefully solves...
Last edited by Sintek; 10-28-2024 at 12:52 PM.
Sintek/ John , thanks for your help. While testing with originalpdf loaded, ok but issues come loading same format but different pdf file name
However , when changing pdf file renaming it to original pdf file as loaded on forumn, got some debug issues .
Reload another pdf same format but different data entry but get debug error . could you check where it going wrong.
This is in guru Sintek's court ... way above my pay grade
Mine too it seems lolway above my pay grade
Jean...I encounter the same issue...Works perfectly with Sample PDF, but not with new sample provided...what is different...Is this new sample protected in some way?
I notice that after the first paste...something happens in the pdf as if it is doing some kind of processing in the background and this stops the process from scrolling down......
Edit...
Most definately something happening in background that did not happen with other file...
I increased the pause and it solved...Although not ideal...
By the way Jean...This data layout is also different to original upload...Not to sure how one would make provision for all eventualities...Even the no of entries in the transaction table vary...Please Login or Register to view this content.
Good luck on getting this resolved...
Last edited by Sintek; 10-30-2024 at 02:25 AM.
I am not sure why I have been approched to have a look at this, but I tried to import tables from the PDF and it crashed my Excel (just for information).
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
Here are the tables from just page one of the PDF (no transformation attempted as I don't understand the requirement).
The supplied code works perfectly for the extraction into excel as it should...
The issue arises with the new pdf upload being vastly different in its data as the first...Hence the 2nd snippet of code errors...
My concern is the fact that their could be multiple different pdf's with multiple different entry setups and vba cannot predict what the data is going to look like...
Jean...should have a look at all the possibilities of Pdf setup and then come back and offer an explanation as to what the output is to be...
John Topley asked me to do this - maybe he can comment. I did not post here in order to offer a solution or question any solution already given (I didn't even read the whole thread): I was responding merely to John's request to see if I could extract the data using PQ.
Over and out.
@AliGW
I do not have any experience in using it...
Your contribution could assist if someone else can extract the required data from the tables PQ has extracted...
Thanks for offereing to assist...Appreciate...
@Sintek and @AliGW
Mea Culpa! I asked Ali to look at this to see if it would help in addressing the problem highlighted by Sintek on the variability of the data.
It certainly was NOT intended in anyway to criticise Sintek code so my apologies if my "interfering" was interpreted as such: simply looking at alternatives!
No issue here @John or @Ali...Is important to find alternatives...and hopefully a solution that takes all into account...
Sintek , am testing on diffrent files , some were pdf were from acrobat print driver , microsoft print driver and pdf creator driver , let you know
What are these PDFs being printed from in the first place? Why so many different formats???
Is there a reason you can't get the data supplied in a suitably-delimited electronic format, or even as a Word document with tables? Your PDF has had to have been produced from either the data source directly or via an intermediate process such as a Word mailmerge.Originally Posted by JEAN1972
So @Jean...Just to give you clarity...This is taking into account your Post 1 requirement and initial supplied PDF...
The attached file shows the extracted pdf data succesfullly...
Based on that output in Excel, the 2nd snippet extracts the required info into your Table under all the required headings...
This works as it should...And solves your request as per Post 1...
The issue I have is as follows...
The code was written for your initial upload and as the new pdf's extraction has more rows and data as per the first, there is no uniformity in the extracted text...and therefore the code fails...
So if this 2nd Pdf data is more than the 1st one, what about the 3rd or 4th or 5th?
Last edited by Sintek; 10-31-2024 at 02:07 AM.
Hi All,
After testing, variance comes from box 40 tax calculation differ, zip as attached the different combination.
I have reload the excel files extract from 25 to 39 skip 40 but copy 42 only. the format remains same all time
A second module to extract and copy only 26 , 42 and 40 on a separate sheet as attached , as this part is the challenging one as it varies,causing issues with vba code
For the moment no csv can be extracted.
It varies
Sample pdf and screenshot of tax 40 box
Other differences in your posted files: Date spread over 3 rows vs 2 rows, assumn "1 FCL" and "1 HAZARDOUS FCL" are the same "item"
John , Yes , It varies , when dangerous cargo the word priority appears in the first line and and second line 1 hazardous fcl other when non dangerous 1 fcl appears in first line
As AliGw pointed out, is there any specific reason for using several PDF conversion tools rather then just one? My [very limited] experience of such tools has shown that they do vary in the format of their output.
The difference in the "40 TAX" is not a big problem: the major problem, as Sintek stated, is the relative postioning of the data needeing to be extracted.
Last edited by JohnTopley; 10-31-2024 at 08:13 AM.
John , thanks for explanation , to simply as best as I can , could it be extracted as attached sample the requested data , minimum data and second sheet 40 tax, so that I can do a vloolup to extract the taxes to correspondinG item no 1
John , thanks for explanation , to simply as best as I can , could it be extracted as attached sample the requested data , minimum data and second sheet 40 tax, so that I can do a vloolup to extract the taxes to correspondinG item no 1
On 2 sheets one for the extraction of key data and the most important one tax 40 on the second sheet , the key component is the Item 002 ,OO3 and so on .
Simplified version as much as I can
Sheet "Limited Extract": is this what you want? Using Sintek's file.Please Login or Register to view this content.
Re: extract specific data from pdf file to excel using vba
@ John , tested but got some anomalyclipboard error using adobe 11. but reboot my computer and it was succesful this time , I dont know why the adobe bug and vba code also, it did not copy all but rebooting did the job
For that post , file worked no issued , just informing the first issue encountered
However , Am testing on other pdf to ensure all is ok and will revert.
Last edited by JEAN1972; 10-31-2024 at 04:09 PM. Reason: clariifcation
Change the pause interval and check...As per Post 11...As I said previously...Some of your pdf's are doing things in the background...
Sintek , I changed as in bold red as there are 27 pages it worked as it did not loop continously , but when left on 150 pages , it continues looping
So I got a work around for it in bold red.
but on applying the @ Sub DataExtract2()@ as per John modifcations Limited Extract sheet did not succeed as per screen shot debuging
However, Column A and B in limited Extract sheet is not mandatory and can be removed as the description varies.
I uploaded the file another pdf file and the excel file and issues encountered
Please Login or Register to view this content.
Back to my initial statement in posts above...Variable row extractions not matching...
The loop 1 to 150 was based on your original pdf sample upload which had 150 pages...obviously it must change based on page nums of pdf...Like I said...Is impossible to predict all scenarios of all your different pdf set-ups...
That is why I have not responded...
Can go around in circles amending code for each pdf but in my opinion...POINTLESS
Still awaiting response on Post 22...
Last edited by Sintek; 10-31-2024 at 05:04 PM.
@Sintek , I do understand your point , but I have no choice , I do it manually in modifying vba code . I received the pdf as it is a daily tedious task . the pdf varies pages varies from 15 pages min to max 700 pages. Not easy at all
But at least you find a start where it can be achieved , everything is not perfect, there just some fine tuning in the code for extraction to limited extract sheet as described above. doing this manually is a real pain for the eyes.
For post 22 John has made some modifications as per post 30 as it was becoming too bulky and complicated. It worked , the go around techniques for the no pf pages work as explained no choice but a least it can be done even manually Just some modifcations of dataextract code is required as per previous post for the limited extract sheet issues as per screen shot.
As per post 33.
Last edited by JEAN1972; 10-31-2024 at 05:20 PM. Reason: clarification
I cannot write predictive pdf reading code...And like I said...The issue is not with the page numbers...It is with the layout of the pdf entries within the Tables located on the pdf's...The 2nd snippet of code is based on the line items from pdf input extracted to the excel file...If that extraction varies, the code will fail...
If we can have anchor points...then perhaps possible...Thus far you have given multiple pdf's all with different entries and therefore different extraction lines that the code refers to...
Remember...Anyone can adapt the 2nd snippet of code...It is however based on the extracted format from the pdf's...
@Sintek the layout table is always the same , but it varies as per screenshot,if code does not take in account this part , it will be perfect .
The issue of tax code 40 is resolved where John did some modifications to your code , the screenshot to item 25 (Culprit) is part where the code is failing.is there a way to bypass it .
It strongly believe it can be achieved ,
Post 27 & 30
HTML Code:
Last edited by JEAN1972; 10-31-2024 at 05:33 PM. Reason: explanation
You do realize that, after all the effort of developing code to work with the current PDF file, the slightest change at the creator's end (e.g. an update to the pdf-generating software or a trivial change in the layout or content or applying pdf copy protection) is liable to render the whole lot unusable??? That's why I asked you about whether you can get the raw data or at least in the form of Word tables.
@Macropod, I do understand that all of you did a great job , but am doing my best to obtain what of all you ask,I have tried through a converter to excel and txt unsuccessful,
As below a sample through word, the system that generates the files is an old fashion software(1992).
But I will try my best how to get the requested format .
I take it that your Word document attachment has been produced by some sort of PDF conversion process. It's pretty much unusable in its present form because the layout is inconsistent and especially because much of the content is in a plethora of textboxes and tables in textboxes floating on many of the pages.
If the system producing these data is in-house, it should be a simple matter to have a delimited text file produced that you could load directly into Excel with little or no processing. Otherwise, given that the system is probably using a database, an SQL query from Excel could probably be used to import the data directly into Excel - without having to mess with intermediate files.
Supply 5 different pdf's with 5 expected output extractions and we'll see what we can do...
But like I said...Supplying a code to cover these 5 might work now until a 6th or 7th comes up which is different again...
This is a fool's errand - you're chasing ghosts.
What should be happening is that the OP should be deciding on ONE PDF generator and sticking to it. At least then there will be consistency.
I would be interested to see a file from which these PDFs are being generated - maybe the PDF creation can be cut out of the process altogether.
@ AliGW, attached , still chasing to have proper data on my side.
@Jean
You are not making this easy...I asked for a result for the pdf's based on your new limited requirement...
Untitled.png
So please upload a sample file showing at least 5 entries for each of the attached above pdf's
below each other seperated by the PDF name so that we can compare...
If the 25 result is 1 FCL for wMTBOED2QRP then is it PRIORITY for Jean2 or 1 HAZARDOUS FCL
Last edited by Sintek; 11-02-2024 at 04:09 AM.
Maybe everyone should stop now and wait until you have consolidated the data 'on your side'. I feel that a lot of effort is being put in 'on this side', only for the goalposts to keep shifting, and this really is NOT fair to your helpers.@ AliGW, attached , still chasing to have proper data on my side.
As I was done anyway...This works for all pdf's uploaded thusfar...
Just need clarity on the "PRIORITY" question above...A quick change in code...
Please Login or Register to view this content.
Last edited by Sintek; 11-02-2024 at 04:27 AM.
@Sintek , thanks testing will revert back .
@ Sintek , worked flawlessly, thank you for the time dedicated to achieve the expectations which saved me from a very tedious task. Bravo
Also thanks to John and Macropod for their assistance for explanation.
Agree it was very tough, with those pdf files.
If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED. You can also access the SOLVED tag by editing the opening post and choosing SOLVED from the drop-down to the left of the title box.
Also, if you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.
Glad we got it sorted...Tx for rep +
I think it would have been perfect if one could read the number of pages in the pdf prior and loop accordingly...Their are so many options available on the web to achieve this but none stable enough for me...
Could have a user input no of pages prior to running and use that in a variable to loop accordingly...Or store the pdf names on a sheet with the page numbersnext to it and loop accordingly...
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks