Importing Data into MySQL using a Comma Separated File (.CSV)

Category: Software and Systems Engineering
Author: Sean O'Donnell
Sun, Jun. 23rd, 2002 @ 4:45:12 (MDT)

When working with Data between multiple Database Platforms (MS-Access, MS-SQL, MySQL, PostgreSQL, Oracle, etc), using CSV files can be a practical alternative to using a direct ODBC connection.

This example file (example_file.csv) stores a few rows of data that are to be imported tp a MySQL database.

example_file.csv

"Sean O\'Donnell","Los Angeles","CA"
"Bob Smith","Sacramento","CA"
"Rick Gunther","Queens","NY"

For this example, we save the contents to the following file/path:

/home/example_file.csv

Our Next step is to construct our SQL Query in order to correctly process our data during the import procedure.

load data local infile

this portion of the query is to declare the path of the file to import. ('/home/example_file.csv' for this example)

into table

the into table declaration signifies the database and table to be used to import the data

fields terminated by

the fields terminated by declaration assigns the field-seperator flag, which tells mysql when the field ends. (in this case, we use a comma to sepearte fields)

enclosed by

the enclosed by declaration assigns the character in which we use to wrap string values in each field. (in this case, we use double-quotes)

lines terminated by

depending on the formatting of the text/csv file (unix or dos), we declare the 'nr' to seperate each line break as a row.

We then use (something like) the query below, to import the CSV file into our MySQL Database:

LOAD DATA LOCAL INFILE '/home/example_file.csv' 
INTO TABLE db_example_name.tbl_example_name 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '\'' 
LINES TERMINATED BY '\r\n' 
(example_field_name,example_field_city,example_field_state);

Copyleft (<) 1998-2019 www.seanodonnell.com