473,698 Members | 2,339 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

insertion de donnees dans une table MySQL a partir d'un fichier texte

Bonjour,

Je souhaite insérer dans une table MySQL des données provenant d'un fichier
texte grâce à un script PHP. Mon fichier porte l'extension "txt" et les
données sont séparées par des ";'.

J'ai créé un script qui upload le fichier texte sur le serveur et qui lit le
contenu de chaque ligne, sépare chaque champ, puis stocke les données dans
un tableau indicé pour ensuite insérer ces données dans une table MySQL.
Mon script fonctionne bien, seulement je ne comprends pas pourquoi certains
enregistrements ne sont pas pris en compte alors qu'ils semblent répondre au
même formatage que les autres lignes du fichier texte.

Sur 1357 enregistrements dans mon fichier texte, 194 ne sont pas pris en
compte. Je n'en trouve pas la raison d'autant plus que qu'ils ne se suivent
pas forcément, dans ce cas difficile pour moi de trouver un "dénominate ur
commun" à ces 194 enregistrements ignorés de façon un peu "aléatoire" . J'ai
"épluché" mon fichier texte sous toutes les coutures, je ne vois pas où se
trouve la clé du mystère. A moins que cela puisse venir de mon script? Ça me
parait peu probable puisque ce script se contente d'ouvrir le fichier texte,
de le lire ligne à ligne, de faire un explode() pour séparer les données au
niveau des ";", d'attribuer la valeur contenue dans chaque champ à une
variable et enfin de récupérer grâce à une boucle les valeurs contenues dans
ces variables pour les insérer dans ma table MySQL jusqu'à ce que la fin du
fichier soit atteinte.

Je ne reçois aucun message d'erreur.
Je travaille avec PHP 4.2.0 et MySQL 3.23.49 (issu d'EasyPHP 1.6) sous WinXP
pro.

Je souhaitais savoir si quelqu'un avait déjà rencontré ce problème et si
vous aviez une solution.

Merci à tous

Ci-dessous mon script :

<?PHP
require("funct/connexion.inc.p hp");
require("funct/function_upload csv.inc.php");

//upload csv

if ($file_upload_n ame) {

upload(
$file_upload,
$file_upload_na me,
$file_upload_si ze,
1000000
);//1Mo max

} else {
header("Locatio n: importcsv.php?e rror=fichier");
}//EndIf;

//effacement de tous les enregistrements de la table avant l'import
mysql_query("DE LETE FROM ma_table");

//ouverture du fichier uploadé et lecture du contenu
if ( file_exists($fi le_upload) ){
$fp = fopen("$file_up load","r");
} else {
echo "<link href='../privatezone.css ' rel='stylesheet '
type='text/css'>"."<p align='center' class='TexteNor mal'>Fichier
introuvable, importation stoppée !</p>";
exit();
}//endif;

while( !feof($fp) ) {
$ligne = fgets($fp,4096) ;
$liste = explode(";",$li gne);//crée le tableau des éléments séparés par des
;

$civilitep = $liste[0];//la variable $civilitep reçoit la première valeur
du tableau $liste etc.
$prenomp = $liste[1];
$nomp = $liste[2];
$adressep = $liste[3];
$cpp = $liste[4];
$villep = $liste[5];
$bdayp = $liste[6];
$telp = $liste[7];
$civilitepn = $liste[8];
$prenompn = $liste[9];
$nompn = $liste[10];
$civilitemed = $liste[11];
$prenommed = $liste[12];
$nommed = $liste[13];
$adressemed = $liste[14];
$cpmede = $liste[15];
$villemed = $liste[16];
$telmed = $liste[17];
$vide = $liste[18];
$prescript = $liste[19];
$typeprescript = $liste[20];
$forfaitprescri pt = $liste[21];
$humid = $liste[22];
$deamb = $liste[23];
$marqueapp = $liste[24];
$modeleapp = $liste[25];
$compteurapp = $liste[26];
$consosapp = $liste[27];
$insitupat = $liste[28];
$observancep = $liste[29];
$dernvisite = $liste[30];
$procvisite = $liste[31];
//comparer nom prénom de la table pour trouver id
$sql1 = " SELECT id FROM table_utilisate urs WHERE nom = '$liste[10]' AND
prenom = '$liste[9]' ";
$query1 = mysql_query($sq l1);
$rs1 = mysql_fetch_arr ay($query1);
$varidpn = $rs1["id"];

if( mysql_error() ){
print "Erreur dans la base de données : ".mysql_error() ;
print "<br>importatio n stoppée";
fclose($fp);
exit();
} else {
//insertion des données dans la table, découpage de l'instruction sur deux
lignes
$sql = "INSERT INTO ma_table (civilitep, prenomp, nomp, adressep, cpp,
villep, bdayp, telp, idpn, civilitepn, prenompn, nompn, civilitemed,
prenommed, nommed, adressemed, cpmed, villemed, telmed, prescription, type,
forfait, humid, deamb, marque, modele, consos, insitu, observance,
dernierevisite, prochainevisite )";
$sql .= " VALUES ('$civilitep', '$prenomp',' $nomp', '$adressep', '$cpp',
'$villep', '$bdayp', '$telp', '$varidpn', '$civilitepn', '$prenompn',
'$nompn', '$civilitemed', '$prenommed', '$nommed', '$adressemed', '$cpmed',
'$villemed', '$telmed', '$prescript', '$typeprescript ', '$forfaitprescr ipt',
'$humid', '$deamb', '$marqueapp', '$modeleapp', '$consosapp', '$insitupat',
'$observancep', '$dernvisite', '$procvisite')" ;
$query = mysql_query($sq l);

//affiche chaque ligne insérée pour controle
echo $sql."<br><br>" ;

}//endif;

}//EndWhile;
echo "<link href='../privatezone.css ' rel='stylesheet '
type='text/css'>"."<p align='center' class='TexteNor mal'>"."Fichier importé
avec succès</p>";
echo " importation terminée, ";

$sql3 = " SELECT * FROM ma_table ";//pour compter les enregistrements
présents
$query3 = mysql_query($sq l3);
$nb = mysql_num_rows( $query3);
echo $nb;
echo " enregistrements insérés dans la table";

fclose($fp);
mysql_close();
?>

Jul 17 '05 #1
1 6283
Agathe wrote:

From GOOGLE translation tools:

Hello, I wish to insert in a MySQL table of the data coming from a textual file
thanks to a script PHP. My file carries the extension "txt" and the data are
separated by ";'. I created a script which upload the textual file on the
waiter and which reads the contents of each line, each field separates, then
stores the data in a subscripted table for then inserting these data in a MySQL
table. My script functions well, only I do not include/understand why certain
recordings are not taken into account whereas they seem to answer the same
formatting as the other lines of the textual file. On 1357 recordings in my
textual file, 194 are not taken into account. I do not find of it the reason
more especially as that they are not followed inevitably, in this case difficult
for me to find a "common denominator" to these 194 ignored recordings in a
"random" way a little. I "peeled" my textual file under all the seams, I do not
see where the key of the mystery is. To less than that can come from my script?
That appears not very probable to me since this script is satisfied to open
the textual file, to read it line with line, to make a explode() to separate the
data on the level from ";", to allot the value contained in each field to a
variable and finally to recover thanks to a loop the values contained in these
variables to insert them in my MySQL table until the end of the file is reached.
I do not receive any error message. I work with PHP 4.2.0 and MySQL 3.23.49
(resulting from EasyPHP 1.6) under WinXP pro. I wished to know if somebody had
already encountered this problem and if you had a solution. Thank you with all

Ci-dessous mon script :

<?PHP
require("funct/connexion.inc.p hp");
require("funct/function_upload csv.inc.php");

//upload csv

if ($file_upload_n ame) {

upload(
$file_upload,
$file_upload_na me,
$file_upload_si ze,
1000000
);//1Mo max

} else {
header("Locatio n: importcsv.php?e rror=fichier");
}//EndIf;

//effacement de tous les enregistrements de la table avant l'import
mysql_query("DE LETE FROM ma_table");

//ouverture du fichier uploadé et lecture du contenu
if ( file_exists($fi le_upload) ){
$fp = fopen("$file_up load","r");
} else {
echo "<link href='../privatezone.css ' rel='stylesheet '
type='text/css'>"."<p align='center' class='TexteNor mal'>Fichier
introuvable, importation stoppée !</p>";
exit();
}//endif;

while( !feof($fp) ) {
$ligne = fgets($fp,4096) ;
$liste = explode(";",$li gne);//crée le tableau des éléments séparés par des
;

$civilitep = $liste[0];//la variable $civilitep reçoit la première valeur
du tableau $liste etc.
$prenomp = $liste[1];
$nomp = $liste[2];
$adressep = $liste[3];
$cpp = $liste[4];
$villep = $liste[5];
$bdayp = $liste[6];
$telp = $liste[7];
$civilitepn = $liste[8];
$prenompn = $liste[9];
$nompn = $liste[10];
$civilitemed = $liste[11];
$prenommed = $liste[12];
$nommed = $liste[13];
$adressemed = $liste[14];
$cpmede = $liste[15];
$villemed = $liste[16];
$telmed = $liste[17];
$vide = $liste[18];
$prescript = $liste[19];
$typeprescript = $liste[20];
$forfaitprescri pt = $liste[21];
$humid = $liste[22];
$deamb = $liste[23];
$marqueapp = $liste[24];
$modeleapp = $liste[25];
$compteurapp = $liste[26];
$consosapp = $liste[27];
$insitupat = $liste[28];
$observancep = $liste[29];
$dernvisite = $liste[30];
$procvisite = $liste[31];
//comparer nom prénom de la table pour trouver id
$sql1 = " SELECT id FROM table_utilisate urs WHERE nom = '$liste[10]' AND
prenom = '$liste[9]' ";
$query1 = mysql_query($sq l1);
$rs1 = mysql_fetch_arr ay($query1);
$varidpn = $rs1["id"];

if( mysql_error() ){
print "Erreur dans la base de données : ".mysql_error() ;
print "<br>importatio n stoppée";
fclose($fp);
exit();
} else {
//insertion des données dans la table, découpage de l'instruction sur deux
lignes
$sql = "INSERT INTO ma_table (civilitep, prenomp, nomp, adressep, cpp,
villep, bdayp, telp, idpn, civilitepn, prenompn, nompn, civilitemed,
prenommed, nommed, adressemed, cpmed, villemed, telmed, prescription, type,
forfait, humid, deamb, marque, modele, consos, insitu, observance,
dernierevisite, prochainevisite )";
$sql .= " VALUES ('$civilitep', '$prenomp',' $nomp', '$adressep', '$cpp', ^^^ remove extra space.
'$villep', '$bdayp', '$telp', '$varidpn', '$civilitepn', '$prenompn',
'$nompn', '$civilitemed', '$prenommed', '$nommed', '$adressemed', '$cpmed',
'$villemed', '$telmed', '$prescript', '$typeprescript ', '$forfaitprescr ipt',
'$humid', '$deamb', '$marqueapp', '$modeleapp', '$consosapp', '$insitupat',
'$observancep', '$dernvisite', '$procvisite')" ;
$query = mysql_query($sq l);
You should also check the mysql_error() to see if it succeeded. If not display
the error message. Some of your data may be trying to insert text into a numeric
field. Numeric data should not be enclosed in ' ' as a general rule.


//affiche chaque ligne insérée pour controle
echo $sql."<br><br>" ;

}//endif;

}//EndWhile;
echo "<link href='../privatezone.css ' rel='stylesheet '
type='text/css'>"."<p align='center' class='TexteNor mal'>"."Fichier importé
avec succès</p>";
echo " importation terminée, ";

$sql3 = " SELECT * FROM ma_table ";//pour compter les enregistrements
présents
$query3 = mysql_query($sq l3);
$nb = mysql_num_rows( $query3);
echo $nb;
echo " enregistrements insérés dans la table";

fclose($fp);
mysql_close();
?>

--
Michael Austin.
Donations welcomed. Http://www.firstdbasource.com/donations.html
:)
Jul 17 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
5266
by: Fredo | last post by:
Hello, I have this .txt file : Roger|tow25$rank259 Isabelle|tow36$rank24 Pascal|tow12$rank29 Sergeï|tow45$rank5 Michel|tow1245$rank45478 Frédéric|tow1$rank125425
3
2817
by: Raymond H. | last post by:
Bonjour, Est-il possible d'exécuter seulement un msgbox dans l'événement clik d'une ToolBar mais sans que le bouton en question (de la ToolBar) ne s'enfonce mais reste normal sans enfoncement ? Car dans mon programme, lorsque je clique sur le point d'interrogation mon curseur change en point d'interrogation. Et si je clique sur la ToolBar avec ce point d'interrogatin je veux seulement afficher le msgbox d'aide qui explique le bouton de...
3
2404
by: Francis | last post by:
Bonjour, j'aimerais savoir si l'un d'entre vous pourrais me dire comment je peut faire pour avoir le path (l'adresse logique sur internet) du fichier .class d'un applet java pendant son exécution. merci
0
1533
by: Bernhard Schmidt | last post by:
------=_NextPart_000_0030_01C34C51.B8F4D1A0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable hi dear mysql list members =20 i have setup a mysql database 4.013 with innodb enabled. when i set the =
0
1318
by: Murad Nayal | last post by:
Hello, I vaguely remember reading in the manual that the order of the retrieved rows in a response to a select statement is unpredictable (unless you use an order by clause). this possibly depends on the indices set up for the table and/or used in constructing the result etc. is this accurate? if so is there any way to insure that rows retrieved are returning in the order by were inserted in, say other than ordering by some 'insertion...
1
2535
by: eXistenZ| | last post by:
Bonjour, je suis entrain de recupérer des données a partir d'une table; et je mets ces données là d'un un tableau, puis j'essaie de mettre le contenu de ce tableau ds une table; le pb c'est qu'il m'insere qu'un seul enregistrement ds ma table . je precise que je suis en train d'utiliser une variable de session. ci joint le code pour mettre mes données ds le tableau
0
4282
by: Francesco | last post by:
Ciao a tutti... Premetto che non sono molto pratico di MySql (anche se ho già fatto qualche piccola applicazione che sfrutta questo DBMS) Ho un problema da risolvere molto importante. Ho un DBMS MySql dove sono presenti solo i dati (le tabelle). In un DataBase Access 2000 ho creato le maschere per la gestione e le varie stampe per l'analisi. Al Database Access devo collegare le tabelle presenti nel Db MySql. Il tutto gira su Pc con...
0
9157
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9027
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8895
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
1
6518
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5860
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4369
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4619
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3046
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
2001
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.