CODEWITHSUNDEEP
phpmysqldatabase
homlyn
homlyn

Reputation: 253

How do I import this data file to a Mysql table

How do I import this data file to MySQL table?

1,AF,Afghanistan
2,AX,Aland Islands
3,AL,Albania
4,DZ,Algeria
5,AS,American Samoa
6,AD,Andorra
7,AO,Angola
8,AI,Anguilla
9,AQ,Antarctica
10,AG,Antigua And Barbuda
11,AR,Argentina
12,AM,Armenia
13,AW,Aruba
14,AU,Australia
15,AT,Austria
16,AZ,Azerbaijan
17,BS,Bahamas
18,BH,Bahrain
19,BD,Bangladesh
20,BB,Barbados
21,BY,Belarus
22,BE,Belgium

Upvotes: 2

Views: 300

Answers (4)

Patrick Costello
Patrick Costello

Reputation: 3626

In order to load a file into mysql, you'll want to use the LOAD DATA INFILE mysql command. You want to be careful about what you load into though. A lot of the time, you'll need to specify the columns if your table isn't organized the same way as your text file. To elaborate on ChrisR's answer:

LOAD DATA INFILE '/tmp/test.txt' INTO TABLE test 
FIELDS TERMINATED BY ',' (id, code, name);

This would work if your column names were id, code, and name for the three columns you specified. This would allow you to import the file even if the table were laid out with columns:

+------+------+----+
| name | code | id |
+------+------+----+

Upvotes: 1

Aaron W.
Aaron W.

Reputation: 9299

You can use a GUI like phpMyAdmin or Navicat to import it, or write a PHP script to do it yourself or use LOAD DATA INFILE with MySQL. I would check out fgetcsv to read in the file as an array of rows and then traverse each row and insert them into your database

Upvotes: 0

Christian Mann
Christian Mann

Reputation: 8125

preg_split() should do the trick for each line.

Upvotes: 0

ChrisR
ChrisR

Reputation: 14447

LOAD DATA queries to the rescue!

LOAD DATA INFILE '/tmp/test.txt' INTO TABLE test
FIELDS TERMINATED BY ','

More info on http://dev.mysql.com/doc/refman/5.1/en/load-data.html

Upvotes: 8

Related Questions