Skip to Main Content
HBS Home
  • About
  • Academic Programs
  • Alumni
  • Faculty & Research
  • Baker Library
  • Giving
  • Harvard Business Review
  • Initiatives
  • News
  • Recruit
  • Map / Directions
Research Computing Services
  • Online Requests
  • FAQ
  • Blog
  • Contact Us
  • About Us
  • Faculty Projects
  • Training
  • Compute Cluster & Data Storage
  • Data Practices
  • Help
  • …→
  • Harvard Business School→
  • Research Computing Services→
  • Data Practices
    • Data Practices
    • Best Practices
    • Data Tips & Toolkits
    • Database Best Practices
    • Transferring Data
    →
  • Database Best Practices
    • Database Best Practices
    • Delimiters
    • Exporting
    • Import & Export of Text Files
    • Importing
    →
  • Delimiters→

Database Best Practices

Database Best Practices

  • Delimiters
  • Exporting
  • Import & Export of Text Files
  • Importing

Delimiters

Delimiters

  • Best Practices
  • Data Tips & Toolkits
  • Database Best Practices
    • Delimiters
    • Exporting
    • Import & Export of Text Files
    • Importing
  • Transferring Data
2ms

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

ǁ
Campus Map
Research Computing Services (RCS) 
Harvard Business School
Baker Library, B90, 25 Harvard Way
Boston, MA 02163
Phone: 617.495.6100
Email: research@hbs.edu
→Map & Directions
→More Contact Information
→Terms Of Service
  • Make a Gift
  • Site Map
  • Jobs
  • Harvard University
  • Trademarks
  • Policies
  • Accessibility
  • Digital Accessibility
Copyright © President & Fellows of Harvard College