Importing Excel spreadsheet into Access - data type problem

[Andrew Good]

A number of users here at the University of Plymouth have reported to
me a problem when they import Excel spreadsheets into Access. It does
not allow them to specify the data types of the fields to be imported.
I have tried this myself and have encountered the same problem.

Versions are:
Windows XP Professional SP 1
MS Office XP Professional SP 2. (The problem is not resolved by
applying Office XP Pro SP 3.)

Here's the recipe: Create an Excel spreadsheet, with column headings
in its first row, and save it as test.xls. Exit from Excel.

Start up Access and create a new, empty database.
Choose File -> Get External Data -> Import... and select test.xls.

The Import Spreadsheet Wizard starts up. Select Sheet1, check "First
Row Contains Column Headings", and select "Store Data in a New Table."

It now asks for information about each of the fields to be imported.
But the Data Type is grayed out and cannot be changed from the Data
Type that Access displays.

Can anyone tell me why this is happening, and how to put it right?

Andrew Good
Applications Team
University of Plymouth

 

[Tonín]

Hi, Andrew

I really cannot tell you the reason for that behavior in Access, and I'll be
waiting for other responses to your question. Where I work, people sometimes
ask me about the problem you described. I usually suggest them these two
possibilities to "bypass" the problem:

1. Customize Data Types AFTER data imported (in the table design view)

2. If user gets "Importing errors", then I suggest him/her insert a row in
Excel under the headings row, BEFORE data imported, just to declare types. I
mean, for example, all employees in my company have a "Personal ID number".
For about 90% employees this ID number is effectively an 8 digit NUMBER, but
there are some employees whose ID is something like that: "11321X67" (no
idea about why). If a user tries to import a list of employees from Excel
like this:


ID Name Birth
-----------------------------------
27387623 Peter 12/06/66
74632547 Arthur 06/01/58
(... 100 additional populated rows ...)
27387623 Tom 12/06/66
7463X547 Fred 06/01/58

user will get an importing error because the "7463X547" ID. Reason is Access
determines Data Type exploring only the very first rows in the spreadsheet.

Solution I usually tell to my users is this:

ID Name Birth
-----------------------------------
abcdefgh abcdefgh 01/01/01 <----- Insert a row like this
27387623 Peter 12/06/66
74632547 Arthur 06/01/58
(... 100 additional populated rows ...)
27387623 Tom 12/06/66
7463X547 Fred 06/01/58

Then, after data imported, delete the first no meaning record.


Regards

Tony (Spain)

 

[Jamie Collins]

(e-mail address removed) (Andrew Good) wrote ...

A number of users here at the University of Plymouth have reported to
me a problem when they import Excel spreadsheets into Access. It does
not allow them to specify the data types of the fields to be imported.

Read:

http://www.dicks-blog.com/excel/2004/06/external_data_m.html

In summary, rather than the user, Jet determines the data type based
on the data itself.

Jamie
Plymouth Poly Alumni

--