So far, you could have noticed that there are several types of files that Excel can work with. One of these files is a CSV file, whose name is an acronym for comma-separated values file.
Different regions have different default delimiter values. It can either be semicolons or commas. With these things in mind, we can have a lot of upload issues, or we can have a situation where all the files are put into the first column (column A) after file opening.
In the text below, we will show how can you avoid these problems.
Set the Default Delimiter for the Exporting CSV Files
For the first scenario, i.e., when we want to save our file as a CSV file, we have two options- we can convert our file to be delimited by semicolon, or comma-delimited.
To make it semicolon-delimited, we need to set the default decimal separator to be a comma. With this move, we will get Excel to use semicolons for separating our data.
Our data will show sales figures for a couple of years, as follows:
To check the separator options in the Excel as a whole, we need to go to the File tab >> Options >> Advanced >> Editing Options:
Once there, we will go and choose a Decimal separator to be a comma, and a Thousand separator to be a dot (as in the picture above).
Then we will click OK, go to File >> Save As, and choose CSV (Comma delimited) as an option in Save as Type:
To check the delimiter, we will go to our saved file, select it, right-click on it and choose Open with >> Notepad:
When we open the file, we will see that our data is separated by semicolons:
We will repeat the same process, but only this time, we will define the separator for decimal to be a dot, and for thousands to be a comma:
We will save the file as the CSV file again, and open it with Notepad:
You will notice that the data is now separated by the comma.
Remember that you can manipulate this by going to the File tab >> Options >> Advanced >> Editing Options.
Set the Default Delimiter for the Importing CSV Files
Now we will talk about the way in which you can define the separator when opening the CSV files with Excel. Suppose that we have the CSV file with the following data:
You will notice a CSV extension in the name of the file. We can now basically close the file, open the new Excel file, and then go to Data >> Get & Transform Data >> From Text/CSV:
Once there, we will find the location of our CSV file and click Import:
In a window that appears, Excel will ask us what kind of a delimiter we want for our file, and we can choose among colon, comma, equal sign, semicolon, space, tab, or we can define the custom separator:
Once we choose the Delimiter and Data Type Detection (based on the entire dataset is the preferred option), we click on Loan, and the file will be imported. However, this does not resolve our original issue- how to set the default delimiter when importing a CSV file?
To do this, we need to open our original CSV file with Notepad:
When we do this, we will insert the following in front of our text, depending on the delimiter that we want:
- If we want our separator to be a comma: sep=,
- If we want our separator to be a semicolon: sep=,
- If we want our separator to be a pipe: sep=|
We open our file, and then define the separator to be a colon:
We will save the file, and now our data will be separated by a colon.
In the same way, we could only change the first line in our file, according to the bullets, and we would organize the data in a way we want.