Excel 2000 to Access 2000

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

Guest

I have received data in an Excel spreadsheet that I wish to import into Access 2000. The data in the spreadsheet, unfortunately, has the columns at the side and the details across the page, which means that the headings are constantly repeated, eg

Name: Jo Blogg
Company: Jo Bloggs & C
City: New Yor
Name: Sue Smit
Company: Sue Smith’s boutiqu
City: Los Angele
Name: John Brow
Company: John Brown Seafood Delight
City: Washingto

How can I bring this into Access properly? Grateful for any suggestions
 
Trish,

I'm not claiming there's no better solution, but I have one that's worth
giving a shot if your spreadsheet is big...
It takes a step in Excel first: assuming your data in Excel is already in
Columns (if colon delimited as below, then just do a Text to Columns); then
use an extra column to add a unique number for each group of data, by using
formula:
=INT((ROW(A1)-1)/3+1)
for data starting in row 1, or
=INT((ROW(A3)-ROW(A$2)-1)/3+1)
for data starting in row 3 (change the cell references accordingly for any
other starting row)
and copy down to the end of the data. Then save the spreadsheet and go to
Access.
Use menu item File > Get External Data > Link to make the spreadsheet
available to Access as a linked table.
Make a crosstab query on it, using the column with the repeating field names
as Column headings, the one with names etc. as Values (Totals function >
First) and the one with the numbers as Row Headings. This should get you the
data in a table-like format. This done, you can make a new query on the
previous one, that can be either a make-table or append one, to get the data
in a table. The numbers inserted with the trick in Excel can well serve as a
PK if you don't want an autonumber, otherwise you don't need to put them in
the table.

HTH,
Nikos

Trish said:
I have received data in an Excel spreadsheet that I wish to import into
Click to expand...
Access 2000. The data in the spreadsheet, unfortunately, has the columns
at the side and the details across the page, which means that the headings
are constantly repeated, eg:
 
Back
Top