David Emerson
newsgrps at dalyn.co.nz
Mon Mar 2 18:42:18 CST 2009
Good suggestion. I tried importing manually and the data came in ok. This seems to indicate that the TransferText command is not being consistant. The funny thing is that they are fields in the middle of the record. The fields on either side are in their right places. 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