Errors During Import From Excel to Access

[Quintios]

I'm getting errors when I import a table from Excel97 into Access 2000.

The existing table and the Excel spreadsheet match exactly (the table was created originally from an Excel import into a new table.) The table fields were all changed to 'Text', so that either numbers or characters would be accepted.

However, on one particular column, there are numbers in the cells (in Excel) until row number 28, where the entry is Text. I seem to remember that there's a way to set the depth which an application searches to find a file type. I need to set that number to something much larger.

Does anyone know how to do this? The data imports regardless, so it doesn't really matter that I get a "File Type Conversion" message. I guess the thing I'm wanting to avoid is the table that's created with the error list in it, as each import would create an additional table (I hope to have all tables hidden when I finally distribute the database.)

Any help?

Onwards,

Q-

 

[B827]

The microsoft article Q109376 says insert a line with text at the top of the sheet. Then you have to delete it in the database! I usually sort the data in the spreadsheet by the problematic field(s) in descending order this puts data with alphas at the top, or it does with the data I use. Sandy

 

[Quintios]

Does anyone know how to suppress the 'db_import_errors' table that's created?

Thanks!
Onwards,

Q-

 

[dkaplan]

I know it's been almost a year since your original post, but I've just hit this bug myself, so I thought I'd add two things which may or not be useful to you.

1. The problem only shows up on our computers running on NT. Those on Win 2k import without the error.

2. If your import is automated, you can delete the table named "Input errors..." with either a macro or code.

 

[Quintios]

I just happened to be browsing around at my previous posts and saw your note. Thanks for the info. As it turns out, if you use a TXT (or CSV) file to import you can avoid this extra table. Also, there's a registry hack to prevent access from assuming a certain format based on the first 4 lines of a XLS sheet. Basically what you do is increase the number of lines that Access has to look at, and it will prevent some of the import errors based on formatting...

Dunno where I put that code, but if someone emails me I'll look for it.

Onwards,

Q-

 

[dkaplan]

Hi Quintios.

Yes, if you can find that code that would be great. I could find no reference to it in the Knowledge Base other than article 189897 which implied that the setting could only be referenced through regedit (which scares me).

So far my fix has been to import to a temporary table with text in a dummy first row which eventually gets deleted.

Clunky and time consuming, but it works.

Dennis

 

[Quintios]

That's the article... It is a registry fix. That's exactly what you have to do.

Don't be afraid of the registry tho. Just follow the article word for word and you'll be fine. But this is also a good time to learn how to back up and restore your registry, knowledge that no computer guru should be without.

Have FUN! Onwards,

Q-

 

[dkaplan]

Thanks for the encouragement.
I'll give it a try.