Import numbers with trailing signs both positive and negative

[Guest]

I'm trying to download a very large file with multiple numeric fields... ie
over 100, that have the sign to the right of the number... both positive &
negative. I'll have to re-import this file multiple times. I'm looking for
a way to get excel, or access to recognize this as a number. Can anyone help
me?

 

[John Nurick]

Hi Roger,

I assume this is a text file.

After downloading the file, the general idea is to link to it rather
than import it. Then use an append query to shift the data into the
"real" table, with calculated fields to sort out the numbers.

If it's a fixed-width file, set up the import spec so that each number
and sign is in a separate field:
Field_A: 456.75-
becomes
Val_A: "456.75"
Sign_A: "-"
and then the calculated field is something like this:
Field_A: IIF([Sign_A]="-",-1,1)*Val([Val_A])

If it's a delimited file, use Right([Field_A],1) to get the sign.

 

[Guest]

Some of the numeric conversion functions recognise trailing signs.
For example, cdbl(2.0-) is -2.
If the file import doesn't recognise trailing signs (have you tried it?),
load the values as text and use a conversion function to convert
them to numbers.

(david)