Save Excel as CSV with custom delimiter
Let’s hope that you as a Data Engineer never need this article, because that means the data you are working with is by default read-friendly. However, life of a Data Engineer is not always simple and straightforward, and you will most likely be confronted data structures that can give you a headache. One of these can be working with delimiters in Excel and or CSV file, combined with other special characters in your file. In this article I will discuss the scenario where you need to convert an Excel into a CSV with a custom delimiter, for example pipe.
Case example
I was confronted with a file that was extracted from a source system and saved as Excel file. My task was to load the data into an Azure SQLDB, using the automated ingestion workflow that we have setup. In this automated workflow, a Databricks notebook reads a csv file from ADLS Gen 2 using the file delimiter that is set in the metadata description of the source file.
When opening the Excel file, the default delimiter of the file is a comma. Using the Text to Columns feature of Excel to display the data I noticed the data values were not correctly assigned to their columns. A deeper investigation showed that some columns contained a comma separated list of values, creating additional columns. How inconvenient!
The first thought was to switch to semicolon as delimiter. However, also semicolon was already represented as a character in some values in the file.
To be able to correctly read the file in our automated ingestion workflow, we need a custom delimiter, other than comma or semicolon.
Save an Excel File with a custom delimiter
You cannot simply select a delimiter in Excel when saving the file. You have to configure the default “List separator” in your system’s settings after which Excel will use it when saving the file. Let’s go through it step-by-step how to save your Excel file with a custom set delimiter.
- Go to Control Panel > Region Settings > Change date, time or number formats
2. In tab Formats click on Additional Settings
3. In tab Numbers, find the option for List separators, and input the delimiter of your choice. NB this is a dropdown list, however if the delimiter of your choice is not in the list you can simply type it in and hit Apply.
That’s it! Now go back to your Excel, hit Save As, and save the file as CSV (Comma delimited) (*.csv).
Happy data engineering!
Azure Tutorials frequently publishes tutorials, best practices, insights or updates about Azure Services, to contribute to the Azure Community. Azure Tutorials is driven by two enthusiastic Azure Cloud Engineers, combining over 15 years of IT experience in several domains. Stay tuned for blog updates and follow us if you are interested!
https://www.linkedin.com/company/azure-tutorials
