Brad Marks
BradM at blackforestltd.com
Mon May 17 14:50:51 CDT 2010
Robin, I think that you will find that ODBC access is pretty easy to work with, once you have it set up. I would start by contacting the people who host the site and tell them that you want to use ODBC to get at the MySQL database and that you need the information for the "ODBC connection". They should be able to provide you with this. I have not worked with MySQL, but I would guess that it is similar to connecting to SQL-Server. There are a lot of people here in AccessD who are probably willing to help you get this cooking. It is uncanny how similar your situation is to what we were facing last year. Brad -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Robin Lawrence Sent: Monday, May 17, 2010 2:11 PM To: Access Developers discussion and problem solving Subject: Re: [AccessD] CSV file from hell Hi Brad, Thanks for your thoughts, I appreciate all the help given here... In the long term that's what I'm aiming at, the data is in MySQL on a hosted ecommerce shop, as we are a charity we don't have funds to employ a web developer. I'm looking to expand my knowledge in this field so we can get rid of import / export over the next few months and will certainly be back asking questions! Meanwhile I have this all working except the final automation ( 3 buttons to click) as I'm on holiday next week I need to keep the operators out of the back end. So as Edward says I'll just have to roll my sleeves up and get on with it...... Thanks for all the help Regards Robin ----- Original Message ----- From: "Brad Marks" <BradM at blackforestltd.com> To: "Access Developers discussion and problem solving" <accessd at databaseadvisors.com> Sent: Monday, May 17, 2010 7:37 PM Subject: Re: [AccessD] CSV file from hell > Robin > > I have been in CSV_Hell and it is not a fun place. > > About a year ago, we were starting to build an interface between two > systems. The output from the first system was in CSV format. When we > tried to work with the data in Access 2007, approximately 20% of the > records would not import nicely (extra commas, missing commas, extra > CRFLs , etc.) It was a real PITA. > > Fortunately, we were able to go further upstream in the process and grab > the data via ODBC instead of messing with CSV. This allowed us to > escape CSV_Hell and bask in the gleaming glory of OBDC. > > You wrote "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". > > Is it possible to take a second look at this? How is the data stored on > the Web? SQL-Server? (remember the gleaming glory of ODBC). > > This is very similar to our experience. If you share more about the > website and how the data is stored there, new doors may open up. > > Good luck with this. > > Brad > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ > > > -----Original Message----- > From: accessd-bounces at databaseadvisors.com > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Edward Zuris > Sent: Monday, May 17, 2010 1:26 PM > To: 'Access Developers discussion and problem solving' > Subject: Re: [AccessD] CSV file from hell > > > Sometimes the VBA method is the only option. > > And you can build in all kinds of tests to > see if there the desired files on the hard > disk, or tables exist in Access. > > Just a little more elbow grease and sweat. > > > -----Original Message----- > From: accessd-bounces at databaseadvisors.com > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Robin > Lawrence > Sent: Monday, May 17, 2010 10: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 > > -- > This message has been scanned for viruses and > dangerous content by MailScanner, and is > believed to be clean. > > > -- > 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 -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean.