[php] Importing a file?

[Zombie]

I have a text file containing 8000 ID's and names I need to import to sql and I really don't wanna do it by hand... I first thought of doing the ID's first then the name later which would work except I don't know myself how to read a file and insert each line into the database and can't find anything online...

[Actually thinking of it, I could just create the ID's to the database with a while() but the names are still an issue...]

Can anyone assist in pulling a text file w/ multiple lines into a database?

 

[jaimunki]

google: "php parse text file to mysql" or even "php parse text file to mysql line by line"

else, your going to need to dump a few lines of the file (change sensitive info, but leave structure in place) in here so we can see the exact format and any delimiters etc.

 

[Zombie]

ID LIST: http://pastebin.com/LRs0S0xC
[Imagine the spaces being | and no NPC ID: at the top]

Code attempt 2 [2 Attempts, similar code, both failed]

<?php
include "config.php";
ERROR_REPORTING(E_ALL);
$data = fopen("c:/xampp/htdocs/rs/site/npcid.txt", 'r');

$information =  fread($data, filesize("c:/xampp/htdocs/rs/site/npcid.txt"));
$array = explode(" \r\n ", $information);
print_r($array);
$count = count($array);
echo $count;
for($i=0; $i==$count; $i++) {
echo $i;
	$data = explode('|', $array[$i]);
    mysql_query("INSERT INTO `site_npcids` ( id, npc) VALUES ('{$data[0]}', '{$data[1]}')") 

or die('failed somewhere'); 
}
?>

Attempt 1

<?php
mysql_pconnect("localhost","root","------");
mysql_select_db("-------");
ERROR_REPORTING(E_ALL);
$data = fopen("c:/xampp/htdocs/rs/site/npcid.txt", 'r');

$information =  fread($data, filesize("c:/xampp/htdocs/rs/site/npcid.txt"));
$closeit = fclose($data);
//list($id, $npc) = print_r(explode('|', $information, 2));
print_r(explode('|', $information, 2));
echo "ID: $array[0]<br />"; // foo
echo "NPC: $array[1]<br />"; // *
//$count = round(count($array) / 2);
//echo $count;
//for($i=0; $i==$count; $i=$i+2) {  
//     mysql_query("INSERT INTO `site_npcids` ( id, npc) VALUES ('{$array[$i]}', '{$array[$i //+ //1]}')") or die('failed somewhere');  
//}

?>

Exert of the page result

Array ( [0] => 0|Hans 1|Man 2|Man 3|Man 4|Woman 5|Woman 6|Woman 7|Farmer 8|Thief 9|Guard 10|Schoolgirl 11|Tramp 12|Barbarian 13|Wizard 14|Druid 15|Warrior woman 16|Man 17|Schoolgirl 18|Al-Kharid warrior 19|White Knight 20|Paladin 21|Hero 22|Forester 23|Knight of Ardougne 24|Man 25|Woman 26|Knight of Ardougne 27

Nothing posted to the database, nor formatted correctly since the npcid.txt is formatted ID|NAME

Any assistance?

 

[sunnyverma1984]

you can do this via phpmyadmin using csv import

 

[Dynash]

In your attempt one you have your db inserts commented out - but you have your prints uncommented. That's why you see Array ( [0] => 0|Hans 1|Man 2|Man 3|Man 4 etc, and nothing is imported.

 

[Zombie]

Well at first the database wasn't commented out at first, and at first it didn't work either. It was just commented out cause I was just trying to figure out how to get it to format correctly anyway, I had my friend helping me through some of it and neither of us were able to get it to work...

 

[iBrightDev]

hope this helps

$handle = $fopen("file.txt", "r");
$conn = mysql_connect("localhost","user","pass");
mysql_select_db("dbname",$conn);

while (!feof($handle)) { //this will loop till end of file

$buffer = fgets($handle, 4096);
list($a,$b)=explode("|",$buffer);

echo "{$a} - {$b}<br/>";

$sql = "INSERT INTO table (id,other_column_name) VALUES('".$a."','".$b."')";
mysql_query($sql,$conn) or die(mysql_error());

}

that should work for what you need, but, without seeing all your stuff, im just guessing.

*edit
sorry, typo in query. fixed now

 

[Tanika]

Great Stuff Friend. I too have the same query in my project where i need import all the student register number more than 1000. Instead of importing, you have any idea of generating the ids to student from particular number to another number. For example, consider 232212 - 233212.
So, i will enter the start and ending of register number only but it will automatically generate and assign from the top of the table till the end for all students.

 

[iBrightDev]

if you need to know where the last student number is, are you having it as an auto increment in the DB? if not, so you know what number you want to start with? either way, you will need to set what the first one will be, weather auto or hardcoded, then do a for loop that will add +1 to the count. you will also have to count how many things need to be inserted and tell the for loop to break at that number to return a successful message.