Jim Lawrence
accessd at shaw.ca
Mon May 24 19:57:00 CDT 2010
Ahhhh, the infamous 'comma' or double quote in the middle of the text field issue. That is why I like to either pre-process the data or manage data in as text; where all text data is surrounded by quotes and all numbers are numbers with commas delimitating each field... if the combination of quotes and comma or comma and quote was not found replace quote with single-quote or delete the comma and carry on... Fixed-width is another good export option if available In your case an error is unavoidable...maybe not this time or next time but the time after that.... those things can be so sporadic. Jim -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin Sent: Monday, May 24, 2010 2:02 PM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Transfer Text Problem Well, since it is a CSV file TransferText worked a hell of a lot better than TransferSpreadsheet. Substitutes # for . I actually abandoned the Transfer approach and went straight code until I got to a record that had commas in a field and then Split on comma delimiter failed. Went back to TransferText and all seems to be OK for the moment. I'm sure there other gotchas here somewhere. Rocky -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Lawrence Sent: Monday, May 24, 2010 1:45 PM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Transfer Text Problem Not an easy one. Many years ago I had to program a module which opened a text file, through a loop read each line, separated each field and then wrote the detail into a table. It did not take many lines of code, took a while to put together but worked very well. Maybe that route might be worth investigating if the standard methods are not working reliably. Jim -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin Sent: Monday, May 24, 2010 7:46 AM To: 'Access Developers discussion and problem solving' Subject: [AccessD] Transfer Text Problem Dear List: I have to import a CSV file with field names. I did a manual import first to get the import table created. Problem is that one of the columns in the CSV file has the name "P.O. Good Thru Date" and that gets created in the table as "PO Good Thru Date". Period is an invalid character in a field name in an Access table. So when I try to do the import using TransferText I get an error message saying the field doesn't exist. Which of course it doesn't. The CSV file is an export from Peachtree so I don't think the user has the option of changing the field name in the first row of the CSV file. Is there a programmable way around this? MTIA Rocky Smolin Beach Access Software 858-259-4334 www.e-z-mrp.com <http://www.e-z-mrp.com/> www.bchacc.com <http://www.bchacc.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