MYSQL Load Data Text File


Using Load Data to Add File to MYSQL table

With mysql, 4 typical methods to import data into a database are to iport the data, loading text data and dumping a csv file from Excel, or importing data from a xml file. This tutorial discusses loading the data from a text file.

The file below is contents of a text(.txt) file. Note that line 1 just tells us what the two database table columns could be.

Country    City
CAN    Vancouver
CAN    Toronto
CAN    Montreal
CAN    Regina

The text below would add the file(my_file.txt) shown above into a database table called canadian_cities.

To load data,
1) Open the desired database with phpmyadmin or mysql console.
2) Open SQL in phpmyadmin or a) Open mysql console >Type: use databasename;
3) With the above file, we have 2 columns. Therefore, the table should already be setup with 2 columns to insert the data. In this case, the two table columns could be country and can_city.
3) Type the command:
LOAD DATA LOCAL INFILE 'c:/my_file.txt' INTO TABLE `canadian_cities`FIELDS TERMINATED BY '\t'    LINES TERMINATED BY '\r\n' IGNORE 1 LINES;

Decyphering the command:
The file myfile.txt gets loaded into the table canadian_cities which is in the database called world_cities.

The fields are terminated by '\t' which means there is a tab space between the two columns; in this case between country name CAN and a city like Toronto.

Lines terminated by '\r\n' means that each new line is a new row in the table.

IGNORE 1 LINES means the first line with the junk text 'Country City' is not inserted into the database. In order to load the data, we had already set the table and columns up. You could omit this condition if you deleted the first line ‘Country City’.