While import from excel, + sign is not inserted in number field, django

Admin 1
Viewed 49

I have one excel file which has SMS-no and WhatsApp-no fields. We are storing numbers with country codes in our database. While importing .xls file number is inserted successfully but the '+' sign is not inserted in the database.

I am sharing a screenshot of the excel file and how it is stored in a database.

Screenshot of excel file enter image description here

Screenshot of value store in DB

enter image description here

Add contact option

enter image description here

Data store in DB through Add Contact option enter image description here

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="", db="mbs")
                cursor = database.cursor()
                query = """INSERT INTO addcontact (f_name, l_name, add_ln1, add_ln2, country, city, zip, sms_no, whtsp_no) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)"""
                for r in range(1, sheet.nrows):
                        f_name = sheet.cell(r, 0).value
                        l_name = sheet.cell(r, 1).value
                        add_ln1 = sheet.cell(r, 2).value
                        add_ln2 = sheet.cell(r, 3).value
                        country = sheet.cell(r, 4).value
                        city = sheet.cell(r, 5).value
                        zip = sheet.cell(r, 6).value
                        sms_no = sheet.cell(r, 7).value
                        whtsp_no = sheet.cell(r, 8).value
                        values = (f_name, l_name, add_ln1, add_ln2, country, city, zip, sms_no, whtsp_no)
                        cursor.execute(query, values)
                cursor.close()
                database.commit()
                database.close()
                fs.delete(filedata)

Every data is inserted successfully. But number added without + sign in database.

excel django python
2 Answers

You need to change field from some IntegerField to ChardField. Database treats integer +123 as simply positive number 123.

user_127756620

I converted data in string and concate + sign, but after concating it is appending .0 in each, number, So I used the split function and only get the value before. (dot).

here is my code

sms_no =  sheet.cell(r, 8).value
whtsp_no = sheet.cell(r, 9).value
sms1 = "+"+str(sms_no)
smsno = sms1.split(".")
finalsms = smsno[0]
whtsapp1 = "+"+str(whtsp_no)
whtsapno = whtsapp1.split(".")
finalwhtsap = whtsapno[0]
Admin1
Related