Data conversion on import

[FP1]

I'm importing data from an Excel spreadsheet and Access *refuses* to import
a column that has a model number that is sometimes text and sometimes a
number. In excel, I've done a 'format cells' and defined all as text, to no
avail. Access won't treat the numbers as text. Ironically, it imports the
text ones just fine. I suppose I could prefix all the model numbers with
'XX' or something, but that seems pretty cheesy.

WHAT am I doing wrong? Any help appreciated!

 

[FP1]

=?Utf-8?B?SmVycnkgV2hpdHRsZQ==?=

Access has a very bad habit of checking the first few rows of the
Excel spreadsheet and assuming the data type. If there's a number or
empty cell, Access might say that it's a number field no matter what
is in the cells farther down.

One trick is to insert a bogus first row in Excel that had the correct
data types such as ABC for a text field. Import the spreadsheet then
delete the bogus record.

Your "cheesy" method should work if you append XX to the problem field
in just the first record.

Good idea! I forgot about the first row thing. Thanks!

 

[Guest]

Access has a very bad habit of checking the first few rows of the Excel
spreadsheet and assuming the data type. If there's a number or empty cell,
Access might say that it's a number field no matter what is in the cells
farther down.

One trick is to insert a bogus first row in Excel that had the correct data
types such as ABC for a text field. Import the spreadsheet then delete the
bogus record.

Your "cheesy" method should work if you append XX to the problem field in
just the first record.

 

[John W. Vinson]

I'm importing data from an Excel spreadsheet and Access *refuses* to import
a column that has a model number that is sometimes text and sometimes a
number. In excel, I've done a 'format cells' and defined all as text, to no
avail. Access won't treat the numbers as text. Ironically, it imports the
text ones just fine. I suppose I could prefix all the model numbers with
'XX' or something, but that seems pretty cheesy.

WHAT am I doing wrong? Any help appreciated!

You're not doing anything wrong. Since Excel cells don't have strong datatypes
and Access fields do, the import mechanism must GUESS the appropriate datatype
based on the first few rows in the sheet being imported - and it can (and
will) guess wrong in circumstances like this.

The only good getaround I know is to put a dummy row at the top of the
spreadsheet with an unambiguous text value, and discard the row after import.

You *might* be able to get satisfactory results by linking to the spreadsheet
(rather than importing), and run an Append query.

John W. Vinson [MVP]