field def problem when importing from Excel

[Keith G Hicks]

Using Access03 but a2k format.

I'm pretty sure this has worked for me in the past but I could be mistaken.
When I import an Excel spreadsheet using the import wizard, I have no option
to set the field definitions in the wizard. The pages in the wizard are as
follows:

1. "First Row Contains Column Headings" (this is fine)
2. "In a new table" or choose an existing table (this is fine)
3. This page is supposed to let you click on the columns and specify a field
name, data type, etc. for each column. However, no matter which column I
click in the grid at the bottom of the page, the options at the top do not
change. It always shows the first column. I am unable to change anythign for
any other columsn. In addition, I cannot change the field type of the first
column. It's grayed out.

The import actually works ok but one of the columsn is not importing as I
need it to be. It's a zip code column where quite a few are 5+4 digit zips.
Access is forcing that column to type "double" and putting all the entries
that are like "12547-1243" into an import errors table. In addition, any zip
codes that start with 0's are coming in incorrectly. This is a big problem
as zip codes are NOT really numeric (especially if there are any Canadian
ones in there, eh?). I know I can change the field types after the import
and manually go over the data and make corrections, but it's not supposed to
work that way. Any suggestions?

Thanks,

Keith

 

[Pieter Wijnen]

Change the following key (mine is set to 25 rows)

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel]
"TypeGuessRows"=dword:00000019

HtH

Pieter

 

[Keith G Hicks]

I read this webpage:
http://dailydoseofexcel.com/archives/2004/06/03/external-data-mixed-data-typ
es/
And my setting is hex 19 (decimal 25). Which makes no sense because
http://support.microsoft.com/kb/189897 says it cannot be higher than 16.
I don't see what this will do to solve my problem anyway. All that should do
is to tell Access to scan 25 rows instead of 19. How do I turn on the
feature that lets me set the datatype manually in the wizard?

Keith

"Pieter Wijnen"
<it.isi.llegal.to.send.unsollicited.mail.wijnen.nospam.please@online.replace
..with.norway> wrote in message news:[email protected]...

Change the following key (mine is set to 25 rows)

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel]
"TypeGuessRows"=dword:00000019

HtH

Pieter

Using Access03 but a2k format.

I'm pretty sure this has worked for me in the past but I could be
mistaken.
When I import an Excel spreadsheet using the import wizard, I have no
option
to set the field definitions in the wizard. The pages in the wizard are as
follows:

1. "First Row Contains Column Headings" (this is fine)
2. "In a new table" or choose an existing table (this is fine)
3. This page is supposed to let you click on the columns and specify a
field
name, data type, etc. for each column. However, no matter which column I
click in the grid at the bottom of the page, the options at the top do not
change. It always shows the first column. I am unable to change anythign
for
any other columsn. In addition, I cannot change the field type of the
first
column. It's grayed out.

The import actually works ok but one of the columsn is not importing as I
need it to be. It's a zip code column where quite a few are 5+4 digit
zips.
Access is forcing that column to type "double" and putting all the entries
that are like "12547-1243" into an import errors table. In addition, any
zip
codes that start with 0's are coming in incorrectly. This is a big problem
as zip codes are NOT really numeric (especially if there are any Canadian
ones in there, eh?). I know I can change the field types after the import
and manually go over the data and make corrections, but it's not supposed
to
work that way. Any suggestions?

Thanks,

Keith

 

[Pieter Wijnen]

I agree with you (one of the reasons I insist on tab seperated text files if
possible).
& the setting was a test by me that I haven't reset, due to the same
problem.
The only "solution" AFAIK is to "prepare" the Excel sheet beforehand

Pieter

I read this webpage:
http://dailydoseofexcel.com/archives/2004/06/03/external-data-mixed-data-typ
es/
And my setting is hex 19 (decimal 25). Which makes no sense because
http://support.microsoft.com/kb/189897 says it cannot be higher than 16.
I don't see what this will do to solve my problem anyway. All that should
do
is to tell Access to scan 25 rows instead of 19. How do I turn on the
feature that lets me set the datatype manually in the wizard?

Keith

"Pieter Wijnen"
<it.isi.llegal.to.send.unsollicited.mail.wijnen.nospam.please@online.replace
.with.norway> wrote in message

Change the following key (mine is set to 25 rows)

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel]
"TypeGuessRows"=dword:00000019

HtH

Pieter

Using Access03 but a2k format.

I'm pretty sure this has worked for me in the past but I could be
mistaken.
When I import an Excel spreadsheet using the import wizard, I have no
option
to set the field definitions in the wizard. The pages in the wizard are as
follows:

1. "First Row Contains Column Headings" (this is fine)
2. "In a new table" or choose an existing table (this is fine)
3. This page is supposed to let you click on the columns and specify a
field
name, data type, etc. for each column. However, no matter which column
I
click in the grid at the bottom of the page, the options at the top do not
change. It always shows the first column. I am unable to change
anythign
for
any other columsn. In addition, I cannot change the field type of the
first
column. It's grayed out.

The import actually works ok but one of the columsn is not importing as I
need it to be. It's a zip code column where quite a few are 5+4 digit
zips.
Access is forcing that column to type "double" and putting all the entries
that are like "12547-1243" into an import errors table. In addition,
any
zip
codes that start with 0's are coming in incorrectly. This is a big problem
as zip codes are NOT really numeric (especially if there are any Canadian
ones in there, eh?). I know I can change the field types after the import
and manually go over the data and make corrections, but it's not supposed
to
work that way. Any suggestions?

Thanks,

Keith

 

[Keith G Hicks]

Fortunately this import is small enough that I can handle some manual
modifications. But where I'm really lost is that this used to work. I just
tried it on a tab delimited text file and I have the same problem. There is
no way to edit the field types in the wizard. Now I knwo this used to work
at one time, maybe in an earlier version of Access because I clearly
remember doing this before. Anyone know when Microsoft was gracious enough
to introduce this bug and why it was never fixed?!?!?!?

Keith

 

[Jamie Collins]

I read this webpage:http://dailydoseofexcel.com/archives/2004/06/03/external-data-mixed-d...
es/
And my setting is hex 19 (decimal25). Which makes no sense becausehttp://support.microsoft.com/kb/189897says it cannot be higher than 16.
I don't see what this will do to solve my problem anyway. All that should do
is to tell Access to scan 25 rows instead of 19.

I think the KB articles means that 16 is the considered maximum e.g.
you can specify a higher value but it won't be honoured. I suggest you
change the value to zero, which should result in *all* rows being
scanned.

Jamie.

--