Data of a table in a database may be dumped (exported) to a file called export database. Similarly, same data may be loaded (exported) from a CSV file called import database. These functions are very important in any work with databases and it is a good idea to know them. Once when we know how to export or import data and work with a CSV files, we may use that knowledge to make our backups, migrate to another database, upload and download a bunch of retail goods to and from our online store, etc...

Import

The LOAD DATA INFILE statement reads data from a text file and imports the data to the database very fast. To import a file, we need to have oue database table with the number and type of columns which matches to that in file. We need to have FILE and INSERT privileges to login to the database. The syntax to import a .csv file in \tmp directory is:

Syntax

LOAD DATA INFILE 'c:/tmp/tblstudents.csv' 

INTO TABLE tblstudents 

FIELDS TERMINATED BY ',' 

ENCLOSED BY '"'

LINES TERMINATED BY '\n'

IGNORE 1 ROWS;

Where:

  • The file to be imported is 'c:/tmp/tblstudents.csv'.
  • The FIELD TERMINATED BY clause tells how the data is terminated in text file.
  • ENCLOSED BY determines how data are enclosed.
  • LINES TERMINATED BY indicates how the rows are separated from each other in CSV file.
  • If the first row has the column headings, first row can be asked to be ignored in the SQL statement.

If the format of the data does not match with the columns in the table, it can be transformed using SET clause (as shown in the example below). If the dateOfBirth stored in .csv is in dd/mm/yyyy format than the database requirements is a format mm/dd/yyyy; STR_TO_DATE function is used to transform the format in order to match:

LOAD DATA INFILE 'c:/tmp/tblstudents.csv' 

INTO TABLE tblstudents 

FIELDS TERMINATED BY ',' 

ENCLOSED BY '"'

LINES TERMINATED BY '\n'

(name,@dateOfBirth,salary)

SET dateOfBirth = STR_TO_DATE(@dateOfBirth, '%m/%d/%Y');

Export

The data of a table can be stored in a CSV file such as MS Excel, , etc..., and can be later used for processing. To export the data, we use clause given here:

SELECT * FROM tblpatients

INTO OUTFILE 'E:/pat.csv'

FIELDS ENCLOSED BY '"'

TERMINATED BY ';'

LINES TERMINATED BY '\r\n';

Where:

  • INTO OUTFILE gives the path and name of the file to which data is exported.
  • FIELDS ENCLOSED BY defines how the data is enclosed.
  • TERMINATED BY defines the separation of each column data.
  • LINES TERMINATED BY defines the character by which each row data is terminated.

If any of the data has NULL value, it is written as 'N' in '.csv' file rather than 'NULL'. Hence syntax can be written using IFNULL function as given below:

SELECT name, age, IFNULL(phone, 'N/A') FROM tblpatients

INTO OUTFILE 'E:/pat.csv'

FIELDS ENCLOSED BY '"'

TERMINATED BY ';'

LINES TERMINATED BY '\r\n';