[AccessD] TransferText Puzzle

Have you tried to create a manual import of the text file to see if it
gets cut off?

Could it be something that it thinks is a delimeter?


On Mon, Mar 2, 2009 at 4:00 PM, David Emerson <newsgrps at dalyn.co.nz> 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
>