Importing Excel, problem with data types.

[Jeff]

Hello,

When trying to import an Excel file, the Excel File has a column with most
of the data as Numbers, but some have Text. Because most are Numbers, Access
is thinking this Field is a Number & then when trying to import will give
Errors on the rows that have letters in that Field.

Becase this Excel file comes from other companies it is not an option for me
to ask them to format the data.

One option as a work around was from another Response from another post,
where (Ken Snell MS ACCESS MVP) stated
"There are ways to make changes in the registry to force Jet to examine all
rows before deciding on a data type"

But he didnt put what to change in the Registry. Any one know ? Or Ken if
you happen to read this, how can I do this ?

If not what other work around is their, because again changing the data in
the Excel sheet is not really an option, I need someway to Force Access to
take that field as a Text Field.

Any help would be greatly appreciated.

Thank you,
Jeff

 

[rmullen]

ok - i'm not wicked smart like Ken ...

but why don't you put the excel file into a CSV file and then import it
like that into Access? i usually have been able to force Access to take
anything with csv files.

 

[Klatuu]

My guess is you are importing into a new table. Try creating a table with
the field types and lengths you want and import into it. You will, of
course, need to clear old data out before doing the import. This line of
code works well for that:

CurrentDb.Execute("DELETE * FROM MyTableName;", DbFailOnError

 

[Jeff]

Thank you for your replies...

But,

Response 1 - exporting to as ASCII file will work but dont want that as an
option as extra work for user.

Response 2 - Your suggestion of Clearing data 1st doesnt work. Again Access
sees the data 1st & looks at first 25 rows & if they are all #s then thinks
Field is a Number. So that is why I wanted Kens way.

So Ken are you out there ?

Anyone else out there know specifically what the Registry Setting to change
to do what Ken had suggested ?

Thank you,
Jeff

 

[Klatuu]

If you use an existing table and define the field in the Access table as
text, it will import as text.