How to load CSV file to Mysql database

There some tricks to know how to load csv file to mysql:

1. mysql -u <user> -p --local-infile
   (the --local-infile is necessary, otherwise you will get error during dataloading: ERROR 1148 (42000): The used command is not allowed with this MySQL version)
2. use <database>
3. create table script (create_tables.sql) in text editor
   drop table kontakt;
   create table kontakt
    (klkood        bigint(20)
    ,kontakti_liik varchar(32)
    ,vaartus       varchar(100)
    ,kasutusluba   varchar(100)
    ,keeluaeg      varchar(100)
   );
   create index kontakt_klkood on kontakt(klkood);
4. run the script from mysql prompt
   .\ create_tables.sql
5. create load data script (load_data.sql) in text editor
   LOAD DATA LOCAL INFILE '/vagrant/uptime/EA_kliendi_kontaktinfo_3012.csv' INTO TABLE kontakt
   FIELDS TERMINATED BY ';'
   LINES STARTING BY '"' TERMINATED BY '"\r\n'
   IGNORE 1 LINES;
6. run the script from mysql prompt
   .\ load_data.sql
7. If there are warnings then execute
   show warnings

For me most difficult part was to get load_data.sql to work and specially --local-infile parameter.

How to install mysql and mysql workbench in Ubuntu 12.04

Its really simple task to install mysql server and mysql workbench in Ubuntu 12.04. You shuold run from commandline



sudo apt-get install mysql-server
sudo apt-get install mysql-workbench

First row installs mysql server, asks root password and starts mysql service. To restart server run

sudo service mysql restart

Second row installs mysql-workbench and you are ready to access mysql server in localhost.