I can't tell you how many hundreds of times I've dealt with this problem! Sometimes it is easier to just use Excel and fix the data manually, sometimes I have written code to run through and do the parsing, but with "non-standard" delimiters, it would probably be a nightmare. Some utilities I've used have to run through the records multiple times... ie, one time to normalize delimiters, etc. I agree, have your boss at the very least remove contacts that he doesn't want retained, that will save you some time.
-------------------------------------------------- From: "Mike Chabot" <[email protected]> Sent: Monday, February 08, 2010 11:58 AM To: "sql" <[email protected]> Subject: Re: string manipulation question > > Instead of trying to do this using SQL you might try using MS Excel, > or a program like TextPad that supports regular expressions. I would > personally lean towards trying to use regular expressions to split > apart the text. > > If you have less than 100 records it is probably faster to do the > parsing manually. > > Maybe ask your boss to purge irrelevant contacts before you start to > clean them up. > > -Mike Chabot > > On Mon, Feb 8, 2010 at 11:34 AM, Douglas Brown <[email protected]> > wrote: >> >> My boss has a contacts database that is very out of date and not really >> that useful for his purpose. The database program he is using can export >> the data into a tab delimited file for import into the new database that >> I am designing. My problem is the way the old data is formatted. I would >> like to have city, state and zip separated into separate fields but the >> old data in the text file is formatted with the city, state, zip in one >> field. How can I pull the data out of the old field and populate the new >> database with it without following the same scheme. I have also noticed >> that there is no standard delimiter that was used to separate those >> values. IE: city, state, zip and city state zip. Hoping this makes sense. >> >> >> Thanks >> >> >> Doug >> >> > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/sql/message.cfm/messageid:3272 Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.6
