David Emerson
newsgrps at dalyn.co.nz
Mon Mar 2 20:27:21 CST 2009
At 3/03/2009, you wrote:
>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.
Good spotting. That may be it. Presumably when there are more than
1 field in the details then Access has more information to make the
guess for the data type.
>Two possibilities:
>
>1. Create and use an Import Specification which explicitly defines
>all fields as strings.
As far as I have been able to see ADP's don't have import
specifications. The advanced button doesn't appear on the import screen.
>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
This may be the answer. Thanks.
David
>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
>
>
>--
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com