Solved

Reading printer files with Access

Posted on 2014-04-24
12
266 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
 
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

760 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now