The Genoc Library

This is a small code snippet on how to import a SQL file into MySQL on Windows.

- First change to the MySQL Directory
- Now change into the bin directory
- Now run this command:
mysql -u USERNAME -pPASSWORD -h localhost DATABASENAME < c:\\DIR1\\DIR2\\DATA.SQL

I capitalized all the potential variables above (so USERNAME is your username to mysql). Pay attention to the letter “p” before the password. So if your username is testuser, and your password is 12345, and your database is called testdb, then the command will be:

mysql -u testuser -p12345 -h localhost testdb < c:\\DIR1\\DIR2\\DATA.SQL

Sometimes you want to search for a specific word in a MySQL VARCHAR or TEXT field. For example, let’s say you have a field called description in a table called mytable, and you want to search for the word car in the description field. Now if you use this query: SELECT description FROM mytable WHERE description LIKE ‘%car%’; you will get not only the list of descriptions with the word car, but also those with the words care, careless, card, caring, carpenter, etc… If you want only the word car, then you can use the following advanced query (it has regular expressions): SELECT description FROM mytable WHERE description REGEXP ‘[[:<:]](car)[[:>:]]’
The above query will return results with the word car only. Enjoy!

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!