Click here to Skip to main content
15,440,991 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
See more:
I have one excel file which have candidate details such as name,position,place,email and date. And I am importing that file into my project for bulk import, but it is gives error while importing date column even if I stored date in excel in proper format. I am doing it in django python

Here is my code of views.py

fs = FileSystemStorage()
                    filedata = fs.save("ImportDB.xls", myfile)
                    BASE_DIR = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
                    filepath = BASE_DIR + "\media\ImportDB.xls"
                    book = xlrd.open_workbook(filepath)
                    sheet = book.sheet_by_name("Sheet1")
                    database = MySQLdb.connect(host="localhost", user="root", passwd="1234", db="rms")
                    cursor = database.cursor()
                    query = """INSERT INTO candidate_details (CandidateName, Position, PhoneNumber, Mobile,EmailID, Place,CreatedDate) VALUES (%s, %s, %s, %s, %s,%s,%s)"""
                    for r in range(1, sheet.nrows):
                        CandidateName = sheet.cell(r, 1).value
                        Position = sheet.cell(r, 2).value
                        PhoneNumber = sheet.cell(r, 3).value
                        Mobile = sheet.cell(r, 4).value
                        EmailID = sheet.cell(r, 5).value
                        Place = sheet.cell(r, 6).value
                        CreatedDate = sheet.cell(r, 7).value
                        values = (CandidateName, Position, PhoneNumber, Mobile , EmailID, Place,CreatedDate)
                        cursor.execute(query, values)
                    cursor.close()
                    database.commit()
                    database.close()
                    fs.delete(filedata)



I am attaching screenshot of my excel file which I am importing and error page.

Phutos – Google Drive[^]

What I have tried:

I have tried to convert it but still not working.
Posted
Updated 4-Jul-22 19:16pm
Comments
Richard MacCutchan 4-Jul-22 11:01am    
How is CreatedDate defined in your schema? If it does not match the value returned from the workbook then that will cause problems. See API Reference — xlrd 2.0.1 documentation[^] for ways to check the content type.
Richard MacCutchan 4-Jul-22 13:17pm    
matblue25 4-Jul-22 14:53pm     CRLF
Dates in Excel are stored as numbers with the value 1.0 being January 1, 1900 12:00:00am. You're going to have to convert the Excel value using the xlrd method xlrd.xldate.xldate_as_datetime(xldate, datemode). You get the "datemode" value from the workbook (it can refer to different starting dates). The error message says its getting the value 44579, which is January 18, 2022 in Excel, so it is getting the right cell. This may help too: XLRD Note on Excel Dates In particular note that it says: "When using this package’s xldate_as_tuple() function to convert numbers from a workbook, you must use the datemode attribute of the Book object. If you guess, or make a judgement depending on where you believe the workbook was created, you run the risk of being 1462 days out of kilter."

1 solution

Thank you all for your response I tried to convert it with xldate and it is successfully inserted with this line of code.

CreatedDate = datetime.datetime(*xlrd.xldate_as_tuple(sheet.cell(r, 7).value,book.datemode))
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900