txt file to mysql help..

[techker]

Hey guys i have to choices either .txt or .xls or even .htm file to upload to my MYSQL DB..

 

we have a school software that export all the students in .txt-.htm-.xls files..

 

so im trying to see witch file would be the easiest to work with...

 

so i got this going

 

$file = "LT.txt";
$fp = fopen($file, "r");
$data = fread($fp, filesize($file));
fclose($fp);

$output = str_replace("\t|\t", "|", $data);


$output = explode("\n", $output);


foreach($output as $var) {
$tmp = explode("|", $var);
$Ecole = $tmp[0];
$N_Fiche = $tmp[1];
$Nom = $tmp[2];
$Prenom = $tmp[3];
$Code_P = $tmp[4];

$sql = "INSERT INTO Etudiant SET Ecole='$Ecole', N_Fiche='$N_Fiche', Nom='$Nom',Prenom='$Prenom',Code_P='$Code_P'";

mysql_query($sql);
}
echo "Done!";

 

So it works but it only does the first row ..so i open the txt file it noticed that there is no coma or anything sepereating the data..just a space...

 

like:

 

263 7924004 tim allan  ABtim16059609 801 3 1 116263560

 

 

[fenway]

Formatted data is always better -- xls is preferable -- LOAD DATA INFILE is your firend.

[techker]

ya..i tryed..

mysql_connect($db_host,$db_username,$db_password);
@mysql_select_db($db_database) or die( "Unable to connect to database.");

$fname = $_FILES['sel_file']['name'];

$table = "Etudiant";


$sql = "LOAD DATA INFILE '$fname.xls' 
REPLACE INTO $table 
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '\"' 
LINES TERMINATED BY '\r\n' IGNORE 1 LINES ";

 

what if in the exported file there is no field id..

 

i needed to add the fields to the exported csv ,..

[fenway]

What/