Stripping and splitting.

[ErikZ]

Well, I decided to do a pet project this weekend, and I forgot to bring my Access references home from work. So this may seem simple to you guys.

I found a list of stories on the web, so I imported the list into Access. I want to change three things:

1) After the title, it lists the size of the story file in brackets:

Amazing Story(25k)

I'd like to make a new numeric field called "Size" and move the number over, leaving only the story title.

2) The descriptions sometimes have quotes, and sometimes do not. It looks messy and I'd like to remove all " from the field.

3) One of the fields is a list of attributes of the story, in shorthand. “Mag, Tech, Adl” means that the story involves Magic, High technology and adult themes. I want to make a Boolean field for each attribute. How do I make Access go though the field and mark off the corresponding Boolean field?

Searching the Forum, someone mentioned using the "Split/Join" function, but I can't seem to find reference in Access to that function.

I realize that this may require a written function. If this is the case, please tell me how to implement it, for I have never added a homegrown function to a query before. (I'm assuming that this will require an update query.)

Thanks!
Erik Z

 

[dneufarth]

Don't know how much data is involved, but sometimes it's just as easy to open your data in Word and insert a unique character (delimiter) to separate data for each field. Very often this can be don with find/replace. For example, replace all occurences of "k)" with "~". Some text editors allow the find to be restricted to a range of columns rather than all columns. Once that's done for each field in all the records, you can import the data into Access using the field delimiter.

You may also be able to eliminate other characters using find/replace in Word or Access eliminating the need for special functions.


Dave

 

[ErikZ]

There are 700 items. Otherwise I'd would massage the data by hand.

But boring repetitive work is what computers are for.

 

[raskew]

Think what's needed is for us to see some concrete examples. Would you please provide, or point us to the site you downloaded.

 

[ErikZ]

Actually, I was able to fix my problems with the help of Excel.

1) I used the "Text to columns" function to split off the "size of the story" from the "Title" into it's own field. After that it was easy to do a search and replace on "K)" to get rid of them.

2) All the descriptions had quotes, it was something in the import. I checked off the box that put quotes around all text and it was fine.

3) Lots of update queries. Created a Boolean field for each type of story. I set up an update query for each field,

Like "*Magic*"

and put the checkmark if it found it.

Everything is where I want it now. Not quite the way I thought I'd update it though.