Excel to MySQL

Anon

Is there a way to transfer info from a excel spreadsheet into a MySQL database (maybe using ODBC like with Access)?

I have a feeling the answer is no, but I thought I would check.

Thanks!
cw

Anon

A solution would be to export your sheet in csv and parse it to inject data in a mysql table.

May be odbc would help, but i dont know odbc 🙂

Real trouble would be that excel use a 2 dimentionnal way of storing data, so it cannot be as much powerfull as a relationnal db. However, this problem would only affect you if you try to export data from mysql into excel not the contrary.

Another trouble may be the embeded vb script in your tables.

Anon

Is there a way to transfer info from a
excel spreadsheet into a MySQL database
(maybe using ODBC like with Access)?

If you're only doing it once, the easiest way is to save the Excel-spreadsheet as a CSV (semicolon-delimited) ASCII-file, and import it into your mySQL-database, either by using a tool like phpMyAdmin, or with pure SQL, something like: LOAD DATA LOCAL INFILE '/table.txt' INTO TABLE mydata FIELDS TERMINATED BY ';' ENCLOSED BY '\"' ESCAPED BY '\' LINES TERMINATED BY '\n'

That would do it.

If you want to have "constant" access to the data in a Excel-spreadsheet, thats changing all the time, on the other hand, you'll probably have to do it via ODBC.

Good luck.

Anon

I do this frequently. You wouldn't use ODBC unless you were a db Wiz and had very complex needs.

I have found a tool that makes this task very easy, called phpMyAdmin which can be <www.phpwizard.net>

It can be done without this tool, but you need to use a rather long LOAD query.

Save your spreadsheet as CSV format, making sure that any header cells have been stripped off. Also make sure that the columns are in the same order as your database.

Using phpMyAdmin, click on your table name and choose the option Insert textfile into table.

Use the upload form to select your file, then change the delimiter to a comma. Put a check mark in the OPTIONALLY box and send your file.

Hit submit and your done.

cw wrote:

Is there a way to transfer info from a excel spreadsheet into a MySQL database (maybe using ODBC like with Access)?

I have a feeling the answer is no, but I thought I would check.

Thanks!
cw

Anon

Try this:
1. Create a tab-delimited file from Excel.
2. Upload this file to your server.
3. Create a MySQL table (CREATE table ...) - manually or with a PHP script.
4. Insert records in your table.
e.g.

 $file = fopen(\"uploaded_file_path/TEXTFILE.txt\", \"r\");


  while(!feof($file)) {

      $line = fgets($file, 8092);

      if ($line) {

        $fields = explode(\"\\t\", $line);

        //each field in a text file will become a field in MySQL table
        $id = $fields[0];
        $Product_id = $fields[1];
        $Brand = addslashes($fields[2]);
        $Sdescription = addslashes($fields[3]);
        $Price = $fields[4];
        $Weight = $fields[5];
        $fields[6] = trim ($fields[6]);   //get rid of spaces left and right

        $Maincat = ucwords($fields[6]);  //capitalize first letter of all words
        $Maincat = addslashes($Maincat);

        $fields[7] = trim ($fields[7]);
        $Subcat = addslashes($fields[7]);
        $Image = addslashes($fields[8]);
        $Ldescription = addslashes($fields[9]);
        $Accessory = addslashes($fields[10]);
        $Warranty = addslashes($fields[11]);


        // insert into YOUR table

        $sql = \"INSERT INTO $tablename (id, Product_id, Brand, Sdescription, Price, Weight, Maincat, Subcat, Image, Ldescription, Accessory, Warranty)
         VALUES (\'$id\',\'$Product_id\',\'$Brand\',\'$Sdescription\',\'$Price\',\'$Weight\',\'$Maincat\',\'$Subcat\',\'$Image\',\'$Ldescription\',\'$Accessory\',\'$Warranty\')\";
        // create connection

$connection = mysql_connect(\"www.yoursite.com\",\"userid\",\"password\") or die (\"Couldn\'t connect to server.\");
// select database
$db = mysql_select_db(\"yourdb\", $connection) or die (\"Couldn\'t select database.\");
//execute SQL query and get result
$sql_result = mysql_query($sql,$connection) or die (\"Couldn\'t execute query.\");

I do it all the time. Next time, when you want to upload the same db again (with some changes), your script should delete it first (DELETE from YOURTABLE) and then insert new records.

And it\'s all in one script. 🙂

alex

Anon

There is. Here are the 3 sets of steps:

Adding the ODBC Data Source

Open your ODBC control in the Control Panel.
On the User DSN tab click \'Add\'.
Select \'Microsoft Excel Driver\' from the list.
For Data Source Name pick any name you want. (this will be your ODBC connection name) ex. \"excelodbc\"
Click on \'Select Workbook\' and browse to your .XLS file.
Click on OK until you have closed the ODBC control.

Editing the XLS file.

Open your .xls file.
using your mouse, select all of the rows and colums that contain data, including headings. If you do not have headings for each column, you may want to add them.
Once these are selected, go to Insert->Name->Define and type a name for the selected region. ex. \"spreadsheet\"

PHP Code.

$cx=odbc_connect(\'excelodbc\',\'\',\'\');
$cur=odbc_exec($cx,\"select * from spreadsheet\");
$Fields = odbc_num_fields($cur);
print \"<table border=\'1\' width=\'100%\'><tr>\";
for ($i=1; $i <= $Fields; $i++){
printf(\"<th bgcolor=\'silver\'>%s</th>\", odbc_field_name( $cur,$i));
}
$Outer=0;
while( odbc_fetch_row( $cur )){
$Outer++;
print \"<tr>\";
for($i=1; $i <= $Fields; $i++){
printf(\"<td>%s</td>\", odbc_result( $cur, $i ));
}
print \"</tr>\";
}
print \"</table>\";
print \"<b> Your request returned $Outer rows!</b>\";
odbc_close($cx);

** The above code was taken from a posting I located somewhere else. Thanks to whoever you are.

Anon

Please ignore all of the \'s

sabbagh

i have the same problem, i've tried some of youre suggestions.
When i use the phpmyadmin it works, but it just import the first row of my csv file.
Could somebody help me?
thanks 4 all!!
José Sabbagh

cdherold

Following those directions I can't seem to get more than the first row imported. Any suggestions on how to get the entire file in?

thanks,

cdherold

Anon

With phpMyAdmin, I can get the whole spreadsheet to work, although I'm trying to find a fast easy solution to have my sql query strip that header row out for me. This is for a client and it's bad enough that they have to save the excel spreadsheet out to a csv file and upload it, but i don't want them to have to also manually strip the headers out.

thanks in advance for any help!

mike

Anon

at mysql.com, under utilites, I found the easiest way to do it. there's a utility called dbf2mysql. you save the excel spreadsheet as dbf, then open dbf2mysql, then export the dbf info into a mysql table. it is very easy and awsome!