CODEWITHSUNDEEP
pythonmysqlexceldatedatetime
akrama81
akrama81

Reputation: 351

How to insert a date time column from excel into datetime field in mySQL?

I have written a python script to import data from an excel spreadsheet into mysql database. But two of the columns in the excel spreadsheet are in the following custom format: YYYY-MM-DD HH:MM:SS. And the mysql table has these columns in datetime format. However when I run the script I get error that this format cannot be added to datetime format in SQL.

Below is my python script:

query = """INSERT INTO table (col1, col2, col3, col4, col5) VALUES (%s, %s, %s, %s, %s)"""
    for r in range(1, sheet.nrows):
        col1 = sheet.cell(r,0).value
        col2 = sheet.cell(r,1).value
        col3 = sheet.cell(r,2).value
        col4 = sheet.cell(r,3).value

        col5       = sheet.cell(r,4).value
        #col5 = xlrd.xldate.xldate_as_datetime(sheet.cell(r,4).value, book.datemode)

        # Assign values from each row
        values = (col1, col2, col3, col4, col5)

        # Execute sql Query
        cursor.execute(query, values)

    # Close the cursor
    cursor.close()

    # Commit the transaction
    con.commit()

Can anybody tell me how to insert a date time column from excel into datetime field in mySQL??

Upvotes: 3

Views: 1412

Answers (1)

Anowar Hossain
Anowar Hossain

Reputation: 581

Maybe you've solved the problem. But if you didn't solve yet please try this: 

import xlrd, datetime
...

col5 = datetime.datetime(*xlrd.xldate_as_tuple(sheet.cell(r,4).value, book.datemode))

Upvotes: 0

Related Questions