Python parses excel files and saves them to the sqlite database,

Python parses excel files and saves them to the sqlite database,

1. Create a database

Create a database as needed, create two tables, and ensure that data can be stored in an existing database. The Code is as follows:

import sqlite3def createDataBase():cn = sqlite3.connect('check.db')cn.execute('''CREATE TABLE IF NOT EXISTS TB_CHECK(ID integer PRIMARY KEY AUTOINCREMENT,NUMBER INTEGER,ITEM TEXT,REFERENCE TEXT,SUMMARY TEXT,OBJECT TEXT,METHOD TEXT,CONDITION TEXT,VALUE TEXT,RESULT TEXT,SCORE TEXT,REMARKS TEXT,PROVINCE TEXT,TIME TEXT);''')cn.execute('''CREATE TABLE IF NOT EXISTS TB_SCORE(ID integer PRIMARY KEY AUTOINCREMENT,PROVINCE TEXT,TIME TEXT,FILETYPE TEXT,SCORE INTEGER);''')if __name__ == '__main__':createDataBase()

Ii. Use Python to parse excel

The xlrd module in Python is used to parse the excel file.

The related functions are described as follows:

1. Import

import xlrd

2. Read data

data = xlrd.open_workbook('file.xls')

3. Functions

(1) retrieve from Index

table = data.sheet()[0]table = data.sheet_by_index(0)

(2) Get by name

table = data.sheet_by_name(u'sheet1')

(3) Get the value of the whole row and the whole column (array)

table.row_values(i)table.col_values(i)

(4) obtain the number of rows and columns

nrows = table.nrowsncols = table.ncols

(5) Cyclic row List Data

for i in range(nrows):print table.row_values(i)

(6) Cells

cell_A1 = table.cell(0,0).value

(7) Use row and column Indexes

cell_A1 = table.cell(0,0).value

Exercise code:

Import xlrdimport xlwtfrom datetime import date, datetimedef read_excel (): # open the file workbook = xlrd.open_workbook(r'file.xls ') # obtain all sheetsheet_name = workbook. sheet_names () [0] sheet = workbook. sheet_by_name (sheet_name) # Get the content of a row for I in range (6, sheet. nrows): for j in range (0, sheet. ncols): print sheet. cell (I, j ). value. encode ('utf-8') if _ name _ = '_ main _': read_excel ()

3. Read and parse the file name in Python

To differentiate the data of each file, you need to store the symbolic fields in the file name. The code for parsing the file is as follows:

import osdef getFileList(dir,wildcard,recursion): os.chdir(dir) fileList = [] check_province = [] check_time = [] file_type = [] exts = wildcard.split(" ") files = os.listdir(dir) for name in files:  fullname=os.path.join(dir,name)  if(os.path.isdir(fullname) & recursion):   getFileList(fullname,wildcard,recursion)  else:   for ext in exts:    if(name.endswith(ext)):     fileList.append(name)     check_province.append(name.split('-')[1])     check_time.append(name.split('-')[0])     file_type.append(name.split('-')[2]) return fileList,check_time,check_province,file_type

In the next use, you will encounter encoding problems. Therefore, when using these fields, you must first transcode them. The transcoding function is as follows:

# Transcoding function def changeCode (name): name = name. decode ('gbk') name = name. encode ('utf-8') return name

4. parse the excel file and store it to sqlite

Python is used to connect to the database. The sqlite database that comes with Python is relatively simple. I will not introduce it too much here. If you are confused about the Python operation of sqlite, I personally recommend the rookie tutorial ~

The following section parses the excel file and saves it to the database, which contains the content of the judgment cell:

Def readExcel (filename, cn, check_province, check_time, FileType): # Read workbook = xlrd. open_workbook (filename) # obtain sheet sheet_name = workbook. sheet_names () [0] sheet = workbook. sheet_by_name (sheet_name) check_Item = 'A' itemCount = 0 score = 0 second = sheet. cell (7,1 ). value. encode ('utf-8') for I in range (7, sheet. nrows): if sheet. cell (I, 1 ). value. encode ('utf-8') = second: check_Item = sheet. cell (I, 0 ). va Lue. encode ('utf-8') continue temp = [] for j in range (0, sheet. ncols): temp. append (sheet. cell (I, j ). value. encode ('utf-8') answer = sheet. cell (I, 7 ). value. encode ('utf-8') if answer = "yes" or answer = "no": score = score + 1 if answer = "other ": print "!!! Failed to import '% S' "% (filename) print "!!! Please Choose an Right Answer for '% s' -------- "% (filename) break else: cn.exe cute (" insert into TB_CHECK (ITEM, FIELD, TYPE, CONTENT, "" ATTRIBUTE, CHECKPOINT, REMARKS, ANSWER, DESCRIPTION, "" SUGGESTION, PROVINCE, TIME, STYLE) "" values ('% s ', '% s ', '% s',' % s') "" % (temp [0], temp [1], temp [2], temp [3], temp [4], temp [5], temp [6], temp [7], temp [8], temp [9], check_province, check_time, Check_Item) itemCount = itemCount + 1 if itemCount! = 0: score = round (score * (100/itemCount), 2) cn.exe cute ("insert into TB_SCORE (PROVINCE, TIME, FILETYPE, SCORE) "" values ('% s',' %. 2f ') "% (check_province, check_time, FileType, score) print" Successful for' % s' -------- "% (filename) cn. commit ()

Integrate the above functions:

Def importData (path): # database createDataBase () database = sqlite3.connect ("check. db ") # file type wildcard = ". xls "list = getFileList (path, wildcard, 1) nfiles = len (list [0]) # file name = list [0] # time = list [1] # province = list [2] # file type FileType = list [3] for count in range (0, nfiles): filename = file [count] check_province = changeCode (province [count]) check_time = time [count] File_type = changeCode ( FileType [count]) readExcel (filename, database, check_province, check_time, File_type) if _ name _ = '_ main _': if len (sys. argv )! = 2: print "Wrong Parameters" else: path = sys. argv [1] importData (path)

Summary

The above is all the content of this article. I hope the content of this article will be helpful for everyone to learn or use python. If you have any questions, please leave a message.