?
Solved

How to upload excel files into mysql DB

Posted on 2011-04-21
5
Medium Priority
?
405 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:Squadless
  • 2
  • 2
5 Comments
 
LVL 8

Accepted Solution

by:
PranjalShah earned 1000 total points
ID: 35444437
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
 
LVL 1

Author Comment

by:Squadless
ID: 35444450
I am actually using Java, any suggestions as to how to help with that?
0
 
LVL 8

Expert Comment

by:PranjalShah
ID: 35444485
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
 
LVL 1

Author Comment

by:Squadless
ID: 35444506
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
 
LVL 25

Assisted Solution

by:Ron Malmstead
Ron Malmstead earned 1000 total points
ID: 35448217
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question