How to upload excel files into mysql DB

Hi,
I have a bunch of excel files that I would like to upload into one table in mysql db.  Every row would have an assigned ID (primary key) and a second column (the excel sheet in binary format?).  How can i do this?
I have Navicat 8 for mySql and i have MySql workbench.  I cant find an import option where i can do this.  I can import a spreadsheet as a table or into a table but what i primarily want to do is to store the entire spreadsheet as binary format or whichever format is possible so that it only takes up one cell in the table.

Thanks
LVL 1
SquadlessAsked:
Who is Participating?
 
PranjalShahCommented:
I am not sure how you can do this by just using MySQL.  Here are examples with how you can do this using PHP..

http://www.phpbuilder.com/columns/florian19991014.php3

http://forum.codecall.net/php-tutorials/6937-tutorial-storing-images-mysql-php.html
0
 
SquadlessAuthor Commented:
I am actually using Java, any suggestions as to how to help with that?
0
 
PranjalShahCommented:
I am not familiar with Java but here is something i found online..

http://www.javazoom.net/jzservlets/uploadbean/uploadbean.html


Or you can post this question in Java Zone.
0
 
SquadlessAuthor Commented:
Thanks!, i would like to stay away from using a language to help me out.  maybe something similar to "load data infile" or a derivative of it..
0
 
Ron MalmsteadInformation Services ManagerCommented:
If this is just a "one time" effort...do the folllowing.

Install the ODBC driver for MySql on your xp machine...
Create an ms access database and link the table.
Open the DB in ms access... copy the contents of your excel files and paste them into the table.


If this is something you want to automate then it can get a little more tricky but you have options.
You could automate the imports by writing code in the access database, or you could create some scripts that run at specified times on your machine and upload data into the table.


Here's a vb script that I use to attach to a MySQL db using it's ODBC driver on Xp.  
set args=wscript.arguments

Set Server = CreateObject("ADODB.Connection")
strConnectString = "Driver={MySQL ODBC 5.1 Driver};Server=192.168.6.2;Database=Asterisk;UID=dbuser2;PWD=mypass"
Server.Open strConnectString
blacklist = args(0)

SQL = "DELETE FROM blacklist WHERE phonenumber=" & blacklist & ";"

Server.Execute(SQL)                                    
Server.close
set dbc = nothing

Open in new window

This could easily be adapted to work in VBA, and INSERT rather than DELETE method.  You might even create a module in Excel, which could be used on any worksheet, provided they are always in the same format that is..


Some more details might improve the advice you get as well.  What is this for precisely ?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.