Importing a text file with a negative number

[Guest]

I have a text file that contains a column of numeric values. The negative
values have the negative sign at the end of the number. Access file|import
can not handle this issue. Any suggestions?

 

[Guest]

You can import the column that contains the "-/+" or "-/Null" into a separate
field in your table. Run an update query after the import to modify your
number field based on this.

 

[Guest]

Yeah, that would work. Wondering if there is a better way such as reading
each line in code, or using the transfertext with a schema.ini file.
Schema.ini seems to have a CurrencyNegFormat setting but it seems this
setting requires a "$" sign somewhere in the number which I don't have.

Thanks.

 

[John Nurick]

If you're importing via a query (as opposed to TransferText), define the
field as Text in schema.ini and then use an expression in the query to
convert it to the desired numeric type, perhaps like this:

IIF(Right([XXX], 1)='-', -1 * CLng(Left([XXX], Len([XXX]) - 1),
CLng([XXX])) AS [YYY]