IUG FORUM

Stay always connected!
  1. Bruce Eames
  2. Sierra/ Millennium/ Encore
  3. Wednesday, 27 June 2018
  4.  Subscribe via email
Hi All,

Something we do a lot for collections staff is produce lists of items in Excel, from data exported out of a review file in a delimited form.

With an item list one can usually manage a fairly neat job of getting exported fields from items and bibs into Excel columns - but what we have a lot of in our database is items linked to multiple bibs e.g. for bound collections of separate titles.

Exporting bib data where an item is linked to multiple bibs produces a nightmarish mess of columns in Excel where for example if an item is linked to 5 bibs Sierra will export 5 authors in 5 consecutive columns, then 5 titles in 5 columns etc etc.

I was wondering if anyone had found an easy way to deal with this? SQL might give a neater result but it is harder to craft the query and some result sets are many tens of thousands of items which might tax the system and just time out.

Any advice which may reduce my stress levels would be gratefully received.

Thanks,

Bruce.

bme@unimelb.edu.au
Comment
There are no comments made yet.
Accepted Answer Pending Moderation
0
Votes
Undo
I am by far not an Excel guru, but I find that using more than one field delimiter, say, the @ and the ; will at least parse out the multi-valued fields into their own column. Depending on the quantity of them, I then sort by the 2nd field of say the author, or the url and move blocks of data to the right or to the left of the columns to compensate for the records that don't have a second author, or a second url. I'm usually using datasets of about 5000 or less, so it's not terribly odious to sort 4x to accomodate the multiple fields. We rarely use the "," as a delimiter when we output from create lists, and so we often use the @ as the default, but it took me about 4 years to realize I could in fact in excel use more than one delimiter to split the data in a single field output to split it by multiple columns. Hope that helps!Susan
From: "Innovative Users Group" <discussions@innovativeusers.org>
To: "Susan Johns-Smith" <sjohnssmith@pittstate.edu>
Sent: Tuesday, June 26, 2018 8:25:05 PM
Subject: [IUG] [#12205]: Exporting from lists into Excel when linked records are present




















New Question Posted

Bruce Eames created a new discussion Exporting from lists into Excel when linked records are present in Sierra/ Millennium/ Encore














Hello,


This is a notification to let you know that Bruce Eames has created new discussion on the site. You may find the snippet of the new discussion below:








Hi All,



Something we do a lot for collections staff is produce lists of items in Excel, from data exported out of a review file in a delimited form.



With an item list one can usually manage a fairly neat job of getting exported fields from items and bibs into Excel columns - but what we have a lot of in our database is items linked to multiple bibs e.g. for bound collections of separate titles.



Exporting bib data where an item is linked to multiple bibs produces a nightmarish mess of columns in Excel where for example if an item is linked to 5 bibs Sierra will export 5 authors in 5 consecutive columns, then 5 titles in 5 columns etc etc.



I was wondering if anyone had found an easy way to deal with this? SQL might give a neater result but it is harder to craft the query and some result sets are many tens of thousands of items which might tax the system and just time out.



Any advice which may reduce my stress levels would be gratefully received.



Thanks,



Bruce.



bme@unimelb.edu.au






http://innovativeusers.org/index.php/iug-forum/sierra-millennium-encore/12205-exporting-from-lists-into-excel-when-linked-records-are-present.html"">Read this discussion














This email was sent to you, because you have subscribed to the discussion.

To unsubscribe, please http://innovativeusers.org/index.php/iug-forum.html?controller=subscription&task=unsubscribe&data=dHlwZT1zaXRlDQpzaWQ9NDI2MQ0KdWlkPTYxOTcNCnRva2VuPTY4NzVkMzA5ZDA5NTlmM2QxNThkNDM2Y2YxMzBlMTNm";">click here. http://innovativeusers.org/index.php/iug-forum/profile.html#Subscriptions"">Manage subscriptions.













--
Susan M. Johns-Smith
University Professor & Systems Librarian
Axe Library / Library Services
Pittsburg State University
Pittsburg KS 66762
620-235-4115
sjohnssmith@http://pittstate.eduorcid.org/0000-0002-5281-0983


sjohnssmith@pittstate.edu
Comment
There are no comments made yet.
  1. more than a month ago
  2. Sierra/ Millennium/ Encore
  3. # 1
Accepted Answer Pending Moderation
0
Votes
Undo
This is something I've been looking for a solution for for a couple of years now*. I've found a clunky sort of workaround which I shared at the most recent IUG but no truly adequate solution. If I could go back in time I'd create "dummy" item records for all those bibs...

I am still hoping SQL will be the answer and will let you know if I manage to work out anything at all.

*Partial list of what I've tried that didn't work includes MarcEdit, Excel macros, different export profiles.

:(

Laura.Wright@colorado.edu
Comment
There are no comments made yet.
  1. more than a month ago
  2. Sierra/ Millennium/ Encore
  3. # 2
Accepted Answer Pending Moderation
0
Votes
Undo
Thanks Laura,
I will check your IUG presentation. What I ended up doing was using SQL : a bit of effort creating the query but at least i can use this in the future now.
Cheers,
Bruce.

bme@unimelb.edu.au
Comment
There are no comments made yet.
  1. more than a month ago
  2. Sierra/ Millennium/ Encore
  3. # 3
Accepted Answer Pending Moderation
0
Votes
Undo
Yes this is always a fun one. I’ve taken a few steps to help mitigate the headaches you’re describing. But first off, I would say that you should just keep the data in excel. You have lots of data there and people know what to do with excel. The challenge you have is compressing that data better. Definitely stick with items and not bibs.

Now as for data munging, the first thing I do is when you are exporting you can choose the character for delimiting fields. I found commas to add too many problems in large files, so I started using the tilde ~ instead. Then, when I use Data/Text to Columns, on the second option screen, I just use ~ as the “Other” option. I’ve been doing this for probably 5 years now and I have yet to find any MARC or Sierra file that uses the tilde in any field. It has been quite reliable and I get nice clean data (ok there is the occasional slip, but those are really easy to catch). Of course, this way gives me numerous duplicates. Hence the second part.

The second problem of using SQL is an issue too. PGAdmin is great for getting the data out – and it does it super fast, but the real problem is once I have it out, I need to manipulate it for different needs. Too often we get our results out of Sierra and into excel and then have to manipulate them further. SQL doesn’t solve the manipulation problem. My response to this is to stick with getting the data out with Excel and use Access to create the nuanced data you need.

My particular and similar problem with this is when I get the list of textbooks from the bookstore and have to compare it to our current holdings so I know which ones to order. The problem I had with my bookstore list of textbooks is that each class would have the same textbook repeated multiple (in many cases over 100) times as each text is reported for every section of the class. I just needed the books, but the Acquisitions folks and reserves needed it organized by the classes. Hence, I had Accounting textbooks coming up 200 times as it was used in multiple classes which had 20 + classes. I used Access to take the original data, and with different queries, I could compress that huge excel sheet down to the single titles I needed for selection. All I had to do was to group my query on the title and then I had the simplicity I needed. But the next problem was for the classes. That was when I used a Crosstab query to summarize the books along with the number of classes it was used in. I had the single title data I wanted and Acquisitions and Reserves have the data they need. The nice part is, when I bring that Excel data into Access, I’m not changing the data at all, I just create the views people need and send it back out to them in an excel sheet.

I wanted to talk about the Textbooks in my IUG session on Access but it proved too complicated. Might do it next year instead.

Hope this helps?

Sincerely,

Trev

smitht7@douglascollege.ca
Comment
There are no comments made yet.
  1. more than a month ago
  2. Sierra/ Millennium/ Encore
  3. # 4
Accepted Answer Pending Moderation
0
Votes
Undo
I'm not well-versed in SQL, but I tried various ways to remove the data but nothing worked. There is probably a way to do it via Python script, but I don't have the time to devote to it right now.

laura.shea-clark@mountainview.gov
Comment
There are no comments made yet.
  1. more than a month ago
  2. Sierra/ Millennium/ Encore
  3. # 5
  • Page :
  • 1
  • 2


There are no replies made for this post yet.
Be one of the first to reply to this post!