Type Conversion Error Issue

[Glencannon4424 via AccessMonster.com]

Hello,

I am trying to import several Excel sheets into Access. 4 columns are text;
the remaining 50 are numeric. I’ve scrubbed the data prior to import but am
getting the dreaded ‘import errors’ table for cells which are null. Based on
previous postings, I’ve tried:

- Adding several several rows of Xs and 1s at the beginning of the data to
indicate which rows I want captured as text and which I want as numeric
- Saving the Excel file as a CVS and TEXT file, importing it and then
appending it to an existing table which has the proper text / numeric columns

Access is importing the majority of the data properly into the text and
numeric columns but always lists the blank cells as errors in import error
table. How do I get Access to recognize that I want those cells to be blank
during the importing process?

Any help would be appreciated!

Heath

 

[Jeff Boyce]

It sounds as though you are relying on Access to properly guess the correct
datatype. Instead, just create a table of your own in Access with the
correct datatypes. Import to that.

Or better still, since Excel data is rarely well-normalized, import to that
you-defined table as a temporary location, then use queries to parse the
data into your more permanent and well-normalized Access table structure.

Regards

Jeff Boyce
Microsoft Office/Access MVP