David Emerson
newsgrps at dalyn.co.nz
Mon Mar 2 18:48:46 CST 2009
Something else I have noticed - I tried taking the original file and creating copies by saving as, copy and paste, and entering in a new file. In all cases if there is only one detail record the fields are missing, but if there are more than 1 detail record they transfertext ok. David At 3/03/2009, you wrote: >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 > > > >-- >AccessD mailing list >AccessD at databaseadvisors.com >http://databaseadvisors.com/mailman/listinfo/accessd >Website: http://www.databaseadvisors.com