[AccessD] CSV file from hell

Hi Rocky,
Tried to import to a table that doesn't exist  - I got the 3709 error again 
which is how the whole problem started....
Reimported the file from the web site, changed all the file names and 
destination table names - still no go
Will have a cup of tea and scratch my head.
Regards
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 4:20 PM
Subject: Re: [AccessD] CSV file from hell


> Try it but specify a table name that doesn't exist.  If you want to use 
> the
> same table name all the time to process the transferred data, just delete
> the table first.  It will (should) import the data and create field names
> F1, F2, etc.
>
> HTH
>
> 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 8:11 AM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] CSV file from hell
>
> 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
>
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>