Solved

Reading printer files with Access

Posted on 2014-04-24
12
274 Views
Last Modified: 2014-05-02
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.
0
Comment
Question by:Jim P.
  • 7
  • 4
12 Comments
 
LVL 45

Assisted Solution

by:aikimark
aikimark earned 333 total points
ID: 40021783
@Jim

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:
http:Q_28406044.html#a39996681

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.
0
 
LVL 38

Author Comment

by:Jim P.
ID: 40021822
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.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40021828
What is your industry/niche?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 38

Author Comment

by:Jim P.
ID: 40021832
Nursing home SW.
0
 
LVL 45

Assisted Solution

by:aikimark
aikimark earned 333 total points
ID: 40021845
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.
0
 
LVL 38

Author Comment

by:Jim P.
ID: 40021852
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.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40021854
That is why I mentioned the Generic/text printer definition in Windows.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40021856
In my list, this is File (print to file)
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 167 total points
ID: 40022276
<<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.

Jim.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40034229
My list of tools in the other question thread includes the Data Junction product suggested by Jim Dettman.  I was calling it Data Exchange.
0
 
LVL 38

Author Comment

by:Jim P.
ID: 40036225
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.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40036915
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 this...you 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.
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question