#!/usr/bin/env python __author__ = 'holger' import codecs import csv from datetime import datetime from dateutil.relativedelta import relativedelta import locale import logging import os import shutil import time import xlsxwriter REF_COL_ITEM_NO = 'ItemNo' REF_COL_EAN = 'EAN' REF_COL_ITEM_FREE1 = 'ItemFree1' REF_COL_ITEM_FREE2 = 'ItemFree2' REF_COL_ITEM_FREE4 = 'ItemFree4' REF_COL_ITEM_FREE15 = 'ItemFree15' REF_COL_ITEM_FREE18 = 'ItemFree18' REF_COL_ITEM_FREE19 = 'ItemFree19' REF_COL_ITEM_FREE20 = 'ItemFree20' REF_COL_ITEM_PRODUCER = 'ItemProducer' REF_COL_VAR_STRING = 'VariationString' def read_ref_colors(ref_path): # Expects colors.csv file to exist in ref folder ref_dict = {} header_read = False full_ref_path = os.path.join(ref_path, "Ref_Farben.csv") with codecs.open(full_ref_path, "r", encoding='utf-8', errors='ignore') as f: reader = csv.reader(f, delimiter=';') for row in reader: #print(row) if header_read: ref_dict[row[0]] = row[1] else: header_read = True return ref_dict def read_ref_manufacturers(ref_path): # Expects colors.csv file to exist in ref folder ref_dict = {} header_read = False full_ref_path = os.path.join(ref_path, "Ref_Vendor2_Hersteller.csv") with codecs.open(full_ref_path, "r", encoding='utf-8', errors='ignore') as f: reader = csv.reader(f, delimiter=';') for row in reader: #print(row) if header_read: ref_dict[row[0]] = row[1] else: header_read = True return ref_dict def read_ref_items(ref_path): # Expects Referenz_Matching_Vendor_2.csv file to exist in ref folder ref_dict = {} header_row = [] item_number_idx = -1 header_read = False full_ref_path = os.path.join(ref_path, "Referenz_Matching_Vendor_2.csv") with codecs.open(full_ref_path, "r", encoding='utf-8', errors='ignore') as f: reader = csv.reader(f, delimiter=';') for row in reader: #print(row) if header_read: idx = row[item_number_idx] if idx in ref_dict: curr_list = ref_dict[idx] curr_list.append(row) ref_dict[idx] = curr_list else: ref_dict[idx] = [row] else: if REF_COL_ITEM_NO in row: item_number_idx = row.index(REF_COL_ITEM_NO) else: raise Exception('ItemNo not found') header_row = row header_read = True return ref_dict, header_row def create_item_file(file_in, file_out, file_templ, ref_dict, ref_header, ref_colors, ref_manufacturers): workbook = None loc = None header_read = False item_no_index = -1 template_columns = 125 current_row = 0 try: # Change locales for currency conversions loc = locale.getlocale() # getlocale() may return (None,None) if not set before. # Workaround by setting locale to default before changing it. if not loc[0]: # Set default locale locale.setlocale(locale.LC_ALL, '') loc = locale.getlocale() # Set to German locale locale.setlocale(locale.LC_ALL, 'de_DE.utf8') workbook = xlsxwriter.Workbook(file_out) worksheet = workbook.add_worksheet("Summary") with codecs.open(file_in, "r", encoding='cp1252') as f: reader = csv.reader(f, delimiter=';') # Let's copy the template header first with codecs.open(file_templ, "r", encoding='cp1252') as tf: temp_reader = csv.reader(tf, delimiter=';') is_first_row = True for row in temp_reader: if is_first_row: template_columns = len(row) is_first_row = False worksheet.write_row(current_row, 0, row) current_row += 1 # Let's read the ItemNos from the input file and grab the ref file content for row in reader: if header_read: item_no = row[item_no_index] if item_no in ref_dict: ref_lists = ref_dict[item_no] for ref_list in ref_lists: # Initialize row to template size out_row = [''] * template_columns out_row[1] = 'Weinachter & Thelen GbR, de sporting goods, WEIND' out_row[4] = item_no mfr_item_no = item_no if mfr_item_no: # Split the color from the item number ADI_ mfr_item_list = mfr_item_no.split("_") mfr_item_no_wo_color = mfr_item_list[0] # Remove ADI, NI, PU, JA, SK prefixes from manufacturer item number if len(mfr_item_no_wo_color) > 1: prefix = mfr_item_no_wo_color[:2].upper() if prefix in ['NI', 'PU', 'JA', 'SK']: mfr_item_no_wo_color = mfr_item_no_wo_color[2:] elif len(mfr_item_no_wo_color) > 2: if mfr_item_no_wo_color[:3].upper() == 'ADI': mfr_item_no_wo_color = mfr_item_no_wo_color[3:] out_row[5] = mfr_item_no_wo_color # See if we have a color code if len(mfr_item_list) > 1: out_row[6] = mfr_item_list[1] out_row[7] = 'EAN' out_row[8] = ref_list[ref_header.index(REF_COL_EAN)] item_free2 = ref_list[ref_header.index(REF_COL_ITEM_FREE2)] if item_free2: item_free2_list = item_free2.split('>') if len(item_free2_list) > 3: out_row[12] = item_free2_list[3] if len(item_free2_list) > 2: out_row[11] = item_free2_list[2] if len(item_free2_list) > 1: out_row[10] = item_free2_list[1] if len(item_free2_list) > 0: out_row[9] = item_free2_list[0] producer = ref_list[ref_header.index(REF_COL_ITEM_PRODUCER)] if producer in ref_manufacturers: out_row[13] = ref_manufacturers[producer] else: out_row[13] = "Not_in_Ref" if out_row[12]: out_row[15] = out_row[12] elif out_row[11]: out_row[15] = out_row[11] out_row[16] = ref_list[ref_header.index(REF_COL_ITEM_FREE1)] out_row[17] = ref_list[ref_header.index(REF_COL_ITEM_FREE20)] colors = ref_list[ref_header.index(REF_COL_ITEM_FREE20)] if "/" in colors: # Multi-color item colors = "mehrfarbig" else: if colors in ref_colors: # translate the color colors = ref_colors[colors] else: logging.warning("Color " + colors + " could not be matched.") colors = "Not_in_Ref" out_row[18] = colors # Target group target_group = ref_list[ref_header.index(REF_COL_ITEM_FREE19)] if target_group: out_row[19] = target_group else: out_row[19] = "Uni" var_string = ref_list[ref_header.index(REF_COL_VAR_STRING)] if var_string: var_string_list = var_string.split(':') if len(var_string_list) > 1: out_row[20] = var_string_list[1] out_row[21] = var_string_list[1] if item_free2: out_row[22] = ';'.join(item_free2_list) out_row[23] = 'Dauerstandard' out_row[24] = str(datetime.today().year) out_row[25] = datetime.today().strftime("%d/%m/%Y") out_row[26] = 100 out_row[27] = datetime.today().strftime("%d/%m/%Y") out_row[28] = 100 out_row[29] = (datetime.today() + relativedelta(months=1)).strftime("%d/%m/%Y") out_row[30] = 100 out_row[31] = (datetime.today() + relativedelta(months=2)).strftime("%d/%m/%Y") out_row[32] = 100 out_row[33] = (datetime.today() + relativedelta(months=3)).strftime("%d/%m/%Y") out_row[34] = 100 out_row[35] = (datetime.today() + relativedelta(months=4)).strftime("%d/%m/%Y") out_row[36] = 100 out_row[37] = (datetime.today() + relativedelta(months=5)).strftime("%d/%m/%Y") # See if we have a price here. If so convert and multiply item_free18 = ref_list[ref_header.index(REF_COL_ITEM_FREE18)] if item_free18: try: # Parse the amount num_item_free18 = locale.atof(item_free18) out_row[38] = "{0:.2f}".format(round(1.377 * num_item_free18, 2)) except Exception as ex: logging.warning("Conversion failed for " + item_free18) logging.error(ex) out_row[39] = datetime.today().strftime("%d/%m/%Y") out_row[40] = ref_list[ref_header.index(REF_COL_ITEM_FREE15)] out_row[41] = ref_list[ref_header.index(REF_COL_ITEM_FREE15)] out_row[48] = 'Ja' out_row[49] = 1 out_row[50] = ref_list[ref_header.index(REF_COL_ITEM_FREE4)] out_row[61] = 'Nein' out_row[62] = 'Nein' out_row[63] = 'Nein' out_row[64] = 'Nein' out_row[115] = 'Nein' if num_item_free18: out_row[124] = "{0:.2f}".format(round(num_item_free18, 2)) worksheet.write_row(current_row, 0, out_row) current_row += 1 else: item_no_index = row.index(REF_COL_ITEM_NO) header_read = True except Exception as ex: logging.error(ex) if workbook: workbook.close() # Clean up. Remove broken out files if they exist if os.path.isfile(file_out): os.remove(file_out) raise Exception() finally: if workbook: workbook.close() # Restore original locale if loc[0]: locale.setlocale(locale.LC_ALL, loc) def process_files(data_root): path_in = os.path.join(data_root, "in") path_out = os.path.join(data_root, "out") path_prc = os.path.join(data_root, "processed") path_ref = os.path.join(data_root, "ref") path_err = os.path.join(data_root, "error") path_log = os.path.join(data_root, "log") # Set up logging datetime_file_name = time.strftime("%Y%m%d-%H%M%S") log_file_name = datetime_file_name + '.log' log_file = os.path.join(path_log, log_file_name) logging.basicConfig(filename=log_file, level=logging.DEBUG) files = os.listdir(path_in) num_files = len(files) logging.info("Found {0} files...".format(num_files)) if num_files == 0: return # Read the ref items ref_items_dict, header = read_ref_items(path_ref) # Read the ref colors ref_colors_dict = read_ref_colors(path_ref) # Read the ref manufacturers ref_manufacturer_dict = read_ref_manufacturers(path_ref) out_template = os.path.join(path_ref, "AmazonItemTemplate-1518282398.csv") for f in files: in_file = os.path.join(path_in, f) proc_file = os.path.join(path_prc, f) err_file = os.path.join(path_err, f) out_file_name = datetime_file_name + ".xlsx" out_file = os.path.join(path_out, out_file_name) if os.path.isfile(in_file): try: logging.debug("Processing {0}...".format(in_file)) create_item_file(in_file, out_file, out_template, ref_items_dict, header, ref_colors_dict, ref_manufacturer_dict) if os.path.isfile(in_file): shutil.move(in_file, proc_file) except Exception as e: logging.error(e) # Move in file to error shutil.move(in_file, err_file) def main(): data_root = r"/ftproot/" #data_root = r"/data/projects/pycharm/thelen/conversion/data/" #data_root = r"c:/temp/thelen/" data_root = os.path.join(data_root, "Matching_Vendor_Artikelanlage") process_files(data_root) if __name__ == "__main__": main()