[AccessD] Is there an easier way?

John,

I worked for a direct marketing company at one time, and our lists
(50,000+) came in formatted differently, with names split differently
and with no consistency.  I did pretty much what you're describing.
There really isn't an easier way ... Except get someone else to do it.
<VBG>

Charlotte Foust

-----Original Message-----
From: jwcolby [mailto:jwcolby at colbyconsulting.com] 
Sent: Monday, May 31, 2004 8:19 AM
To: AccessD
Subject: [AccessD] Is there an easier way?


Folks,

I am working on a system where I need to import (and update existing)
name / address data from a bunch of files collected from the states.
These files contain name / address / license information for individuals
licensed to perform physical therapy etc. and come in all kinds of
formats, with all kinds of field names, many of which we don't use, all
of which which is making the problem tougher.  In the end, for now, the
files will be pre-processed (by hand in many cases) to get them into
Excel files.  The preprocessing will include putting the state
abreviation into the first two characters of the file name and a "data
type" code into the remaining characters.  This still leaves the problem
of different field names, i.e. one file may have "Last Name", the next
"LastName", the next "Lname" etc.

I have built a system that allows the user to select an Excel file using
File Find dialog.  The file is copied to an common location plus an
archive location with a name that includes the date etc.  The program
strips the state code and looks it up in the state table, and strips the
data type code and looks it up in the data type table.  If all this
"passes", then I lookup the file name in tblImportFile.  If it does not
exist I save the file name / path / stateid and datatype id in a new
record.  Most importantly, the file in the common location is
dynamically linked to the FE to allow queries to be created.  By that I
mean that I reach into the table def and change the "database=" of the
connect string to the name/path of the file being processed so that it
points to the file just selected by the user.

If this is a new file (first time processed), the user now "matches"
field names using a pair of combos, one of which displays the field
names in our table and the other displays the field names from the
linked excel file.  As long as the state does not change the field
names, this process only occurs once per file.  The results are stored
in tblImportSpec.  tblImportFile holds the file, path, state id and
datatype id.  tblImportSpec holds the ID from tblImportFile for the file
being processed, then the matching field names from our table / their
table.

With me so far?

I go through all this nonsense so that I can dynamically build a query
that "aliases" their field name to our field name, plus grab the state
ID and datatype ID (and import date) and build up a SQL statement that
when executed results in their field names matching my field names, for
whatever fields in their table match fields in our table.  I then save
this SQL string into the SQL property of an existing query def.  Thus at
any time you can open that query and look at the data in an excel
spreadsheet, with the field names matching my field names, and a handful
of Ids that match up to state Ids and data type ids etc.

Once ALL of this is done, I filter out duplicates, allow the user to set
up filters such as last name matches etc. then use the resulting data to
build a temporary table of data.  

The whole point of this exercise is to get a table of data matching
people in our database so that I can update their address information
with the information that the state provides, and of course add new
people not in our database.

The major issues here - 

Various file formats
Various fields, some of which are not needed
Various field names for the fields that are needed
The process needs to be done regularly (at least once per year,
sometimes more often) so it needs to be possible for a user to do this.

Is my solution harder than it needs to be?  Has anyone handled a
situation like this and if so, how do you deal with it?

John W. Colby 
www.ColbyConsulting.com


-- 
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com