What is CSV delimiter and how to change it

CSV (Comma-Separated Values) files are broadly used for data exchange because they provide an easy and structured format for storing information. However, the delimiter used in CSV files may vary depending on regional settings or software requirements.

This article will explore what a CSV file is, the role of delimiters, and how to change them when using the Excel sheet.

What is CSV

A file is a plain-text format to store data in tabular form, such as spreadsheets or databases. Each line in a CSV file represents a row, and values within each row are segregated by a specific character, called a delimiter (commonly a comma).

Comma-separated values files are widely supported across spreadsheet software, databases, and data-processing tools because of their clarity and compatibility.

For instance, a simple CSV file containing employee data look like below:

Key features of a common-separated values file are:

• Data is stored in a structured manner.
• Delimiters (commas, semicolons, tabs) are used to separate values.
• A CSV file can be opened in text editors like Notepad or spreadsheet software.
• These files do not support rich formatting (colors, formulas, or images).

CSV files are useful when data needs to be transferred between different systems, as they can be opened in Excel, Google Sheets, ONLYOFFICE Spreadsheet Editor, and many other applications.

What is delimiter and what delimiters are used in CSV?

A delimiter is the character used to separate the values in a CSV file. The most common delimiter is a comma (,), which is why it is called a comma-separated value. However, different delimiters might be used depending on regional settings and software compatibility, such as:

• Comma (,) – Default delimiter in most English-based systems and software.
• Semicolon (;) – Used mostly in European countries where a comma is used as a decimal separator.
• Tab (\t) – Used in TSV (tab-separated values) files. This type of delimiter is not supported in some spreadsheet software tools.
• Pipe (|) – Used at times for complex data sets to avoid conflicts with commas.

Understanding which delimiter to use is crucial for ensuring that the data is studied properly when importing or opening the CSV file in different applications.

How to change CSV delimiter when saving an Excel file?

Let’s find out how to change the CSV delimiter in an Excel spreadsheet based on the example of ONLYOFFICE Spreadsheet Editor.

Method 1: Manually changing the delimiter

Step 1. Open your spreadsheet.

Step 2. Select Save Copy as CSV.

Step 3. The CSV options allow users to select their preferred delimiter type based on their needs, choosing from a variety of available options. Choose the desired CSV option and click OK. Select a folder and save your file. This will be saved as a comma-separated values file.

Step 4. Open the CSV file in ONLYOFFICE Spreadsheet Editor. Upon opening, a box will appear to choose one of the CSV options. Click OK.

Method 2: Customization of the delimiter during data import

Step 1. Open your spreadsheet editor. Click Data > From Local TXT/CSV option. Choose the desired file.

Step 2. Customize the settings: select the delimiter type and choose encoding (UTF-8* is the default option).

*UTF-8 is the most universal format that is compatible with most modern applications, operating systems and databases. It supports all characters, including Latin, Cyrillic, Arabic, Chinese, special characters, emoji, etc., and prevents encoding problems.

Step 3. Select the range where you want to input the data. Click OK.

How to open CSV with correct delimiter?

If your CSV file is not displaying correctly in the spreadsheet editor, you may need to manually specify the delimiter when opening the file.

Launch your spreadsheet editor. Click File > Open, and select your CSV file. A CSV Import Window will appear. In the Delimiter section, select the correct delimiter used in the file.

Click OK, and your data should now be correctly formatted. If your data is still incorrect, check the encoding settings (UTF-8 recommended for proper character support).

How to automatically detect the delimiter when opening a file

With the release of ONLYOFFICE Docs 8.3, a key improvement in the spreadsheet editor is the introduction of the AutoDetect of delimiters feature for CSV files.

When opening a comma-separated values file, this function automatically identifies the correct delimiter based on the file’s content and structure, eliminating the need for manual selection, and ensuring that data is properly formatted and displayed without being disorganized.

This feature is useful because it saves time by reducing manual adjustments, minimizes errors in CSV formatting, and enhances user experience by providing a seamless workflow when importing CSV data.

Ensuring smooth CSV formatting with ONLYOFFICE Spreadsheet Editor

CSV files are a simple yet powerful way to store and exchange quantitative data. The delimiter used in a CSV file plays a significant role in ensuring data is rightly separated and analyzed. Although the default delimiter is usually a comma, regional settings, and software preferences may require certain changes.

By pursuing the discussed methods in this article, users can easily change the delimiter when saving a comma-separated values file, amend system settings for default delimiters, and correctly open CSV files in different applications.

With the latest enhancements in the ONLYOFFICE Docs 8.3, handling CSV files in Excel sheets has become even more flexible and efficient, making it easier for users to work with structured data seamlessly.

Effortlessly manage your data with ONLYOFFICE Docs! Watch this quick guide to streamline your workflow. Try it now!

Get ONLYOFFICE Spreadsheet Editor

If you are looking for an advanced, yet user-friendly spreadsheet editor to streamline your data management, ONLYOFFICE Docs offers all the features you need. Sign up for a DocSpace account or download the desktop app to discover advanced tools designed to enhance your workflow.

START ONLINE           GET DESKTOP APP