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
Thanks Trevor for your helpful explanation.
What I ended up doing was creating an SQL query then working with the results in Excel. Not sure I'm smart enough to use Access!
Ans yes it would be worthwhile to talk at IUG.
Cheers,
Bruce

bme@unimelb.edu.au
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
That does not sound like a fun report to be dealing with.

SQL sounds like the best choice. I try to avoid using Sierra's export for anything linked. The one nice feature of the export is that you can select control character 9 (for tab) as the delimiter. pgAdmin's lack of delimiter choices is annoying. We mostly use the pipe symbol '|' but if I have data where I'm really worried about the delimiting I use psql to run the query which allows you to specify the delimiter.

Trevor makes some good points about Access, and it is a good toolset to use when making things pretty for reports and such, but when I just want to play with the data I like to use Excel 2016 Power Query. Do a new query straight from the csv file, don't import it into excel and try to load it from a worksheet or it will likely not load all your rows. Once the data is loaded you can try lots of formatting options and if you do something incorrectly you just remove it from the applied steps. I'm waiting for IT to give me Power BI as well so I can try scripting some of these steps.

If SQL gets too slow for any of your operations, you might want to look at Python's Pandas DataFrames. I've been experimenting a bit with them and found them to be way quicker for multiple column sorting than SQL for example.

Julie.
jcole@langara.ca

jcole@langara.ca
Comment
There are no comments made yet.
  1. more than a month ago
  2. Sierra/ Millennium/ Encore
  3. # 2
  • Page :
  • 1
  • 2


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