Problems with Negative Numbers when importing a txt file

  • Thread starter Thread starter John_
  • Start date Start date
J

John_

I am trying to import a comma-delimited text file that contains negative
numbers in one field. When I import this file, the negative numbers come
into the table as blanks. When I look at the .txt file, the numbers have the
negative sign after the number. I am wondering if there is a way to bring
these negative numbers into the table. Thanks for your help!
 
You could try importing to an existing table where the field is defined as
Text, rather than Numeric. You'd then run an Append query to take the data
from the temporary table and store it in "real" table. Instead of just
append the numeric field to the real table, you'd append something like:

CLng(IIf(Right([TextField], 1) = "-", "-" & Left([TextField],
Len([TextField]) - 1), [TextField]))

(That assumes that the real numeric field is a long integer. If it's some
other type, use the appropriate Cxxx function.)
 
Thanks! I change the field definition to text on the imported table and then
appended that table to a new table which I had defined the text field as
numeric (double), as you suggested. That worked perfectly! Thanks. But I
didn't have to use the formula to move the negative sign...it put the
negative sign in the correct position but just doing the above. Thanks again!

John




Douglas J. Steele said:
You could try importing to an existing table where the field is defined as
Text, rather than Numeric. You'd then run an Append query to take the data
from the temporary table and store it in "real" table. Instead of just
append the numeric field to the real table, you'd append something like:

CLng(IIf(Right([TextField], 1) = "-", "-" & Left([TextField],
Len([TextField]) - 1), [TextField]))

(That assumes that the real numeric field is a long integer. If it's some
other type, use the appropriate Cxxx function.)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


John_ said:
I am trying to import a comma-delimited text file that contains negative
numbers in one field. When I import this file, the negative numbers come
into the table as blanks. When I look at the .txt file, the numbers have
the
negative sign after the number. I am wondering if there is a way to bring
these negative numbers into the table. Thanks for your help!
Click to expand...
Click to expand...
 
Back
Top