Import from Excel Question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm sure there's an easy answer to this, but I can't figure it out. I want
to import data from Excel into an Access database. But, the data columns in
Excel aren't in the same order they are in Access, and I don't want to import
all of them. Is there a way to specify which columns I want to import from
Excel?

Thank you!!
Heather
 
Hi Heather,
The wizard does not allow you to specify columns.
You could write a lot of code to automate Excel and then do anything you can
think of in terms of importing the data. But it is much harder.

Some obvious choices are:
1. Edit the spreadsheet and have it only include the columns you need.
(You could write a macro to do tihs if the original sheet was always in the
same layout.)

2. Import all the columns to a "staging table" in Access (just a regular
table but not one that is part of your main application - it is used to hlld
the imported data and is cleared out - by yur code - every time you import
something else.)
Then write a query to Append the staged data to your real table. The query
will only contain the columns you want.

I recommend #2.
 
Hi Heather,

You can usually do this with a query in SQL view. I assume from your
comment about the fields being in different orders that you are
importing into an existing Access table.

If the Excel sheet has column headers that serve as field names when
importing, something like this, where AA, BB, CC are field names:

INSERT INTO tblT
SELECT AA, BB, CC
FROM [Excel 8.0;HDR=Yes;database=C:\MyWorkbook.xls;].[Sheet1$]
;

Without headers, like this. F1, F2 etc. are the default names for column
1, 2 etc. when there are no headers:

INSERT INTO tblT
SELECT F2 AS AA, F5 AS BB, F1 AS CC
FROM [Excel 8.0;HDR=No;database=C:\MyWorkbook.xls;].[Sheet1$]
;
 
Why not link to the Excel spreadsheet and use an append
query to import the data.

Chris
 
Chirs' idea is good. If it has to be done manually, however, the best way to
do it is to save the Excel File to a comma delimited .csv format.
You can then set up an import spec in Access to import only the colums you
want and define formats for them. I don't remember if it is possilbe to
reorder fields. Is it not possible to reorder the fields in the Access table
to match the order in Excel? This should not be a problem unless you have
code in you app that expects fields in that table to be in a certain order.
If there is, go beat the crap out of whomever wrote that. :)
 
Back
Top