Importing large csv files into sql using the command prompt

I recently did a series of posts on how to convert a csv file into a sql file. You can see a summary here that will lead you to the other posts.

In that series I talked you through doing the import using HeidiSQL and then creating the sql file using phpMyAdmin, a process which is easy and straightforward to use. However, for those who like to use the MySQL command prompt, there is another way to go through this process, using the LOAD DATA INFILE command.

What you do is get to your MySQL command prompt and log in as root. Create the database and the tables using the same basic process I outlined before in my newbie tutorial about importing large sql dumps. In summary:

1. Create the database:

mysql> CREATE DATABASE database_name;

3. If this is done correctly with no typos, you will get a success statement to the effect:

Query OK, 1 row affected <0.00 sec>

4. Switch to the database you just created using the USE command.

mysql> USE directory_name;

You should see the text

Database Changed

Then create the table using the create table command:

CREATE TABLE “table_name”
(“column 1” “data_type_for_column_1”,
“column 2” “data_type_for_column_2”,
… )

Remember that you want your columns to correspond to the fields and data types in your csv file.

Once the database and table are created, you are ready to import your csv file. Remember that you can use this same process for any other kind of delimited file, whether it uses commas, semicolons, or other types of delimiters. To import the csv or other delimited file into MySQL, use the LOAD DATA INFILE command. This command has several options and things you need to consider. Here is the basic syntax:

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE ‘file_name’
[REPLACE | IGNORE]
INTO TABLE tbl_name
[CHARACTER SET charset_name]
[FIELDS
[TERMINATED BY ‘string’]
[[OPTIONALLY] ENCLOSED BY ‘char’]
[ESCAPED BY ‘char’]
]
[LINES
[STARTING BY ‘string’]
[TERMINATED BY ‘string’]
]
[IGNORE number LINES]
[(col_name_or_user_var,…)]
[SET col_name = expr,…]

This may look a little confusing, but it really isn’t. The stuff in square brackets [] is your options that are determined by your source and your data, and a lot of them may be optional and unnecessary. If you study the code carefully you will recognize a lot of the same options we encountered when using HeidiSQL to import the file, for example

[FIELDS
[TERMINATED BY ‘string’]
[[OPTIONALLY] ENCLOSED BY ‘char’]
[ESCAPED BY ‘char’]
]

for the example file that we used in the previous tutorials would be:

[FIELDS
[TERMINATED BY ‘,’]
[[OPTIONALLY] ENCLOSED BY ‘””‘]
[ESCAPED BY ‘/n’]
]

Does this look familiar? See if you can redo the process of importing the csv file that we went through in the tutorial but this time using the MySQL command line. This is a useful exercise because you may be faced with a situation where the command line is all you have. So have a go at it!

I recommend that you read up on the syntax of the LOAD DATA INFILE command at http://dev.mysql.com/doc/refman/5.1/en/load-data.html. This is for v.5.1, so if you are using a different version, please check to make sure the syntax is correct.

If you have any difficulties, questions, comments, or have noticed some errors in this tutorial please do leave a comment. I will greatly appreciate it!

Happy Coding!