Can't change a field's data type when importing from Excel

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm using Access 2000 and Excel 2000. I'm trying to import a spreadsheet
from Excel, but the Import wizard does not allow me to change the data type
of any of the fields. Anyone know why?

The reason I need to change it is there is one field in my spreadsheet that
is giving me problems. I have it formatted to text in Excel, but Access
thinks it is a number (specfically the Double format). I need it to be text
in Access as well.
 
You cannot specify the data type when importing from EXCEL.

Jet looks at the first "x" number of rows (actual number is based on a
setting in a registry key -- default usually is 25, but can be as small as
8) to see the data that are present, and then decides what the data are for
the import. In your case, I'm guessing that the nonumeric values are in a
row farther down than the 25th row.

Can you put in the first row the "nonnumeric" value so that ACCESS sees it
and knows that not all values are numbers.

Or can you go into EXCEL and insert a ' character in front of each of the
values in the column? That will tell ACCESS to treat the value as a number?

You could try using an append query to read the data directly from the EXCEL
worksheet (EXCEL file must be closed when you do this):

INSERT INTO DestinationTableName ( Field1, Field2, Field3 )
SELECT ColA AS Col1, ColB AS Col2, ColC AS Col3
FROM [Excel 11.0;Database=C:\MyFolder\MyWorkbook.xls;].[MySheet$]


If nothing else works, you can use Automation (via VBA code) to open the
EXCEL file and read the spreadsheet cell by cell and write the values into a
recordset that is based on the target table.
 
Yes, you are right. My non-numeric data is far beyond the 25th row for that
field. So I sorted my Excel file by that field in decending order so all the
non-numeric data came to the top. Then I was able to import it into Access
with no problem. Thank you for your help.

Is there a way to change the default number of rows that Jet looks at to
determine the type of data that is in that field?

Ken Snell said:
You cannot specify the data type when importing from EXCEL.

Jet looks at the first "x" number of rows (actual number is based on a
setting in a registry key -- default usually is 25, but can be as small as
8) to see the data that are present, and then decides what the data are for
the import. In your case, I'm guessing that the nonumeric values are in a
row farther down than the 25th row.

Can you put in the first row the "nonnumeric" value so that ACCESS sees it
and knows that not all values are numbers.

Or can you go into EXCEL and insert a ' character in front of each of the
values in the column? That will tell ACCESS to treat the value as a number?

You could try using an append query to read the data directly from the EXCEL
worksheet (EXCEL file must be closed when you do this):

INSERT INTO DestinationTableName ( Field1, Field2, Field3 )
SELECT ColA AS Col1, ColB AS Col2, ColC AS Col3
FROM [Excel 11.0;Database=C:\MyFolder\MyWorkbook.xls;].[MySheet$]


If nothing else works, you can use Automation (via VBA code) to open the
EXCEL file and read the spreadsheet cell by cell and write the values into a
recordset that is based on the target table.

--
Ken Snell
<MS ACCESS MVP>



jeremy nickels said:
I'm using Access 2000 and Excel 2000. I'm trying to import a spreadsheet
from Excel, but the Import wizard does not allow me to change the data
type
of any of the fields. Anyone know why?

The reason I need to change it is there is one field in my spreadsheet
that
is giving me problems. I have it formatted to text in Excel, but Access
thinks it is a number (specfically the Double format). I need it to be
text
in Access as well.
Click to expand...
Click to expand...
 
Depending upon your OS, look in the registry editor under
HKLM\Software\Microsoft\Jet\4.0

and then inspect the subkeys that are located in that key (including the
"folders" under 4.0). For my Windows XP SP2 setup, the key is
HKLM\Software\Microsoft\Jet\4.0\Engines\Excel\TypeGuessRows

There also is a key here for the maximum number of rows that are scanned:
HKLM\Software\Microsoft\Jet\4.0\Engines\Text\MaxScanRows

Here is an article that discusses some of this info:
http://www.dicks-blog.com/archives/2004/06/03/external-data-mixed-data-types/


--

Ken Snell
<MS ACCESS MVP>


jeremy nickels said:
Yes, you are right. My non-numeric data is far beyond the 25th row for
that
field. So I sorted my Excel file by that field in decending order so all
the
non-numeric data came to the top. Then I was able to import it into
Access
with no problem. Thank you for your help.

Is there a way to change the default number of rows that Jet looks at to
determine the type of data that is in that field?

Ken Snell said:
You cannot specify the data type when importing from EXCEL.

Jet looks at the first "x" number of rows (actual number is based on a
setting in a registry key -- default usually is 25, but can be as small
as
8) to see the data that are present, and then decides what the data are
for
the import. In your case, I'm guessing that the nonumeric values are in a
row farther down than the 25th row.

Can you put in the first row the "nonnumeric" value so that ACCESS sees
it
and knows that not all values are numbers.

Or can you go into EXCEL and insert a ' character in front of each of the
values in the column? That will tell ACCESS to treat the value as a
number?

You could try using an append query to read the data directly from the
EXCEL
worksheet (EXCEL file must be closed when you do this):

INSERT INTO DestinationTableName ( Field1, Field2, Field3 )
SELECT ColA AS Col1, ColB AS Col2, ColC AS Col3
FROM [Excel 11.0;Database=C:\MyFolder\MyWorkbook.xls;].[MySheet$]


If nothing else works, you can use Automation (via VBA code) to open the
EXCEL file and read the spreadsheet cell by cell and write the values
into a
recordset that is based on the target table.

--
Ken Snell
<MS ACCESS MVP>



message
I'm using Access 2000 and Excel 2000. I'm trying to import a
spreadsheet
from Excel, but the Import wizard does not allow me to change the data
type
of any of the fields. Anyone know why?

The reason I need to change it is there is one field in my spreadsheet
that
is giving me problems. I have it formatted to text in Excel, but
Access
thinks it is a number (specfically the Double format). I need it to be
text
in Access as well.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top