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’.

Strict Coding Standards

Service List

HTML / CSS

Small Business Websites

Custom Web Apps

E-commerce

Wordpress

Joomla

Jquery / Angular.js

PHP / SQL

PHP Frameworks

Pro Content Writing

Android & IOS Apps

Social Media

SEO Committment

Links

Our affordable web design and SEO services are carefully planned and implemented; starting with a breathtaking style.

After that, I try to ensure your viewers will maximize their experience while Google and other search engines rank you high.

Using Load Data to Add File to MYSQL table

Our Skills

About

Strict Coding Standards

Service List

Contact Us

SEO Services

Links