Excel-to-MySQL

Does anyone know how I many transfer data from an Excel file across to a MySQL database?

Cheers

Well, there's three ways I use.

It depends on how large is the record size. And how often you will need to do this.

1st, by hand (one time, easy structure in your excel).-

In the rightmost cell in your excel, put a formula that result in a SQL insert statement... it should look something like this:

="INSERT INTO products (id,name,price) values (NULL,'"&A1&"',"&B1&");"

Which will result in a text like this:

INSERT INTO products (id,name,price) values (NULL,'mirror',157.25);

Then just copy that formula down, and then copy-paste all that into your MySQL manager (phpmyadmin, mysqlfront) and run it.

2nd, Saving as CSV and importing from MySQL

Just save your excel as a CSV (comma separated values) file and then use your MySQL manager to impot the file (or run mysqlimport from the shell) to your table.

3rd, ODBC

Make your XLS file available as an ODBC source, and then use MySQL Front or any other tool to import the data.

This could be better if you plan to do this in a regular basis or if the ammount of data is too big.

------------------- ------------------ ------------
I know there must be better ways to achieve this, but I wanted to share how do I handle this. And I do it very often because most of my customers have their information in excel files (I deal with small companies almost all the time)

You could also write a little vba program to do this for you...

Uhm, excuse me lack on knowledge in MySQL here, but i've never used, mysqlimport before. Is it just a simple command, that uses the CSV file as a parameter?

Check this out:


http://www.mysql.com/doc/en/mysqlimport.html

or this, if you want to do it from a script:

http://www.mysql.com/doc/en/LOAD_DATA.html

If I was to save the Excel file as a CSV (comma separated value) file, and imported that file into MySQL, would I need to create the database in MySQL first?

If I was to have 5 columns in Excel, then CSV'd that file, would MySQL put the data into the right coulmns in the MySQL table??

cheers

Hi All,

Wondering on how to convert excel spreadsheet to MYSQL database easily without incurring any cost? Visit this web site

http://excel2mysql.f2g.net

By following simple and intuitive steps, you will be able to get a mysql database. But, get a free copy of phpMyadmin from the internet first. It's simple to get it, just type phpmyadmin at the google search engine or visit http://www.phpmyadmin.net/home_page/.

Thanks.

how may i do:
"Make your XLS file available as an ODBC source, and then use MySQL Front or any other tool to import the data." ???

how may i do:
"Make your XLS file available as an ODBC source, and then use MySQL Front or any other tool to import the data." ???