> For example my database requires data to be in the form of:
> Given_Names | Family_Name | Address_1| Address_2 | City | State | Zip
>
> and I receive data in the format of
> Names | Address | City_&_State_Zip
I usually don't recommend using spreadsheets for databases, but your
problem seems to be simple enough to tolerate it. I would recommend your
getting a handle on awk or perl if you're going to do a lot of this, though.
Here's how you can do it using a spreadsheet:
Import your records into a spreadsheet (Format: Names Address City&ST_Zip.
Name the first three columns just like that, i.e., A1=Names; A2=Address; etc.
(Insert a row ahead of all the data if you must.)
Next, continue naming columns, but this time in your own format: A4=
Given_Names A5= Family_Name A6= Address_1, etc...
In cell B4, enter the text formula which will extract the first name of the
Names entry. For example, say Cell B1 contains "Jim Plante"; the formula to
break out the "Given Name" would be LEFT(A2;SEARCH(" ";A2;1)). In other
words, I'm using the space between the two names as a delimeter, and I'm
simply putting the first name into A4. Catching the last name would work
similarly. You'll run into problems with people with three names (first,
middle,
last) and with Jr., Sr., III, etc.) but you'll just have to figure out how to
separate
those. (N.B.: You'd have the same problem using awk, perl, or regular
expressions, too.)
Now, after you've got all the formulae written for row 2 (your headings are in
row 1), then select row 2, go to the end of your data, shift click in the lower
right corner of the data set, and fill down. All your data should be broken out
correctly. Scan it to be sure, and make corrections as you find problems.
Once you've finished cleaning the data, and you're satisfied that you've got a
good data set, delete the first three columns and save the sheet. Then save it
again as a DBF. Now you can use it as a data source, and use Open Office as a
front end to enter or correct data.
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
