[AccessD] CSV file from hell

I'd go with the second option every time. :-)

With Line Input # and  Split(), it is realy simple to pull the fields you want from a record.

Just read the first line into an Array of field names  and use that to determine the ordinal 
position of the fields you need.

-- 
Stuart
 
On 17 May 2010 at 16:10, Robin Lawrence wrote:

> Hi Rocky,
> Just tried that - I get 'Field F1 doesn't exist in destination table 
> 'va_orders' (error 2391)
> I think I need to either :
> Try and write my own Schema.ini by opening the import file first and parsing 
> the fields, then import using that schema
> or
> rewrite the whole thing in ADO and open the text file directly
> 
> Either way is a real nuisance - thought I'd done the hard work on this one 
> already...:)
> Any other thoughts gratefully received
> Rgds
> Robin
> 
> 
> 
> ---- Original Message ----- 
> From: "Rocky Smolin" <rockysmolin at bchacc.com>
> To: "'Access Developers discussion and problem solving'" 
> <accessd at databaseadvisors.com>
> Sent: Monday, May 17, 2010 3:35 PM
> Subject: Re: [AccessD] CSV file from hell
> 
> 
> > Could you set the Field Names parameters to False, define all the fields 
> > of
> > the target table as Text, and know that the first record in the imported
> > table would contain the field names?
> >
> > Rocky
> >
> >
> > -----Original Message-----
> > From: accessd-bounces at databaseadvisors.com
> > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Robin Lawrence
> > Sent: Monday, May 17, 2010 7:17 AM
> > To: Access Developers discussion and problem solving
> > Subject: Re: [AccessD] CSV file from hell
> >
> > Hi Steve,
> > The code for the import is a straghtforward docmdtransfer text as posted
> > previously...
> >
> > The csv file is currently 179 fields, a lot of which have repeated field
> > names in the header row.
> > Between field "Product Options" and "Buying Price" are a variable number 
> > of
> > fields all named "Product Options"
> > When I import in manually using the wizard Access renames these fields
> > "Field121,Field123" etc I then use the fields collection to find the
> > positions of these two fields so I can extract the data in between.
> >
> > What's happening at present when running the docmdtransfertext - If the
> > destination table va_orders is not present I get : 'the search key was not
> > found in any record (3709)
> > If the destination table is present : (imported via the wizard)    :
> > 'duplicate output destination '[fieldname] (3063) If I import with the
> > wizard, save the specification and run with that specification it all 
> > works
> > until the fields in the import file change
> >
> > If I remove the import file : I get as expected my error message generated
> > by error 3011
> >
> > I haven't yet been able to run it on a different installation of Access (
> > it's XP SP3 by the way) to eliminate corruption
> >
> >>From what you're saying I assume your tests just result in the table
> >>being
> > added with no errors?
> >
> > Regards
> > Robin
> >
> >
> >
> > ----- Original Message -----
> > From: "Steve Schapel" <miscellany at mvps.org>
> > To: "Access Developers discussion and problem solving"
> > <accessd at databaseadvisors.com>
> > Sent: Monday, May 17, 2010 2:41 AM
> > Subject: Re: [AccessD] CSV file from hell
> >
> >
> >> Hi Robin,
> >>
> >> Do you mind letting us know the details of your TransferText method?  I 
> >> am
> >> interested in this problem, but have been unable to replicate the error
> >> reported by yourself and Andy.  I am using:
> >> DoCmd.TransferText acImportDelim, , "TestTable",
> >> "C:\Databases\TestStats.csv", True
> >>
> >> Regards
> >> Steve
> >>
> >>
> >> --------------------------------------------------
> >> From: "Robin Lawrence" <robin.lawrence at ukonline.co.uk>
> >> Sent: Sunday, May 16, 2010 10:30 PM
> >>
> >>> If you've got a moment Andy could you just run a quick docmdtransfertxt
> >>> without the destination table being present and let me know what error
> >>> number you get.
> >>> I still haven't solved the problem - it either gives me error 3107 or
> >>> 3011
> >>> but inconsistently
> >>
> >>
> >> -- 
> >> 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
> >
> > -- 
> > 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