Converting Excel Spreadsheet into SQL script

[asy1mpo]

A client has provided the content for their products and categories in a excel spreadsheet

Does anyone know a free program which can convert the excel spreadsheets into a nice sql script which I can then import into Mysql

I have previously used SQL Script Builder which work sometimes and then not others. It used to be free but then I emailed the developer about the problem. He then fixed it but then made to not freeware anymore - 10 days max use.

Thanks

[ncc50446]

Convert it into a .csv file. (Microsoft excel can do this, and prolly even open office, though not sure about that. just save it as a .csv file). Once its convertered, upload, and run this script

$loading = "LOAD DATA LOCAL INFILE 'yourfile.csv' INTO TABLE yourtable
FIELDS TERMINATED BY ','
(fieldname1, fieldname2)";
mysql_query($loading) or die(mysql_error());

and it should work. I think the .csv file has to be uploaded to the same folder as the script that will be running it.

It seperates everything by ','.

Hope it helps. :)

[Starchild]

phpMyAdmin can do this for you as well.

[asy1mpo]

Convert it into a .csv file. (Microsoft excel can do this, and prolly even open office, though not sure about that. just save it as a .csv file). Once its convertered, upload, and run this script

$loading = "LOAD DATA LOCAL INFILE 'yourfile.csv' INTO TABLE yourtableFIELDS TERMINATED BY ','(fieldname1, fieldname2)";mysql_query($loading) or die(mysql_error());

and it should work. I think the .csv file has to be uploaded to the same folder as the script that will be running it.It seperates everything by ','.Hope it helps. :)

Thanks for that. Will give that a goIs the script PHP??

phpMyAdmin can do this for you as well.

Really!! :)Thanks a load!!

[asy1mpo]

phpMyAdmin can do this for you as well.

how is it done in PHPMyAdmin?

[Echilon]

I did loads of searching around until I designed my own in Excel. You have one sheet to hold data and one to hold the SQL output. Mine has two columns, `name` and `value`.

Sheet1

Column A contains `name`, column B holds `value`

Sheet2

Column A will hold `name`, column B will hold the SQL statements. In A1 enter:

=IF(MID(Languages!A2,1,3)="STR",Languages!A2,"")

Then in cell A2:

=IF(Languages!B2="","",CONCATENATE("INSERT INTO `languages` (`name` , `value`"),"` ) VALUES ('",A1,"','",Languages!B2,"'))

I'm sure you could simplify it into one sheet with three columns.

I use the spreadsheet to hold translations, and PHP to output strings to different files depending on their prefix. So for me, the first cell contains all the translations of the word 'error'. You can add as many columns as you like:

=IF(Languages!B2="","",CONCATENATE("INSERT INTO `languages` (`name` , `",SUBSTITUTE(Meta!$A$1," ","` , `"),"` ) VALUES ('",A3,"','",Languages!B2,"','",IF(Languages!C2="",Languages!B2,Languages!C2),"','",IF(Languages!D2="",Languages!B2,Languages!D2),"','",
IF(Languages!E2="",Languages!B2,Languages!E2),"','",IF(Languages!F2="",Languages!B2,Languages!F2),"','",IF(Languages!G2="",Languages!B2,Languages!G2),"','",
IF(Languages!H2="",Languages!B2,Languages!H2),"','",IF(Languages!I2="",Languages!B2,Languages!I2),"','",IF(Languages!J2="",Languages!B2,Languages!J2),"','",
IF(Languages!K2="",Languages!B2,Languages!K2),"','",IF(Languages!L2="",Languages!B2,Languages!L2),"','",IF(Languages!M2="",Languages!B2,Languages!M2),"');"))

I start the table on the third row and have the first and second contain DROP and CREATE statements so I can select the whole B column, then copy and paste in to nav cat and have everything done automatically.