The Genoc Library

I know it’s been so long I have written on this website, but it’s just that I didn’t have any time. Anyway, here’s a little snippet I would like to share with you, how to load a tab delimited CSV into MySQL on Windows. Here’s a step by step guide:

Running MySQL in the Console

- Click on Start, and then type cmd into the textbox where it says “Start Search”. This will open up the black console.
- In the console, go to to the directory where MySQL is installed, for me this is how I go there: cd c:\Software\MySQL (for most people MySQL is installed under the Program Files directory)
- Now change to the bin directory under MySQL: cd bin
- Now type the following command to run MySQL: mysql -u root -p
- You will be prompted for the root password, enter it. (Note, you can also connect using another user by just changing the username in the above step).
- Now you are connected to MySQL.

Selecting the Database

- While you are connected to MySQL, type: use databasename; (where databasename is the name of the database you wish to use).

Loading the Tab Delimited CSV

- Now type the following command:

LOAD DATA LOCAL INFILE ‘c:\\testdata.txt’ INTO TABLE testtable FIELDS TERMINATED BY ‘\t’ LINES TERMINATED BY ‘\n’ (`field1`, `field2`, `field3`);

The table testtable has to exist in the database testdatabase, and has to have the fields “field1″, “field2″, “field3″. Note that in Windows, you add a double backward slash “\\” in your path (so for example, if the csv file is located under c:\myfiles\test.txt, your path will be c:\\myfiles\\test.txt .

- The data now is loaded into the table, where each row in the file becomes a row in the table, and each column in that row fills in a field (for example, the first column of the first row goes to field1, the second column of the first row goes to field2, etc…). When the data is loaded, you will see a similar message:

 Query OK, 69 rows affected, 267 warnings (0.03 sec)
Records: 69  Deleted: 0  Skipped: 0  Warnings: 198

- To show the warnings, just type: show warnings;

That’s it!

If you want to load a comma separated (delimited) file (a CSV), just change the above command that loads the data to:

LOAD DATA LOCAL INFILE ‘c:\\testdata.txt’ INTO TABLE testtable FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘\n’ (`field1`, `field2`, `field3`);

Hope this helps someone!

You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.