Configure import of invoice Excel files

The first thing to do is tell the system how the information is organized in the Excel file. This consists in specifying what information each row and each column contains.

You access the configuration from Settings > Invoicing> Invoice Excel import :

Once you click on Edit you will be able to enter the layout configuration from the Excel file you will import. Describe your Excel layout as a JSON document in the box.

When you are done, tick the option in the top to enable the "Import from Excel link in the invoice details page" so that the option is accessible in the invoice documents of your projects.

Remember

Only authorized users can upload Excel files to a project invoice.

The JSON format

The JSON document is an array. Each element is an object and describes a specific type of Excel file.

If you have 3 very different Excel layouts, you would configure a JSON array with 3 objects (one per layout):

CODE

[
    { Excel layout 1 },
    { Excel layout 2 },
    { Excel layout 3 }
]

The Excel layout

Each layout object has these properties:

Property	Description	Type
filePattern	Use if you define multiple Excel layouts. The pattern permits the system to select the applicable layout for a given upload. The pattern is a regular expression on the file name. The following configuration applies only to file names containing "xyz1", "xyz2" etc.: CODE `{ "filePattern": "xyz\\d", .... }` NOTE: In JSON, you need to escape backslash and double quotes. Write \\ instead of \ and \" instead of ".	Optional string
firstRow	The first row in the Excel that contains documents and counts. By default, the value is 1 (first row).	integer
calculateCost	Tells if the invoice shall be costed upon import. Costing uses the standard Wordbee Translator mechanisms, including: Client price list attached to the invoice or project Services in the price list with unitary amounts and any applicable discounts	boolean
priceListCode	Optional (null). By default, costs are calculated from the price list linked to the invoice/project. However, you can explicitly specify the name of a client invoice to use. Note: To specify the default price list of your platform, write: "priceListCode": ""	Optional string
serviceUnitCode	Specifies the units of the count values, such as words, characters, pages, etc. If not specified, the system chooses "WD" (words). When costing, the system locates services for this kind of unit only. WD: Words CH: Characters See Price units for all options	Optional string
serviceProductCode	Optional (null). When costing, the system locates the first matching service. If you have multiple service options you probably set the "product code" field to make a distinction. In that case, you can indicate the product code to use in this field.	Optional string
columns	An array of all the Excel columns you want to extract. See next table.

Each element in the columns array has these properties:

Property	Description	Type
id	The Excel column such as "A", "B", "C", "XY".	Mandatory string
field	The content in the column. Use any of: NAME: Mandatory column. Contains the document name or a service SRC: Mandatory column. The source language code. This must be an ISO code such as "en", "en-GB", "fr", ..., TRG: Mandatory column. The target language code. TSK: Mandatory column. The type of work such as TR (Translation), RV (revision) etc. NOMATCH: Contains counts without any pre-translation, repetition or fuzzy match. PRETRANS100: Contains 100% pre-translation counts. PRETRANS110: Contain 100% (perfect or in-context) pre-translation counts. PRETRANSMT: Contains machine translations FUZZY1: Counts corresponding to the first fuzzy match interval configured in the applicable price list. FUZZY2: Second fuzzy match interval FUZZY3: .. FUZZY4: .. FUZZY5: .. FUZZY6: .. NOTRANS: Contains counts that shall not be costed at all. For example, words that stay untranslated. OTHER: This column contains no counts. The only purpose of this column type is if you want to leverage the ignoreRowPattern feature on a column that does not contain any counts. Important notes: If you use any of FUZZY1 to FUZZY6 please make sure that the applicable price list actually defines these intervals. If this is not the case then the lines cannot be costed correctly (they will show the full and not discounted amount).	Mandatory string
title	An optional title that will be appended to the document name (see column NAME). You would use this to detail the type of counts so that everything is crystal clear for your client: Example with a PRETRANS100 column: CODE `{ "title": "Exact pre-translations" .... }`	Optional string
keepZero	Optional boolean. Default is false. If true then zero counts (0) are added to the invoice. If false, then such counts are disregarded. Please note that empty cells are always disregarded.	boolean
ignoreRowPattern	Optional regular expression. If the cell matches this pattern then the entire row is skipped. This is useful if your Excel contains rows with sub-total, totals or other irrelevant data. Set pattern for the columns containing text that tells you if a row is to be disregarded.	Optional string

Example

The configuration for the example on the main page (also attached):

File	Modified
File wordcount-excel-io-settings.json	Sept 17, 2018

CODE

[
  {
    "firstRow": 4,
    "calculateCost": true,
    "priceListCode": null,
    "serviceUnitCode": "WD",
    "serviceProductCode": null,
    "columns": [
      {
        "id": "A",
        "field": "NAME",
        "ignoreRowPattern": "TOTAL"
      },
      {
        "id": "B",
        "field": "SRC"
      },
      {
        "id": "C",
        "field": "TRG"
      },
      {
        "id": "D",
        "field": "TSK"
      },
      {
        "id": "E",
        "field": "FUZZY1",
        "title": "Repetitions",
        "keepZero": true
      },
      {
        "id": "F",
        "field": "NOTRANS",
        "title": "Untranslated",
        "keepZero": true
      },
      {
        "id": "G",
        "field": "PRETRANS110",
        "title": "Perfect match"
      },
      {
        "id": "H",
        "field": "FUZZY1",
        "title": "100%"
      },
      {
        "id": "I",
        "field": "FUZZY2",
        "title": "95% - 99%"
      },
      {
        "id": "J",
        "field": "FUZZY3",
        "title": "85% - 94%"
      },
      {
        "id": "K",
        "field": "FUZZY4",
        "title": "74% - 84%"
      },
      {
        "id": "L",
        "field": "FUZZY5",
        "title": "50% - 74%"
      },
      {
        "id": "M",
        "field": "NOMATCH",
        "title": "No Match"
      },
      {
        "id": "N",
        "field": "OTHER"
      }
    ]
  }
]