[AccessD] CSV file from hell

Hi Rocky,
Yes to your previous,
 Access XP SP3 on Win XP SP3 - fully updated according to Microsoft update
Regds
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 5:14 PM
Subject: Re: [AccessD] CSV file from hell


> Version of Access?  All updates applied?
>
> -----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:51 AM
> To: Access Developers discussion and problem solving
> Subject: Re: [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
>>
>
> -- 
> 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
>