473,320 Members | 1,846 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

How can i import data from *.xls file into MYSQL without using Com Object

245 100+
Hey all,
I want to import data into mysql Database without using Com object using php technique from a Microsoft Excel File?
Apr 17 '09 #1
5 6419
Dormilich
8,658 Expert Mod 8TB
at first glance I'd say that's impossible, you have to read the file format somehow (and since it's a proprietary format…).
Apr 17 '09 #2
neovantage
245 100+
Yeh but you can see that
PHP MyAdmin give the option of importing data through Excel File without using Com object. Is there any special technique PHPMyadmin Using or PHPMYadmin has it's own algorithm.
Apr 17 '09 #3
Dormilich
8,658 Expert Mod 8TB
this is beyond my skill level…
Apr 17 '09 #4
Ciary
247 Expert 100+
have you tried this?
http://www.webmasterworld.com/php/3334579.htm
Apr 17 '09 #5
Step1 Form: save it as enterfile.php
Expand|Select|Wrap|Line Numbers
  1.  
  2. <form method="post" enctype="multipart/form-data" action="import.php">
  3.     <table width="628" border="0" align="center" cellpadding="5" cellspacing="0" bgcolor="#eeeeee">
  4.     <tr>
  5.       <td width="219"><font size=3><b>Selectionner votre fichier *.csv :</b></font></td>
  6.       <td width="244" align="center"><input type="file" name="userfile" value="userfile"></td>
  7.       <td width="137" align="center">
  8.         <input type="submit" value="Envoyer" name="envoyer" >
  9.       </td>
  10.     </tr>
  11.     </table>
  12.     </form>
  13.  


Step2 create connect script: save it as connect.php
Expand|Select|Wrap|Line Numbers
  1. <?php
  2. // paramètres de connexion
  3. $hostname_smoby = "localhost"; /
  4. $database_smoby = "sample"; 
  5. $username_smoby = "root"; 
  6. $password_smoby = "";
  7. $db = mysql_connect($hostname_smoby, $username_smoby, $password_smoby) or trigger_error(mysql_error(),E_USER_ERROR);
  8. mysql_select_db($database_smoby)or trigger_error(mysql_error(),E_USER_ERROR); 
  9. ?>
  10.  

Step 3 import.php
Expand|Select|Wrap|Line Numbers
  1.     <a name="haut"></a>
  2.     <?php
  3.     // vérification sur la session authentification (la session est elle enregistrée ?)
  4.     // ici les éventuelles actions en cas de réussite de la connexion
  5.      require_once('connect.php'); 
  6. //===========================================================
  7. //
  8. // Code de : Xavier Manzoni
  9. // Email : kaptain_kavern_23@hotmail.com
  10. // Date: 23 Novembre 2004
  11. // Objectif :
  12. //            Selection d'un fichier xls enregistre sous format 
  13. // CSV (separateur ";") puis enregistrement dans la base.
  14. //            Base: smoby
  15. //            Table: bdd
  16. //===========================================================
  17.  
  18.  
  19. //=========================
  20. // Traitement des donnees
  21. //=========================
  22.  
  23. //recupere le nom du fichier indiqué par l'user
  24.  $fichier=$_FILES["userfile"]["name"];
  25.  
  26. // ouverture du fichier en lecture    
  27. if ($fichier)
  28.     {
  29.     //ouverture du fichier temporaire 
  30.     $fp = fopen ($_FILES["userfile"]["tmp_name"], "r"); 
  31.     }
  32. else{ 
  33.     // fichier inconnu 
  34.     ?>
  35.     <p align="center" >- Importation échouée -</p>
  36.     <p align="center" ><b>Désolé, mais vous n'avez pas spécifié de chemin valide ...</b></p>
  37.     <?php
  38.       exit(); 
  39.     }
  40. // declaration de la variable "cpt" qui permettra de conpter le nombre d'enregistrement réalisé
  41. $cpt=0;
  42.  
  43. // importation    
  44. while (!feof($fp))
  45. {
  46.   $ligne = fgets($fp,4096);  
  47.   // on crée un tableau des élements séparés par des points virgule
  48.   $liste = explode(";",$ligne); 
  49.   // premier élément
  50.   $liste[0] = ( isset($liste[0]) ) ? $liste[0] : Null;
  51.   $liste[1] = ( isset($liste[1]) ) ? $liste[1] : Null;
  52.   $liste[2] = ( isset($liste[2]) ) ? $liste[2] : Null;
  53.   $liste[3] = ( isset($liste[3]) ) ? $liste[3] : Null;
  54.  
  55.   $champs0=$liste[0];
  56.   $champs1=$liste[1];
  57.   $champs2=$liste[2];
  58.   $champs3= $liste[3];
  59.  
  60.  
  61.       // pour eviter qu un champs "nom" du fichier soit vide
  62.     if ($champs1!='')
  63.         {
  64.         // nouvel ajout, compteur incrémenté
  65.         $cpt++; 
  66.         // requete et insertion ligne par ligne 
  67.         // champs1 id en general dc on affecte pas de valeur
  68.  
  69.           $result= mysql_query("INSERT INTO names  VALUES('','$champs0','$champs1','$champs2','$champs3')");
  70.  
  71.         }
  72. }
  73.  
  74. // fermeture du fichier
  75. fclose($fp);
  76.  
  77.  
  78. //==================
  79. // FIN
  80. //==================
  81. ?>
  82. <br /><br />Nombre de valeurs nouvellement enregistrées: <b><?php echo $cpt;?></b>.<br /><br />
  83.  
I wish that help sorry for the frensh explaination inside the php scripts . it was just fast reply .have funn with php
Mar 9 '10 #6

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: dave | last post by:
Hello there, I am at my wit's end ! I have used the following script succesfully to upload an image to my web space. But what I really want to be able to do is to update an existing record in a...
2
by: Damien | last post by:
Hi to all, I need to design an import/export system. Data comes from a filemaker pro DB in a big CSV file. Some alterations are made on the data as it is imported into my mysql table. Data is...
2
by: jet | last post by:
Hi, Maybe this is an easy task, but I'm having a really hard time figuring out how to do this. I'm a complete newbie to SQL Server. I have a database dump file from MySQL that's in .sql...
4
by: news | last post by:
Our production database in an exported textfil runs about 60 MB. Compressed that's about 9 MB. I'm trying to import the export into another machine running FC3 and mySQL 11.18, and it appears as...
7
by: phillip.s.powell | last post by:
We're looking at a GUI interface for our MySQL DB and I am interested in MySQL Administrator, however, one of our requirements is to be able to import/export databases. Is this possible or do I...
10
by: Avi | last post by:
Hi I need to read in a large set of text files (9GB+ each) into a database table based on fixed width lengths. There are several ways to complete this, but I am wondering if anyone has...
0
by: NewbieSupreme | last post by:
I'm using PHPMyAdmin on an Apache2Triad install (latest version; 5.x.x, which installs PHP5 and PHPMyAdmin 2.8 as well). In Access, I exported a table to a text file, tab-delimited, text qualifyer...
7
by: Randy | last post by:
Folks: We have a web-based app that's _really_ slowing down because multiple clients are writing their own private data into a single, central database. I guess the previous programmer did...
2
by: GarryJones | last post by:
(I am sorry if this is the wrong group for this posting, I cant find a group on usenet for phpmyadmin, but maybe someone would be nice enough to answer me anyway....) I need to import data into...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

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.