How to insert a database in a text file to mysql?

faydra92

http://ip-to-country.com/downloads/March-2003.zip is the database i want to add.. how do i do this?

dalecosp

What's it look like?

faydra92

its in the zip file

tsinka

Hi,

mysql supports selectíng from a file so try to do something like this

LOAD DATA INFILE 'textfile'
INTO TABLE 'table'
FIELDS ENCLOSED BY '"'
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(fielda.fieldb,fieldc,fieldd);

Please note that you must have the FILE privilege on the database and that the textfile must either be in the database directory or be readable for all.

Thomas

dalecosp

Originally posted by faydra92
its in the zip file

Yup, I'm sure it is. Some folks don't stick their neck out that far...

Use Tsinka's suggestion. 🙂

faydra92

LOAD DATA INFILE 'ip.txt' INTO TABLE 'ip' FIELDS ENCLOSED BY '"' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (IP_FROM,IP_TO,COUNTRY_CODE,COUNTRY_NAME);

i named the file ip.txt, and put it in every directory, so it should find it wherever it looks.. but i get error 1064 when i put this in mysql...

what am i doing wrong?
do i need to make a table first? a database?
heres what i did..

open mysql in console, connect. then put in that line, but i got the error saying something is wrong with my sql syntax

heres the website where i got the text file from
http://ip-to-country.com/database/

tsinka

Ok,

if you're using the mysql command line tool you must first create a database. The following queries should do it for you:

use databasename;
LOAD DATA INFILE ...;

If you only use the file name like 'ip.txt' it has to be in the directory of the database like ...data/databasename.

If you use the full path to the text file like '/tmp/dump/ip.txt' or 'c:\temp\ip.txt' it does not have to be in the database directory.

faydra92

did that..
then went into mysql

used "use ip;" (name of database i want this in).. the file ip.txt was in that directory (\data\ip)

then typed
LOAD DATA INFILE 'ip.txt' INTO TABLE 'ip' FIELDS ENCLOSED BY '"' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (IP_FROM,IP_TO,COUNTRY_CODE,COUNTRY_NAME);

but again, "error in syntax - 1064" garbage came up..
what am i doign wrong?

tsinka

Hi,

my fault, I had some syntax errors in the query:

Please ensure that you created the table like mentioned on the site ... the query that worked for me was:

USE ip;
LOAD DATA INFILE 'c:\table.txt'
INTO TABLE tblip
FIELDS ENCLOSED BY '"'
TERMINATED BY ','
LINES TERMINATED BY '\n' (IP_FROM,IP_TO,COUNTRY_CODE,COUNTRY_NAME);

FIELDS is only required once, \ has to be escaped

faydra92

Originally posted by tsinka
Hi,

my fault, I had some syntax errors in the query:

Please ensure that you created the table like mentioned on the site ... the query that worked for me was:

USE ip;
LOAD DATA INFILE 'c:\table.txt'
INTO TABLE tblip
FIELDS ENCLOSED BY '"'
TERMINATED BY ','
LINES TERMINATED BY '\n' (IP_FROM,IP_TO,COUNTRY_CODE,COUNTRY_NAME);

FIELDS is only required once, \ has to be escaped

thanks 🙂
how can you do that with php?

faydra92

how can u send these queries with php?

USE ip;
LOAD DATA INFILE 'c:\table.txt'
INTO TABLE tblip
FIELDS ENCLOSED BY '"'
TERMINATED BY ','
LINES TERMINATED BY '\n' (IP_FROM,IP_TO,COUNTRY_CODE,COUNTRY_NAME);

crosbystillsnas

its an sql-query like select, insert, update...

$db = mysql_connect(SERVER, USER, PASS);
$query = "USE ip;\n";
$query.= "LOAD DATA INFILE 'c:\\table.txt'\n";
$query.= "INTO TABLE tblip \n";
$query.= "FIELDS ENCLOSED BY '\"'\n"; 
$query.= "TERMINATED BY ',' \n";
$query.= "LINES TERMINATED BY '\\\n' (IP_FROM,IP_TO,COUNTRY_CODE,COUNTRY_NAME);\n";
$result = mysql_db_query(DATABASE, $query);

mysql_close($db);

where you'll have to replace SERVER, USER, PASS, DATABASE.

faydra92

$query.= "LOAD DATA INFILE 'c:\\table.txt'\n";

what do i use for 'c:\table.txt' if table.txt is in the directory of the php script?

crosbystillsnas

i would just guess something like this.

$path2file = $_SERVER["DOCUMENT_ROOT"]."/dir1/dir2/file.txt";
$query.= "LOAD DATA INFILE '".$path2file."'\n";

didnt try it.