While importing from excel date is not inserting in django Python

Question

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 4-Jul-22 3:07am

HellySoni

Updated 4-Jul-22 19:16pm

Add a Solution

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

I just ran a test using openpyxl - A Python library to read/write Excel 2010 xlsx/xlsm files — openpyxl 3.0.10 documentation[^] and it correctly converts date values.

matblue25 4-Jul-22 14:53pm

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

Add a Solution

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

HellySoni · Accepted Answer · 2022-07-04T19:16:00

Solution 1

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

Posted 4-Jul-22 19:16pm

HellySoni

While importing from excel date is not inserting in django Python

1 solution

Solution 1

Add your solution here

Preview 0

While importing from excel date is not inserting in django Python

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us