Resolve Excel CSV Delimiter and Quotation Issues

Introduction

This document describes how to address CSV formatting issues caused by European settings while working on the Seedfile Template for CX Cloud.

Prerequisites

Requirements

Cisco recommends that you have knowledge of these topics:

• Understanding of locale-based formatting differences in office productivity suites (such as Microsoft Excel)
• Familiarity with CSV (Comma-Separated Values) file structures and delimiters
• Basic text editing skills using Notepad or equivalent text editors
• Ability to navigate Excel menus and use data import features like Text to Columns

Components Used

The information in this document is based on these software and hardware versions:

• Microsoft Excel with European regional settings (locale uses semicolon as default CSV delimiter and comma as decimal symbol)
• Standard text editor (such as Notepad on Windows OS)

The information in this document was created from the devices in a specific lab environment. All of the devices used in this document started with a cleared (default) configuration. If your network is live, ensure that you understand the potential impact of any command.

Background Information

In some countries, Microsoft Excel and other office suite applications use a semicolon (;) as the default separator in CSV files, rather than the comma (,), which is the standard in North America and many other regions. This difference is due to locale-specific settings, where the comma is used as the decimal separator and the semicolon is used to separate fields within a CSV.

When a CSV file is edited and saved in Excel with European locale settings, the resulting file contains semicolons instead of commas as field delimiters and also include unnecessary quotation marks ("), which can cause compatibility issues when importing the file into systems expecting comma-delimited CSV files. These issues are commonly encountered when working with Seedfile Templates or importing/exporting data between systems with different regional configurations.

Problem

Using office suites configured with European formats cause issues when editing and saving CSV files:

• CSV files saved from Excel contain semicolons (;) as field delimiters instead of commas (,).
• Additional quotation marks (") are present around fields or values.
• CSV files exported from Excel instances with this configuration are not compatible with systems expecting comma-delimited files, resulting in improper data imports or processing errors.
• When attempting to use these CSV files as seedfiles for CX Cloud, the incorrect delimiter can prevent successful data ingestion or automation.

These issues typically arise after editing or exporting a CSV file using the default settings in versions of Excel configured with the European formatting, leading to seedfile incompatibility and a need for manual correction or reconfiguration.

Solution

There is a method to resolve the CSV delimiter and quotation mark issues caused by European locale settings in Excel. Steps are detailed here.

Tip: You can change the separator when saving Excel file as CSV. Under File > Options > Advanced > Editing options > Use system separators. Keep in mind this change impacts the format of all CSV files.

Manually Correct the CSV File Using Notepad and Excel

You can manually edit the CSV file to ensure compatibility.

Note: You can use any plain text editor as long as you replace the needed characters from the file. This example uses Notepad.

1: Open the CSV file in Notepad.

Right-click your CSV file and select Open with > Notepad.

2: Open a new Excel workbook.

Launch Excel and create a new, blank workbook.

3: Copy the CSV file content to Excel.

3.1. Select all the content in Notepad (Ctrl+A, then Ctrl+C).

3.2. Paste the content into cell A1 of your new Excel workbook.

4: Use Text to Columns to separate data by comma.

4.1. Click cell A1 to select it.

4.2. Navigate to the Data tab.

4.3. Select Text to Columns.

4.4. Choose Delimited, then click Next.

4.5. Select Comma as the delimiter.

4.6. Complete the wizard to parse your CSV data into columns.

5: Save as a .csv file.

5.1. Click File > Save As.

5.2. Choose CSV (Comma delimited) (*.csv) as the file type.

5.3. Save the file with a new name if you wish to preserve the original.

6: Open the saved CSV in Notepad again.

6.1. Right-click the newly saved CSV file.

6.2. Open it with Notepad.

7: Remove all quotation marks and adjust delimiters.

7.1. In Notepad, use Find and Replace (Ctrl+H).

7.2. Find: ".

7.3. Replace with: [leave blank]

7.4. Click Replace All to remove all quotation marks.

7.5. Find: ;

7.6. Replace with: ,

7.7. Click Replace All to change all semicolons to commas.

7.8. Replace the first comma (,) in the file with a semicolon (;) using Find Next and manual replacement.

8: Save the corrected file.

8.1. Save the file in Notepad to overwrite the incorrect CSV.

8.2. Once done, the corrected file is suitable to be used as a seedfile on CX Cloud.

Related Information

• Cisco Technical Support & Downloads
• Open a Support Case in CX Cloud