Importing a text file with a negative number



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?


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.


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.


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]

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question