How to get DoCmd.TransferText to Recognise Negative Numbers Using "()"?

[jbt007]

Hi All,

System: Access 2010, WinXP

I have a text file I am importing using the following code:

'Import text report into tblBVRRaw table using the BVRImp Spec...
DoCmd.TransferText acImportFixed, "BVRImp", "tblBVRRaw", strSourceFile, False

It works fine except where ther are negative numbers in the report being imported. I checked the spec to be sure both "()", were included in the column. The number (5,342,892) for example is imported as a null value.

Any suggestions? What is the "CodePage" option? Would that tell Access that () means negative?

Thanks in advance...

[ADezii]

1. Within the BVRImp Specification, change the Data Type of the Field that may contain Negative Numbers to TEXT, rather than NUMERIC. My guess is that (<Some Value>) is not being interpreted as a Negative Number, either within the Specification, or Import Process itself. If I am correct, you are now getting a Data Type Conversion Error along with a subsequent <TableName>_Imp ortErrors Table.
2. Have you tried a Manual Import, bypassing the Specification?

[jbt007]

ADezii - you are correct, are Data Type Conversion Errors for the fields in question. I do not see how changing the data type of the field from Number (Doubel), to Text is going to solve my issue. I need it to be a number. Could you elaborate on your suggestion a bit?

[ADezii]

Wanted to make sure that that was the problem, namely the Data Type Conversion. Once these Negative Numbers are in the Imported Table, as TEXT, in the Format of (xxx...), then they can easily be converted to Negative Numbers, such as: -xxx..., the Data Type of the Fields changed to Number/DOUBLE, and the Format Property set to something such as 0;(0);"Zero";"N ull", which will display Negative Numbers within Parenthesis. If you need further assistance, just ask.

[NeoPa]

I'm using Access 2003 but I couldn't find any way that would work (IE. Importing negative numbers identified by parentheses). I changed the system settings to recognise (n) as -n but nevertheless such entries were always treated as invalid and dropped. There was also nowhere within the Import Specification where such a setting could be applied. In short, it appears it isn't supported :-(

PS. ADezii is talking about importing the data in as text and subsequently updating it using a query. Probably by importing it into one table first followed by appending it to another using a query to handle the transfer and conversion at the same time.

[Mihail]

Or manage this before importing by replacing (number) to -number in the original file.

It is just an idea because I post for subscription purpose.

[Mariostg]

I had a similar problem before but in my case, negative numbers were in the form - 13456. i.e. negative sign followed by 7 spaces followed by number. Importing this from a text file would lead to a numeric overflow type error if I remember correctly. Since I was already manipulating the text file line be line to clean other garbage, I wrote a function to clean the negative numbers.

Here is the function that maybe will imspire some people.

Function FixNegativeNumber(LineToFix As String) As String
'This is to remove the spaces (-   6.54) to (-6.54) that exist in the DRMIS file.

    Dim re As New RegExp
    Dim RetStr As String
    Dim pattern As String
    re.pattern = "(,-)\s+([0-9]+)"
    re.Global = True

    If (re.Test(LineToFix) = True) Then
        FixNegativeNumber = re.Replace(LineToFix, "$1$2")
    Else
        FixNegativeNumber = LineToFix
    End If
End Function

[jbt007]

Solution

All,

Thanks for the great feedback. With y'alls (Ya, a Texan!) help I have created a solution that works. Basic process is as follows:

First create a temp table that is all text fields:

    strSQL = "CREATE TABLE tblTxtRpt ([JVID] Text(5),[SkpFld] Text(2),[Spec] Text(9),[JVTitle] Text(26), " & _
             "[OBTot] Text(17),[COTot] Text(13),[BTot] Text(16),[CTot_td] Text(16),[ATot_tp] Text(13), " & _
             "[ATot_td] Text(16),[PTot_tg] Text(16),[PTot_wm] Text(16),[VTot_td] Text(13),[PftLos] Text(13), [RecID] AutoIncrement);"
    db.Execute strSQL, dbFailOnError

Then import the data in to the temporary table:

    DoCmd.TransferText acImportFixed, "BVRImp", "tblTxtRpt", strSourceFile, False

Next, delete all unwanted records. This would include headding records, subtotals, etc. from the report.

    'Part 1 - Delete Headers, SubTotals, and other text type records that are not needed in tblTxtRpt...
    strSQL = "DELETE tblTxtRpt.JVID, tblTxtRpt.Spec, tblTxtRpt.SkpFld, tblTxtRpt.JVTitle, tblTxtRpt.OBTot, " & _
             "tblTxtRpt.COTot, tblTxtRpt.BTot, tblTxtRpt.CTot_td, tblTxtRpt.ATot_tp, tblTxtRpt.ATot_td, " & _
             "tblTxtRpt.PTot_tg , tblTxtRpt.PTot_wm, tblTxtRpt.VTot_td, tblTxtRpt.PftLos, tblTxtRpt.RecID " & _
             "From tblTxtRpt " & _
             "WHERE (((tblTxtRpt.JVID) Is Null)) OR (((tblTxtRpt.JVID) Like ""*JC BV*"")) OR " & _
             "(((tblTxtRpt.JVID) Like ""*perio*"")) OR (((tblTxtRpt.JVID) Like ""*----*"")) OR " & _
             "(((tblTxtRpt.JVID) Like ""*COST*"" Or (tblTxtRpt.JVID) Like ""*CODE*"")) OR " & _
             "(((tblTxtRpt.Spec) Like ""*request*"")) OR (((tblTxtRpt.Spec) Like ""*Total*"")) OR " & _
             "(((tblTxtRpt.JVID) Like ""*GRAND*""));"
    'Debug.Print strSQL
    db.Execute strSQL, dbFailOnError
    
    'Part 2 - Remove zero values... Can't do this in one step because "----" will not convert to 0 using CDbl()
    '(Well, technically I quess you could using an iif(), but that is slow.  Two delete queries works just fine...)
    strSQL = "DELETE tblTxtRpt.JVID, tblTxtRpt.Spec, tblTxtRpt.JVTitle, CDbl(Nz([OBTot])) AS vOBTot, " & _
             "CDbl(Nz([COTot])) AS vCOTot, CDbl(Nz([BTot])) AS vBTot, CDbl(Nz([CTot_td])) AS vCTot_td, " & _
             "CDbl(Nz([ATot_tp])) AS vATot_tp, CDbl(Nz([ATot_td])) AS vATot_td, CDbl(Nz([PTot_tg])) AS vPTot_tg, " & _
             "CDbl(Nz([PTot_wm])) AS vPTot_wm, CDbl(Nz([VTot_td])) AS vVTot_td, CDbl(Nz([PftLos])) AS vPftLos " & _
             "From tblTxtRpt " & _
             "WHERE (((CDbl(Nz([OBTot])))=0) AND ((CDbl(Nz([COTot])))=0) AND ((CDbl(Nz([BTot])))=0) AND " & _
             "((CDbl(Nz([CTot_td])))=0) AND ((CDbl(Nz([ATot_tp])))=0) AND ((CDbl(Nz([ATot_td])))=0) AND " & _
             "((CDbl(Nz([PTot_tg])))=0) AND ((CDbl(Nz([PTot_wm])))=0) AND ((CDbl(Nz([VTot_td])))=0) AND " & _
             "((CDbl(Nz([PftLos])))=0));"
    'Debug.Print strSQL
    db.Execute strSQL, dbFailOnError

Next, import the data into the "real" table with an insert query:

    'Transfer final records form temp table into tblBVRRaw for processing...
    strSQL = "INSERT INTO tblBVRRaw ( JVID, Spec, JVTitle, OBTot, COTot, BTot, CTot_td, ATot_tp, ATot_td, PTot_tg, PTot_wm, VTot_td, PftLos )" & _
             "SELECT tblTxtRpt.JVID, tblTxtRpt.Spec, tblTxtRpt.JVTitle, CDbl(Nz([OBTot],0)) AS vOBTot, CDbl(Nz([COTot],0)) AS vCOTot, " & _
             "CDbl(Nz([BTot],0)) AS vBTot, CDbl(Nz([CTot_td],0)) AS vCTot_td, CDbl(Nz([ATot_tp],0)) AS vATot_tp, CDbl(Nz([ATot_td],0)) AS vATot_td, " & _
             "CDbl(Nz([PTot_tg],0)) AS vPTot_tg, CDbl(Nz([PTot_wm],0)) AS vPTot_wm, CDbl(Nz([VTot_td],0)) AS vVTot_td, CDbl(Nz([PftLos],0)) AS vPftLos " & _
             "From tblTxtRpt " & _
             "ORDER BY tblTxtRpt.RecID;"
    'Debug.Print strSQL
    db.Execute strSQL, dbFailOnError

Finally, drop the temporary table.

    'Drop the Temp table.
    strSQL = "DROP TABLE tblTxtRpt;"
    db.Execute strSQL, dbFailOnError

The "key" was the CDbl() function, which translates (<somenumber>) to negative numbers. It will also translates -<somenumber> to negatives.

Again, thanks for all the comments!

[jbt007]

Final Question...

One Final question...

ADezii - you said "...Within the BVRImp Specification, change the Data Type of the Field that may contain Negative Numbers to TEXT...". I don't seem to have a way of changing the report specification field types. How would one do this? I have uploaded a screen shot of BVRImp spec when I click the edit button...

[imgnothumb]http://bytes.com/attachments/attachment/5919d1326123261/bvrimp_spec.jpg[/imgnothumb]

[NeoPa]

An alternative would be to avoid creating and deleting the table every time, or at all in code, and simply create it once (manually is fine) but clear it of all data after use. This has the same effect without the need for deleting and recreating the table (and I would expect it to run faster too). It also means the design of your project is visible in the database, rather than part of the design being hidden in code.

[jbt007]

NeoPa - The intent IS to keep it the temporary table hidden. I tried to do this using the "hidden" property, but every time an import was done, access would unhide the table. Additionally, in another import (Different report, same temp table) the order of the data is very important, so I added the RecID autonumber field. This allows me to keep the report in exact order as the text file's printed report. The problem with importing multiple times and NOT dropping the table is the AutoNumber field does not get set back to 1 when the data is purged. You have to (as far as I know) drop the table and recreate it to reset the AutoNumber field. If there is a way to keep this table hidden AND reset the AutoNumber field I am all for faster and easier!

This is starting to sound like a new thread so if we need to I can post a new question on this topic.

[NeoPa]

JBT007:
This is starting to sound like a new thread so if we need to I can post a new question on this topic.

No worries. If that is your requirement then dropping and recreating fits perfectly. Personally, I prefer all the design to be visible and obvious, as it avoids inadvertent errors in development, but your solution fits your requirements just fine it seems. No need to create a new thread just to agree I guess ;-)

PS. To reset numbering you could delete and recreate the AutoNumber field, but that's just an aside.

[jbt007]

NeoPa - I went back and looked at my original post:

How to hide a table that is made with SQL "CREATE TABLE"?

It didn't generate much discussion... If I recall, I tried altering the table to delete the filed, then added the field again and Access STILL remembered the AutoNumber and didn't reset it back to "1". It may be the BFI method, but it seems to be working...

[NeoPa]

JBT007:
NeoPa - I went back and looked at my original post:

I see nothing in the original post. Perhaps you're referring to your first thread?

I must admit it's not something I use frequently, so I can't be sure, but I thought I remembered it being suggested for that purpose in another thread here recently.

[Mariostg]

@jbt007, if you prefix your table name with usys, it will be a hidden table by default.