[AccessD] CSV file from hell

Hi liz,
Unfortunately not, the import file has a variable amount of fields so the 
destination table needs to vary accordingly .
Regards
Robin

----- Original Message ----- 
From: <Elizabeth.J.Doering at wellsfargo.com>
To: <accessd at databaseadvisors.com>
Sent: Monday, May 17, 2010 6:33 PM
Subject: Re: [AccessD] CSV file from hell


>
> I haven't been following this thread closely....but...if you know what the 
> destination table should look like, can't you keep a template of it on 
> hand and copy that template to the name you need to create an empty 
> 'existing' destination table on the fly?
>
>
>
> Liz
>
>
> This message may contain confidential and/or privileged information. If 
> you are not the addressee or authorized to receive this for the addressee, 
> you must not use, copy, disclose, or take any action based on this message 
> or any information herein. If you have received this message in error, 
> please advise the sender immediately by reply e-mail and delete this 
> message. Thank you for your cooperation.
>
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com 
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Robin Lawrence
> Sent: Monday, May 17, 2010 11:34 AM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] CSV file from hell
>
> Hi Edward,
> That's what it looks like I'll have to do, annoying because I just need to
> automate the existing procedure which works fine with the import wizard.
> I'm just trying to ascertain that docmstransfer text definitely requires 
> an
> existing destination table when importing and the problem is not something
> simple I've overlooked or some sort of corruption
> Regards
> Robin
>
> ----- Original Message ----- 
> From: "Edward Zuris" <edzedz at comcast.net>
> To: "'Access Developers discussion and problem solving'"
> <accessd at databaseadvisors.com>
> Sent: Monday, May 17, 2010 5:16 PM
> Subject: Re: [AccessD] CSV file from hell
>
>
>>
>> If you can get the data file on your hard drive
>> and view it from notepad, etc.  You should be able
>> to write some VBA code to read the data, fix it,
>> and store it into some Access table.
>>
>> I normally import complex CSV files as a wide
>> fixed length data element with only one giant
>> field.  Then use VBA and SQL to make it into
>> something useful.
>>
>> This works for lots of things besides CSV files.
>> For example: complex text report files generated
>> from old mini-computers and mainframes.
>>
>> It kind of brute force, but it works.
>>
>>
>> -----Original Message-----
>> From: accessd-bounces at databaseadvisors.com
>> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Robin
>> Lawrence
>> Sent: Monday, May 17, 2010 9: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
>>
>
> -- 
> 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
>