python reads excel files to generate sql file instance details

python reads excel files to generate sql file instance details

After learning python for so long, I finally used it once in my work. This is to read the data from the excel file and write it to the database. This logic would be too heavy to write in java, so consider using the python script this time.

To do this, you need to add an xlrd module to python, which is dedicated to working with excel files.

The module can be automatically installed in mac with the easy_install xlrd command

import xdrlib ,sys
import xlrd
def open_excel(file= a.xlsx'):
  try:
    data = xlrd.open_workbook(file)# Open the excel file 
    return data
  except Exception,e:
    print str(e)

def excel_table_bycol(file='a.xlsx',colindex=[0],table_name='Sheet1'):
  data = open_excel(file)
  table = data.sheet_by_name(table_name)# To obtain excel The inside of the a 1 page 
  nrows = table.nrows# Get number of rows 
  colnames = table.row_values(0)# For the first 1 The value of the row, as key To use, for different excel The file can be adjusted 
  list = []
  # ( 1 . nrows ) 1 The line after the line, because the first 1 A row is usually the header 
  for rownum in range(1,nrows):
     row = table.row_values(rownum)
     if row:
       app = {}
       for i in colindex:
          app[str(colnames[i]).encode("utf-8")] = str(row[i]).encode("utf-8")# Fill in the data 1 In each dictionary, the data is processed simultaneously utf-8 Transcoding, because some of the data is unicode The coding 
       list.append(app)# Add the dictionary to the list 
  return list
def main():
  #colindex is 1 An array to choose which to read 1 Columns, because often excel In the 1 Small pieces are what we need 
  tables = excel_table_bycol(colindex=[1,4],table_name=u'areaCode')
  file = open('channel_area_code.sql','w')# create sql File and enable write mode 
  for row in tables:
    if row['area_code'] != '':
        file.write("update table_name set para1='%s' where para2='%s';\n"%(row['para1'],row['para2']))# Put it in a file sql statements 
if __name__=="__main__":
  main()

This is not a generic python script, and it still needs to be adjusted to the format of the excel file, but the code is not complicated and the development speed is fast, which is much easier than using java before.

Thank you for reading, I hope to help you, thank you for your support of this site!