How to Open CSV with Correct Delimiter
Reading Time: 2 minutes
Table Of Contents
Excel is the ultimate program for number-crunching. It will give you a hand with everything from tough calculations, spreadsheets, to even leave tracking. However, even though Excel is more or less the same tool for users across all regions, there are some notable differences that make it stand out for some. Just take CSV delimiter in files, for example.
In some regions like Europe, or based on your Excel regional settings, your default delimiter or separator may be using either a semicolon (;) or comma (,) to separate items in a CSV file. However, this may cause issues when you’re sending those files to users in different locations worldwide that may have a different CSV delimiter setting. It can either cause file upload issues or cluster all the field values into column A, which will severely mess up all the data in your file.
Having said that, here is a guide to opening CSV files with the correct delimiter or separator.
Adjusting the CSV delimiter in Windows
Follow the steps below to adjust your CSV settings in Windows.
- Go to the Windows Start Menu and click on Control Panel
- Select the Regional and Language Options button
- Click on the Regional Options tab
- Choose Customize/Additional settings (if you’re using Windows 10)
- Type in a comma (,) into the ‘List separator’ box
- Press ‘OK’ twice to confirm your changes
Please note that this method only works if the Decimal separator is also not a comma. You can also open comma-delimited files using a different method down below if you do not want to change this setting.
Adjusting the CSV delimiter in MacOS
If you’re a Mac user, here’s how you can adjust the CSV settings on your computer.
- Go to System Preferences
- Select the Language & Region option and select the Advanced tab
- Change the ‘Decimal Separator’ as indicated below:
- if the Decimal Separator is a period (.) then the CSV separator will be a comma.
- if the Decimal Separator is a comma (,) then the CSV separator will be a semicolon.
An alternative way to change the CSV delimiter from Excel
If you don’t want to adjust these settings within your computer, here’s an alternate way to do it from within Excel.
1. Open a new empty Excel spreadsheet by selecting a Blank Workbook then clicking on Select.
2. Go on to the Data tab, and click on Get Data.
3. Then select the From Text option.
4. Select the CSV file you’d like to open from your desktop and hit Get Data. In our case, we’re choosing this file with grades.
5. Once the Text Import Wizard pop-up appears, click on the Delimited option and click on Next to proceed.
6. Choose the correct delimiter that displays the metadata correctly in the Preview pane. This can be Tab, Comma, Space, Semicolon, Other, or a combination of all these factors. Then, click on Next.
7. Lastly, click on Finish and you’re done!
It’s important to remember that once you make the necessary changes, save the file as .TXT (tab-delimited). This way, the file will be separated by tabs instead of a variable character.
Easy PTO tracking, right at your fingertips.
Automate leave policies, improve visibility and plan better
No credit card required. Instant set-up.
Snigdha Gupta
An avid writer and aspiring marketer, Snigdha is a student at Concordia University’s John Molson School of Business.
