Hi all, Running into something that seems like it should be simple, but I'm running in circles trying to figure it out -- is there a simple (or not simple) way to export a document containing titles, authors, and isbn numbers? In truth, all I really need are the isbns -- stocktaking and inventory exports don't seem to include isbn numbers as a field. I could make a "List" and export that as csv, but I want ALL the isbns in my library, in which case it seems like i'd have to add all titlesmanually to a list. So, in short, my goal is: a document, preferably CSV, containing all isbns in my koha library of approx 3000 items. Any tips? Many thanks, Daniel
You should create a guided report for this. Do it at Reports > Create from SQL, and paste this into the SQL field: SELECT title, author, isbn FROM biblio LEFT JOIN biblioitems ON (biblio.biblionumber=biblioitems.biblionumber) Choose an apropriate name for the report. 2016-04-18 16:39 GMT-03:00 Daniel Glendening <danieljglendening@gmail.com>:
Hi all,
Running into something that seems like it should be simple, but I'm running in circles trying to figure it out -- is there a simple (or not simple) way to export a document containing titles, authors, and isbn numbers?
In truth, all I really need are the isbns -- stocktaking and inventory exports don't seem to include isbn numbers as a field. I could make a "List" and export that as csv, but I want ALL the isbns in my library, in which case it seems like i'd have to add all titlesmanually to a list.
So, in short, my goal is: a document, preferably CSV, containing all isbns in my koha library of approx 3000 items.
Any tips?
Many thanks, Daniel
_______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha
-- Tomás Cohen Arazi Theke Solutions (http://theke.io) ✆ +54 9351 3513384 GPG: B2F3C15F
Daniel, Tomas's suggestion is good, but I would like to propose to switch its SELECT for using ExtractValue(marcxml, '//datafield[@tag="020"]/subfield[@code="a"]') The reason is that the isbn column in the database contains concatenated ISBNs and the field length is limited, so the list of ISBNs could be truncated. The SELECT assume that you are using MARC21. In addition, you could also get invalid ISBN using the subfield z The final query would be : SELECT ExtractValue(marcxml, '//datafield[@tag="020"]/subfield[@code="a"]') as ISBN FROM biblioitems where ISBN <>"" Note that some lines may content more than 1 ISBN. Cheers, Eric Bégin Solutions inLibro inc. 7240, Waverly St. Montreal (Quebec) Canada 888 604-2627 | www.inLibro.com On 2016-04-18 15:57, Tomas Cohen Arazi wrote:
You should create a guided report for this. Do it at Reports > Create from SQL, and paste this into the SQL field:
SELECT title, author, isbn
FROM biblio
LEFT JOIN biblioitems
ON (biblio.biblionumber=biblioitems.biblionumber)
Choose an apropriate name for the report.
2016-04-18 16:39 GMT-03:00 Daniel Glendening <danieljglendening@gmail.com>:
Hi all,
Running into something that seems like it should be simple, but I'm running in circles trying to figure it out -- is there a simple (or not simple) way to export a document containing titles, authors, and isbn numbers?
In truth, all I really need are the isbns -- stocktaking and inventory exports don't seem to include isbn numbers as a field. I could make a "List" and export that as csv, but I want ALL the isbns in my library, in which case it seems like i'd have to add all titlesmanually to a list.
So, in short, my goal is: a document, preferably CSV, containing all isbns in my koha library of approx 3000 items.
Any tips?
Many thanks, Daniel
_______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha
At 05:16 PM 4/18/2016 -0400, Eric Bégin wrote:
Daniel,
Tomas's suggestion is good, but I would like to propose to switch its SELECT for using ExtractValue(marcxml, '//datafield[@tag="020"]/subfield[@code="a"]')
The reason is that the isbn column in the database contains concatenated ISBNs and the field length is limited, so the list of ISBNs could be truncated.
A good suggestion, but to be a tad pedantic, SchemaSpy has biblioitems.isbn as mediumtext, 16777215, [checkmark], null, ISBN (MARC21 020$a); I'm not sure how many you'd have to concatenate to truncate one and two thirds Mb ;=) Best -- Paul
The SELECT assume that you are using MARC21.
In addition, you could also get invalid ISBN using the subfield z
The final query would be :
SELECT ExtractValue(marcxml, '//datafield[@tag="020"]/subfield[@code="a"]') as ISBN FROM biblioitems where ISBN <>""
Note that some lines may content more than 1 ISBN.
Cheers,
Eric Bégin Solutions inLibro inc. 7240, Waverly St. Montreal (Quebec) Canada
888 604-2627 | www.inLibro.com
On 2016-04-18 15:57, Tomas Cohen Arazi wrote:
You should create a guided report for this. Do it at Reports > Create from SQL, and paste this into the SQL field:
SELECT title, author, isbn
FROM biblio
LEFT JOIN biblioitems
ON (biblio.biblionumber=biblioitems.biblionumber)
Choose an apropriate name for the report.
2016-04-18 16:39 GMT-03:00 Daniel Glendening <danieljglendening@gmail.com>:
Hi all,
Running into something that seems like it should be simple, but I'm running in circles trying to figure it out -- is there a simple (or not simple) way to export a document containing titles, authors, and isbn numbers?
In truth, all I really need are the isbns -- stocktaking and inventory exports don't seem to include isbn numbers as a field. I could make a "List" and export that as csv, but I want ALL the isbns in my library, in which case it seems like i'd have to add all titlesmanually to a list.
So, in short, my goal is: a document, preferably CSV, containing all isbns in my koha library of approx 3000 items.
Any tips?
Many thanks, Daniel
_______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha
_______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha
--- Maritime heritage and history, preservation and conservation, research and education through the written word and the arts. <http://NavalMarineArchive.com> and <http://UltraMarine.ca>
Thank for pointing that out Paul ! My mind still think the isbn column is a varchar(30) :) Registry updated ! :) Eric On 2016-04-18 19:52, Paul A wrote:
At 05:16 PM 4/18/2016 -0400, Eric Bégin wrote:
Daniel,
Tomas's suggestion is good, but I would like to propose to switch its SELECT for using ExtractValue(marcxml, '//datafield[@tag="020"]/subfield[@code="a"]')
The reason is that the isbn column in the database contains concatenated ISBNs and the field length is limited, so the list of ISBNs could be truncated.
A good suggestion, but to be a tad pedantic, SchemaSpy has biblioitems.isbn as mediumtext, 16777215, [checkmark], null, ISBN (MARC21 020$a); I'm not sure how many you'd have to concatenate to truncate one and two thirds Mb ;=)
Best -- Paul
The SELECT assume that you are using MARC21.
In addition, you could also get invalid ISBN using the subfield z
The final query would be :
SELECT ExtractValue(marcxml, '//datafield[@tag="020"]/subfield[@code="a"]') as ISBN FROM biblioitems where ISBN <>""
Note that some lines may content more than 1 ISBN.
Cheers,
Eric Bégin Solutions inLibro inc. 7240, Waverly St. Montreal (Quebec) Canada
888 604-2627 | www.inLibro.com
On 2016-04-18 15:57, Tomas Cohen Arazi wrote:
You should create a guided report for this. Do it at Reports > Create from SQL, and paste this into the SQL field:
SELECT title, author, isbn
FROM biblio
LEFT JOIN biblioitems
ON (biblio.biblionumber=biblioitems.biblionumber)
Choose an apropriate name for the report.
2016-04-18 16:39 GMT-03:00 Daniel Glendening <danieljglendening@gmail.com>:
Hi all,
Running into something that seems like it should be simple, but I'm running in circles trying to figure it out -- is there a simple (or not simple) way to export a document containing titles, authors, and isbn numbers?
In truth, all I really need are the isbns -- stocktaking and inventory exports don't seem to include isbn numbers as a field. I could make a "List" and export that as csv, but I want ALL the isbns in my library, in which case it seems like i'd have to add all titlesmanually to a list.
So, in short, my goal is: a document, preferably CSV, containing all isbns in my koha library of approx 3000 items.
Any tips?
Many thanks, Daniel
_______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha
_______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha
--- Maritime heritage and history, preservation and conservation, research and education through the written word and the arts. <http://NavalMarineArchive.com> and <http://UltraMarine.ca>
_______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha
At 07:39 PM 4/18/2016 +0000, Daniel Glendening wrote:
So, in short, my goal is: a document, preferably CSV, containing all isbns in my koha library of approx 3000 items.
Create a report: SELECT isbn FROM biblioitems; and select download as csv Best -- Paul --- Maritime heritage and history, preservation and conservation, research and education through the written word and the arts. <http://NavalMarineArchive.com> and <http://UltraMarine.ca>
participants (4)
-
Daniel Glendening -
Eric Bégin -
Paul A -
Tomas Cohen Arazi