How to convert a value when importing

[Frank M.]

I am importing data from textfile. Several of the fields contain logical values that will fit well in a field of the data Yes/No. However, three different formats are used for these fields in the text file, i.e. "yes" may be indicated by the value "J" in one field and "1" in another field - within each field in the text file it consistent, but not from one field to the next
Now, these values are not recognized as logical values when importing, so I get type conversion errors.
In order to handle it, I need to be able to convert the values. Since I import the data into a temporary table, the conversion could possibly be done later - i.e. let the receiving fields be of the data type text, so they will accept all values, and then in an update query convert the value when I assign it to the final table in the database. I do not, however, know how to convert data types in the SET part of an UPDATE query statement. Is possible to have conditional assigment of values?
While writing this post, I just realised that I could use the Cbool function in the SET statement of the UPDATE query. Still, I would like to know about conversion possibilities in the import or in the SET part of an UPDATE query for other data types, e.g. if you want to convert a text value to another value

Regards

Frank M.

 

[Joe Fallon]

I always import to a temp table and then use queires to move the data to the
real table.

You can use the IIF function in your append query.
e.g.
MyField: iif(myfield="Y", -1, 0)

--
Joe Fallon
Access MVP

I am importing data from textfile. Several of the fields contain logical

values that will fit well in a field of the data Yes/No. However, three
different formats are used for these fields in the text file, i.e. "yes" may
be indicated by the value "J" in one field and "1" in another field - within
each field in the text file it consistent, but not from one field to the
next.

Now, these values are not recognized as logical values when importing, so I get type conversion errors.
In order to handle it, I need to be able to convert the values. Since I

import the data into a temporary table, the conversion could possibly be
done later - i.e. let the receiving fields be of the data type text, so they
will accept all values, and then in an update query convert the value when I
assign it to the final table in the database. I do not, however, know how to
convert data types in the SET part of an UPDATE query statement. Is possible
to have conditional assigment of values?

While writing this post, I just realised that I could use the Cbool

function in the SET statement of the UPDATE query. Still, I would like to
know about conversion possibilities in the import or in the SET part of an
UPDATE query for other data types, e.g. if you want to convert a text value
to another value.