text to number conversion

[diversoln]

I am importing an excel file that has a column of numbers. Unfortunately there are several rows of text and intermixed blank rows in the excel table that are not relavent to analyzing the data. I'd like to set up a query that builds a new table with this column as a numeric with the goal of ridding the database of all records that were text or just blank in the excel file. This needs to be an automated process.

I get an error if I try to import the column into a numeric field because it contains text.

I've tried using the Val function with a make table query but it returns all zeros even when the field contains numbers.

Please help. Thanks !

 

[raskew]

One possibility might be to go ahead and import the column as a text field, add a numerical field and use a variation of this query to populate the numerical field:

SELECT Table4.mytxtnum, IIf(IsNumeric([mytxtnum]),Val([mytxtnum]),-1) AS Expr1
FROM Table4;

Finally, delete those rows where the new field = -1 (which indicates that it contained non-numeric data).

 

[Pat Hartman]

I seem to remember having this problem with a feed I got from an SAP system. The solution was to import the data into a table with text columns to avoid the import errors and then we ran an append query to append the data to the real table. The append query, provided you set warnings off, will append all the valid rows and drop the invalid ones which I think is what you want.