# coding=utf-8
import csv
import datetime
import ntpath
import os
import time
import pyhdb
import pandas as pd
def parse_excel(df, file, program, ending_time):
file_group = file.replace('__', '_').split('_')[1]
if file_group not in prefix_dict_1:
prefix_dict_1[file_group] = []
prefix_dict_2[file_group] = []
lots = file.replace('__', '_').split('_')[0]
op = file.replace('__', '_').split('_')[2]
part_no = file.replace('__', '_').split('_')[3]
if len(op) < 2:
return
if 'T1' == op or 'P1' == op:
op = 'P1'
retest = 0
elif 'R1' == op:
retest = 1
elif 'R2' == op:
retest = 2
elif 'R3' == op:
retest = 3
else:
print('\t', '非T1R123')
return
if 'FA' in file.replace('__', '_').split('_')[4]:
temperature = 25
elif 'FH' in file.replace('__', '_').split('_')[4]:
temperature = 150
elif 'FM' in file.replace('__', '_').split('_')[4]:
temperature = 80
else:
temperature = 0
res_data = []
# 对规则和不规则csv文件判断
df_group = df.groupby('PART_ID')
for key in df_group.groups:
pro_data = df_group.get_group(key)
now_row = None
for idx, row in pro_data.iterrows():
if key is None or len(key.strip()) == 0:
continue
if now_row is None:
now_row = row
else:
now_row = row
if now_row is None:
continue
for x in range(len(title)):
# print(title)
nt = title[x]
if len(nt.strip()) <= 0:
continue
line = [file, ending_time, key, lots, part_no, program, file_group, temperature, retest, nt,
now_row[nt], now_row['T_TIME'], now_row['SOFT_BIN'], op]
# print(line)
res_data.append(line)
sel_line(res_data)
for idx, rows in df.iterrows():
if idx < 1:
global parameter_id, is_chart
if parameter_id <= 50:
is_chart = 1
else:
is_chart += 1
for x in range(len(title)):
nt = title[x]
if len(nt.strip()) <= 0:
continue
module_line = ['AUTOMOTIVE', part_no, file_group, nt, limit_l[x], limit_u[x],
unit[x], parameter_id, program, parameter_id, unit[x], 1]
parameter_id += 1
is_chart += 1
# print(module_line)
# PARAMETER
module_excel.append(module_line)
# 数据格式化
def sel_line(res_data):
res_header = ['file', 'ending_time', 'part_id', 'lot_id', 'part_no', 'program', 'file_group', 'temperature',
'retest', 'nt', 'value', 't_time', 'soft_bin', 'lots']
df = pd.DataFrame(res_data, columns=res_header)
df_group = df.groupby('part_id')
for key in df_group.groups:
# print(key)
pro_data = df_group.get_group(key)
for idx, row in pro_data.iterrows():
out_line = row
# Fixed
line_3 = [out_line['file'], out_line['ending_time'], out_line['part_id'], out_line['lot_id'],
out_line['lot_id'], out_line['part_no'], 0, 0, '', '', out_line['program'],
out_line['file_group'],
out_line['temperature'], out_line['lots'], 0, 0, '', 'TF', 0, 0]
# Other
line_1 = [out_line['part_id'], 0, 0, out_line['retest'], out_line['nt'], out_line['value'],
out_line['program'], out_line['lot_id'], out_line['ending_time']]
# Devices
line_2 = [out_line['part_id'], 0, 0, out_line['retest'], out_line['t_time'], out_line['soft_bin'],
out_line['soft_bin'], out_line['lot_id'], '', 1, out_line['program'], out_line['ending_time']]
if not pd.isna(out_line['value']) and len(str(out_line['value'])) > 0:
prefix_dict_1[out_line['file_group']].append(line_1)
if line_2 not in prefix_dict_2[out_line['file_group']]:
prefix_dict_2[out_line['file_group']].append(line_2)
if line_3 not in all_ft:
all_ft.append(line_3)
# Fixed
header = ['FLIE_NAME', 'ENDING_TIME', 'WAFER_ID', 'LOT_ID', 'C_LOT', 'PART_NO', 'RECORDS', 'INSERT_NUM', 'UPDATE_NUM',
'MACHINE_NAME', 'PROGRAM', 'STEP', 'TEMPERATURE', 'FLOW', 'PARA_RECORDS', 'PARA_INSERT', 'PARA_UPDATE',
'VENDOR', 'RAW_WAFERID', 'RASS_DIE']
# Other
prefix_header_1 = ['WAFER_ID', 'LOCATE_X', 'LOCATE_Y', 'RETEST', 'TEST_NAME', 'VALUE', 'PROGRAM', 'LOT_ID',
'ENDING_TIME']
# Devices
prefix_header_2 = ['WAFER_ID', 'LOCATE_X', 'LOCATE_Y', 'RETEST', 'T_TIME', 'SOFT_BIN', 'HARD_BIN', 'LOT_ID', 'PART_ID',
'SITE_NUM', 'PROGRAM', 'ENDING_TIME']
# Color
new_header = ['SOFT_BIN', 'BIN_NAME', 'PROJECT_TYPE', 'PART_NO', 'PROCESS', 'TEST_PROGRAM', 'COLOR', 'CREATE_DATE']
# Parameter
module_header = ['PROJECT_TYPE', 'PART_NO', 'PROCESS', 'PARAMETER', 'LIMIT_L', 'LIMIT_H', 'UNIT', 'TEST_NUM',
'TEST_PROGRAM', 'PARAMETER_ID', 'DISPLAY_UNIT', 'IS_CHART']
new_excel = []
module_excel = []
# 输出文件夹
out_file_dir = 'Output'
# 读取excel目录
file_dir = r'ASAASDAASDC_FT2_P1_ASDASFS_FM111_A3_SR01_2022-02-02_01_11_11_.csv'
file = file_dir.split('\\')[-1]
prefix_dict_1 = {}
prefix_dict_2 = {}
all_ft = []
num = 0
xlsx_file = file_dir
read_data = []
head = None
new_head = None
program = file.replace('__', '_').split('_')[5] + '_' + file.replace('__', '_').split('_')[6]
file_group = file.replace('__', '_').split('_')[1]
ending_time = file.replace('__', '_').split('_')[7] + ' ' + file.replace('__', '_').split('_')[8] + ':' + \
file.replace('__', '_').split('_')[9] + ':' + file.replace('__', '_').split('_')[10].split('.')[0]
# print(file_group)
parameter_id = 1
with open(xlsx_file, encoding='gbk') as f:
row = csv.reader(f, delimiter=',')
for idx, r in enumerate(row):
if len(r) <= 0:
continue
if head is not None or new_head is not None:
read_data.append(r)
first = r[0]
# 初始化数据开始行
if 'SITE_NUM' in first:
head = r
elif 'SBin' in first:
l1 = first.split(']')[0].replace('SBin[', '')
if 'Fail__Default' in first:
l2 = 'Fail__Default'
elif 'Pass__Default' in first:
l2 = 'Pass__Default'
else:
l2 = first.split()[1].replace('__AllFail', '')
part_no = file.replace('__', '_').split('_')[0]
# COLOR
new_excel.append([l1, l2, 'AUTOMOTIVE', part_no, file_group, program, 'test color', ''])
# print('\t', '当前开始行:', head)
map = {}
df = pd.DataFrame(read_data, columns=head, dtype=object)
df['SOFT_BIN'].replace('3', '1', inplace=True)
title = list(df.columns.values[6:])
unit = df.iloc[0, 6:].values
# print(unit)
limit_l = df.iloc[1, 6:].values
# print(limit_l)
limit_u = df.iloc[2, 6:].values
# print(limit_u)
parse_excel(df, file, program, ending_time)
"""--------------------------------------------------Upsert To HANA--------------------------------------------------"""
"""--------------------------------------------------Upsert To HANA--------------------------------------------------"""
"""--------------------------------------------------Upsert To HANA--------------------------------------------------"""
def get_connection():
conn = pyhdb.connect(
host="",
port=34215, # 多租户的端口需要准确的如30053,
user="",
password=""
)
return conn
"""---------------------------------------------------Fixed To HANA--------------------------------------------------"""
df_fixed = pd.DataFrame(all_ft, columns=header)
# print(df_fixed['WAFER_ID'])
def GetDb(conn):
A = list(df_fixed['WAFER_ID'])
cur = conn.cursor()
sql = "SELECT WAFERID,DEVICES_TIME FROM AUTOMOTIVE_FT_DEVICES_FIXED_ITEM WHERE WAFERID IN ({})" \
.format(','.join(["?" for _ in A]))
cur.execute(sql, A)
results = cur.fetchall()
return results
conn = get_connection()
results = GetDb(conn)
cursor = conn.cursor()
# ----------------------------------------------------------------------------------------------------------------------
hana_columns = ['ID', 'TIME']
df_hana = pd.DataFrame(results, columns=hana_columns)
# print(df_hana)
data_name = df_fixed['WAFER_ID']
hana_name = df_hana['ID']
data_time = pd.Timestamp(ending_time)
hana_time = df_hana['TIME']
count = 0
for i in range(len(hana_name)):
aa = hana_name[i]
for j in range(len(data_name)):
if aa == data_name[j]:
# print(da1_time[j])
if data_time > hana_time[j]:
# 删除含有指定字符的行
df_fixed = df.drop(df[df['WAFER_ID'].str.contains(pat=data_name[j], regex=False) == True].index,
inplace=True)
else:
# 删除表内重复WAFER_ID一行 ???
print(hana_name[i])
del_id = hana_name[i]
cur = conn.cursor()
sql = "DELETE FROM AUTOMOTIVE_FT_DEVICES_FIXED_ITEM WHERE WAFERID = ({})" \
.format(','.join(["?" for x in del_id]))
cur.execute(sql, del_id)
df_fixed.to_csv(ntpath.join(out_file_dir, 'Automotive_FT_Devices_Fixed_Item.csv'),
header=header, encoding='gbk', index=False)
# -----------------------------------------------------------------------------------------------------------------------
def every_strand(list_temp, n):
for i in range(0, len(list_temp), n):
yield list_temp[i:i + n]
def write_fixed_item(fixed_item):
mark = '?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?' # sql values
cols_str = 'FILE_NAME,' \
'DEVICES_TIME,' \
'C_LOT,' \
'SUB_LOT,' \
'PART_NO,' \
'WAFERID,' \
'RECORDS,' \
'INSERT_NUM,' \
'UPDATE_NUM,' \
'MACHINE_NAME,' \
'PARA_RECORDS,' \
'PARA_INSERT,' \
'STEP,' \
'TEMPERATURE,' \
'TESTER_ID,' \
'PARA_UPDATE,' \
'FLOW,' \
'RAW_WAFERID,' \
'PASS_DIE,' \
'VENDOR'
sql = '''UPSERT "{}" ({}) values ({}) WITH PRIMARY KEY'''.format(fixed_table_name, cols_str, mark)
cursor.executemany(sql, fixed_item)
conn.commit()
print("==== FIXED Inserted Successfully ====")
fixed_table_name = 'AUTOMOTIVE_FT_DEVICES_FIXED_ITEM'
num = 1000 # 每次插入的条数
df_fixed['ENDING_TIME'] = pd.to_datetime(df_fixed['ENDING_TIME'], format='%Y-%m-%d')
list_data_fixed = []
for idx, row in df_fixed.iterrows():
val = [row['FLIE_NAME'],
row['ENDING_TIME'],
row['C_LOT'],
row['LOT_ID'],
row['PART_NO'],
row['WAFER_ID'],
row['RECORDS'],
row['INSERT_NUM'],
row['UPDATE_NUM'],
row['MACHINE_NAME'],
row['PARA_RECORDS'],
row['PARA_INSERT'],
row['STEP'],
row['TEMPERATURE'],
row['PROGRAM'],
row['PARA_UPDATE'],
row['FLOW'],
row['RAW_WAFERID'],
row['RASS_DIE'],
row['VENDOR']]
for x in range(len(val)):
if pd.isna(val[x]):
val[x] = None
if val[x] == '':
val[x] = None
list_data_fixed.append(val)
fixed_result = [list_data_fixed[i * num:(i + 1) * num] for i in range(int(len(list_data_fixed) / num) + 1) if
list_data_fixed[i * num:(i + 1) * num]]
for fixed_item in fixed_result:
write_fixed_item(fixed_item)
"""--------------------------------------------------Color To HANA---------------------------------------------------"""
df_color = pd.DataFrame(new_excel, columns=new_header)
df_color.to_csv(ntpath.join(out_file_dir, 'Global_Map_Color_V2.csv'),
header=new_header, encoding='gbk', index=False)
def write_color_item(color_item):
mark = '?,?,?,?,?,?,?' # sql values
cols_str = 'PROJECT_TYPE,' \
'PART_NO,' \
'PROCESS,' \
'COLOR,' \
'SOFT_BIN,' \
'BIN_NAME,' \
'TEST_PROGRAM'
sql = '''UPSERT "{}" ({}) values ({}) WITH PRIMARY KEY'''.format(color_table_name, cols_str, mark)
cursor.executemany(sql, color_item)
conn.commit()
print("==== COLOR Inserted successfully ====")
color_table_name = 'GLOBAL_MAP_COLOR_V2'
num = 1000 # 每次插入的条数
list_data_color = []
for idx, row in df_color.iterrows():
val = [row['PROJECT_TYPE'],
row['PART_NO'],
row['PROCESS'],
row['COLOR'],
row['SOFT_BIN'],
row['BIN_NAME'],
row['TEST_PROGRAM']]
for x in range(len(val)):
if pd.isna(val[x]):
val[x] = None
if val[x] == '':
val[x] = None
list_data_color.append(val)
color_result = [list_data_color[i * num:(i + 1) * num] for i in range(int(len(list_data_color) / num) + 1) if
list_data_color[i * num:(i + 1) * num]]
for color_item in color_result:
write_color_item(color_item)
"""------------------------------------------------Parameter To HANA-------------------------------------------------"""
df_parameter = pd.DataFrame(module_excel, columns=module_header)
df_parameter.to_csv(ntpath.join(out_file_dir, 'Global_Test_Parameter_V2.csv'),
header=module_header, encoding='gbk', index=False)
def write_parameter_item(parameter_item):
mark = '?,?,?,?,?,?,?,?,?,?,?,?' # sql values
cols_str = 'PROJECT_TYPE,' \
'PART_NO,' \
'PROCESS,' \
'PARAMETER,' \
'LIMIT_L,' \
'LIMIT_H,' \
'UNIT,' \
'TEST_NUM,' \
'TEST_PROGRAM,' \
'PARAMETER_ID,' \
'DISPLAY_UNIT,' \
'IS_CHART'
sql = '''UPSERT "{}" ({}) values ({}) WITH PRIMARY KEY'''.format(parameter_table_name, cols_str, mark)
# print(item)
cursor.executemany(sql, parameter_item)
conn.commit()
print("==== PARAMETER Inserted successfully ====")
parameter_table_name = 'GLOBAL_TEST_PARAMETER_V2'
num = 1000 # 每次插入的条数
list_data_parameter = []
for idx, row in df_parameter.iterrows():
val = [row['PROJECT_TYPE'],
row['PART_NO'],
row['PROCESS'],
row['PARAMETER'],
row['LIMIT_L'],
row['LIMIT_H'],
row['UNIT'],
row['TEST_NUM'],
row['TEST_PROGRAM'],
row['PARAMETER_ID'],
row['DISPLAY_UNIT'],
row['IS_CHART']]
for x in range(len(val)):
if pd.isna(val[x]):
val[x] = None
if val[x] == '':
val[x] = None
list_data_parameter.append(val)
parameter_result = [list_data_parameter[i * num:(i + 1) * num] for i in range(int(len(list_data_parameter) / num) + 1)
if list_data_parameter[i * num:(i + 1) * num]]
for parameter_item in parameter_result:
write_parameter_item(parameter_item)
"""--------------------------------------------------Other To HANA---------------------------------------------------"""
for key in prefix_dict_1:
df_other = pd.DataFrame(prefix_dict_1[key], columns=prefix_header_1)
df_other.to_csv(ntpath.join(out_file_dir, 'Automotive_' + key + '_Devices_Other_Items.csv'),
header=prefix_header_1, encoding='gbk', index=False)
other_table_name = 'AUTOMOTIVE_' + key + '_DEVICES_OTHER_ITEMS'
def write_other_item(other_item):
mark = '?,?,?,?,?,?,?,?,?' # sql values
cols_str = 'WAFERID,' \
'LOCATE_X,' \
'LOCATE_Y,' \
'RETEST,' \
'RESULT,' \
'PARAMETER,' \
'TEST_PROGRAM,' \
'DEVICES_TIME,' \
'SUB_LOT'
sql = '''UPSERT "{}" ({}) values ({}) WITH PRIMARY KEY'''.format(other_table_name, cols_str, mark)
# print(item)
cursor.executemany(sql, other_item)
conn.commit()
print("==== OTHER Inserted successfully ====")
num = 10000 # 每次插入的条数
df_other['ENDING_TIME'] = pd.to_datetime(df_other['ENDING_TIME'], format='%Y-%m-%d')
list_data_other = []
for idx, row in df_other.iterrows():
val = [row['WAFER_ID'],
row['LOCATE_X'],
row['LOCATE_Y'],
row['RETEST'],
row['VALUE'],
row['TEST_NAME'],
row['PROGRAM'],
row['ENDING_TIME'],
row['LOT_ID']]
for x in range(len(val)):
if pd.isna(val[x]):
val[x] = None
if val[x] == '':
val[x] = None
list_data_other.append(val)
other_result = [list_data_other[i * num:(i + 1) * num] for i in range(int(len(list_data_other) / num) + 1) if
list_data_other[i * num:(i + 1) * num]]
for other_item in other_result:
write_other_item(other_item)
"""-------------------------------------------------Devices To HANA--------------------------------------------------"""
for key in prefix_dict_2:
df_devices = pd.DataFrame(prefix_dict_2[key], columns=prefix_header_2)
df_devices.to_csv(ntpath.join(out_file_dir, 'Automotive_' + key + '_Devices.csv'),
header=prefix_header_2, encoding='gbk', index=False)
devices_table_name = 'AUTOMOTIVE_' + key + '_DEVICES'
def write_devices_item(devices_item):
mark = '?,?,?,?,?,?,?,?,?,?,?,?'
cols_str = 'WAFERID,' \
'LOCATE_X,' \
'LOCATE_Y,' \
'SOFT_BIN,' \
'DEVICES_TIME,' \
'RETEST,' \
'HARD_BIN,' \
'PART_ID,' \
'TEST_PROGRAM,' \
'SITE_NUM,' \
'TEST_TIME,' \
'SUB_LOT'
sql = '''UPSERT "{}" ({}) values ({}) WITH PRIMARY KEY'''.format(devices_table_name, cols_str, mark)
# print(item)
cursor.executemany(sql, devices_item)
conn.commit()
print("==== DEVICES Inserted successfully ====")
num = 1000 # 每次插入的条数
df_devices['ENDING_TIME'] = pd.to_datetime(df_devices['ENDING_TIME'], format='%Y-%m-%d')
list_data_devices = []
for idx, row in df_devices.iterrows():
val = [row['WAFER_ID'],
row['LOCATE_X'],
row['LOCATE_Y'],
row['SOFT_BIN'],
row['ENDING_TIME'],
row['RETEST'],
row['HARD_BIN'],
row['PART_ID'],
row['PROGRAM'],
row['SITE_NUM'],
row['T_TIME'],
row['LOT_ID']]
for x in range(len(val)):
if pd.isna(val[x]):
val[x] = None
if val[x] == '':
val[x] = None
list_data_devices.append(val)
devices_result = [list_data_devices[i * num:(i + 1) * num] for i in range(int(len(list_data_devices) / num) + 1) if
list_data_devices[i * num:(i + 1) * num]]
for devices_item in devices_result:
write_devices_item(devices_item)
print("TableName :" + other_table_name)
print("TableName :" + devices_table_name)
"""----------------------------------------------------HANA Close----------------------------------------------------"""
try:
cursor.close()
conn.close()
except Exception as e:
print("Close HANA :" + str(e))
pass
py查库删库
最新推荐文章于 2023-03-14 13:25:31 发布