Difference between revisions of "User:Hitspacebar"
Hitspacebar (talk | contribs) |
Hitspacebar (talk | contribs) |
||
Line 28: | Line 28: | ||
=== Create the CSV file === | === Create the CSV file === | ||
+ | |||
+ | '''Note''' that this SQL has been written with ''my'' primary verified records in mind and does ''not'' necessarily export all of ''your'' primary verified records (e.g. SERIALs are missing here). | ||
Replace the "-1" for the user_id below (above the ORDER BY) with your user id. | Replace the "-1" for the user_id below (above the ORDER BY) with your user id. |
Revision as of 12:45, 30 December 2015
A very brief fact list about me
- From Germany.
- Avid reader of speculative fiction since childhood, mostly Science Fiction, sometimes Fantasy, Magic Realism etc. and very rarely Horror.
- Sometimes reads English books in English, somtimes reads the German translation - depending an author, current mood and availability.
- Started editing here because I saw an error in a publication, created an account to correct that and then couldn't stop myself adding and editing. :)
One-time export of primary verified records to Book Catalogue Android app
This describes a way to do a one-time export of your primary verified records to a CSV file which can be imported by the Book Catalogue Android app (as of November 2015 and Book Catalogue 5.1.2). This might not work for later versions of ISFDB or Book Catalogue.
It's a one-time export, nothing you can do repeatedly to update your app data with later primary verifications.
Data export
Note that the import/export format of Book Catalogue described on its web site might not be up-to-date. One should create one book record in the app, export that to a CSV file and compare it to the described CSV format.
Get your user id
First get your user id: search one of the publications you primary verified as "Primary" (not "Primary2" etc.) and replace the "-1" in the SQL below with the id of that publication:
select v.user_id from pubs p, verification v
where v.reference_id = 1
and v.pub_id = p.pub_id
and p.pub_id = -1;
Create the CSV file
Note that this SQL has been written with my primary verified records in mind and does not necessarily export all of your primary verified records (e.g. SERIALs are missing here).
Replace the "-1" for the user_id below (above the ORDER BY) with your user id.
select
null as '_id',
(
select
GROUP_CONCAT(
CONCAT(
a.author_lastname, ', ', TRIM(SUBSTRING_INDEX(a.author_canonical, a.author_lastname, 1))
)
SEPARATOR '|'
)
from authors a, pub_authors pa
where pubs.pub_id = pa.pub_id
and a.author_id = pa.author_id
) author_details,
pubs.pub_title title,
pubs.pub_isbn isbn,
pb.publisher_name publisher,
case
when pubs.pub_year = '0000-00-00' then ''
when extract(month from pubs.pub_year) = 0 then SUBSTRING(pubs.pub_year, 1, 4)
when extract(day from pubs.pub_year) = 0 then SUBSTRING(pubs.pub_year, 1, 7)
else pubs.pub_year
end date_published,
'0' as 'rating',
'1,' as 'bookshelf_id',
'Default,' as 'bookshelf',
'0' as 'read',
case
when ps.pub_series_name is not null and pubs.pub_series_num is not null then CONCAT(ps.pub_series_name, ' (', pubs.pub_series_num, ')')
else ps.pub_series_name
end series_details,
pubs.pub_pages pages,
null as 'notes',
null as 'list_price',
case
when pubs.pub_ctype = 'ANTHOLOGY' then '1'
else '0'
end anthology,
null as 'location',
null as 'read_start',
null as 'read_end',
case
when pubs.pub_ptype = 'tp' then 'Paperback'
when pubs.pub_ptype = 'pb' then 'Taschenbuch'
when pubs.pub_ptype = 'hc' then 'Hardcover'
when pubs.pub_ptype = 'digest' then 'Heft'
else pubs.pub_ptype
end format,
'0' as 'signed',
null as 'loaned_to',
null as 'anthology_files',
null as 'description',
null as 'genre',
case
when l.lang_name = 'German' then 'Deutsch'
when l.lang_name = 'English' then 'Englisch'
else l.lang_name
end language,
convert(v.ver_time, DATE) as 'date_added',
null as 'goodreads_book_id',
null as 'last_goodreads_sync_date',
null as 'last_update_date',
null as 'book_uuid'
from verification v
inner join pubs on pubs.pub_id = v.pub_id
inner join pub_content pc on pubs.pub_id = pc.pub_id
inner join titles t on t.title_id = pc.title_id
left outer join languages l on t.title_language = l.lang_id
left outer join publishers pb on pb.publisher_id = pubs.publisher_id
left outer join pub_series ps on ps.pub_series_id = pubs.pub_series_id
where pubs.pub_ctype in ('COLLECTION', 'ANTHOLOGY', 'MAGAZINE', 'NOVEL', 'CHAPBOOK', 'OMNIBUS', 'NONFICTION')
and ((t.title_ttype = pubs.pub_ctype) or (t.title_ttype = 'EDITOR' and pubs.pub_ctype = 'MAGAZINE'))
and v.reference_id in (1, 15, 16, 17, 18)
and v.ver_status = 1
and v.user_id = -1
order by pubs.pub_title
INTO OUTFILE '/tmp/isfdb_export.csv'
CHARACTER SET 'utf8'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';
Data post-processing
Some data in the CSV file needed to be adjusted in order to be understood correctly by Book Catalogue.
- Replace all \N values (that's not a newline character but meant verbatim) with "" (two double quotes).
- Add a comma at the end of each row except for the last row.