Mapping imported data fields

[DannyJay]

The order of imported data fields may not be correct. I need a routine that will allow verification of fields and mapping to an access table.

Is there an easy solution within access?

 

[RickSpr]

There are too many possible solutions to this problem to list them all, so I need more information.

What kind of file are you importing from (text, Excel, etc.)? Do you want to create a new table or append to an existing one? Are you looking for a solution in the user interface (which can be converted to a macro solution) or a solution in VBA code? Is it for a developer's or an end user's use? Rick Sprague

 

[DannyJay]

I am importing a comma delimited text file. I am appending the data to and existing file, however a routine removes all data from the table prior to the import. I am looking for a solution in the user interface such that the end user can map the correct fields to the existing table. The end user will be responsible to see that all required data is in the text file however I wanted to avoid having to force the user to "arrange" the data in the correct order before the import. That is the purpose of the mapping routine. The data whill come from several different sources and vendors.

 

[RickSpr]

There is no "easy" solution to this, but I'll suggest a few ideas in case you want to build a solution.

If the CSV file has field names in it, and your users could match fields based on the field name, you could link the CSV file as a table. That would let you use DAO objects to retrieve the field names in code. You could then save the field names as rows in a small table, with two other columns, one a yes/no field and the other a long integer.

Next, use DAO to retrieve the field names in the target table and save them in another table, which would have an Autonumber field. (Or, if the target table fields are known, you can just create this second table without code.)
The Autonumber field is the primary key. The Long Integer field in the first table is used to link a row in the first table with a row in the second. In other words, it relates an import table field with a target table field.

Note: In the first table, you don't want to set Required to Yes on the long integer field, you don't want to index it, and if you create a relationship between them in the Relationships window, you don't want to set Enforce Referential Integrity on. The reason is that some import fields may be skipped--these would have Null in the long integer--and sometimes two may be related temporarily to the same field in the target table, as explained below.

Now you can open a small form with the Record Source set to the first table, and the Default View set to Continuous Forms. Each row in this table appears on the form as a (locked) text box containing the field name in the import table, a check box to indicate whether the import field should be skipped, and a combo box whose list comes from the table of target table field names. For each import field, the user can indicate that it is to be skipped, or can select a target table field into which it will be imported.

An AfterUpdate event procedure for the check box should set the combo box empty when the check box is checked, since this indicates that the import file field will be skipped.

The form's BeforeUpdate event procedure would need to first verify that each import field is either to be skipped, or has been matched with a target field name. It then should check to make sure that each target field is mapped to no more than one import field. You may also want to check that any Required fields in the target table are mapped to one import field.

The form's AfterUpdate event should then create and execute a SQL INSERT statement that copies the data from the linked import table to the target table. After that, it can delete the linked table, and maybe delete the records from the two small tables that map the fields.

I know how complicated this sounds, especially if you're not very familiar with VBA code or DAO objects. I thought of a few alternatives, but they started out pretty much the same and didn't wind up any simpler. Rick Sprague

 

[vikasjain]

Hi,

Actualy I want to know how I transfer the data from the text file to the database(MS-Acess).

vikas

 

[DBR]

Rick,
I'm writing a similar interface and your solution is good.
I've written the DAO field imports etc. and everything up to the SQL INSERT statement. Would the statement call the fields on the form,ie. me!fieldname, and how would you cycle through fieldname records to do the insert. Any Ideas on how to structure this statement?