Recently I had a unique requirement during the Data Import process. The upload file was supposed to be in .CSV format with Semicolon as a delimiter and encoded with Unicode.
By default the .CSV uses comma(,) as a delimiter. While saving the Excel file we do have space delimiter format and tab delimiter format as an file option too but no semicolon delimiter format option.
1.> Change File format to .CSV (semicolon delimited)
To achieve the desired result we need to temporary change the delimiter setting in the Excel Options.
Move to File -> Options -> Advanced -> Editing Section
Uncheck the “Use system separators” setting and put a comma in the “Decimal Separator” field.
Now save the file in the .CSV format and it will be saved in the semicolon delimited format !!!
2.> Encode the .CSV file in Unicode format
The simplest approach to achieve the Unicode Encoding is:
- Open the result file in Notepad
- Click Save as
- Give the File Name as xyz.csv
- Change the Save as Type to All Files
- Choose the Encoding as Unicode
- Press Save.
Finally you have the desired file ready to be imported.
Update :- I’ve written another blog post on Saving Excel Data file to CSV format with any Special Character Delimiter.