Loading data in MySQL Table from a Text File

There are many situation when we need to load some text data into a table. In this post I will tell you some simple steps to load Data into a MySQL table from a text File.

1. First of all data should be in proper format
• Fields/Columns should be separated by some character like space, tab or comma etc.
• Rows/Records/ Lines should be separated by some character like "\r\n" if there is one record per line.
• If data is Enclosed using some character it should be same for all columns.


2. Create a Table in MySQL to hold data.
3. Use LOAD DATA command to load data into the Table.

Example:

I have following data saved in a file c:\sample.txt

"http://dbpedia.org/resource/AfghanistanHistory" "http://www.w3.org/2000/01/rdf-schema#label" "AfghanistanHistory"
"http://dbpedia.org/resource/AfghanistanGeography" "http://www.w3.org/2000/01/rdf-schema#label" "AfghanistanGeography"
"http://dbpedia.org/resource/AccessibleComputing" "http://www.w3.org/2000/01/rdf-schema#label" "AccessibleComputing"
"http://dbpedia.org/resource/AfghanistanPeople" "http://www.w3.org/2000/01/rdf-schema#label" "AfghanistanPeople"
"http://dbpedia.org/resource/AfghanistanCommunications" "http://www.w3.org/2000/01/rdf-schema#label" "AfghanistanCommunications"
"http://dbpedia.org/resource/AfghanistanTransportations" "http://www.w3.org/2000/01/rdf-schema#label" "AfghanistanTransportations"
"http://dbpedia.org/resource/AfghanistanMilitary" "http://www.w3.org/2000/01/rdf-schema#label" "AfghanistanMilitary"
"http://dbpedia.org/resource/AfghanistanTransnationalIssues" "http://www.w3.org/2000/01/rdf-schema#label" "AfghanistanTransnationalIssues"
"http://dbpedia.org/resource/AssistiveTechnology" "http://www.w3.org/2000/01/rdf-schema#label" "AssistiveTechnology"
"http://dbpedia.org/resource/AmoeboidTaxa" "http://www.w3.org/2000/01/rdf-schema#label" "AmoeboidTaxa"
"http://dbpedia.org/resource/AlbaniaPeople" "http://www.w3.org/2000/01/rdf-schema#label" "AlbaniaPeople"
"http://dbpedia.org/resource/AlbaniaHistory" "http://www.w3.org/2000/01/rdf-schema#label" "AlbaniaHistory"
"http://dbpedia.org/resource/AsWeMayThink" "http://www.w3.org/2000/01/rdf-schema#label" "AsWeMayThink"

Columns are separated by space , each row starts at new line and data is Enclosed in ' " '.

Now we can use the LOAD DATA command

mysql> LOAD DATA INFILE 'C:\\sample.txt' INTO TABLE NER.Types FIELDS TERMINATED BY ' ' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';

Thats All :)