Importing Excel Spreadsheet into Access 2000

[Guest]

I have created a macro to import data from an Excel spreadsheet to an Access
table. All of the column headings and data types match i.e. text =text, etc.
One of the columns, Operator ID, in the Excel spreadsheet has both numbers
and alpha, or alpha-numeric characters. Example:

Operator ID
202
GUP
GT4

The numeric characters import without issue, but the alpha/alpha-numeric
characters are left off and end up in a import error table. It seems as
though there should be a simple, non-cosmic solution, but it escapes me. I
have read previous posts and just become confused. Is there a simple fix to
this problem? If not a simple fix, can someone help me to overcome the
problem?

 

[Guest]

Try giving the column a different header name. I think Access gets confused
when it sees text in a column headed with "ID."

Dave

 

[Guest]

Sometimes the integration between microsoft products is too tight. By
importing an Excel spreadsheet, you do not get the option of assigning a
field type. It will automatically assign the field type by the first record
imported. You had the issue with text and numbers but it will also do it
with double versus integer where an integer is the first record, no decimals
will be imported. It is much better to save your spreadsheet as a .csv file
and then import the .csv file. Then you can assign your own Import
Specifications.

Hope this helps.

 

[Joseph Meehan]

I have created a macro to import data from an Excel spreadsheet to an
Access table. All of the column headings and data types match i.e.
text =text, etc. One of the columns, Operator ID, in the Excel
spreadsheet has both numbers and alpha, or alpha-numeric characters.
Example:

Operator ID
202
GUP
GT4

The numeric characters import without issue, but the
alpha/alpha-numeric characters are left off and end up in a import
error table. It seems as though there should be a simple, non-cosmic
solution, but it escapes me. I have read previous posts and just
become confused. Is there a simple fix to this problem? If not a
simple fix, can someone help me to overcome the problem?

My suggestion is to add a row in Excel right after the headings. Make
sure you have text in any column that will contain text and numbers in
columns that you want to import as numbers.

 

[Guest]

Actually, the solution was far easier than I could have imagined. I simply
sorted the data in the Excel spreadsheet by the Operator ID [Descending] so
that the alpha characters were first. The data impoted to the table without
error.

Thank you for the suggestions!