map excel data fields to access field names

[directmail]

Hi,

If I have to import data from a excel sheet on a regular basis. Is there a
way to save the mapping of excel data fields with access field names? How do
I do this?

Thanks,
Girish.

 

[Klatuu]

How are you doing it now?

 

[directmail]

Hi,

I don't know how to do it. I figured, I am missing something. However I was
not able to find any mapping information in the manual either.

regards,
Girish.

 

[directmail]

As an example the field name in excel is "P_Address" however in Access the
corresponding field name is "Property Address". There are about 15 different
fields which have different names in the spreadsheet and access table. Now if
these field names are identical then Access is ok. So I am looking for a way
to tell access to load the excel "P_Address" to "Property Address" column in
the access table. Same for other columns as well. This mapping is what I am
looking for.


regards,
Girish.

 

[John Nurick]

One way to do this is to link the spreadsheet rather than import it, and
then build an append query based on the linked table, using calculated
fields to map the field names.

Or you can do it in one hit by building a query in SQL view that renames
the fields as it imports them. The syntax is like this. Note the [ ]
around the field name that includes a space (it's much simpler not to
use spaces or other special characters in the names of objects), and the
$ after the worksheet name.

INSERT INTO MyExistingTable (NewField1, [Property Address], NewField3)
SELECT OldField1, P_Address, OldField3
FROM [Excel 8.0;HDR=Yes;database=D:\Folder\File.xls;].[Sheet1$]
;