Excel CSV delimiters and MySQL field termination
Posted: Tue Jan 10, 2017 5:16 pm
Usually, CSV files use comma(,) as the delimiter for columns/cells. Sometimes, columns/cells may contain the delimiters in them. Users can use the so-called "Text qualifier" to avoid the column being split into multiple columns/cells. There are 2 possible text qualifiers: double-quote (") and single-quote ('). When the text qualifier is present, the column/cell contains the delimiter(s) would be able to keep the whole data in it.
Example 1 (Without text qualifier):
abc,123,%&%$ will be converted to "abc", "123" , "%&%$" in 3 columns/cells
Example 2 (With text qualifier):
abc,123,%&%$ will be converted to "abc,123,%&%$" in one column/cell
In MySQL, the following code can be used to import CSV type of files into tables. The (FIELDS TERMINATED BY ',') is equivalent to Excel's delimiter while (ENCLOSED BY '"') is equivalent to Excel's text qualifier.
Example 1 (Without text qualifier):
abc,123,%&%$ will be converted to "abc", "123" , "%&%$" in 3 columns/cells
Example 2 (With text qualifier):
abc,123,%&%$ will be converted to "abc,123,%&%$" in one column/cell
In MySQL, the following code can be used to import CSV type of files into tables. The (FIELDS TERMINATED BY ',') is equivalent to Excel's delimiter while (ENCLOSED BY '"') is equivalent to Excel's text qualifier.
Code: Select all
LOAD DATA INFILE '/<PATH>/<new csv file>'
INTO TABLE Automobile.2016_Volvo_XC60_VOC
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';