[SOLVED] Populating a MySQL database from an excel file

[mgs019]

Hi,

 

I have an excel document with around 1000 records which I want to insert in to an SQL database. Is there an easy way to do this? I know I must get the data in a regularised format and so on first but is there an easy set of php commands to take a file apart and turn it to sql. I thought maybe from a tab delimited file? I want to be able to update regularly as I get an updated spreadsheet every week so I wanted to make a page to delete all the records and then repopulate with the new version. What I do not want to do is enter 1000 records manually!!!

 

Any help will be appreciated,

 

Martin

[jguy]

to make things really sweet and simple as possible, if you are using MySQL, you can use phpMyAdmin to do this with a CSV really easily. 

[cmgmyr]

I have a few clients that would like to easily import new data into their database...They download the current products in excel, make price changes, then upload the same excel file in the same format. Here is what I use:

 

<?php
// mysql connection script ... 

// tab delimited file 
$file = "All_Catalog_Manufacturers.xls"; 

// open file 
$handle = fopen($file, "r"); 

$x = 0;
echo "<table border=1>";
// loop through results with fgetcsv() function 
while(($data = fgetcsv($handle, 1000, "\t")) !== FALSE) { 
     
    // populate field vars just to make it easier to work with ..  
    // you could access the $data[] array directly in the sql if you want 
    $field1 = $data[0]; 
    $field2 = $data[1]; 
    $field3 = $data[2]; 
    $field4 = $data[2]; 
    // etc ... 
     
    // build your sql statement 
    $sql = "insert into table set  
                                testid='1', 
                                category='foo', 
                                field1='".$field1."', 
                                field2='".$field2."', 
                                field3='".$field3."'"; 
     
    //if($x >0) $result = mysql_query($sql); 

if($x >0) echo "<tr><td>$field1</td><td>$field2</td><td>$field3</td></tr>";

$x++;
} 
echo "</table>";
// close file 
fclose($handle); 


?> 

 

***I just have that outputting to a table, but you can just comment out the echo to the table and un-comment the mysql query.

 

hope this helps

[jguy]

cmgmyr,

      That's sweet!  Nice...

[cmgmyr]

no problem, enjoy

[fenway]

LOAD DATA INFILE works too.