Mixed Data Import from Excel

[mikeg710]

I need to import Excel spreadsheet data into an Access 2003 table. Once in
Access, some of the data will be used to perform aggregate functions for
reports.

Several columns being imported from Excel contain mixed data (numeric-only
entries and text-only entries). These cells SHOULD be all numeric-only (as
they are being used to enter financial/budget numbers), but a lot of the
cells have text-only entries (such as "TBD", "N/A", "CA", or just blank)
where an actual numeric value is not yet known, not applicable, etc.

I import the spreadsheet using the 'Get External Data' File menu option in
Access. Access determines the data type of the field based on the first 25
rows it reads.

Once it assigns a "mixed data" field a numeric data type and then hits one
of the text-only entries, an 'ImportError' record is flagged with a "Type
Conversion Failure" error. Obviously if it reads a text-only entry within
the first 25 records it assigns the field as 'text', and all the numeric-only
entries are now text fields.

I would prefer to keep these "mixed data" fields as numeric in my Access
table, if possible. This will make query and report aggregate functions with
the numeric data a lot easier.

Do I just allow the imported "mixed data" fields to be saved as text fields
in my Access table, and write expressions to evaluate said fields as numeric
for calculations when needed?

Is there a way I can "force" the handful of text-only entries to save as
numeric values in my Access table instead?

 

[MH]

Would it not make more sense to tidy up the spreadsheet before importing it?

MH

 

[George Nicholson]

One approach:

Import your Excel data into a pre-existing empty temp table with all fields
set to text. Then run a query that appends that data to a "real" table. This
query would coerce, transform or ignore your imported data into "usable"
data per your desire on a field-by-field basis (i.e., if the value of a
certain field is numeric, append the value to table record, otherwise field
empty, yada, yada).

HTH,