Converting a .pdf to Excel database

[delaneybob]

Hi,
I was referred here by a work colleague and need to know if this can be done and at what cost

I am looking for the .pdf to be converted to the excel format in the attached files. I have tried this myself and getting it in excel is easy but making the data be in 1 row per record is the hard part. Looking for some VBA code out there who can get the data in useable format for a price. If you can do this- please quote a price. I can not get the raw data and this is semi urgent

Thank you

[HansV]

Welcome to Eileen's Lounge!

We don't do paid work - we provide free help, but only within reasonable limits.

Some locations have multiple objects- do you wan one row per object?
Could you provide a small sample of what you get in Excel from the PDF file? I think this depends on the PDF reader you use.

[delaneybob]

Attached is what I get when saving as .xml and opening of the .xml in Excel. It basically places a record per sheet. I expect Excel has a limit on number of sheets.

[HansV]

I fear that this format is too jumbled to do much with.
What happens if you export or copy to plain text (.txt)?

[delaneybob]

Attached

[HansV]

I'm very sorry for you, but this won't work. The order of the elements in the Excel workbook and in the text file is variable, and different from that in the PDF file. Because of the inconsistencies, it will be very difficult, if not impossible, to reconstruct the original data.

A PowerPoint MVP once wrote

Converting a PPT to a PDF is like turning meat, veggies, spices and water into stew. Pretty simple.
Converting a PDF to a PPT is like turning stew back into the original meat, veggies, spices and water. Darn near impossible.

Replace PPT with XLS and it's still true.

[dasadler]

There is a commercial product called Monarch that takes print jobs (which I think includes PDF) and converts them to database files. It is pretty flexible in that it allows the user to map fields to physical locations on the report or print job.

Once you get the data into any manageable form, it should be easy to bring into Excel.

[VegasNath]

Monarch is an excellent tool that I use frequently. I did not realise that it would work with a PDF. Should do the job nicely.

[delaneybob]

Just a quick question before I buy Monarch, I assume it can map and name fields etc and move items in rows to columns and start a new row for the new data record?

[dasadler]

I cannot answer that with any high level of confidence. My understanding is that it is very flexible and should do exactly what you describe but I suggest you contact them with a PDF sample and pose that question to their sales/support staff. Maybe VegasNath can provide a better answer.

[delaneybob]

Hi- I looked at Monarch examples and it appears i need to first convert the .pdf to a .prn- any ideas on that?

[HansV]

The website that dasadler linked to states that "Monarch Pro: Enables data mining from PDF files" so conversion doesn't seem necessary. However, if you do need it: you create a .prn file by printing a document to a file instead of directly to a printer.

[delaneybob]

Hi the trial version has a .pdf limit of 1000 charatcters- I looked at their help guide-examples and it appears a .prn is what i need. i tried a .ps but it's too much junk in there

i also tried .html not too good

I tried to print to a .prn but appears you can not do that wiht a .pdf- Adobe Acrobat Pro 9.1 only allows a .ps

I am stuck currently

[HansV]

You can set up a file printer in the Printers control panel - set it to use the "FILE" port instead of LPT1:

[delaneybob]

That resulted in more ugliness :-). i tried different drivers etc but the crux of it is Adobe will not pritn to a .prn only a .ps

is there a work around for this? I looked at lesson 15 in monarch and it appears it's just what i need- however- Monarch again wants .prn. If I use .txt in Monarch- it's ugly too

[HansV]

Sorry, I don't know how to solve this.

[dasadler]

delaneybob - since you are trying to determine if this application will meet your needs and you are constrained by the 1000 character limit... why not replicate as much of the original PDF manually as will fit withing 1000 characters then create a new PDF that is within the limit? That would allow you to test the app at least.

[delaneybob]

Great suggestion- i tried that and no- not good either- i am disappointed.

Lesson 15 in monarch help leads one to believe you can use a .prn- so i creat one form a tabled word doc- no luck there either- i will try raw text but i think the logic of the program will not work then

[VegasNath]

I cannot comment on your query regarding file types (pdf) etc. I only use it with text files.

What monarch pro does is enables you to create "models" for your reports. Providing your report structure is stable, you build and save a model for future use.

I use bat files on a daily basis that opens monarch, opens files, opens corresponding models and saves the required output automatically as excel databases.

My job would be a nightmare without it.

I can't make any promises, but if time allows tomorrow or Tuesday, I will attempt to model your sample data above to see if it will provide what you are looking for. Could you provide a small excel sample of what you require?

[HansV]

Could you provide a small excel sample of what you require?

Bob also included that in the first post.