How to convert a value when importing

[Guest]

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.

 

[Guest]

Thanks a lot, this was just what I was looking for

Regards

Frank M

----- Joe Fallon wrote: ----

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

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

--
Joe Fallo
Access MV

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

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

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

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

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

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