remedy: import problems with alpha and numeric data in column

[Guest]

I have mixed data in a record (column) such as PA123 and 10234. When I
import two different worksheets into Access from Excel, I get type conversion
errors. What is the best was to format these columns to avoid errors. I use
Access to compare the data from one spreadsheet to another. The data above
represents position number in my organization and some positions start with
the alpha characters and some with just numeric numbers. I formatted the
columns as "text" prior to importing but one spreadsheet imports that column
as "number" (even though I formatting it as text. and the other comes in as
text--so I get a type mismatch and the import deletes records.

 

[Guest]

Hi, Veronica.

The easiest way to get around Jet making the wrong guess about a column's
data type is to use the Import Text Wizard, not the Import Spreadsheet Wizard.

To do so, convert the spreadsheet to a CSV file (don't worry, the formatting
will be lost anyway when importing the data into an Access table). Use
Access's Import Text Wizard to import the CSV file into the database.
Designate the appropriate column to be a text field, not a numerical field.
When using this Wizard, save these settings as a new import specification so
that it can be reused next time, and Access will always use the data types
for each column that you've designated in this import specification.

In case you're hesitant to take the extra time to convert the spreadsheet,
importing text is _much_ faster than importing XLS files (the Wizard has to
dig through a lot of formatting in the XLS file to dig out the data), so the
more records imported, the more time will be saved by converting to CSV or
TXT first.

And if there are any errors while using the Import Text Wizard, Access gives
an error message to the user about what failed and saves information about
each record's inability to be imported in an Import_Errors table. If the
Import Spreadsheet Wizard is used and something fails, that Wizard will give
the fatal error message ". . . failed to import <fileName>," which leaves one
completely in the dark as to what went wrong.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

 

[Default User]

underwent

 

[Van T. Dinh]

You have mixed columns in Excel in your spreadsheets but all values in a
Field in an Access Table must have the same data type.

What I normally do with a mixed column is to create a calculated column in
Excel using the spreadsheet function TEXT() to convert the mixed column to a
column of Text data type (some values will contain only digit characters!).

When I import the modified Excel spreadsheet, I import the calculated column
and skip the original mixed column.