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

 

[Guest]

Hi,
sorry forgot something, when importing an Excel Spreadsheet into Access,
Access analyses the first 15 (approx.) rows of data and determines on the
found data the type of the field (numeric, text, date, etc.) - you are not
able to change the type. If you save the file as .csv ot .txt and import it
then you have more control of the type and are able to select it.

Bernd

 

[John Nurick]

Hi Andrew,

This is normal. The Access routine that imports Excel data doesn't allow
direct control over the types of the fields it creates, and often runs
into trouble with Excel columns that contain a mix of numeric and text
values.

You can work round this in any of the following ways:

1) create the table yourself with the field types you need, then import
the spreadsheet data. The field names in the table must exactly match
the column headings in Excel.

2) make sure that at least one row near the top of the Excel table
contains values that can only be interpreted as the data types you need
(e.g. text that cannot be interpreted as a number if you want the column
to become a text field). Sometimes the simplest way to do this is to
insert a first row of "dummy" data into Excel just for this, and then
delete it from the Access table once the data has been imported.

3) Access assigns field types on the basis of the data it finds in the
first dozen or so rows of the spreadsheet table. It pays no attention to
cell formats. Sometimes a useful trick is to put an apostrophe ' in
front of numeric values in thecells (e.g. '999): this forces Excel and
Access to treat them as text, but the apostrophe is not displayed in
Excel or imported into Access.

(Just to make things more confusing, Access applies different rules
when you're linking Excel data rather than importing it. Simplifying
somewhat: when importing, any text value in the first few rows will
cause a field to be imported as text. When linking, any *numeric* value
in the first few rows will cause a field to be linked as numeric even if
all the other values are non-numeric.)

3) Write your own import code using Automation to get the values direct
from the worksheet cells and recordset operations or queries to append
them into your table.



Sub AddApostrophes()
Dim C As Excel.Range
For Each C In Application.Selection.Cells
If IsNumeric(C.Formula) Then
C.Formula = "'" & C.Formula
End If
Next
End Sub

Sub RemoveApostrophes()
Dim C As Excel.Range
For Each C In Application.Selection.Cells
C.Formula = C.Formula
Next
End Sub

 

[Jamie Collins]

Access assigns field types...

No, this is a Jet process.

...on the basis of the data it finds in the
first dozen or so rows of the spreadsheet table.

This is determined by a Jet registry key which, if set to zero, scans
all rows. For more details see:

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

It pays no attention to
cell formats.

That is incorrect. As proof, create an Excel workbook containing a
single cell formula

=38000

Change the cell format to (custom) dd mmm yyyy. Include the column in
a query e.g.

SELECT F1
FROM [Excel 8.0;HDR=No;C:\Tempo\db.xls;].[Sheet1$]
;

The value appears as 14 JAN 2004 (in local format) and, using ADO's
OpenSchema method, the column is show to have been determined as
adDate ('a date value').

Jamie.

--