Reading printer files with Access

This is a blue sky type question.  No data, just looking at future planning.

My old SW company was bought out by our new SW company a a while back. We had access to the source code and data so it was relatively easy us to use Access to build conversion routines from the old SW to the new SW. Our conversion team was so good that we broke records for conversions and made the new company expand their data services team. They were struggling to keep up with us and still do the other conversions.  Well we're now getting to the end of our SW customers.

That leaves me in the thinking ahead mode. I want to see if I can come up with a way to convert other customer's data from "foreign" competitors. Some of them are on-site server/client type apps. Others are a website types. The server/client sort of worry me, but if I can find a way to read the DB (legally) I can probably build a conversion system.

The worst idea that may work is have the clients dump the files to PCL/PRN/PDF or other printer file types and then read those files.

So what I'm looking for is some type of Access/VBA routine that will allow me to read that type of file and extract data. I can deal with the "wrapping" of files and finding text. It's a matter of how to read that data.

Any suggestions will be appreciated.
LVL 38
Jim P.Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

Jim Dettman (Microsoft MVP/ EE MVE)Connect With a Mentor PresidentCommented:
<<Any suggestions will be appreciated. >>

 I haven't read through the thread, so this may be a dup, but I would look for an open source project that creates PDF from Post Script output.

 With that said, it's obvious that someone's already managed to do what your looking to do.

 I would suggest though the hub and spoke design with your data conversions (sounds like it's pretty well built though).

  You bring all data into a common format (the hub).  Each spoke is the conversion from one specific source (i.e. spreadsheet, XYZ's company data, printer file, etc).

  One of the spokes is conversion to your software.

  Company call Data Junction used this approach years ago and in the end, could translate just about anything to anything.

aikimarkConnect With a Mentor Commented:

You can certainly read ASCII text files with VBA. (duh)

* Your client can 'print' to a flat file, using the generic/text printer.
* If your client 'prints' to a PDF file, you can use an app like PDF2Text to extract the text back out into an ASCII text file.

I anticipate that the problem you will encounter is that the data won't be printed as data, but rather as a report.  Parsing reports is never as simple as you would hope it would be.  It might be worth looking at some third-party tools that are designed to extract data from report files.  I suggested some of these cheap third-party utilities in this comment:

I have been rethinking what I've been doing in this area.  I suspect there is a design pattern in the ETL process that lends itself to a wide variety of tools and technologies with interchangable mixing.  Unfortunately, I haven't even drafted the article for this rethink. :-(

I would also look at some other tools you might have in common with your clients, such as Microsoft Office (Word & Excel), Powershell, VBScript (batch or HTA), and regular expressions.  What we want to do is get your (future) clients' data persisted. The obvious choices are delimited (I prefer tab over comma), XML, JSON/YAML.  These are all industry standards.  What I try to avoid are solutions that require my end-user clients to install some software.  Most of them have PC desktop environments that are locked down by their IT support/admin staff.  I'm even looking at some portable applications, so that I might put software on a flash memory stick, such as Portable Python.
Jim P.Author Commented:
Our clients range from Mom & Pop shops where the janitor is the day-to-day guy that plays with  computers. Essentially they will listen to what we say.  Then there are corporate clients that have a full IT and know that management is ordering the conversion. So thew will work to move forward.

It is the middle crowd that has plodders that just do IT as a contract and don't want to lose it. They're the ones I'm thinking of. My industry only has about 15 competitors. So if I can read the report files without IT intervention from the end IT staff and an automated reader I buy a lot of customer and sales support.
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to and use offer code ‘EXPERTS’ to get 10% off your first purchase.

What is your industry/niche?
Jim P.Author Commented:
Nursing home SW.
aikimarkConnect With a Mentor Commented:
You can also include OCR technology in your toolbox in case the only producible reports are on  paper.

With 15 competitors, you should be able to populate a workbook with matrices of sources and ETL options.
Jim P.Author Commented:
I prefer not to go paper from a time issue.

The issue is that John  Doe enters a nursing home in with arrhythmia and is allergic to penicillin and apples. He is also Protestant and a college grad. There really isn't a normal report to list the diagnoses, education, religion or allergies  among residents,

So for all intents and purposes you have to dump each individual resident to a file and read each one to build the conversion files.

That is the point I'm getting stuck on readable files.  Most of the packages have a standard "face sheet" which would get the majority of data, But they aren't really designed to dump to text.
That is why I mentioned the Generic/text printer definition in Windows.
In my list, this is File (print to file)
My list of tools in the other question thread includes the Data Junction product suggested by Jim Dettman.  I was calling it Data Exchange.
Jim P.Author Commented:
I'm going to sort of have to go "rogue" on the project. As in, my current manager isn't going to allocate time for me to do it. But I generally have enough spare time that I can probably build it and present it up chain to various managers to get it supported.

Once that happens, I'll see where it goes. But I think that if the sales manager finds out that we can do it, they will heap praise on me. And from there it will become part of the routine.
It would be nice to know some market share figures for your competitors.  That way, you could prepare the migration paths for the leaders first.  If the sales teams go after everyone equally, this probably won't be your primary factor in where to do your work.  It may be that you will need to understand which customers are most likely to convert and prepare for them first or find out if the least likely to convert potential clients are reluctant because of the migration costs and concerns or because they are really happy with your competitor's product or because of some accounting metrics.

I don't know how to advise you on gleaning the activity of what the sales team is doing without divulging what you are doing.

How about have some lunches/dinners with members of the sales team and hint that you are thinking about switching your career to sales or moving into a technical sales role.  Engage them about their strategies and challenges.  After meeting with the sales team, you should do the same with the sales manager/director.  Make them think you are asking career advice.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.