Delimiters

Word of Caution on Delimiters

The field delimiter is the character which splits data or text into separate fields. A field is a column within your database. As an example, we may have the dataset below:

Column_1

Column_2

Column_3

25

Harvard Way

Boston, MA

When we export this data to a text file, it would appear as:

Line 1: Column_1 DELIMITER Column_2 DELIMITER Column_3

Line 2: 25 DELIMITER Harvard Way DELIMITER Boston, MA

Often, the comma is used as the field delimiter. In this example, using a comma would cause Line 2 to appear to have 4 fields instead of 3!

Line 1: Column_1,Column_2,Column_3

Line 2: 25,Harvard Way,Boston, MA

Column_1

Column_2

Column_3

 

25

Harvard Way

Boston

 MA

When exporting data, you should not use a field delimiter that may also occur within your data. Commas and tabs are common field delimiters but are likely to occur when storing text strings. Your options are to either change your field delimiter or have MariaDB enclose the data. We recommend you use the pipe character, |, as your field delimiter. Otherwise you may have MariaDB enclose the data by adding OPTIONALLY ENCLOSED BY … as described here [https://mariadb.com/kb/en/mariadb/select-into-outfile/].

When importing data, you must specify the field delimiter that had been used. You should verify with the dataset author as to the field delimiter, and whether any fields were enclosed by another character. Aside from the field delimiter, you will also have to specify the line delimiter. In most cases, the line delimiter is \n. You may also try using \r.

Using Excel with NonStandard Delimiters such as the Pipe Character

Many users will use Excel to view their data. If you decide to set the pipe character, |, as your field delimiter, then Excel will not automatically parse your data into separate columns. As an example, we can enter the above data into a text editor.

Line 1: Column_1|Column_2|Column_3

Line 2: 25|Harvard Way|Boston, MA        

delim1

Now we will open the file with Excel. As seen below, each row is within one column.

To have Excel parse the data into appropriate columns, select the entire column by click on the column header A

delim2

Within the DATA tab at the top, click on Text to Columns

delim3

In the new window, Convert Text to Columns Wizard, on Step 1 you should select Delimited. Next, on Step 2, you would select only Other and enter the pipe character, |, into the accompanying box.

delim4

Once you are complete the remaining step of the Convert Text to Columns Wizard, you should find your data separated into appropriate columns.

delim5