How to convert excel file into mysql database? [du

Question

This question already has answers here:

How to import an excel file in to a MySQL database (11 answers)

Closed 6 years ago.

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

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 2

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 3

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 4

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 5

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 6

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