current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote -1 down vote favorite

How can I import an excel file into my SQL database? I have two options, MSSQL or MySQL.

Thank you

share|improve this question

3 Answers 3

up vote 1 down vote accepted

You could export the excel file as a CSV and then use mysqlimport : http://dev.mysql.com/doc/refman/5.0/en/mysqlimport.html

share|improve this answer
    
Reading on the page, there is this direct command for importing a CSV file : mysqlimport --fields-optionally-enclosed-by=""" --fields-terminated-by=, --lines-terminated-by="\n" --user=YOUR_USERNAME --password YOUR_DATABASE YOUR_TABLE.csv –  madhurtanwani Oct 15 '10 at 18:18
    
BTW, I've never used this, but MSSQL supports BULK INSERTS for imports : msdn.microsoft.com/en-us/library/ms188365.aspx –  madhurtanwani Oct 15 '10 at 18:23
    
perfect, thanks lots!!!!!! i chose the second one, for MSSQL :) –  anon271334 Oct 15 '10 at 18:39
    
Which mssql server are you using? DTS provides a very simple interface to excel spreadsheet imports. I imagine ssis out of 2005 and later would have the same functionality, but I've never had the opportunity to try it –  Twelfth Oct 15 '10 at 18:42
up vote 2 down vote

In Python it would be something like:

import MySQLdb, xlrd

def xl_to_mysql():
    book = xlrd.open_workbook('yourdata.xls') 
    to_db = []
    for sheet in book.sheets(): 
        for rowx in xrange(sheet.nrows): 
            to_db.append(tuple(sheet.cell(rowx, colx).value 
                               for colx in xrange(sheet.ncols)))

    conn = MySQLdb.connect(host="yourhost",user="username",
                           passwd="yourpassword",db="yourdb")
    curs = conn.cursor()
    # however many placeholders `%s` you need in the query below 
    curs.executemany("INSERT INTO yourtable VALUES (%s,%s,%s);", to_db)
    conn.commit()

    curs.close()
    conn.close()

if __name__ == '__main__':
   xl_to_mysql()
share|improve this answer
1  
Thanks :) I'll keep this one in mind for when I start learning python after my current project :D –  anon271334 Oct 15 '10 at 18:40
1  
Cheers, mate. Good on ya for challenging the old noggin :-) –  bernie Oct 15 '10 at 18:42
up vote 0 down vote

You can import the file as any other file.

If the question is about data from Excel then in SQL Server I would have linked Excel as a linked server, see here or here, or used OPENROWSET. There are other options like exporting/importing as XML, etc.

All options are pretty well covered on internet. What us the concrete context and/or problem?

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.