Transferring from Access

[Bob Whitaker]

One of our users has asked us to convert some Access tables one of which has a 255 character field for address and the full address has been entered in the one field. It would appear that Access allows carriage returns of some sort in a normal character field and most of the addresses have been entered as

56 High Street
Somewhere
Anywhere
AY7 6TR

and this formatting has carried over when importing the data into an Alpha Table, however, and heres my problem, a number of records have been entered as

56 High Street Somewhere Anywhere AY7 6TR

Is there anyway I can insert some sort of soft carriage return in Alpha so the records are all consistent.

Bob

[Richard Rabins]

RE: Transferring from Access

Bob

Have you tried the new ODBC import Genie in the latest beta build.

The Genie imports any ODBC data source (multiple files as well) along with Access, OUTLOOK etc

[Bob Whitaker]

RE: Transferring from Access

Thanks Richard but the problem is not getting the data in from Access it is the format that it has been entered in access. The vast majority of the addresses have been entered in a single 255 char field in the access table with a soft carriage return between lines and alpha 4.5 imports them and retains that format which is fine. The problem is the small number of records that have been entered in the access table without the soft returns so they are just a continous line. I wondered if there was anyway to insert a soft carriage return in a single 255 character field in alpha.

Bob

[forskare]

RE: Transferring from Access

Bob,

Would using an Update work? Look at the Update genie and then choose break a field or break a city, state field into it's parts.

It might do the trick.

kenn

[Bob Whitaker]

RE: Transferring from Access

Ken
Good to hear from you, thanks for that I never knew that option existed, I always feel that its good to learn one new trick with Alpha everyday, shame I won't live long enough to learn them all. That suggestion whilst not being the path i was wandering down has made me think of another way to fix the problem.

Thanks from a sunny UK - wouldn't it be good to have England and USA meet in the world cup.

Bob

[CALocklin]

RE: Transferring from Access

Sounds like you might be headed there anyway but I would opt for something like Ken suggested which will separate the field into its component parts. Having all that information in one field seems like bad structure which will lead to other problems down the road. (Maybe that's part of the reason they want to do the conversion?)

[forskare]

RE: Transferring from Access

As long as they don't hollar, "Remember the Boston Tea Party!" Course, that was surpassed by the tomato juice incident by some guy from Iowa. ~(:>}

kenn