HELP! Bad excel layout import into Access

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

Guest

I have an excel spreadsheet that I need to import into an access table.
HOWEVER, the spreadsheet is not laid out correctly; what would be the field
names in access are rows in one column in excel and the data field is another
column with rows. For example:

Column "A" Column B
Field 1 10%
Field 2 20 %
Field 3 10 %

Column A is what I need to be the field name in Access and Column B is the
data that should populate that field.

How do I write this so that it imports correctly???? I have tried

INSERT INTO TABLE A (FIELD1, FIELD 2)
SELECT F1, F2
FROM <excel path and info here>

This DOES NOT work, what am I missing?

Cathi
 
Cathi said:
I have an excel spreadsheet that I need to import into an access table.
HOWEVER, the spreadsheet is not laid out correctly; what would be the field
names in access are rows in one column in excel and the data field is another
column with rows. For example:

Column "A" Column B
Field 1 10%
Field 2 20 %
Field 3 10 %

Column A is what I need to be the field name in Access and Column B is the
data that should populate that field.

How do I write this so that it imports correctly???? I have tried

INSERT INTO TABLE A (FIELD1, FIELD 2)
SELECT F1, F2
FROM <excel path and info here>

This DOES NOT work, what am I missing?
Click to expand...

Cathi, open up a new Excel workbook. Highlight your original data, Copy,
then go to the new workboook and do Edit/Paste Special, select Values
and Transpose.

Excel will switch around the original data so your Field1 etc. are
across columns in Row1 and your data in rows.

Then import that into Acess instead of the original data.

--
Terrell Miller
(e-mail address removed)

"Every gardener knows nature's random cruelty"
-Paul Simon RE: George Harrison
 
That won't work for what I need. Already thought of that :-) This is an
ongoing process and I need to automate it for the users. Now, if I can
perform the "transpose" function in code through access than I could get it
to work. Do you know if that is possible.
 
Hi Cathi,

One general approach is

(a) link the Excel sheet as a two-column table
tblXL
F1 Text
F2 Number

(b) use a crosstab query to transpose this

(c) base an append query on the crosstab.
 
Back
Top