Help With SQL file

[Looktrne]

I have a SQL file that won't load into my database

 

it seems like it is stripped of any code used to install and is mainly plain text. the first line is the fields followed by the data

 

could someone please direct me on what I need to do to use this type of SQL to populate my database?

 

"username","email","gender","age","photo1","photo2","photo3","description","country","state_province","city","sexual_orientation","zodiak","bodytype","hair","eyes","ethnicity","education","religion","kids","smoke","drink","income"
"me303","1personsemail@mail.com","Woman",18,"nophoto.jpg","nophoto.jpg","nophoto.jpg","helloe very one i am a verry fun person to be around i like to go out or stay home if i have that special soem one right their with me i am willing to try eveything once so if u want to talk ","US","CT","Denver","Straight","Aquarius","Average","Black","Black","Hispanic/Latino","Some College","No Answer","No","No","Ocasionally","No Answer"
"nice guy","personsemail@mail.com","Man",20,"nophoto.jpg","nophoto.jpg","nophoto.jpg","im smart funny,i like to live life to its fullest.looking for a friend or more","US","MO","cape","Straight","Gemini","Athletic","Blonde","Green","Caucasian (white)","Some College","Catholic","No","Ocasionally","Often","Less than $24,999"

 

thank you in advance for any advise

 

Paul

[S3cr3t]

INSERT INTO data (`username`,`email`,`gender`,`age`,`photo1`,`photo2`,`photo3`,`description`,`country`,`state_province`,`city`,`sexual_orientation`,`zodiak`,`bodytype`,`hair`,`eyes`,`ethnicity`,`education`,`religion`,`kids`,`smoke`,`drink`,`income`) VALUES ("me303","1personsemail@mail.com","Woman",18,"nophoto.jpg","nophoto.jpg","nophoto.jpg","helloe very one i am a verry fun person to be around i like to go out or stay home if i have that special soem one right their with me i am willing to try eveything once so if u want to talk ","US","CT","Denver","Straight","Aquarius","Average","Black","Black","Hispanic/Latino","Some College","No Answer","No","No","Ocasionally","No Answer");

INSERT INTO data (`username`,`email`,`gender`,`age`,`photo1`,`photo2`,`photo3`,`description`,`country`,`state_province`,`city`,`sexual_orientation`,`zodiak`,`bodytype`,`hair`,`eyes`,`ethnicity`,`education`,`religion`,`kids`,`smoke`,`drink`,`income`) VALUES ("nice guy","personsemail@mail.com","Man",20,"nophoto.jpg","nophoto.jpg","nophoto.jpg","im smart funny,i like to live life to its fullest.looking for a friend or more","US","MO","cape","Straight","Gemini","Athletic","Blonde","Green","Caucasian (white)","Some College","Catholic","No","Ocasionally","Often","Less than $24,999");

 

If you got the right table...

[Looktrne]

Thank you is there a way to insert this automatically I have 40,000 inserts that need to be done.

 

also is there a way to create the row if it does not exist?

 

thanks

 

Paul

[S3cr3t]

Well you could make a script that reads each line and inserts it into the INSERT INTO string.

 

The lazy way would be to go into phpMyAdmin and create those missing tables manually.

[The Little Guy]

I believe that mysql can only insert 1000 rows all at once, so you need to break it up into chunks of 40

 

example:

 

insert into my_table (col1, col2, col3) values
('v1', 'v2', 'v3'),
('v1', 'v2', 'v3'),
('v1', 'v2', 'v3'),
('v1', 'v2', 'v3'),
/* 1,000 ish rows */
('v1', 'v2', 'v3');


insert into my_table (col1, col2, col3) values
('v1', 'v2', 'v3'),
('v1', 'v2', 'v3'),
('v1', 'v2', 'v3'),
('v1', 'v2', 'v3'),
/* 1,000 ish rows */
('v1', 'v2', 'v3');

[mikosiko]

assuming that you already have created the table, then to upload your comma delimited data on it you simply can use

LOAD DATA INFILE    usage and examples here http://dev.mysql.com/doc/refman/5.1/en/load-data.html

[Looktrne]

I used a PHP Script thanks for all the input guys...

 

Paul

[Looktrne]

Oh in case anybody wants to see what I used here it is

 

<?php

set_time_limit(0);
$fin = fopen('mem.csv','r') or die('cant open file');
$link = mysql_connect('localhost', 'databaseuser', 'databaseassword');
If (!$link) {
    die ('Could not connect: ' . mysql_error());
}
@mysql_select_db('databasename') or die ('Unable to select database');
echo "Connection succeeded <br />\n";
while (($data=fgetcsv($fin,1000,","))!==FALSE) {


$query="INSERT INTO `members` ( `username`, `email`, `gender`, `age`, `photo1`, `photo2`, `photo3`, `description`, `country`, `state_province`, `city`, `sexual_orientation`, `zodiak`, `bodytype`, `hair`, `eyes`, `ethnicity`, `education`, `religion`, `kids`, `smoke`, `drink`, `income`) VALUES
('".$data[0]."', '".$data[1]."', '".$data[2]."', '".$data[3]."', '".$data[4]."', '".$data[5]."', '".$data[6]."', '".$data[7]."', '".$data[8]."', '".$data[9]."', '".$data[10]."', '".$data[11]."', '".$data[12]."', '".$data[13]."', '".$data[14]."', '".$data[15]."', '".$data[16]."', '".$data[17]."', '".$data[18]."', '".$data[19]."', '".$data[20]."', '".$data[21]."', '".$data[22]."');";


mysql_query($query);
    echo "Record updated <br />\n";
    }
fclose($fin);
mysql_close();
?>

 

Paul