Convert PDF to Excel spreadsheet within Access 2

[TheresAlwaysAWay]

I have mentioned several times in the past my 20+ user system for an insurance company running access 2013 and SQL Server Xpress 2018.

The individual insurance companies with whom we do business send us a list of canceled policies in a PDF format. Our personnel have to manually go through that list and enter the data in order to bring up our matching records to make notations or changes or other things that are required.

I am looking for a method of converting a PDF file to an XL spreadsheet within Access. Ideally, I would direct Access to the file of interest, the conversion to XL would happen, and then I can write additional programming to transfer the data to a table to make comparisons and do the other things that are necessary. The main task is to get the data into Access so it can be dealt with.

I know that there are external programs that will do this, and I know that Word is also capable of doing it. I just don’t know the best method of approaching this and I’m hoping one of the wonderful brainiacs out there will know the answer.

Thank you all in advance for any assistance you might provide.

 

[combo]

If you have office 365, you can import data directly to excel using Power Query (see

https://www.howtogeek.com/770474/how-to-import-data-from-a-pdf-to-microsoft-excel/

for instance).

It is not so easy in excel 2016 (the version I use) with built in Power Query, there is as access to pdf data in M, but it is not intiutive.

Power Query for excel 2010/2013 is a free separate COM add-in from MS (

https://www.microsoft.com/en-us/download/details.aspx?id=39379

), I don't know its functionality, as this version may be old. This tool becomes more and more functional, being a part of Power BI and Excel.

combo

 

[Andrzejek]

Those 'individual insurance companies' have the data in already suitable format (one would hope...) and they use it to create a PDF for you.
I would use my lazy approach and ask them for the same data in csv, json, xml, whatever you need (Excel as a last resort)

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[TheresAlwaysAWay]

Thank you so much for the suggestions. Combo, I don’t have office 365 and the solution you offered, while workable, sounds complex.

Skip, you’re absolutely right. I was defining a solution rather than the problem.

The PDF file is in a tabular format, so it is importable. CSV definitely seems like the most logical solution here.

Since CSV can be imported directly into Access without first going through Excel, that seems an even more logical solution. I can use update queries and other such things to correct values that get corrupted like dates that you mentioned.

Since each company is sending their data in their own PDF file it’s highly likely that I would have to massage the data from each one independently before it’s in a consistent and useful format.

I like that suggestion very much but what I don’t see is information on how to use vba to turn a specified PDF into that CSV.

Any suggestions that could be offered would be appreciated.

One way or the other I am looking for the simplest and most efficient way of accomplishing this task. Once it is within access I can do whatever else is necessary. It’s just getting there that’s the problem for me at this moment.

 

[TheresAlwaysAWay]

Andy, we did try that first. Unfortunately we were met with the dumb, blank stare of incomprehension.

 

[Andrzejek]

dumb, blank stare of incomprehension"
You were talking to the wrong people. Try to reach an IT who created the PDF

You are (probably) not the only one who would benefit from making it happen...

I see it way too often, even in the same company (I know, it happens where I work) where at one end data from DB is used to create a (pdf) report, which is printed, sent to another department, scanned, OCR'ed, and the data moved to another DB.


---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[TheresAlwaysAWay]

You're right, Andy. This is a problem for the insurance companies to solve and not for us to create a workaround for their incompetence. I'll see if we can apply more pressure to them.

 

[Andrzejek]

You don't really need to 'apply more pressure', just explain the issue and propose the (obvious) solution. Who knows, they may be happy to comply since it would help a whole other 'recipients' of their data.

You are getting their data already, you (and others) just need it in different format. That's all.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[TheresAlwaysAWay]

Ya know, Andy, it's not my direct responsibility to deal with the companies. I deal with the aftermath, if you know what I mean. But I've told the folks that do deal with the companies not to accept their BS. Of COURSE they can provide it in Excel if they want to. I told them I'd be happy to call the companies and do it for them, but it's in the works.

This whole exercise is jumping through workaround hoops because they're not doing their jobs and providing what we need.

I'll let you all know how it plays out but you're absolutely right. I shouldn't have to do this at all.

BTW, Skip, if you're reading this thread, I don't know what happened to your post. I was gonna give you a star but it's not there. Send another post so I can give you credit.

 

[Andrzejek]

Skip, [...] I don't know what happened to your post

If you look closer, you will see:

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[TheresAlwaysAWay]

Obviously inadvertent. I'd still like to give Skip a star.