ansaurus

Question

Adding Excel Spreadsheet to SQL Database

Answer 1

+2 A:

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

madhurtanwani 2010-10-15 18:14:35

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 2010-10-15 18:18:12

BTW, I've never used this, but MSSQL supports BULK INSERTS for imports : http://msdn.microsoft.com/en-us/library/ms188365.aspx

madhurtanwani 2010-10-15 18:23:09

perfect, thanks lots!!!!!! i chose the second one, for MSSQL :)

lucifer 2010-10-15 18:39:11

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

M.E. 2010-10-15 18:42:57

Answer 2

+1 A:

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()

Adam Bernier 2010-10-15 18:38:57

Thanks :) I'll keep this one in mind for when I start learning python after my current project :D

lucifer 2010-10-15 18:40:44

Cheers, mate. Good on ya for challenging the old noggin :-)

Adam Bernier 2010-10-15 18:42:31

Answer 3

+1 A:

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?

vgv8 2010-10-16 17:07:26

ansaurus

tags:

views:

answers:

Adding Excel Spreadsheet to SQL Database

related questions