Type conversion error importing from Excel

[Guest]

Hi, I'm not sure if this problem is with Access or Excel, but here goes.

I have an Excel spreadsheet that I want to import into a table in an Access
Db.

I've tried importing to an existing table, a new table, and even a
completely new db, but I keep getting this error on the same column when
trying to import.

In the spreadsheet, the column is formatted as text - some rows contain
numbers only, some letters and numbers, and some have '.' in them. No matter
what I do, Access insists that this column is a number.

I've tried linking to the spreadsheet then appending to a new table, but
still when you look at the link properties, this column shows as Number, no
matter what it's set to in the spreadsheet (where I've tried text, general
and even number to see what would happen).

So no matter what I do to this column in the spreadsheet, Access sees it as
a number and rejects it.

Any suggestions please? Thanks in advance.

G

 

[Guest]

Have you tried making an importspecification from this one? When you choose
import choose [Advanced] on the first page of the wizard. But if I remeber
right importing from xls doesn't offer the advanced option. In that case try
converting your xls file to csv (save as). When importing from csv you can
choose the [advanced] option. In the next dialog you can set every fieldtype
you want.

hth

 

[Guest]

Excellent! Just the job thanks!

Had tried csv already, but wasn't aware of the advanced options.

Cheers

G

Have you tried making an importspecification from this one? When you choose
import choose [Advanced] on the first page of the wizard. But if I remeber
right importing from xls doesn't offer the advanced option. In that case try
converting your xls file to csv (save as). When importing from csv you can
choose the [advanced] option. In the next dialog you can set every fieldtype
you want.

hth
--
Maurice Ausum

Hi, I'm not sure if this problem is with Access or Excel, but here goes.

I have an Excel spreadsheet that I want to import into a table in an Access
Db.

I've tried importing to an existing table, a new table, and even a
completely new db, but I keep getting this error on the same column when
trying to import.

In the spreadsheet, the column is formatted as text - some rows contain
numbers only, some letters and numbers, and some have '.' in them. No matter
what I do, Access insists that this column is a number.

I've tried linking to the spreadsheet then appending to a new table, but
still when you look at the link properties, this column shows as Number, no
matter what it's set to in the spreadsheet (where I've tried text, general
and even number to see what would happen).

So no matter what I do to this column in the spreadsheet, Access sees it as
a number and rejects it.

Any suggestions please? Thanks in advance.

G

 

[Ken Snell \(MVP\)]

You cannot use an import specification for an EXCEL import. That is only for
text file imports.

I assume that the "just numbers" strings are within the first 8 to 25 rows
or so of the EXCEL file.

What ACCESS and Jet are doing is assuming that the "text" data actually are
numeric data, and thus all your non-numeric text strings are "not matching"
to a numeric data type.

One way to "fix" this problem is to iInsert a ' character at the beginning
of each cell's value for that column in the EXCEL file. That should let Jet
(ACCESS) treat that column's values as text and not number.

Or insert a dummy row of data as the first row, with nonnumeric characters
in the cell in that column. That should let Jet (ACCESS) treat that column's
values as text and not number.

It's possible to force Jet to scan all the rows and not guess the data type
based on just the first few rows. See this article for information about the
registry key (see TypeGuessRows and MaxScanRows information):
http://www.dicks-blog.com/archives/2004/06/03/external-data-mixed-data-types/

--

Ken Snell
<MS ACCESS MVP>

Have you tried making an importspecification from this one? When you
choose
import choose [Advanced] on the first page of the wizard. But if I remeber
right importing from xls doesn't offer the advanced option. In that case
try
converting your xls file to csv (save as). When importing from csv you can
choose the [advanced] option. In the next dialog you can set every
fieldtype
you want.

hth
--
Maurice Ausum

Hi, I'm not sure if this problem is with Access or Excel, but here goes.

I have an Excel spreadsheet that I want to import into a table in an
Access
Db.

I've tried importing to an existing table, a new table, and even a
completely new db, but I keep getting this error on the same column when
trying to import.

In the spreadsheet, the column is formatted as text - some rows contain
numbers only, some letters and numbers, and some have '.' in them. No
matter
what I do, Access insists that this column is a number.

I've tried linking to the spreadsheet then appending to a new table, but
still when you look at the link properties, this column shows as Number,
no
matter what it's set to in the spreadsheet (where I've tried text,
general
and even number to see what would happen).

So no matter what I do to this column in the spreadsheet, Access sees it
as
a number and rejects it.

Any suggestions please? Thanks in advance.

G

 

[Guest]

Ken,

You are right, but when converting it to a .csv file you can create an
importspec. It's nothing more than a text file ... placing the additional '
in front of text would be my very last resort because you'd have to get rid
of them afterwards in the excelfile again.
--
Maurice Ausum

You cannot use an import specification for an EXCEL import. That is only for
text file imports.

I assume that the "just numbers" strings are within the first 8 to 25 rows
or so of the EXCEL file.

What ACCESS and Jet are doing is assuming that the "text" data actually are
numeric data, and thus all your non-numeric text strings are "not matching"
to a numeric data type.

One way to "fix" this problem is to iInsert a ' character at the beginning
of each cell's value for that column in the EXCEL file. That should let Jet
(ACCESS) treat that column's values as text and not number.

Or insert a dummy row of data as the first row, with nonnumeric characters
in the cell in that column. That should let Jet (ACCESS) treat that column's
values as text and not number.

It's possible to force Jet to scan all the rows and not guess the data type
based on just the first few rows. See this article for information about the
registry key (see TypeGuessRows and MaxScanRows information):
http://www.dicks-blog.com/archives/2004/06/03/external-data-mixed-data-types/

--

Ken Snell
<MS ACCESS MVP>

Have you tried making an importspecification from this one? When you
choose
import choose [Advanced] on the first page of the wizard. But if I remeber
right importing from xls doesn't offer the advanced option. In that case
try
converting your xls file to csv (save as). When importing from csv you can
choose the [advanced] option. In the next dialog you can set every
fieldtype
you want.

hth
--
Maurice Ausum

Hi, I'm not sure if this problem is with Access or Excel, but here goes.

I have an Excel spreadsheet that I want to import into a table in an
Access
Db.

I've tried importing to an existing table, a new table, and even a
completely new db, but I keep getting this error on the same column when
trying to import.

In the spreadsheet, the column is formatted as text - some rows contain
numbers only, some letters and numbers, and some have '.' in them. No
matter
what I do, Access insists that this column is a number.

I've tried linking to the spreadsheet then appending to a new table, but
still when you look at the link properties, this column shows as Number,
no
matter what it's set to in the spreadsheet (where I've tried text,
general
and even number to see what would happen).

So no matter what I do to this column in the spreadsheet, Access sees it
as
a number and rejects it.

Any suggestions please? Thanks in advance.

G

 

[Ken Snell \(MVP\)]

A .csv file can be handled by text import process, yes; and you can use an
import specification for that, yes. But that is a roundabout method to use
for most EXCEL files.
--

Ken Snell
<MS ACCESS MVP>

Ken,

You are right, but when converting it to a .csv file you can create an
importspec. It's nothing more than a text file ... placing the additional
'
in front of text would be my very last resort because you'd have to get
rid
of them afterwards in the excelfile again.
--
Maurice Ausum

You cannot use an import specification for an EXCEL import. That is only
for
text file imports.

I assume that the "just numbers" strings are within the first 8 to 25
rows
or so of the EXCEL file.

What ACCESS and Jet are doing is assuming that the "text" data actually
are
numeric data, and thus all your non-numeric text strings are "not
matching"
to a numeric data type.

One way to "fix" this problem is to iInsert a ' character at the
beginning
of each cell's value for that column in the EXCEL file. That should let
Jet
(ACCESS) treat that column's values as text and not number.

Or insert a dummy row of data as the first row, with nonnumeric
characters
in the cell in that column. That should let Jet (ACCESS) treat that
column's
values as text and not number.

It's possible to force Jet to scan all the rows and not guess the data
type
based on just the first few rows. See this article for information about
the
registry key (see TypeGuessRows and MaxScanRows information):
http://www.dicks-blog.com/archives/2004/06/03/external-data-mixed-data-types/

--

Ken Snell
<MS ACCESS MVP>

Have you tried making an importspecification from this one? When you
choose
import choose [Advanced] on the first page of the wizard. But if I
remeber
right importing from xls doesn't offer the advanced option. In that
case
try
converting your xls file to csv (save as). When importing from csv you
can
choose the [advanced] option. In the next dialog you can set every
fieldtype
you want.

hth
--
Maurice Ausum


:

Hi, I'm not sure if this problem is with Access or Excel, but here
goes.

I have an Excel spreadsheet that I want to import into a table in an
Access
Db.

I've tried importing to an existing table, a new table, and even a
completely new db, but I keep getting this error on the same column
when
trying to import.

In the spreadsheet, the column is formatted as text - some rows
contain
numbers only, some letters and numbers, and some have '.' in them. No
matter
what I do, Access insists that this column is a number.

I've tried linking to the spreadsheet then appending to a new table,
but
still when you look at the link properties, this column shows as
Number,
no
matter what it's set to in the spreadsheet (where I've tried text,
general
and even number to see what would happen).

So no matter what I do to this column in the spreadsheet, Access sees
it
as
a number and rejects it.

Any suggestions please? Thanks in advance.

G