How to convert excel file into mysql database??

Question

Hi guys

my boss wants me to create a mysql database for an existing excel file. I was wondering if there are any ways to do convert or import excel file? I have searched google but didn't find anything useful. I appreciate for any reply....Thanks.

Answer 1

You can use an ODBC driver to "mount" an Excel file as database and then make SQL queries to it. All you need then, is a simple migration tool, to copy the tables to another databases system.

I believe there's even an mysqldump-like tool for ODBC driven databases.

Answer 2

1. Save as CSV
2. Use a COPY sql statement
3. Profit

Answer 3

A low tech solution would be to use the concatenation function in excel to convert the data into a series of insert statements and then copy and paste them into mysql query analyzer or whatever client you are using.

Answer 4

I wrote a tool that will let you do sql queries against a csv file. The output is saved as a csv as well. Maybe you will find it useful.

http://whitlock.ath.cx/EasyCSV/

Answer 5

If you have a web server up and running with PHP support, I highly recommend phpMyAdmin.

1. Configure it to connect to MySQL
2. Create the Database and table
3. Click the import tab and you can import a CSV.

If this is a simple one-time import this probably isn't worth the effort. If, on the other hand, you will ever have to work with MySQL again, you will never regret the time to install and configure phpMyAdmin.

Answer 6

From Excel, export the sheet as a text file. In MySQL, use LOAD DATA INFILE to import the text file.

Answer 7

PHPMyAdmin can import CSV files, as well as Excel files (though I've never tried it)

First you need to create your datebase, and add a table. There must be as many fields in that table as there are columns in your Excel document (yes, I know you know)

Then select that database and table in phpmyadmin and use the "Import" tab.

Answer 8

hey, easiest way to do it would be this:

insert into Table (col 1,col 2,col 3...col n) values (val1,...valn);

basically:

do 2 for loops in your excel:

 dim i,j
 dim sqlString,sqlBase
 sqlString=""
 sqlBase="insert into yourTable (col1....coln) values ("
 for i=1 to myRowVariable
   sqlString=""
   for j=1 to myColVariable
     if j=myColVariable then
     sqlString=sqlString & Cells(i,j).value & ");"
     else if j=1 then
     sqlString=sqlBase & sqlString & ","
     else
     sqlString=sqlString & Cells(i,j).value & ","
     end if
 Next j
 Next i 
 'write sqlString into a txt or something 

this will do what you need in a bootstrap but fast and very intuitive way.

Answer 9

Try DBConvert tool. Integrated data selector allows users to select definite range of data to be converted. AutoSelection feature is able to automatically recognize tables for conversion.