[AccessD] TransferText Puzzle

TransferText can be a bit flakey.
The missing fields in the second record appear to be in the same place as Date and 
Numeric fields in the first record.
I'd suspect that TransferText is trying to convert these fields in the second row to numeric of 
some sort.

Two possibilities:

1.  Create and use an Import Specification which explicitly defines all fields as strings.
2.  Get rid of the TranferText and do it yourself so that you have full control:

(Aircode)
     Open strFullFilename for Input as #1
     set rs = currentdb.openrecordset("ttmpTemporary")
     DO While not EOF(1)
        Line Input #1, strTemp
        strFields() = Split(strTemp,",")
        rs.addnew
        for x = 0 to ubound(strFields()) 
               rs(x) = strFields(x)
        next
        rs.update
    Loop  
    close #1
    close rs
    set rs = nothing




On 3 Mar 2009 at 13:00, David Emerson wrote:

> We have text files that we receive from an external source that we 
> need to import into our Access XP ADP (with SQL 2000 BE) and then process.
> 
> The code we use is as follows:
> 
>      strFileName = Dir$(strDirectoryName & "\*.txt")
>      If strFileName <> "" Then
>          Do
>              strFullFileName = strDirectoryName & "\" & strFileName
>              DoCmd.RunSQL "TRUNCATE TABLE dbo.ttmpTemporary"
>              DoCmd.TransferText acImportDelim, , "dbo.ttmpTemporary", 
> strFullFileName, False
>              ... Process file
>              FileCopy strFullFileName, strDirectoryName & 
> "\Processed\" & strFileName
>              Kill strFullFileName
>              strFileName = Dir$
>          Loop Until strFileName = ""
>      Else
>          MsgBox "There are no files to process."
>      End If
> 
> dbo.ttmpTemporary is full of varchar(200) fields to accept the values 
> from the txt file.
> 
> The files loop through fine and load in.  The problem is that when 
> the files are TransferTexed, not all the field data appears in the 
> table.  For example one file has the following:
> 
> HDR,RSSWITCHGNT,GREG,EGLT,03/03/2009,10:34:21,00000001
> P,1001132553QT328,MEEN,,9B,Seaview 
> Rd,Remuera,Auckland,1050,,,03/03/2009,SM,6,GGRP,,N,
> 
> All the first row loads ok.  The second row has blank fields for the 
> data "9B,Seaview Rd,Remuera" so that the SQL table has
> 
> HDR,RSSWITCHGNT,GREG,EGLT,03/03/2009,10:34:21,00000001
> P,1001132553QT328,MEEN,,,,,Auckland,1050,,,03/03/2009,SM,6,GGRP,,N,
> 
> 
> To add confusion, if I have a file with more than 1 detail row:
> 
> HDR,RSSWITCHGNT,GREG,EGLT,03/03/2009,10:34:21,00000001
> P,1001132553QT328,MEEN,,9B,NewFile Rd,Remuera,NewFile 
> City,1050,,,03/03/2009,SM,6,GGRP,,N,
> P,1001132553QT328,MEEN,,9B,Seaview 
> Rd,Remuera,Auckland,1050,,,03/03/2009,SM,6,GGRP,,N,
> 
> Then all fields transfer in correctly.  The second file was created 
> by copying the first one and inserting a new one.
> 
> Has anyone come across this before?  I can send the files off line if 
> someone can look at them and see any differences.
> 
> 
> Regards
> 
> David Emerson
> Dalyn Software Ltd
> Wellington, New Zealand 
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com