Please help with import

  • Thread starter Thread starter Al
  • Start date Start date
A

Al

I have data that comes in excel in the following format:

Date Time Amino1 Amino2 Amino3
8/20/2003 10:00 104.559 3622.123 3.856
8/21/2003 12:00 161.54 4484.632 6.535

I would like to import it into access in the following
fashion:

Date Time Amino Type Value

8/20/03 10:00 Amino1 104.559
8/21/03 12:00 Amino1 161.54
8/20/03 10:00 Amino2 3622.123
8/21/03 12:00 Amino2 4484.632
8/21/03 10:00 Amino3 3.856
8/21/03 12:00 Amino3 6.535

Can anyone help?
Thanks
Al
 
Create a temporary table the same as your Excel (or just link to the Excel
spreadsheet).

Create a final table with the new layout.

Create a query to normalize the temporary table:

SELECT [Date], [Time], "Amino1" AS "Amino Type", Amino1
FROM MyTable
UNION
SELECT [Date], [Time], "Amino2" AS "Amino Type", Amino2
FROM MyTable
UNION
SELECT [Date], [Time], "Amino3" AS "Amino Type", Amino3
FROM MyTable

Use that to append to the final table.

BTW, Date and Time are both reserved words, so you should not use them as
field names. As well, why not store them as a single date/time field?
(Change [Date], [Time] in the query above to [Date] + [Time] AS DateTime)
And I'd recommend strongly against using spaces in your field names.
 
Dear Doug,
thank you I tried this and it worked. of course, the data
I used was just for illustration only. The names will not
have spaces in the real database and the date/Time will be
TestDate and TestTime. However, I need to test this with
the actuall numbe of column that I get usuall 26 column in
excel, not just Amino1 to Amino3, the real file goes to
Amino26. Again I do appreciate this ingenious method. And
I will let you know how it went.
Al
-----Original Message-----
Create a temporary table the same as your Excel (or just link to the Excel
spreadsheet).

Create a final table with the new layout.

Create a query to normalize the temporary table:

SELECT [Date], [Time], "Amino1" AS "Amino Type", Amino1
FROM MyTable
UNION
SELECT [Date], [Time], "Amino2" AS "Amino Type", Amino2
FROM MyTable
UNION
SELECT [Date], [Time], "Amino3" AS "Amino Type", Amino3
FROM MyTable

Use that to append to the final table.

BTW, Date and Time are both reserved words, so you should not use them as
field names. As well, why not store them as a single date/time field?
(Change [Date], [Time] in the query above to [Date] + [Time] AS DateTime)
And I'd recommend strongly against using spaces in your field names.

--
Doug Steele, Microsoft Access MVP



Al said:
I have data that comes in excel in the following format:

Date Time Amino1 Amino2 Amino3
8/20/2003 10:00 104.559 3622.123 3.856
8/21/2003 12:00 161.54 4484.632 6.535

I would like to import it into access in the following
fashion:

Date Time Amino Type Value

8/20/03 10:00 Amino1 104.559
8/21/03 12:00 Amino1 161.54
8/20/03 10:00 Amino2 3622.123
8/21/03 12:00 Amino2 4484.632
8/21/03 10:00 Amino3 3.856
8/21/03 12:00 Amino3 6.535

Can anyone help?
Thanks
Al
Click to expand...


.
Click to expand...
 
It works well thank you
Al
-----Original Message-----
Create a temporary table the same as your Excel (or just link to the Excel
spreadsheet).

Create a final table with the new layout.

Create a query to normalize the temporary table:

SELECT [Date], [Time], "Amino1" AS "Amino Type", Amino1
FROM MyTable
UNION
SELECT [Date], [Time], "Amino2" AS "Amino Type", Amino2
FROM MyTable
UNION
SELECT [Date], [Time], "Amino3" AS "Amino Type", Amino3
FROM MyTable

Use that to append to the final table.

BTW, Date and Time are both reserved words, so you should not use them as
field names. As well, why not store them as a single date/time field?
(Change [Date], [Time] in the query above to [Date] + [Time] AS DateTime)
And I'd recommend strongly against using spaces in your field names.

--
Doug Steele, Microsoft Access MVP



Al said:
I have data that comes in excel in the following format:

Date Time Amino1 Amino2 Amino3
8/20/2003 10:00 104.559 3622.123 3.856
8/21/2003 12:00 161.54 4484.632 6.535

I would like to import it into access in the following
fashion:

Date Time Amino Type Value

8/20/03 10:00 Amino1 104.559
8/21/03 12:00 Amino1 161.54
8/20/03 10:00 Amino2 3622.123
8/21/03 12:00 Amino2 4484.632
8/21/03 10:00 Amino3 3.856
8/21/03 12:00 Amino3 6.535

Can anyone help?
Thanks
Al
Click to expand...


.
Click to expand...
 
Back
Top