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 | 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. |