 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Importing / Linking / March 2006Importing 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. > 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? 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. > 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 [quoted text clipped - 3 lines] > > values have the negative sign at the end of the number. Access file|import > > can not handle this issue. Any suggestions? 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] > 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. [quoted text clipped - 13 lines] >> > file|import >> > can not handle this issue. Any suggestions? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.