How to upload excel files into mysql DB

Posted on 2011-04-21
Last Modified: 2012-05-11
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.

Question by:Squadless
    LVL 8

    Accepted Solution

    I am not sure how you can do this by just using MySQL.  Here are examples with how you can do this using PHP..
    LVL 1

    Author Comment

    I am actually using Java, any suggestions as to how to help with that?
    LVL 8

    Expert Comment

    I am not familiar with Java but here is something i found online..

    Or you can post this question in Java Zone.
    LVL 1

    Author Comment

    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..
    LVL 25

    Assisted Solution

    by:Ron M
    If this is just a "one time" 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=;Database=Asterisk;UID=dbuser2;PWD=mypass"
    Server.Open strConnectString
    blacklist = args(0)
    SQL = "DELETE FROM blacklist WHERE phonenumber=" & blacklist & ";"
    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 ?

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Network it in WD Red

    There's an industry-leading WD Red drive for every compatible NAS system to help fulfill your data storage needs. With drives up to 8TB, WD Red offers a wide array of solutions for customers looking to build the biggest, best-performing NAS storage solution.  

    SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
    Create your own, high-performance VM backup appliance by installing NAKIVO Backup & Replication directly onto a Synology NAS!
    This tutorial will walk an individual through the process of installing the necessary services and then configuring a Windows Server 2012 system as an iSCSI target. To install the necessary roles, go to Server Manager, and select Add Roles and Featu…
    This Micro Tutorial will teach you how to reformat your flash drive. Sometimes your flash drive may have issues carrying files so this will completely restore it to manufacturing settings. Make sure to backup all files before reformatting. This w…

    760 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

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now