Multilingual Excel and delimited text files

Use this filter to import Excel, CSV, or other text files with comma or tab separated values that contain multiple languages in columns.

Each row contains related texts, and each column defines what the text represents (for example, source text or translation).

You can define how memoQ imports text from each column into the project’s source and target languages.If you have a project manager license, you can also export multiple target languages in one go, into a single export file.

You can't import a multilingual Excel or text file into a LiveDocs corpus.

How to get here

  1. Start importing a multilingual Excel or text file.

  2. In the Document import options window, select the Excel or text files, and click Change filter & configuration.

  3. The Document import settings window appears. From the Filter drop-down list, choose Multilingual delimited text filter.

    Document import settings window showing Filter and Filter configuration drop-down lists, Add cascading filter link and tabs with Base format tab open, showing options to choose from: Select base format: Excel, Comma or tab-separated or Simple bilingual configuration, and Segmentation options: Segment source text only if the target cell contains no text, and a sample file. OK, Cancel, and Help buttons are in the bottom right corner.

By default, memoQ doesn't import numbers or formulas from Excel files. To import them, use the Excel options tab.

What can you do?

The settings are a bit different for Excel files and for CSV or tab-separated files.

On the Base format tab, choose between Excel and Comma or tab-separated.

If you translate into one target language only: Select the Simple bilingual configuration checkbox, and work with the columns at the bottom of the window. For more details, see the Dealing with columns section.

Under the Segmentation section, you can:

  • Use segmentation based on language rules:

    • Never - <segment> elements will not be segmented based on the language rules. This option is set as default.

    • When there is no target - <segment> elements that have no <target> elements will be segmented based on the language rules.

    • Only with the same number of segments in the source and target - <segment> elements containing <target> elements will be segmented based on the language rules, but only if the number of source and target segments is the same.

      Need more information? Read about segmentation rules in memoQ

  • Segment on line break - start a new segment each time there is a line break in the cell (Excel only).

  • Include manual line break as inline tag - insert an inline tag for each manual line break in the cell (insert in Excel with Alt+Enter).

  1. On the Base format tab, click the Excel radio button.

  2. Click the Excel options tab.

    You can set the following:

    Document import settings window showing filter and filter configuration drop-down lists, add cascading filer link and tabs with Excel format tab open showing Excel options to choose from: Do not import if background color is, only import if background color is, RGB field (with Add and Remove buttons next to it, and Pick in Excel link under them), Include hidden sheets, Include hidden rows, Include cells containing formulas or numbers checkboxes that are not checked, and If a target cell was not included because it contains a number of formula, overwrite it with the source content, and Maintain inline formatting in cells checked checkboxes. OK, Cancel, and Help buttons are in the bottom right corner.

You can import content that is formatted – or not formatted – with specific background colors.

First, click one of the two radio buttons:

  • Do not import if background color is: This is the default setting. While importing the document, memoQ skips rows where the source cell is formatted with the listed background colors.
  • Only import if background color is: Click this radio button if you want memoQ to import content that is formatted with the listed background colors.

To add colors to the box below the radio buttons:

  • Type a color code in the RGB text field, and click Add.

    The color code is a six-digit hexadecimal number: the first two digits are for blue, the third and fourth for green, and the last two are for red. For example, the code 00FF00 stands for pure green.

OR

  • Pick the color in Excel.

    1. Click the Pick in Excel link. memoQ opens the source document in Excel (Excel must be installed). Then the Add colors from file window appears:

      Add colors from file pop-up window with Add colors of selection and Finish buttons visible.

    2. In Excel, select a range that contains the background colors you want to use. Then click Add colors of selection. The colors appear on the list in the Document import settings window.

    3. To add more colors to the list: Repeat step 2.

    4. Click Finish. This closes the Add colors from file window, and Excel, too.

You can also:

  • Remove a color from the list: Select the color, and then click Remove.

  • Import text from hidden worksheets: Select the Include hidden sheets checkbox.

  • Import text from hidden rows: Select the Include hidden rows checkbox.

  • Import numbers or formulas in source and target cells: Select the Include cells containing formulas or numbers checkbox.

  • Keep the previous formula or number in the target cell: Clear the If a target cell was not included checkbox.

    This option is useful when the target cells already contain (different) numbers or formulas. By default, memoQ will overwrite these by copying the number or formula (or text) from the source cell.

  • Keep extra formatting inside cells: Select the Maintain inline formatting in cells checkbox. memoQ will export the translations (and the source text) with the same formatting as it was imported.

  1. First, on the Base format tab, click the Comma or tab-separated radio button.

  2. Then, click the Comma or tab-separated options tab.

    Document import settings window showing Filter and Filter configuration drop-down lists, Add cascading filer link and tabs with Coma or tab-separated options tab open showing CSV/TSV options - drop-downs for Codepage if not detected, Export codepage, and Write BOM to Unicode-encode files on export. Below there are radio buttons for Separator options (tab, semicolon, coma, and other) and Load preview from sample file link. Below there's preview filed showing data.

    Here you can:

    • Choose an encoding: Normally, memoQ detects the encoding (the code page) of the files you are importing. If the encoding can't be detected, memoQ uses Unicode (UTF-8). To use a different encoding, choose it from the Codepage if not detected drop-down box.

    • Export the translation in a different encoding: If the source encoding is not Unicode, and you're translating into a language with a different writing system (for example, from English into Japanese), choose a different encoding (code page) for the exported translation. To export the translation in a different encoding, choose it from the Export codepage drop-down box.

    • Add a BOM even if it wasn't there in the source: BOM (Byte Order Mark) appears at the beginning of a text file and signals the text encoding. If you don't want to keep the same BOM as in imported file and add another BOM to all files or delete it from all of them, select the correct option from the drop-down list.

    • Choose the character that separates the columns: Normally, memoQ uses the Tab character. You can choose any other character, though: Use radio buttons to choose other common separator characters like the Comma or the Semicolon. If your file uses a different separator, click the Other radio button, and type the character in the text box to the right.

    • Watch the preview: At the bottom of the window, you can see how memoQ would import your file with the current settings. Check if the characters appear correctly, and if the columns are separated properly. If you are importing several files, memoQ shows the preview from the first one. If it's too small, or not typical enough, choose a different file for preview: Click Load preview from sample file, and choose the file to use for the preview.

Are there double quotes or line breaks in the CSV file? The CSV specification has complicated requirements for handling these characters (see section 2., points 5., 6., and 7 of the linked document). If a CSV file doesn't meet these requirements, memoQ fixes the issues before exporting. This means the imported and the exported files will be different. Keep this in mind if you are using non-standard CSV files.

Always use this in bilingual projects: If your project has just one target language, or you are using memoQ translator pro, always use this setting.

  1. On the Base format tab, under Select base format, select the Simple bilingual configuration checkbox.

    The Columns settings you need appear at the bottom of the Base format tab:

    Columns part of the Base format tab showing Source, Comment, Translation, and Context fields you need to fill in. Below there are two checkboxes - First row contains column names and Import target text.

  2. Choose the column that has the source text: In the Source text box, type the letter of the column. Use the letters of the English alphabet. The first column is A, the second is B, the 27th is AA, and so on.

  3. Choose the column where the translation should go: In the Translation text box, type the letter of the column.

    If the Translation column already has translations: memoQ will import them as target text. If you don't want to import these, clear the Import target text checkbox.

  4. Choose the column that has the comments: In the Comments text box, type the letter of the column.

  5. Choose the column that has the context IDs: In the Context text box, type the letter of the column. memoQ uses the contents of this column for context matching and returning 101% matches from translation memories or LiveDocs corpora.

If the table has a header row, and you don't want to import it: Select the First row contains column names checkbox.

Multilingual projects only: Use this setting in memoQ translator pro only, and only when you're preparing a multilingual project for several translators.

MQXLIFF export for one target language at a time: The MQXLIFF format is bilingual - it has one target language. In the project, you must select a target language before you export a multilingual document in MQXLIFF. The MQXLIFF export of such files will have that target language.

  1. On the Base format tab, make sure that the Simple bilingual configuration checkbox is cleared.

  2. On the Columns tab, set the meaning of each selected column.

    Columns tab showing General area with Source language and If input contains target language text drop-downs, and First row contains column names checkbox. Below there's the Define the meaning of columns area with a box containing a list of columns and their meaning, Add next 26 columns and Clear column meanings list under the box. Below the box, there are also Meaning of selected column, For column, and Language drop-downs, as well as Do not import these field to add text that should be ignored. Below there's the Show in preview checkbox.

  3. Choose the status of segments that already have translations. Normally, it's set as edited, but you can choose another status in the If input contains target language text drop-down box.

    If the table has a header row, and you don't want to import it: Select the First row contains column names checkbox.

    To ignore target text that's already there: From the If input contains target language text drop-down, choose Do not import.

    Cells are never segmented. If a cell in the table contains several sentences, those will be imported in a single segment.

Set up columns

memoQ lists all the columns from your file, and sets their meaning to Not imported. In the beginning, only the first 26 columns are visible. To see more, click the Add next 26 columns link.

To set a column as source text:
  1. Select the column in the list.
  2. In the Meaning of selected column drop-down, choose Source text.

If a source cell in a row is empty or contains spaces only, memoQ won't import that cell, its translations, contexts and comments.

If your customer asked you to overwrite the source text:
  1. Select the column in the list.
  2. In the Meaning of selected column drop-down, choose In-place translation.

memoQ will export the translation to the same column, replacing the source text.

To set a column as translation in a target language:
  1. Select the column in the list.
  2. In the Meaning of selected column drop-down, choose Translation.
  3. In the For column drop-down, choose one of the columns that are already marked as Source text. If there is only one column that has source text, the list will contain that column only.
  4. In the Language drop-down, choose the target language of the column. You can choose any language that memoQ supports. But if the project doesn't have that language, the column won't be imported.

If you don't choose a language: The column will be set to In-place translation. memoQ imports this column as source text, and replaces this source text with the translation.

To set a column as context for matching:

  1. Select the column in the list.
  2. In the Meaning of selected column drop-down, choose Context.
  3. memoQ uses the cells in this column as context IDs for source-text cells that are in the same row.

You can choose several context columns: In this case, memoQ will add them together at the end of your document, and use the result as the context.

To set a column as comment:

  1. Select the column in the list.
  2. In the Meaning of selected column drop-down, choose Comment.
  3. From the For column drop-down, choose which column this comment belongs to. You can also choose All: memoQ imports this comment for every cell in the same row.

You can import a different comment for every source and target column.

If you don’t want to import all rows from a file, you can define conditions to exclude specific data.

To do that:

  1. In the list, select the column that contains the values you want to evaluate.

  2. In Meaning of selected column, choose Condition.

  3. In For column, select the column this condition applies to.

    You can also choose All to apply the condition to the entire row.

  4. In Do not import these, enter one or more values (words or expressions) that should prevent import.

    How it works:

    • Enter multiple values by separating them with commas, without spaces.

      For example: error,skipped,invalid

      If a space is added after a comma, memoQ takes only the first value into account.

    • If a value itself contains a comma, put it in quotes.

      For example: "New York, NY"

    • If a cell contains any of these values, it won't be imported.

    • If you selected All to apply the condition to the entire row, memoQ skips the entire row.

Your file may have length limits - contain a column with numbers. This means that the translations can't be longer than the number in the column.

To use length limits:

  1. In the list, select the column that has the length limits.
  2. In the Meaning of selected column drop-down, choose Length limit (characters).
  3. From the For column drop-down, choose which column this length limit belongs to. If you choose a target column, memoQ uses the limits for those cells. If you choose a source column, memoQ uses the limits for all translations that belong to that source column.

Normally, memoQ shows every meaningful field in the preview.

If you want to hide a column or two from the preview:

  1. Select the column in the list.

  2. Then clear the Show in preview checkbox.

    The result will look like this:

    Part of the Grid with translation. Below the Grid, there's a View pane showing the result of hiding a column, with one field marked in red.

  1. On the Base format tab, make sure that the Simple bilingual configuration checkbox is cleared.

  2. On the Columns tab, set the meaning of each selected column.

Columns tab showing General area with Source language and If input contains target language text drop-downs, and First row contains column names checkbox. Below there's the Define the meaning of columns area with a box containing a list of columns and their meaning, Add next 26 columns and Clear column meanings list under the box. Below the box, there are also Meaning of selected column, For column, and Language drop-downs, as well as Do not import these field to add text that should be ignored. Below there's the Show in preview checkbox, and OK, Cancel, and Help buttons.

To set a column as source text:
  1. Select the column in the list.
  2. In the Meaning of selected column drop-down, choose Source text.

If a source cell in a row is empty or contains spaces only, memoQ won't import that cell, its translations, contexts and comments.

To set a column as translation in a target language:
  1. Select the column in the list.
  2. In the Meaning of selected column drop-down, choose Translation.
  3. In the For column drop-down, choose one of the columns that are already marked as Source text. If there is only one column that has source text, the list will contain that column only.
  4. In the Language drop-down, choose the target language of the column. You can choose any language that memoQ supports. But if the project doesn't have that language, the column won't be imported.

If you don't choose a language: The column will be set to In-place translation. memoQ imports this column as source text, and replaces this source text with the translation.

To set a column as start or end time for the segment:

  1. Select the column in the list.
  2. In the Meaning of selected column drop-down, choose Starting timestamp of subtitle or Ending timestamp of subtitle, as needed.

To set a column as length limit:

  1. Select the column in the list.
  2. In the Meaning of selected column drop-down, choose Length limit (characters).

  3. From the For column drop-down, choose which column this length limit belongs to. If you choose a target column, memoQ uses the limits for those cells. If you choose the source column (it has "(all translations)" next to it), memoQ uses the limits for all translations.

Do this on the Subtitles tab.

When you are working on a video translation, it is important to know how much text you can put on the screen at once. Change the value of the Line length limit (characters) field as needed.

Density means how much text is on the screen over time. You can set limits for Words per minute and Characters per second - the two most common measurements.

No warnings in memoQ itself during translation: when you confirm a segment, memoQ does not warn you if you are over these limits. You see which lines are longer than the limit only when you export the file.
However, the memoQ video preview tool shows all three values in real time when playing the video:
Video preview tool character and word limits set by the user. They are visible at the bottom of the tool.

"Online" means a file on a server: At the moment, streaming video services such as YouTube are not supported.

When you finish

In the Document import options window: Click OK again to start importing the documents.

  • If this is a cascading filter, you can change the settings of another filter in the chain: Click the name of the filter at the top of the window.

It is possible to cascade filters after the bilingual and multilingual filters. For example, Multilingual Excel and delimited text can be the first filter in a filter chain, but remember that regex tagger should always be the last filter applied.