Python methods for parsing Excel files into SQLite databases _python

I. Establishment of a database

Set up a database based on requirements, set up two tables, and ensure that the data can be stored in an existing database, the code is as follows:

Import Sqlite3

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

Second, use Python to parse Excel

The XLRD module in Python is used to parse Excel.

Related features are described below:

1. Import

Import xlrd

2. Reading data

data = Xlrd.open_workbook (' File.xls ')

3. function

(1) Access via index

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

(2) Obtaining by name

Table = data.sheet_by_name (U ' Sheet1 ')

(3) Gets the value of the entire row and column (array)

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

(4) Get the number of rows and columns

nrows = table.nrows
ncols = Table.ncols

(5) Cyclic row and column table data

For I in Range (nrows):
print table.row_values (i)

(6) cell

CELL_A1 = Table.cell (0,0). Value

(7) Using row and column indices

CELL_A1 = Table.cell (0,0). Value

Practice Code:

Import xlrd
import XLWT from
datetime import date,datetime

def read_excel ():
# Open File
workbook = Xlrd.open_workbook (R ' File.xls ')
# get all sheet
sheet_name = workbook.sheet_names () [0]
sheet = Workbook.sheet_by_name (sheet_name)

#获取一行的内容
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 ()

Third, Python read the file name and parse

In order to distinguish the data of each file, you need to put the marked fields in the file name into the library, and the code to parse the files is as follows:

Import OS

def 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:< C10/>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 will encounter coding problems, so you need to use these fields first transcoding, write the transcoding function is as follows:

#转码函数
def changecode (name):
 name = Name.decode (' GBK ')
 name = Name.encode (' UTF-8 ') return
 name

Iv. parse and store Excel files to SQLite

Python connection database selected Python SQLite database is relatively simple in this do not do too much introduction if you have doubts about Python operations SQLite personal recommendation Rookie Tutorial ~

Here is the parse Excel file and save it in the database, which contains the contents of the decision cell:

def readexcel (filename,cn,check_province,check_time,filetype): #读取 workbook = xlrd.open_workbook (filename) # get sheet Sheet_name = Workbook.sheet_names () [0] sheet = workbook.sheet_by_name (sheet_name) Check_item = ' a ' ItemCount = 0 s Core = 0 Second = Sheet.cell (7,1). Value.encode (' Utf-8 ') for I-in range (7,sheet.nrows): If Sheet.cell (i,1). Value.enco De (' utf-8 ') = = Second:check_item = Sheet.cell (i,0). Value.encode (' utf-8 ') continue temp = [] for j in range (0,s  Heet.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!!! Answer for '%s '--------% (filename) break Else:cn.execute ("INSERT INTO Tb_check" (Item,fiel D,type,content, "" Attribute,checkpoint,remarks,answer,description, "" Suggestion,province,time,style "" ValUEs ('%s ', '%s ', '%s ', '%s ', '%s ', '%s ', '%s ', '%s ', '%s ', '%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 ItemC  Ount!= 0:score = Round (Score * (100/itemcount), 2) Cn.execute ("INSERT into Tb_score (Province,time,filetype,score) ' Values ('%s ', '%s ', '%s ', '%.2f ') '% (check_province,check_time,filetype,score) ') print ' successful for '%s '--------% (filename) cn.commit ()

Integrate the above features:

def importdata (path):
 # Database
 createdatabase () db
 = Sqlite3.connect ("check.db")

 #文件类型
 Wildcard = ". xls"

 list = Getfilelist (path,wildcard,1)

 nfiles = Len (list[0))
 #文件名
 file = list[0]
 # Time times
 = list[1]
 #省份
 province = list[2]
 # #文件类型
 FileType = list[3]

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

Summarize

The above is the entire content of this article, I hope this article content for you to learn or use Python can help, if you have questions you can message exchange.