×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Convert PDF to Excel spreadsheet within Access
2

Convert PDF to Excel spreadsheet within Access

Convert PDF to Excel spreadsheet within Access

(OP)
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.

RE: Convert PDF to Excel spreadsheet within Access

If you have office 365, you can import data directly to excel using Power Query (see https://www.howtogeek.com/770474/how-to-import-dat... 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.a... ), 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

RE: Convert PDF to Excel spreadsheet within Access

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

RE: Convert PDF to Excel spreadsheet within Access

(OP)
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.

RE: Convert PDF to Excel spreadsheet within Access

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

RE: Convert PDF to Excel spreadsheet within Access

"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.
banghead

---- Andy

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

RE: Convert PDF to Excel spreadsheet within Access

(OP)
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.

RE: Convert PDF to Excel spreadsheet within Access

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

RE: Convert PDF to Excel spreadsheet within Access

(OP)
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.

RE: Convert PDF to Excel spreadsheet within Access

Quote (TheresAlwaysAWay)

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

If you look closer, you will see:

wavey

---- Andy

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

RE: Convert PDF to Excel spreadsheet within Access

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close