[AccessD] CSV file from hell

Edward Zuris edzedz at comcast.net
Mon May 17 11:16:06 CDT 2010


 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




More information about the AccessD mailing list