#67 - Importing and exporting csv data to/from MySQL

How to upload and download a csv file into/from MySQL. This is particularly useful to manipulate data for data analysis. 

Start MySQL from command prompt:

> cd "C:\Program Files\MySQL\MySQL Server 5.7\bin"

> mysql -u root –p

Loading a Csv file:

mysql> create database mycsv;

mysql> show databases

mysql> use mycsv

mysql> create table myTable (id int not null primary key auto_increment,name varchar(20), age decimal);

mysql> describe mytable;

+-------+-------------------+--------+------+----------+----------------+

| Field | Type                | Null   | Key | Default | Extra          |

+-------+--------------------+--------+-----+---------+----------------+

| id       | int(11)             | NO    | PRI | NULL   | auto_increment |

| name | varchar(20)    | YES  |        | NULL   |                    |

| age    | decimal(10,0) | YES  |        | NULL   |                    |

+-------+--------------------+--------+-----+----------+----------------+

Create a simple CSV file for testing:

> notepad mycsv.csv

> type mycsv.csv

Anna, 25

Boris, 32

Carol, 27

• Load the data from csv to mysql:

The key words are "load data infile ... into table ..."

mysql> load data infile 'C:/Users/myUser/mycsv.csv' into table mytable fields terminated by ',' lines terminated by '\n' (name, age);

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

Adding local we can overcome this issue. Also be careful with the direction of the slashes in the path string:

mysql> load data local infile 'C:/Users/myUser/mycsv.csv' into table mytable fields terminated by ',' lines terminated by '\n' (name, age);

mysql> select * from mytable;

+----+-------+------+

| id | name  | age  |

+----+-------+------+

|  1 | Anna  |   25 |

|  2 | Boris  |   32 |

|  3 | Carol  |   27 |

+----+-------+------+

Notes:

• The id column (Primary key) is not added as field parameter into table fields
• Checking the local_infile is ON:

mysql> show global variables like 'local_infile';

If not ON then: set global local_infile=true;

• Checking the location of the folder to upload files:

mysql> show variables like 'secure_file_priv';

C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\

• To copy a table inside a database:

CREATE TABLE new_table LIKE another_table;  //creates a new table with the same format of another table

INSERT new_table SELECT * FROM another_table;  //inserts the data from the another table

• Exporting from mysql to csv: 

The keywords are "query ... into outfile ..."

mysql> select * from test.sqlth_te limit 1000 into outfile 'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/Test.csv' fields enclosed by '"' terminated by ';' escaped by '"' lines terminated by '\r\n';

Note:
The path above is the same location that appears running the query:

mysql> SELECT @@secure_file_priv;

C:\ProgramData\MySQL\MySQL Server 5.7\Uploads\