a. mays, winthrop university, 2005 + automatically generated title-specific unique hotlinks +...

A. Mays, Winthrop University, 2005

+ automatically generated title-specific unique hotlinks+ customized Access data analysis

Slide 1 of 79

Data manipulation using Excel:

Getting your exported text file ready for:

Slide 2 of 79

Slide 3 of 79

This is the plain text file, as exported, in its unedited form.Don’t just open the text-file with the default text editor, butuse Excel to open this file instead.

Slide 4 of 79

Slide 5 of 79

Slide 6 of 79

Next, Excel’s Text Import Wizard takes you through the steps of choosing how to import your data to match the way you’ll need them in Excel...

Default for data is “fixed width”:

Change it to “delimited”.

Slide 7 of 79

Slide 8 of 79Why are we changing field delimiters?

Remember the Millennium Export delimiters? Here’s where they meet the Excel text-import function.

Slide 9 of 79

Slide 10 of 79

Slide 11 of 79

Slide 12 of 79

Slide 13 of 79

Slide 14 of 79

Your goal is malleable Excel data. This looks like an Excel file but isn’t: It is still the text file, so the next step is saving this as an Excel file.

Slide 15 of 79

Slide 16 of 79

insert a blank row at the top.

+ it will be used later to add the field names to be used in the Access database.+ field names can be added from scratch, or they can be copied from an earlier timeframe’s new-titles spreadsheet.

Slide 17 of 79

For now, leave the new top row blank.Reason: bulk data preparation is easier without the new column names

Slide 18 of 79

Remember the cheat-sheet from earlier?

Now is a good time to open it, to get ready for the data clean-up and “prettying up” clunky-looking repeat values. This handy cheat-sheet contains a list of all the repeat data cleanup.

(Note: you’ll have to create itjust once and then update its information as needed)

Slide 19 of 79

The cheat-sheet:

you’ll see itsuse later in this

demo.

Slide 20 of 79

Next: data preparation. Create an invisible title-sort field: Copy the title fieldSlide 21 of 79

Insert the copied title field. At first you’ll have two identical fields. One field will remain the same; the title-sort field will be edited some. These fields’ functionalities will come to full fruition in the Access database.

Slide 22 of 79

Note the copied sort field. Rename it to “TitleBibliographic” for future use. Slide 23 of 79

Title-sort field: Delete all beginning articles, such as “the” “a” “an”, “el”, “la”, etc with the find-and-replace (search by columns, not rows. Make sure you only edit the sorting field and not the neighboring title field. Use “replace”, not “replace all”). Can’t remember all those articles from the applicable languages? Just list them on your cheat-sheet.

Slide 24 of 79

More data preparation: insert a blank column between the “Call#” and “Location” fields. Slide 25 of 79

Slide 26 of 79

Slide 27 of 79

Next: inserting the column names.

Column names will be the basis offields of the Access data table formedfrom this spreadsheet.

Slide 28 of 79

Remember this new empty row, left blank for the time being?

Empty cells are above the following fields:TitleBibliographic – this is the invisible sorting fieldTitle – this is the field embedded in the concatenated search cellAuthor – used for search fields where Call# is too generic for unique searchImprint – brought into the Access report (that generates the webpage)Location – location codeLocationMeaning – this field has more descriptive location name for patron useCall# (bibliographic) – Call# on the shelfNote (order) – this contains some info useful for later data refiningProcessing note – contains information about gifts; part of the Access/web reports

Slide 29 of 79

Now, open last month’s spreadsheet (if you have one) and:

+ copy the top row

+ insert it into this month’s just-prepared spreadsheet

+ from-scratch instructions follow 4 slides down

Slide 30 of 79

Slide 31 of 79

Copy these top rows from a previous new-titles spreadsheet

Slide 32 of 79

Now insert them in the new spreadsheet you’re currently working on.

Slide 33 of 79

And here they are... The just-copied rows.

If you’re just starting, you don’t have any spreadsheet from an earlier month.

And... you can’t copy-and-insertlast month’s top row into this month’sempty row.

+ In that case, just name the empty columns:

Above this field Name the blank field this:TitleBibliographic TitleBibliographicTitle TitleAuthor AuthorImprint ImprintLocation LocationLocationMeaning LocationMeaningCall # (bibliographic) CallNumberNote (order) AlsoUsefulForProcessing note GiftFrom

Slide 34 of 79

“Freeze panes” under the top rows you want to see while scrolling through all records.

(not necessary,but it’s convenientfor data clean-up)

Slide 35 of 79

Next: data clean-up. Start by bulk-deleting unneeded data. Highlight unneeded data and “delete”.

Slide 36 of 79

In this example, earlier data (from the rows below) are now gone.Slide 37 of 79

Slide 38 of 79These data remain: they’ll be in the Access database and data queries.

Remember the cheat-sheet?

Here it is again, this time applied to data clean-up...

Slide 39 of 79

Cheat-sheet eliminates the burden of remembering how to replace each clunky-looking value.Using the same information every month also ensures data consistency in the whole database.

Here’s an example of a home-grown informal value to be replaced with more formal wording to benefit library patrons.

Slide 40 of 79

Throughout Excel, use the easy “find-and-place” function to edit any data in need of refining.

“By Rows” is default. Change it to “by Columns”.

Reason: you’re searching by column, within the same data field.

Slide 41 of 79

Slide 42 of 79

Slide 43 of 79

Next: prepare for automatic search fields.The easiest way to do this is by lumping together all items to be searchedby the same value.

•Example: cluster together everything to be searched by•LC call# and•non-LC call#

•The first step is to sort items by location code.•This will lump together the new titles by their collections and call# types.

•Spreadsheets don’t sort as conveniently as databases.•To get the spreadsheet to sort by the left column (location):

•Copy the “location” column”•Insert it on the left.•Then highlight all data rows to be sorted

•(important: the spreadsheet only sorts the highlighted column!•highlight all data except the header rows, or the sort results will garble)

•Sort them by “A-Z”•(this will sort by location code in ascending order)

Slide 44 of 79

Slide 45 of 79

Copy the entire “location code” column and add it to the left of all other data columns.

It will become the sorting column for this data-editing stage.

Slide 46 of 79

Sort by the new location code field on the very left.

Once everything is sorted by location code, filling in all the “Location Meaning” values is just a matter of easy copy-and-paste.

Copy and paste the descriptive “Location Meaning” value. Copy a value once, then paste it in all applicable subsequent rows.

Slide 47 of 79

Example of finished bulk-copied values for the “Location Meaning” field.

Slide 48 of 79

Slide 49 of 79

Now the data are cleaned-up; you’re ready to generate the title-specific unique search links.

Next: Concatenating fields to build unique hyperlinks.

The structure of concatenated search fields:Slide 50 of 79

How “ProgrammedSearchLink” is built:+ OPAC search start, e.g. by LC call# joined with:+ “SearchElement”is copy of unique call#

Next: example of a complete title-specific hotlink

Title = B5 M5=L5&k5

finished hyperlink: N5 = b5&”#”&m5

Call# = G5 Call# for search:K5 = G5

Slide 51 of 79

Describing archives#http://library.winthrop.edu/search/c?SEARCH=Z695.2 .S625 2004

Slide 52 of 79

All search fields have the same syntax “Title#ProgrammedSearchLink”.

Construct this search field once, then copy-and-paste its syntax into all the remaining “HyperlinkCombined” rows, throughout this entire spreadsheet.

Slide 53 of 79

Highlight all, then paste the copied syntax into all the search fields.

Copy these three fields: + SearchElement + PlainSearchLink + ProgrammedSearcLink

Next, paste them in bulk throughout all remaining records with LC call numbers.

Why these three fields? They contain the building blocks of the unique links for all titles with unique call numbers.

Slide 54 of 79

Slide 55 of 79

Now bulk-paste these three copied fields into all remaining LC-call# titles’ remaining rows.

Slide 56 of 79

After the bulk-paste.

These here intentionally left blank...

Next: constructing search links fornon-LC call# items.

Also on the cheat-sheet: reminders on search links for titles with non-LC call#s

First non-LC example:Juvenile titles with Deweycall numbers

Slide 57 of 79

Slide 58 of 79

Select the non-LC search link for copying

Slide 59 of 79

Slide 60 of 79

Slide 61 of 79

Here, of the non-LC call numbers, on the Dewey call#s are unique for the specific titles.

As with LC-call#s, the “search element” equals the call number.

Copy these three fields again:+ SearchElement+ PlainSearchLink+ ProgrammedSearchLink

Then paste them into all remaining rows of titles with unique non-LC call numbers

Slide 62 of 79

Slide 63 of 79

Slide 64 of 79

Slide 65 of 79

Next: what to do when

•call numbers are too generic for a unique OPAC search and

•there’s no other unique field indexed for OPAC searching in the record...

Slide 66 of 79

Slide 67 of 79

Slide 68 of 79

Slide 69 of 79

Slide 70 of 79

Slide 71 of 79

Slide 72 of 79

Slide 73 of 79

Slide 74 of 79

Sneak preview:Excel concatenated hyperlink search fields in Access...

Slide 75 of 79

When the Excel table is brought into Access: + everything copies over, including the

hyperlink.

Note how the # symbol invokes the following:1. the end of the text2. the beginning of the URL3. the “hiding” of the URL behind the text, as

a fully functional hyperlink.

Slide 76 of 79

You’ll see this again in the Access portion (next).

Slide 77 of 79

Slide 78 of 79

Back to startTo next segment: 5. Access programming (42 slides)

Slide 79 of 79