Difference between revisions of "Data Import Example for MySQL"

Latest revision as of 09:12, 1 July 2017

Using mysqlimport

I have an Open Office Calc spreadsheet with one column character field of movie titles. I saved it as a cvs and ended up with a text file with leading tabs and each movie title in quotation.

"Young Guns"

Next removed the quotation characters with vi using regular expression:

:1,$s/"//g

This can also be accomplished from shell with gawk.

Then in mysql I had to create a database, table, and the table has to have a field matching the name of the text file (or make the text file match the field name). I wanted to create a table with an auto-incrementing key field and a varchar (variable length character) field for the movie titles I was about to import.

mysql -p
<my password>

create database movieinv;
grant select,insert,update,delete,create,drop,alter,index on movieinv.* to username@localhost identified by 'password';
use movieinv;
create table allmovies(mcnt integer not null auto_increment, primary key(mcnt), movietitle varchar(99));
quit

Now that I have a database named 'movieinv' with a table called 'allmovies' and within that a character field 'movietitle' I am ready to drop back to the shell and import my data. I make sure that my data file is called 'movietitle.txt' and type the following to perform the import:

mysqlimport -p movieinv movietitle.txt

There are many warnings but I make sure that nothing was skipped and there were no errors.

For better or for worse, this _is_ one way to import data from a text file to mysql.

additional notes:

• don't strip the leading tabs out of the text file. If the leading tabs are

not present, the text will not import and the database table field will be populated by NULL value.

• however, if for some other reason you ever wanted to strip tabs out of a text

file with vi, the regular expression is: 1,$s/<cntrl-v + tab>//

• oops, you didn't have tabs, you can clear the database table without dropping

the table with the following sql:

delete from allmovies where movietitle IS NULL;

• auto-increment will not reset if you clear the table. one way to reset it is

to drop the table:

drop table allmovies;

Using LOAD DATA INFILE

mysqlimport is simply a front-end for LOAD DATA INFILE

Using LOAD DATA INFILE you have more control, and more options.

• mysqlimport is done from the console prompt
• LOAD DATA INFILE is done from mysql admin

For the following examples the flat text file was placed in the mysql

directory: /var/lib/mysql/fedex

• make sure permissions are set so the file is readable my mysql

To import a pipe seperated data file:

LOAD DATA INFILE 'asciiphonenumbers.psv' INTO TABLE asciiphonenumbers FIELDS TERMINATED BY '|' ENCLOSED BY  ESCAPED BY '\\'; 

Or tab seperated:

LOAD DATA INFILE 'asciitableraw.tsv' INTO TABLE asciitableraw FIELDS TERMINATED BY '\t' ENCLOSED BY  ESCAPED BY '\\';

You will get some statistics when the import completes:

Query OK, 23776 rows affected, 254 warnings (0.30 sec)
Records: 23776  Deleted: 0  Skipped: 0  Warnings: 254

To view the warnings simply execute the following command:

show warnings;

It will not show all warnings, just as many as will fit in the buffer.