Importing with the Dynamic Excel Report


This document describes what it takes to import new publications in batch or to update existing publications using the Dynamic Excel format. For a general introduction to batch importation, please consult this document. Note that you can also import using the ONIX 3.0 standard.

Structure of the document


When importing your metadata with the Excel template, make sure that :

  • You can export your existing metadatas from the interface by clicking on the Dynamic Excel Report button or by downloading the Excel template; You can also have access to this smaller template, specifically made to import library products.
  • Cell A1 contains the version number, it should not be changed;
  • Otherwise, row 1 and 2 are ignored, they contain comments that are not interpreted by the importer;
  • Row 3 contains the actual names of the columns, they are interpreted and case-sensitive;
  • Row 4 contains your data. You can use the values as names, or with their ONIX codes;
  • One line should be added for each publication's formats (i.e. one line for the EPUB, one line for the PDF);
  • A template, in Excel format, complete with each column's description is available here;
  • You can add or remove columns to suit your needs, or change their orders as you see fit;
  • When defining a Group (example : Contributors, Prices, Collections, Medias) all items in that group are required;
  • Groups are repeatable, you can have as many as you'd like;
  • ISBN and Titles are always mandatory;


Description of the tags (columns in the Excel file)


Tags Group
**All groups defined here are repeatable**
Field type Possible values Mandatory value
for new products
(Y)es / (N)o


"
Notes :
For a publication update, an empty value will erase any value already in the metadata for the publication.
A culumn that is erased from the XLS file will be ignored on import.

RecordReference
Text
N Identifier. Usefull for grouping together all the formats of a publication. Can be anything, on export, will be the identifier of the format
ProductFormDetail
Fixed values E107 : pdf
E101 : epub
E101_E200 : epub_reflowable
E101_E201 : epub_fixed
BA : paper
E127 : mobi
A103 : audio
LIB : library
Y ONIX list 78.
The nature of the product
ePublicationVersionNumber
  Fixed values
101A : EPUB_2_0_1
101B : EPUB 3_0
101C : EPUB 3_0_1
116A : Kindle_mobi_7
116B : Kindle_KF8
N
Mandatory if ProductFormDetail is : E101 : epub
E101_E200 : epub reflowable
E101_E201 : epub fixed
ProductIdType
Fixed values 01 : custom
03 : ean
15 : isbn
Y ONIX list 5.
The nature of the key used to define the product. If not referenced, the system will detect the type.
ProductIdentifier
Text
Y The identifier of the product (ISBN, EAN or custom key) This identifier is mandarory for both new products and updates.
PublisherName
Text
N Ignored on import, unless importing from the distributor's account
PublisherIdentifier
Number
Y Organisation id, exemple 123
ImprintName
Text
N Name of the imprint if there's one.
SenderName
Text
N Broadcaster name, ignored on import
TitleText
Text
Y Title of the book (human-readable). This title will be used by the Platform and the stores.
TitleWithoutPrefix
Text
N Title of the book that can be used in a sortable list. For example, without the prefix.
TitlePrefix
Text
N Prefix for the title
Subtitle
Text
N Subtitle
WebsiteLink
Text
N Book's website, if any.
LanguageRoleCode Language Fixed values 01 : language_of_text
02 : original_language

Y ONIX list 22.
01 : Publication's actual language.
02 : Original language of a book, if it was translated.
Language Language Fixed values - fr
- en
- it
Y Language : Format: ISO 639-1 (ex.: fr, en, it)
TextType Text Fixed values 02 : short_description
03 : summary
04 : table_of_content
05 : cover_copy
06 : review_quote
09 : endorsement
10 : promotional_headline
12 : biographical_note
14 : excerpt

N ONIX list 153.
The type of the text defined in the TextContent tag. Optionnal, but mandatory if TextContent is present.
TextContent Text Text
N Text as described by TextType
PageCount
Number
N The total number of pages of the publication. If left blank, this field will contain the number of pages in the PDF. The ePub has no precise count of pages.
BlankPageCount
Number
N Number of white pages at the beginning of the book.
RomanPageCount
Number
N Number of Roman pages at the beginning of the book.
UnlockedPages
Custom
N List of pages that can be viewed on the Flipbook (if a PDF is provided).
Format: 1-6;18-22;105;110"
Pages 1 to 6, 18 to 22, 105 and 110 will be viewed.
CollectionTitle Series Text
N Title of the collection, text value
SeriesIdentifier Series Text
N The identifier of the serie, used to group all item in the same serie. Use a custom, recognizable id or the ISSN, if you have one.
SeriesTitle Series Text
N The name of the serie.
SeriesSequenceTitle Series Text
N Use the written part of the series, for example “Volume 1”, “Tome 3” or “Version 4”
SeriesSequence Series Number
N The numeral part of the series_part_title. For example : 1, 3 or 4
SubjectSchemeBisac
SubjectSchemeClil
SubjectSchemeDewey
SubjectSchemeBic
SubjectSchemeCCE
SubjectSchemeElectre



Mandatory
BIC or BISAC is Mandatory here or in the general SubjectScheme block.

First classification of each scheme is Primary. These columns are shortcuts to rapidly import the most widely used classification scheme. One colum will contain any number of codes, separated by a space. Exemple "ARC000000 ARC001000 ARC002000 ARC003000", for the SubjectSchemeBisac tag.
SubjectSchemeType Classification Text 01 : dewey
02 : abridged_dewey
03 : lc_classification
04 : lc_subject_heading
05 : nlm_classification
06 : mesh_heading
07 : nal_subject_heading
08 : aat
09 : udc
10 : bisac
11 : bisac_region_code
12 : bic_subject_category
13 : bic_geographical_qualifier
14 : bic_language_qualifier
15 : bic_time_period_qualifier
16 : bic_educational_purpose_qualifier
17 : bic_reading_level_and_special_interest_qualifier
18 : ddc_sachgruppen_der_deutschen_nationalbibliografie
19 : lc_fiction_genre_heading
20 : keywords
21 : bic_children_book_marketing_category
22 : bisac_mMerchandising_theme
23 : publisher_own_category_code
24 : proprietary_subject_scheme
25 : tabla_de_materias_isbn
26 : warengruppen_systematik_des_deutschen_buchhandels
27 : swd
28 : electre
29 : clil
30 : dnb_sachgruppen
31 : nugi
32 : nur
33 : ecpa_christian_book_category
34 : siso
35 : korean_decimal_classification
36 : ddc_eutsch_22
37 : bokgrupper
38 : varegrupper
39 : laereplaner
40 : nippon_decimal_classification
41 : bsq
42 : anele_materias
43 : skolefag
44 : videregaende
45 : undervisningsmateriell
46 : norsk_ddk
47 : varugrupper
48 : sab
49 : laromedel
50 : forhandsbeskrivning
51 : spanish_isbn_udc_subset
52 : eci_subject_categories
53 : cce
54 : qualificatore_geografico_cce
55 : qualificatore_di_lingua_cce
56 : qualificatore_di_periodo_storico_cce
57 : qualificatore_di_livello_scolastico_cce
58 : qualificatore_di_eta_di_lettura_cce
59 : vds_bildungsmedien_facher
60 : fagkoder
61 : jel_classification
62 : csh
63 : rvm
64 : ysa
65 : allars
66 : ykl
67 : musa
68 : cilla
69 : kaunokki
70 : bella
71 : yso
72 : paikkatieto_ontologia
73 : suomalainen_kirja_alan_luokitus
74 : sears
75 : bic_e4l
76 : csr
77 : suomalainen_oppiaineluokitus
78 : japanese_book_trade_c_code
79 : japanese_book_trade_genre_code
80 : fiktiivisen_aineiston_lisaluokitus
85 : postal_code
86 : geonames_id
87 : newbooks_subject_classification
91 : gnd
92 : bic_ukslc
93 : thema_subject_category
94 : thema_geographical_qualifier
95 : thema_language_qualifier
96 : thema_time_period_qualifier
97 : thema_educational_purpose_qualifier
98 : thema_interest_age_special_interest_qualifier
99 : thema_style_qualifier
A2 : amnesord
A3 : statystyka_ksiazek_papierowych_mowionych_i_elektronicznych
A5 : rameau
A6 : nomenclature_discipline_scolaire
N ONIX list 26.
The type of classification, for example “bisac”.
SubjectSchemeDescription Classification Text
N First is Primary. Optional, use only if the classification code is not sufficient
SubjectSchemeCode Classification Text
N The actual code of the classification, example “ANT001000”. ONLY ONE CODE per block
Keywords
Text
N Any keyword. Semi-colon or coma separated
ContributorRole Contributor Fixed values A01: author
A02: with
A03: screenplay_writer
A04: libretto_writer
A05: lyrics_author
A06: composer
A07: artist
A08: photographer
A09: created_by
A10: from_an_idea_by
A11: designed_by
A12: illustrated_by
A13: photographs_by
A14: text_author
A15: preface_author
A16: prologue_author
A17: summary_author
A18: supplement_author
A19: afterword_author
A20: notes_author
A21: commentaries_author
A22: epilogue_author
A23: foreword_author
A24: introduction_author
A25: footnotes_author
A26: memoir_author
A27: experiments_author
A29: introduction_and_notes_author
A30: software_writer
A31: book_and_lyrics_author
A32: contributions_author
A33: appendix_author
A34: index_compiler
A35: drawings_by
A36: cover_design_or_artwork_by
A37: preliminary_work_by
A38: original_author
A39: maps_by
A40: inked_or_colored_by
A41: pop_ups_by
A42: continued_by
A43: interviewer
A44: interviewee
A99: other_primary_creator
B01: edited_by
B02: revised_by
B03: retold_by
B04: abridged_by
B05: adapted_by
B06: translated_by
B07: as_told_by
B08: translated_with_commentary_by
B09: series_edited_by
B10: edited_and_translated_by
B11: editor_in_chief
B12: guest_editor
B13: volume_editor
B14: editorial_board_member
B15: editorial_coordination_by
B16: managing_editor
B17: founded_by
B18: prepared_for_publication_by
B19: associate_editor
B20: consultant_editor
B21: general_editor
B22: dramatized_by
B23: general_rapporteur
B24: literary_editor
B25: music_arranged_by
B26: technical_editor
B99: other_adaptation_by
C01: compiled_by
C02: selected_by
C99: other_compilation_by
D01: producer
D02: director
D03: conductor
D99: other_direction_by
E01: actor
E02: dancer
E03: narrator
E04: commentator
E05: vocal_soloist
E06: instrumental_soloist
E07: read_by
E08: performed_by_orchestra_band_ensemble
E09: speaker
E99: performed_by
F01: filmed_photographed_by
F99: other_recording_by
Z01: assisted_by
Z98: various_roles
Z99: other
Y Author ONIX list 17.
The role of the contributor as defined by the ONIX standard. At leats one Author is mandatory
ContributorFirstName Contributor Text
Y First name of the person involved in the contribution.
ContributorLastName Contributor Text
Y Name of the person involved in the contribution.
ContributorCountry Contributor Fixed values - usa
- can
- fra
N Country of origin or actual residence. Format : ISO 3166-1 Alpha-3, lower case. Canada = "can", France = "fra", United States = "usa", Italy = "ita".
ContributorWebsite Contributor Text
N Website of the person.
ContributorBiography Contributor Text
N Biography
MediaNature
Media Fixed values 01 : front_cover
02 : back_cover
03 : cover_other
21 : feature_article
24 : press_release
11 : contributor_interview
17 : review
15 : sample_content
20 : teacher_guide
26 : trailer
15 - ibooksauthor_sample
15 : screenshot
AFC : audio_front_cover
ABC : audio_back_cover
N ONIX list 158.
The nature of the media.
MediaTitle Media Text
N The title of the media as can appear on store's sites
MediaFile Media Text
N The file of the media in the archive or website address
MediaNote Media Text
N The description of the media
OfferTemplateId
Number
N Template of the library offer
ProductRelationCode Relation Fixed values 00 : unspecified
01 : includes
02 : is_part_of
06 : alternative
07 : has_ancillary
08 : ancillary_of
11 : other_language
12 : alternative
13 : print_book
22 : same_author
23 : similar
27 : electronic_version_as
28 : enhanced
29 : basic
30 : same_collection
31 : different_market
Y ONIX list 51.
The relation code to an other Product

A library product as a relation of 31 to it's general public counterpart.
ProductRelationValue

Relation Text
Y The identifier of the product (ISBN, EAN or custom key)
ProductIdValue
PublishingStatus
  Fixed values
  Y
04 : active
08 : inactive
11 : withdrawn_from_sale
PublishingDateRole

Publishing Date 01 : publication
02 : embargo
09 : public_announcement
27 : preorder_embargo
N ONIX list 163.
PublishingDate Publishing Date
N Format ISO 8601. Examples: 2011-04-01T13:01:02 or 2011-04-01. If there is no time, midnight (00:00:00) will be used by default. The time zone is the one of the platform.
StartSaleDate
Date
N SupplyDateRole 08 Start of sales date for the format. Examples: 2011-04-01T13:01:02 or 2011-04-01. If there is no time, midnight (00:00:00) will be used by default. The time zone is the one of the platform.
EndSaleDate
Date
N End date of sale. Format ISO 8601. Examples: 2011-04-01T13:01:02 or 2011-04-01. If there is no time, midnight (00:00:00) will be used by default. The time zone is the one of the platform.
TechnicalProtection
Fixed values 00 : none
02 : watermark
03 : acs4
acs4_timelimited
N ONIX list 144.
The protection that will be applied on the ebook, when being sold. Possible values:

- watermark: A watermark will be applied at the bottom of the pages
- acs4: The file will be packaged and DRM protected with Adobe Content Server 4
- acs4_time_limited
- open: No protection is applied

Leave the protection empty for the Format which has the "paper" nature, as the Platform does not manage selling paper books.
LicenceDuration
Number
N If protection is acs4_timelimited, indicate the number of days.
PrizeName Prize Text
N The name of the lieterary prize. For example “Femina”
PrizeYear Prize Number
N The year of the nomination, example “2014”
PrizeCountry Prize Text
N The country where that prize was won. For example usa, can, fra
PrizeCode Prize Fixed values 01 : winner
02 : runner_up
03 : commended (was commanded)
04 : short_listed
05 : long_listed
06 : joint_winner
07 : nominated
N ONIX list 41.
The position for the prize or award.
AudienceRangeFrom
Number
N Age From (AudienceRangeQualifier 17)
AudienceRangeTo
Number
N Age to (AudienceRangeQualifier 17)
AudienceCodeType Audience Fixed values 01 : onix
06 : btlf_audience
Y ONIX list 29.
The type of code for the Audience code
AudienceCode Audience Fixed values
Y The actual audience code. For example 005 or 008 for BTLF, or 01 or 06 for onix
SalesRightsType
Fixed values 01 : exclusive_sale N ONIX list 46.
Optionnal because if empty, the platform has a default value that will automatically be applied
SalesRightsCountries
Fixed values - can
- usa
- fra
...
N WORLD
or a list of countries separated by a space.
Countries where the distributor/publisher has sales rights. We also accept «WORLD» if a publiher wants to set sales rights for all the countries supported by the platform. Format, separated by a space : ISO 3166-1 Alpha-3, lower case. Canada = "can", France = "fra", United States = "usa", Italy = "ita".
MarketPublishingStatus Market/Price Fixed values 04 : active
08 : inactive
11 : withdrawn
Y
ONIX list 68.
Withdrawn and Inactive have the same meaning
in our DB
MarketDateRole

Market/Price Fixed values 01 : publication_date
N ONIX list 67.
The type of date notification for those markets
MarketDate

Market/Price Date
N Publishing date on market. Format ISO 8601. Examples: 2011-04-01T13:01:02 or 2011-04-01. If there is no time, midnight (00:00:00) will be used by default. The time zone is the one of the platform.
CountriesIncluded Market/Price
- can
- usa
- fra
...
Y Countries where the price is applied. Format, separated by a space : ISO 3166-1 Alpha-3, lower case. Canada = "can", France = "fra", United States = "usa", Italy = "ita".
PriceCurrency Market/Price Fixed values - cad
- usd
- eur
Y Currency for the next amount value. Format : ISO 4217, lower case.
Example : "eur", "cad", "usd".
PriceAmount Market/Price Number
Y Price amount, variable length real number, with explicit decimal point when required, suggested
maximum length 12 characters. Ex : 19.99
PriceEffectiveFrom Market/Price Date
Y Date when this price will be effective. Format: ISO 8601. If this field is empty, the importation date will be used. Examples: 2011-04-01T13:01:02 or 2011-04-01. If there is no time, midnight (00:00:00) will be used by default. The time zone is the one of the platform.
PriceEffectiveUntil Market/Price Date
N Date when this price is no longer effective. Format: ISO 8601. If this field is empty, the importation date will be used. Examples: 2011-04-01T13:01:02 or 2011-04-01. If there is no time, midnight (00:00:00) will be used by default. The time zone is the one of the platform.
PriceType

Market/Price Fixed values DEF : default
01 : recommended_notax
02 ; recommended_tax
03 : fixed_notax
04 : fixed_tax
41 : publisher_notax
42 : publisher_tax
Y ONIX list 58.
default - automatic mapping for C2 requirements.
PriceQualifier Market/Price Fixed values 01 : member
05 : consumer
09 : linked
N ONIX list 59.
Default is 05 : Consumer
DiscountCode Market/Price Text
N Proprietary discount code.
SalesRestriction Market/Price Fixed values 04 : retailer_exclusive
06 : library_edition
11 : retailer_exception
N ONIX list 71.
SalesOutlets Market/Price Fixed values ACM : A C Moore
AAP : AandP
ALB : Albertson
AMZ : Amazon
ANR : Angus and Robertson
ANB : Anobii
APC : Apple
ASD : Asda
AUD : Audible
BDL : B Dalton
BNO : Barnes and Noble
BBB : Bed Bath and Beyond
BST : Best Buy
BIL : Bilbary
BJW : BJ Wholesale Club
BLK : Blackwell
BCA : Book Club Associates
BSH : Bookish
BKP : Bookpeople
BKM : Books A Million
BRD : Borders
BRB : Borders/Books Etc
BRT : British Bookshops
CDL : Casa del Libro
CHD : Christianbook.com
COP : Copia
CST : Costco
CRB : Crate and Barrel
CVS : CVS Drug Stores
CYB : Cyberlibris
DSG : Dick Sporting Goods
DIL : Dilicom
DYM : Dymocks
ELC : Early Learning Centre
ESN : Eason
EBC : Ebooks Corp
ECH : eChristian
ECI : El Corte Inglés
ELE : Electre
ELB : Elib.se
EMP : Empik
ENH : English Heritage
FDB : FeedBooks
FNC : Fnac
FRY : Fry Electronics
GMS : Gamestop
GOO : Google Books
GOS : Blinkbox
HST : Hastings Entertainment
HMV : HMV
HMD : Home Depot
IMM : Immatériel.fr
IND : Indigo-Chapters
IZN : Izneo
JSM : John Smith and Son
KMT : K-Mart
KNB : KNFB/Blio
KNO : Kno Inc
KBO : Kobo
KOO : Koorong
KRG : Kroger
LWE : Lowe
MKS : Marks and Spencer
MAT : Matras
MMS : Media Markt/Saturn
MCR : Microcenter
MRR : Morrisons
MTC : Mothercare
MYB : MyBoox
NTR : National Trust
NUM : Numilog
OFD : Office Depot
OFM : Office Max
OLF : OLF
PST : Past Times
PTS : Pet Smart
PTC : Petco
PLY : Play.com
PTB : Pottery Barn
RDB : Readbooks
RCL : ReadCloud
RST : Restoration Hardware
RET : Rethink
RTZ : Ritz Camera
SFW : Safeway
SNS : Sainsbury
SLF : Selfridges
SKB : Skoobe
SMW : SmashWords
SNY : Sony
STP : Staples
TEA : The Ebook Alternative
TRG : Target
TES : Tesco
TSR : Toys ‘R’ Us
TSO : TSO (The Stationery Office)
TXR : Txtr
VRG : Virgin Megastores
WHS : W H Smith
WTR : Waitrose
WLM : Wal-Mart
WST : Waterstone
WHT : Whitcoul
WLS : Williams Sonoma
WLW : Woolworths
ZVV : Zavvi
N ONIX list 139.
Format: various outlet codes must be separated by a space.


*Only codes are imported and exported for the SalesOutlet. Names are not supported and are mentionned here only for clarification.