[AccessD] CSV file from hell

Do you handle the varying number of fields by letting the transfer text
create the fields, would it work to delete the table yourself in code , then
copy a template table which would only have to have one dummy field to
va_orders?

Rocky

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Robin Lawrence
Sent: Saturday, May 15, 2010 3:16 AM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] CSV file from hell

Hello to all,
I haven't been active in Access for about 10 years, was in the forum then
but I'm afraid more from the listening end (You're all so quick I hardly
ever got a chance to get an answer in!) Anyway I've moved down to Cornwall
(UK) and have a part time job administrating for a theatre, they have been
writing tickets out by hand for 20 years ( 400 seater / 73 shows in the
season!) Needless to say I couldnt resist offering to sort that one
out.......

Enough waffle - down to the problem

I have a csv file coming from the web site ( no possibility of changing the
format at present) which I'm importing into the local box office system

The problem is there is a section in the middle of the file which can vary,
ie between two fixed fields  extra fields can be added. I've figured out how
to handle this in code once the file is imported to a table in the database.


I've been importing the file manually - taking the option to overwrite the
table when asked so that any extra fields (since the last import) will be
included in the field definitions, it all works fine I'm now trying to
automate this in code so that the operators can have a 'press button' menu
to import and process the orders.

Problem is with using 'docmd.transfertext' I dont get the option to
overwrite the table, then the fields don't match and I get an error....
If I delete the table completely first the it complains about it not being
there....

Just wanted to ask if there was any way to force docmd.transfertext to add a
new table before I start writng code to open the file directly and parse it
myself ?

(I have tried to link rather than import the file but it doesn't read the
values correctly)

Regards
Robin Lawrence



--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com