[Koha] Koha Export ISBN

[Eric Bégin]

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 at 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 at lists.katipo.co.nz
>>>> https://lists.katipo.co.nz/mailman/listinfo/koha
>>>
>>
>> _______________________________________________
>> Koha mailing list  http://koha-community.org
>> Koha at 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 at lists.katipo.co.nz
> https://lists.katipo.co.nz/mailman/listinfo/koha