Readcsv.py

最新推荐文章于 2023-03-08 16:37:16 发布
原创最新推荐文章于 2023-03-08 16:37:16 发布 · 726 阅读
0 ·
CC 4.0 BY-SA版权
csv_import 专栏收录该内容
7 篇文章
订阅专栏
#coding: utf-8
import re
import csv, sys
from time import time, strftime, localtime
from  DB import DB
db = DB()
filename = r"D:\workspace\CSV_Import\data\test_example.csv"
# filename = r"D:\workspace\CSV_Import\data\TestMeasurement_20131209131516.csv"

def getStrTime(sec):
    return strftime('%Y-%m-%d %H:%M:%S', localtime(sec))

def insert_data(row,lineflag):
    dic = {}
    dic["LINE_FLAG"]=lineflag
    cols ="""
    referenceid        1,
    lastupdateon       now(),
    lastupdatedby      mabo,
    createdon          now(),
    createdby          Mabotech,
    active             1,
    rowversionstamp    1"""
    for collist in cols.split(","):
        # print collist
        colarr = collist.split(" ")
        # print colarr
        dic[colarr[4]]=colarr[-1]
    param = ['MeasurementName', 'MeasurementDescription', 'MeasurementNumber', 'MeasurementStartTime', 'MeasurementVersion', 'TestName', 'ReleaseNumber', 'RunNumber', 'TestcellParameterFile', 'EngineParameterFile', 'TestParameterFile', 'Frequency', 'LastAppendingTime', 'NumberofAppends', 'TestcellID', 'Project', 'EStopChannel', 'CELL_NAME', 'CELL_SITE', 'CELL_TYPE', 'GRAV_DRIFT', 'VISC_DRIFT', 'BARO_DRIFT', 'HUMI_DRIFT', 'EngineStartedRecognitionSpeed', 'EngineIdleMode', 'EngineIdleDynoSP', 'EngineIdleEngSP', 'EngineID', 'EngineSpeedRampLimit', 'EngineTorqueRampLimit', 'EngineThrottleRampLimit', 'EngineAuxRampLimit', 'EngineMaxSpeed', 'EngineMinSpeed', 'EngineMaxTorque', 'EngineMinTorque', 'EngineMaxAux', 'EngineMinAux', 'EngineIdleDynoRampTime', 'EngineIdleEngRampTime', 'APPLICATION_TYPE', 'CPL', 'CPL_REV', 'CUBIC_CAP', 'FUEL_PUMP_CODE', 'FUEL_PUMP_REV', 'FUEL_PUMP_TYPE', 'MODEL', 'TestID', 'XAxisChannelName', 'XAxisIncrementValue', 'XAxisIncrementValueUnit', 'XAxisStartValue', 'XAxisStartValueUnit', 'XAxisType', 'DateTime', 'SPEED', 'PWR_KW', 'TORQUE', 'BSFC', 'FUEL_RATE', 'OIL_FILTER_P', 'BLOWBY_L_P', 'CELL_AIR_T', 'IN_MANIFOLD_L_P', 'SMOKE', 'OPACITY', 'TURBO_TUR_OUT_L_P', 'FUEL_IN_P', 'FUEL_IN_T', 'FUEL_OUT_P', 'FUEL_OUT_T', 'COOLANT_IN_P', 'COOLANT_IN_T', 'COOLANT_OUT_P', 'COOLANT_OUT_T', 'TEST_RUN_NAME', 'TEST_COMPLETE_STATUS']
    for i in range(0,len(row)):
        dic[param[i]]=row[i]
    # print len(param),len(row)
    #标准化时间格式
    (month,day,yeartime) = dic["MeasurementStartTime"].split('/')
    # print dic["MeasurementStartTime"]
    # print month,day,yeartime[:4],yeartime[4:]
    year = yeartime[:4]
    daytime = day + yeartime[4:]
    if len(month)==1:
        month = '0'+month
        new_date_patten = '-'.join((year,month,daytime))
        # print new_date_patten
        dic["MeasurementStartTime"] = new_date_patten
    else:
        new_date_patten = '-'.join((year,month,daytime))
        # print new_date_patten
        dic["MeasurementStartTime"] = new_date_patten
    # print dic
    sql = "insert into gcic_t_csv_data(MeasurementName,MeasurementDescription,MeasurementNumber,MeasurementStartTime,MeasurementVersion,TestName,ReleaseNumber,RunNumber,TestcellParameterFile,EngineParameterFile,TestParameterFile,Frequency,LastAppendingTime,NumberofAppends,TestcellID,Project,EStopChannel,CELL_NAME,CELL_SITE,CELL_TYPE,GRAV_DRIFT,VISC_DRIFT,BARO_DRIFT,HUMI_DRIFT,EngineStartedRecognitionSpeed,EngineIdleMode,EngineIdleDynoSP,EngineIdleEngSP,EngineID,EngineSpeedRampLimit,EngineTorqueRampLimit,EngineThrottleRampLimit,EngineAuxRampLimit,EngineMaxSpeed,EngineMinSpeed,EngineMaxTorque,EngineMinTorque,EngineMaxAux,EngineMinAux,EngineIdleDynoRampTime,EngineIdleEngRampTime,APPLICATION_TYPE,CPL,CPL_REV,CUBIC_CAP,FUEL_PUMP_CODE,FUEL_PUMP_REV,FUEL_PUMP_TYPE,MODEL,TestID,XAxisChannelName,XAxisIncrementValue,XAxisIncrementValueUnit,XAxisStartValue,XAxisStartValueUnit,XAxisType,DateTime,SPEED,PWR_KW,TORQUE,BSFC,FUEL_RATE,OIL_FILTER_P,BLOWBY_L_P,CELL_AIR_T,IN_MANIFOLD_L_P,SMOKE,OPACITY,TURBO_TUR_OUT_L_P,FUEL_IN_P,FUEL_IN_T,FUEL_OUT_P,FUEL_OUT_T,COOLANT_IN_P,COOLANT_IN_T,COOLANT_OUT_P,COOLANT_OUT_T,TEST_RUN_NAME,TEST_COMPLETE_STATUS,LINE_FLAG,referenceid,lastupdateon,lastupdatedby,createdon,createdby,active,rowversionstamp)values('%(MeasurementName)s', '%(MeasurementDescription)s', '%(MeasurementNumber)s', '%(MeasurementStartTime)s', '%(MeasurementVersion)s', '%(TestName)s', '%(ReleaseNumber)s', '%(RunNumber)s', '%(TestcellParameterFile)s', '%(EngineParameterFile)s', '%(TestParameterFile)s', '%(Frequency)s', '%(LastAppendingTime)s', '%(NumberofAppends)s', '%(TestcellID)s', '%(Project)s', '%(EStopChannel)s', '%(CELL_NAME)s', '%(CELL_SITE)s', '%(CELL_TYPE)s', '%(GRAV_DRIFT)s', '%(VISC_DRIFT)s', '%(BARO_DRIFT)s', '%(HUMI_DRIFT)s', '%(EngineStartedRecognitionSpeed)s', '%(EngineIdleMode)s', '%(EngineIdleDynoSP)s', '%(EngineIdleEngSP)s', '%(EngineID)s', '%(EngineSpeedRampLimit)s', '%(EngineTorqueRampLimit)s', '%(EngineThrottleRampLimit)s', '%(EngineAuxRampLimit)s', '%(EngineMaxSpeed)s', '%(EngineMinSpeed)s', '%(EngineMaxTorque)s', '%(EngineMinTorque)s', '%(EngineMaxAux)s', '%(EngineMinAux)s', '%(EngineIdleDynoRampTime)s', '%(EngineIdleEngRampTime)s', '%(APPLICATION_TYPE)s', '%(CPL)s', '%(CPL_REV)s', '%(CUBIC_CAP)s', '%(FUEL_PUMP_CODE)s', '%(FUEL_PUMP_REV)s', '%(FUEL_PUMP_TYPE)s', '%(MODEL)s', '%(TestID)s', '%(XAxisChannelName)s', '%(XAxisIncrementValue)s', '%(XAxisIncrementValueUnit)s', '%(XAxisStartValue)s', '%(XAxisStartValueUnit)s', '%(XAxisType)s', '%(DateTime)s', '%(SPEED)s', '%(PWR_KW)s', '%(TORQUE)s', '%(BSFC)s', '%(FUEL_RATE)s', '%(OIL_FILTER_P)s', '%(BLOWBY_L_P)s', '%(CELL_AIR_T)s', '%(IN_MANIFOLD_L_P)s', '%(SMOKE)s', '%(OPACITY)s', '%(TURBO_TUR_OUT_L_P)s', '%(FUEL_IN_P)s', '%(FUEL_IN_T)s', '%(FUEL_OUT_P)s', '%(FUEL_OUT_T)s', '%(COOLANT_IN_P)s', '%(COOLANT_IN_T)s', '%(COOLANT_OUT_P)s', '%(COOLANT_OUT_T)s', '%(TEST_RUN_NAME)s', '%(TEST_COMPLETE_STATUS)s','%(LINE_FLAG)s','%(referenceid)s', '%(lastupdateon)s', '%(lastupdatedby)s', '%(createdon)s', '%(createdby)s', '%(active)s', '%(rowversionstamp)s')"%dic
    # import datetime
    # print datetime.datetime.now()
    print sql
    db.execute(sql)

def search(keyparam,colparam):
    flag = re.search(keyparam, colparam)
    return flag

def date_format(ymd):
    ymd = str(ymd)#对datetime.date类型数据进行转换
    (year,month,day) = ymd.split('-')
    assert len(month)==2
    if month[0] == '0':
        monthchg= month[1]
        return '/'.join((year,monthchg,day))
    else:
        return '/'.join((year,month,day))

rowdata = ['SN_90004930', 'Measurement done during Test loaded condition or in a test.', '0', '11/27/2013 10:00', '1', 'tsn_0000090636', '1.3.130301.32 R7 patch 2', '10070', 'GCICTXBOBCAT01_DYNO_20131023150301_Rev.cell', 'eng_d1ed1e2001c_20131119164957_Rev.engine', 'TSN_0000090636_20130606174301_Rev.test', '0', '', '0', '1', 'Cummins_AVL', '', 'GCIC_TC01', 'GCIC', 'DYNO', '0', '0', '0', '0', '600', '0', '1000', '0', '1', '1500', '600', '100', '0', '2600', '700', '1200', '0', '10000', '0', '1', '1', '', '', '', '9.3L', '', '', '', 'C-TIGER', 'Production_Audit', 'Measurement Number', '1', '-', '1', '-', 'ImplicitXAxis', '02:02.7', '694', '1.36', '19.37', '-133366.03', '-8.392', '526.9', '0.14', '21', '0.26', '0.72', '-0.23', '0.67', '10.96', '38', '-0.78', '0', '100.72', '69.3', '94.25', '45.4', 'Hot_Test', '0']

def readcsv(filename):
    data_arr_9min = []
    data_arr_30min = []
    with open(filename, 'rb') as f:
        reader = csv.reader(f)
        try:
            for row in reader:
                if row[-2] == "Hot_Test":
                    if row[-1] == '0':
                        data_arr_9min.append(row)
                        # print data_arr_9min
                    elif row[-1] == '1':
                        # print "9min finished once"
                        # print data_arr_9min[-14]  #第4行
                        insert_data(data_arr_9min[-14],4)
                        # print data_arr_9min[-10] #第8行数据
                        insert_data(data_arr_9min[-10],8)
                        # print data_arr_9min[-8]  #第10行
                        insert_data(data_arr_9min[-8],10)
                        # print data_arr_9min[-6]  #第12行
                        insert_data(data_arr_9min[-6],12)
                        del data_arr_9min[:]
                    else:
                        pass
                elif row[-2] == "EQA":
                    if row[-1] == '0':
                         data_arr_30min.append(row)
                    elif row[-1] == '1':
                        # print "30min finished once"
                        # print data_arr_30min[-12]#第10行
                        insert_data(data_arr_30min[-12],10)
                        # print data_arr_30min[-10]#第12行
                        insert_data(data_arr_30min[-10],12)
                        # print data_arr_30min[-6]#第16行
                        insert_data(data_arr_30min[-6],16)
                        # print data_arr_30min[-4]#第18行
                        insert_data(data_arr_30min[-4],18)
                        del data_arr_30min[:]
                    else:
                        pass
                else:
                    pass
        except csv.Error as e:
            sys.exit('file %s, line %d: %s' % (filename, reader.line_num, e))
if __name__=="__main__":
    readcsv(filename)
    #csvRead()
    # insert_data(rowdata,8)