Data Import

[Garry]

Hi all

My database contains the following fields : Namefull, Address1, Address2,
PostCode.

I receive hundreds of small exell files with differing headings :

Name, Add1, Add2, PostCode

or FullName, Addr1, Addr2, AreaCode

My question ?

I have to alter the excell headings every time before importing.

Is there a way to tel my database that Name data goes into Namefull, Add1
goes into Address1 etc.

Thanks in advance, Garry

 

[G. Vaught]

I think the easiest way is to have the users who create the other Excel
files match your table field names. However, the likely hood of that
happening may be zero to none. Import the data as a new table and then
create an Append query where you can match their fieldnames with your
fieldnames.

 

[Ronald W. Roberts]

Hi all

My database contains the following fields : Namefull, Address1, Address2,
PostCode.

I receive hundreds of small exell files with differing headings :

Name, Add1, Add2, PostCode

or FullName, Addr1, Addr2, AreaCode

My question ?

I have to alter the excell headings every time before importing.

Is there a way to tel my database that Name data goes into Namefull, Add1
goes into Address1 etc.

Thanks in advance, Garry

This is air code. You still need a way to change the name of the
imported table in the routine.
This can be done with an Open Dialog box or import the spreadsheets
using a prefix that will
allow you to loop thru your table and find all of the imported tables.
This routine assumes the
fields are in the same position in each table.

Dim db as database
dim rsIN as recordset
dim rsOUT as recordset
dim x as integer

set db=currentDB
'You need to change the imported table name each time
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Set RsIN = db.OpenRecordset("Imported_Table_Name")
Set RsOUT = db.OpenRecordset("Master_Table_Name")
rsIn.MoveFirst
Do Until rsIn.EOF
rsOut.AddNew
For x=0 to 3
rsOut.Fields(x) = rsIn.Fields(x)
Next x
rsOut.Update
rsIn.MoveNext
Loop

Hope This Helps,
Ron