Friday, March 1, 2013

Import data file to MySql Database


Introduction:

We are trying to add data from a text file (snp file, dep file, etc) to a MySql table but we want to be sure if the import process was successfully before save or discard this data.

Steps:

#From the mysql shell, we start the transaction:

mysql> BEGIN;

#We use "load data infile" to load the data, we can define in which column is going each file column.
mysql> LOAD DATA INFILE '[PATH_OF_THE_FILE]'  INTO TABLE [TABLE] (column2,column3,column5,column8);
Query OK, 69144 rows affected, 1 warning (0.71 sec)
Records: 69144  Deleted: 0  Skipped: 0  Warnings: 1
#After that, we can use show warnings to decide if we want this data or not
mysql> show warnings;
+---------+------+---------------------------------------------+
| Level   | Code | Message                                     |
+---------+------+---------------------------------------------+
| Warning | 1364 | Field 'column1' doesn't have a default value |
+---------+------+---------------------------------------------+
1 row in set (0.00 sec)

#Save data or discard
mysql> commit; #Save
mysql> rollback; #Discard

REFERENCES:

http://dev.mysql.com/doc/refman/5.0/es/load-data.html
http://dev.mysql.com/doc/refman/5.0/en/commit.html