Custom Delimiters and Encoding in LibreOffice Calc CSV

Importing CSV files with non-standard separators or distinct character sets into LibreOffice Calc often results in misaligned columns or corrupted characters. This guide demonstrates how to use the built-in Text Import dialog to adjust character encoding, define standard or custom delimiters (such as pipes, semicolons, or tabs), and set text qualifiers so your data imports cleanly and accurately.

Step 1: Open the CSV File in LibreOffice Calc

  1. Launch LibreOffice Calc.
  2. Go to File > Open (or press Ctrl + O / Cmd + O).
  3. Select your CSV or text file and click Open.
  4. The Text Import dialog window will automatically appear on your screen.

Step 2: Configure the Character Encoding

Setting the correct character encoding prevents special symbols, accented letters, and non-Latin alphabets from displaying as unreadable characters (mojibake).

  1. In the Text Import dialog, locate the Import section at the top.
  2. Click the Character Set dropdown menu.
  3. Select the encoding that matches your source file:
    • Unicode (UTF-8): The standard for modern web and system exports.
    • Western Europe (Windows-1252 / WinLatin 1) or ISO-8859-1: Common for legacy Windows exports.
    • Specific regional encodings (e.g., Shift-JIS, GBK, Windows-1256) if handling localized data.
  4. Check the preview pane at the bottom to verify that special characters are displaying properly.

Step 3: Set Custom Field Delimiters

By default, Calc detects common separators like commas. If your file uses a different delimiter:

  1. Locate the Separator Options section.
  2. Select the Separated by radio button.
  3. Choose the appropriate separator:
    • Check preset options such as Tab, Comma, or Semicolon.
    • For non-standard delimiters (e.g., pipe |, tilde ~, or caret ^), check the Other box and type the specific character directly into the adjacent input field.
  4. If your file contains consecutive delimiters that represent a single separator, check Merge delimiters.

Step 4: Configure Text Delimiters (Qualifiers)

Text delimiters ensure that fields containing separator characters (e.g., "New York, NY") are not split into multiple columns.

  1. Under Separator Options, find the String Delimiter (or Text delimiter) dropdown.
  2. Select the character used to enclose text strings—typically the double quote (") or single quote (').
  3. If necessary, check Format quoted field as text to prevent automatic numeric or date conversion for quoted values.

Step 5: Preview and Complete the Import

  1. Inspect the Fields preview box at the bottom of the dialog. The data should appear organized into clean, distinct columns.
  2. (Optional) Click on any column header in the preview to change its data type from Standard to Text, Date, or Hide (to skip importing that column).
  3. Click OK to import the data into the spreadsheet.