Read more about Text Delimiters & Qualifiers.
When busy with a data migration where I needed to export Microsoft Excel workbook data to CSV for import into a MySQL database, I ran into some issues with export options or rather the lack thereof. There are no options from within Excel to change delimiters and so forth. The solution is cumbersome and involves changing Windows operating system settings which apply to more than just Excel, and would certainly have to be changed back again after the exercise. Additionally, I see no obvious way of defining any other settings I might want to change, for instance, I may want to escape column values with quotations.
In this post:
Required knowledge:
Regional Settings
Navigate like so in Windows 10: Control Panel » Clock, Language and Region » Change date, time or number formats » Additional settings
Find and Replace
You can of course resort to quick and dirty tactics such as find-and-replace in a text editor to find and replace delimiters, however, changing your data to suit your solution is not a good idea.
References:
- Howtogeek.com. (2023) Export or Save Excel Files with Pipe (or Other) Delimiters Instead of Commas. Available at: https://www.howtogeek.com/21456/export-or-save-excel-files-with-pipe-or-other-delimiters-instead-of-commas/ (Accessed: 7 June 2023).