Excel Sheet to Access Table Mapping

  • Thread starter Thread starter Lez
  • Start date Start date
L

Lez

Hi Guys,

Just wondering if anyone knows of a method to create a form that allows you
to map data from a excel spreadsheet to fields in a access database.

I know how to create and import spec, but as our company has a number of
staff all adding data from excel reports I would prefer a form so they can
map the relevant columns on the spreadsheet to import the data themselves.

As part of this routine I would want to check to update or add new data.

If anyone has links to sites or forum posting on doing this I would be most
grateful.

TIA

Lez
 
Hi Lez,

You can get a list of the column headings in an Excel sheet into an
Access combobox or listbox by setting the Row Source Type to "Field
List" and using a query like this as the Row Source:

SELECT * FROM [Excel 8.0;HDR=Yes;
Database=D:\Folder\Subfolder\Workbook.xls;].[SheetName] WHERE FALSE;

(Alternatively you can access the worksheet via a linked table.)

You can get the field names from an Access table the same way; just use
the name of the table as the RowSource.

It's then a matter of working out the user interface and writing VBA
code that assembles the source and destination field names into an
append query like this:

INSERT INTO Destination (FirstName, LastName)
SELECT FName, LName
FROM [Excel 8.0;HDR=Yes;
Database=D:\Folder\Subfolder\Workbook.xls;].[SheetName]

(Again, you can use a linked table in the FROM clause rather than the
incantation above.)

There is room (and maybe need) for a good deal of elaboration. For
example, is the Excel data perfect or does it tend to contain text
values where there should be numbers (or vice versa)? If so, you may
need to import the data to a "staging" table all of whose fields are
defined as Text, and then use an append query that converts field types
en route to the main table. Or will it be necessary to limit the choices
users can make (e.g. to prevent them mapping a column headed "Last Name"
to a field called "First Name")?
 
Cheers John,

That is a help, it gives me something to consider, maybe easier just
creating a import spec.

Thanks

John Nurick said:
Hi Lez,

You can get a list of the column headings in an Excel sheet into an
Access combobox or listbox by setting the Row Source Type to "Field
List" and using a query like this as the Row Source:

SELECT * FROM [Excel 8.0;HDR=Yes;
Database=D:\Folder\Subfolder\Workbook.xls;].[SheetName] WHERE FALSE;

(Alternatively you can access the worksheet via a linked table.)

You can get the field names from an Access table the same way; just use
the name of the table as the RowSource.

It's then a matter of working out the user interface and writing VBA
code that assembles the source and destination field names into an
append query like this:

INSERT INTO Destination (FirstName, LastName)
SELECT FName, LName
FROM [Excel 8.0;HDR=Yes;
Database=D:\Folder\Subfolder\Workbook.xls;].[SheetName]

(Again, you can use a linked table in the FROM clause rather than the
incantation above.)

There is room (and maybe need) for a good deal of elaboration. For
example, is the Excel data perfect or does it tend to contain text
values where there should be numbers (or vice versa)? If so, you may
need to import the data to a "staging" table all of whose fields are
defined as Text, and then use an append query that converts field types
en route to the main table. Or will it be necessary to limit the choices
users can make (e.g. to prevent them mapping a column headed "Last Name"
to a field called "First Name")?

Hi Guys,

Just wondering if anyone knows of a method to create a form that allows
you
to map data from a excel spreadsheet to fields in a access database.

I know how to create and import spec, but as our company has a number of
staff all adding data from excel reports I would prefer a form so they can
map the relevant columns on the spreadsheet to import the data themselves.

As part of this routine I would want to check to update or add new data.

If anyone has links to sites or forum posting on doing this I would be
most
grateful.

TIA

Lez
Click to expand...
Click to expand...
 
Back
Top