Lavsa, Rich
Rich_Lavsa at pghcorning.com
Tue Jun 1 10:59:51 CDT 2004
I have done something like this, but on a smaller scale. I have a database that gets updated once a year by outside providers. I didn't want to go through what you described below so I took the time to build an "Update Database" that I sent out to all the companies that needed to update information. I leave it up to the companies to get the data into the database the way they want it, then they send the "update database" to me once a year. This process is so simple that it is totally left up to the users to decide when to update the database, so far its been a "no brainer" push the button marked "UPDATE" which goes out to the network location where the database expects it to be which will run all the validation code to update and insert data. I thought it was a good idea at first, and even better when I convinced the companies to format their data for me so I wouldn't have to worry about it. Sounds like a better approach for your purposes as well. Maybe feasible, maybe not.. G'luck Rich -----Original Message----- From: Charlotte Foust [mailto:cfoust at infostatsystems.com] Sent: Tuesday, June 01, 2004 11:28 AM To: Access Developers discussion and problem solving Subject: RE: [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 -- _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com