Excel List To Access Table

[ColinKJ]

Hi All,

I'm new to Access, I have the following contract list which I've downloaded from our business system, and I want to create a DB table from this list how do I go about it.

<TABLE style="WIDTH: 283pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=377 x:str><COLGROUP><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 3730" width=102><COL style="WIDTH: 206pt; mso-width-source: userset; mso-width-alt: 10057" width=275><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: olive; WIDTH: 77pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl22 height=17 width=102>Sales document</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: olive; WIDTH: 206pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl22 width=275>Description</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ffff99; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl23 height=17>40000000</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ffff99; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl23>146-148 Clerkenwell Rd - Redab 2002/03</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ffff99; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl23 height=17>40000001</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ffff99; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl23>EHS Crystal Court 2002/03</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ffff99; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl23 height=17>40000002</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ffff99; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl23>Sweden House - Asticus 2002/03</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ffff99; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl23 height=17>40000003</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ffff99; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl23>Washington House - Asticus 2002/03</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ffff99; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl23 height=17>40000004</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ffff99; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl23>39 Cornhill FM</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ffff99; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl23 height=17>40000005</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ffff99; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl23>Lovell House Tenants 2002/03</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ffff99; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl23 height=17>40000006</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ffff99; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl23>South Point - Sutton 2002/03</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ffff99; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl23 height=17>40000007</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ffff99; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl23>Allied Domecq - Conduit St 2002/03</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ffff99; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl23 height=17>40000008</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ffff99; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl23>20 Soho Square - Asticus 2002/03</TD></TR></TBODY></TABLE>
Excel & Access 2003

 

[Joe4]

You can link Excel files in as Table Objects, or import the Excel into to a new Table. See "Import or link data in an Excel workbook" in Access' built-in help files for details.

 

[ColinKJ]

Thanks Joe4,

That's a good start, I'll be back soon 4 some more help

​

 

[ColinKJ]

Hi Joe4,

I've just tried to import data from an excel SS 2003, in to an Access 2003 Table using the suggested route ´Get External Data' in the Access File Menu , but I got an error message to say "An error occured trying to import file (File Name Path). The file was not imported".

But it didn't give any error number or reason.

I've checked the the data and format in the SS match the table field formats.

Any ideas?

 

[Joe4]

Are you trying to import to a new table, or an existing table?
What is the full file path and name of the file you are trying to import?

 

[ColinKJ]

Hi Joe4,

It is an existing table, and the full path is "D:\Acces Test\SAP Contract List 21-7.2011ckj.xls

 

[Joe4]

It is an existing table

That is what I thought. What happens is when you import an Excel file, Access tries to determine the format of each field automatically "on-the-fly" by looking at the first few records in Excel (I think it may be the first ten). If it guesses wrong, and their format guess doesn't match how the field is formatted in your Access table, you will get errors.

I see this happen a lot if you have mixed data, or blanks or nulls in some data records, or numbers formatted as text. It can make importing Excel files directly quite a pain.

To find out which field is the offending issue, you could try importing the data to a new table, then compare the formats Access asigned to those fields to what you have in your existing table.

To get your data in, you have a few options:

1. Mess around with the order of your data in your Excel file to co-erce Access to guess right (i.e. move blanks/nulls towards the bottom of your data);

2. Import into a new Temporary Table in Access, then use an Append Query to write the data from your Temporary Table into your Final Table (this will allow you to make any data-type conversions through calculated fields on your query);

3. Export your Excel file to a text file, and then import that text file into Access (where you control the field formats through the Import Wizard).