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!

--
Posted via a free Usenet account from http://www.teranews.com

=?Utf-8?B?SmVycnkgV2hpdHRsZQ==?=
<(E-Mail Removed)> wrote in
news:B699B9C8-D74C-448C-944E-(E-Mail Removed):

> 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!

--
Posted via a free Usenet account from http://www.teranews.com

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.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

"FP1" wrote:

> 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!

On 07 Nov 2007 17:53:08 GMT, FP1 <(E-Mail Removed)> wrote:

>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]