#!/usr/bin/env python import os import shutil import codecs import csv import logging import xlrd __author__ = 'Holger Knust' def number_columns_populated(column_list): """ >>> number_columns_populated([]) 0 >>> number_columns_populated(['','','']) 0 >>> number_columns_populated(['1','2','3']) 3 >>> number_columns_populated([1,2,3]) 3 >>> number_columns_populated(['1',2,'']) 2 """ num_cols = 0 for c in column_list: if str(c) != '': num_cols += 1 return num_cols def parse_sizes(sizes): """ >>> parse_sizes('S ; M ; L ; XL ; XXL') ['S', 'M', 'L', 'XL', 'XXL'] >>> parse_sizes('S') ['S'] >>> parse_sizes(164.0) ['164'] >>> parse_sizes(6.5) ['6.5'] >>> parse_sizes('2XL ; 3XL ; 2XS') ['2XL', '3XL', '2XS'] """ sizes_list = [] if ';' in str(sizes): sizes_list = [s.strip() for s in sizes.split(';')] else: if type(sizes) == float: if sizes - float(int(sizes)) < 0.001: sizes_list.append(str(int(sizes))) else: sizes_list.append(str(sizes)) else: sizes_list.append(str(sizes)) return sizes_list def read_ean_lookup(ref_path): ref_dict = {} header_row = [] header_read = False remove_col = -1 full_ref_path = os.path.join(ref_path, "Referenz_Matching_Sonderposten.csv") with codecs.open(full_ref_path, "rb", encoding='utf-8') as f: dialect = csv.Sniffer().sniff(f.read(1024), delimiters=";,") f.seek(0) reader = csv.reader(f, dialect) key_col_num = -1 for row in reader: if header_read: if remove_col > -1: row.pop(remove_col) ref_dict[row[key_col_num]] = row else: header_row = row if not "VariationNo" in header_row: logging.error("Could not find hash key in reference file.") break if "VariationIDString" in header_row: remove_col = header_row.index("VariationIDString") header_row.pop(remove_col) key_col_num = header_row.index("VariationNo") header_read = True return ref_dict, header_row def convert_excel(in_file, out_file, err_file, ref_dict, header_row): header_read = False item_index = -1 cost_index = -1 discount_index = -1 sizes_index = -1 remove_cols = list() alt_dict = {'2XL':'XXL','XXL':'2XL','XXXL':'3XL','3XL':'XXXL','2XS':'XXS','XXS':'2XS'} with codecs.open(out_file, "w", encoding='utf-8') as of, codecs.open(err_file,'w',encoding='utf-8') as ef: writer = csv.writer(of, delimiter=';') err_writer = csv.writer(ef, delimiter=';') wb = xlrd.open_workbook(in_file) sh = wb.sheet_by_index(0) num_rows = sh.nrows logging.debug("Found {0} rows".format(num_rows)) # exit if no rows found if num_rows == 0: logging.error("No rows found in spreadsheet. Exiting...") return sample_rows = 20 if num_rows < sample_rows: sample_rows = num_rows max_cols = -1 for row_num in range(sample_rows): row = sh.row_values(row_num) pop_cols = number_columns_populated(row) if pop_cols > max_cols: max_cols = pop_cols if max_cols < 1: logging.error("No populated rows found in sample (size: {0}). Exiting...".format(sample_rows)) return for row_num in range(num_rows): row = sh.row_values(row_num) pop_cols = number_columns_populated(row) # header row should fill every column if not header_read and pop_cols < max_cols: continue csv_row = list() if header_read: if pop_cols < (max_cols / 2): continue item_num = row[item_index] sizes_list = parse_sizes(row[sizes_index]) cost = row[cost_index] discount = row[discount_index] if type(cost) == float and type(discount) == float: discounted_price = round(cost * (1.0-discount),2) # Calculate cost - 30% discounted_price2 = round(cost * 0.7,2) else: logging.warning("Discounted cost calculation failed for item # {0}".format(item_num)) continue for s in sizes_list: item_var_num = item_num + '#' + s found = item_var_num in ref_dict # search for alternative spellings if not found and s in ['XXL','2XL','XXXL','3XL','XXS','2XS']: item_var_num = item_num + '#' + alt_dict[s] found = item_var_num in ref_dict if found: # remove any unwanted columns new_row = list() for idx, c in enumerate(row): if not idx in remove_cols: new_row.append(c) csv_row = new_row + ref_dict[item_var_num] csv_row.append(discounted_price) csv_row.append(discounted_price2) writer.writerow(csv_row) else: # log the original search term logging.debug("Could not find {0}".format(item_num + '#' + s)) err_writer.writerow(row) else: if 'Artikel' in row: item_index = row.index('Artikel') if 'Item No' in row: item_index = row.index('Item No') if 'reg EK' in row: cost_index = row.index('reg EK') if 'Rabatt' in row: discount_index = row.index('Rabatt') if 'verfügbare Größen' in row: sizes_index = row.index('verfügbare Größen') if 'Bild' in row: idx = row.index('Bild') remove_cols.append(idx) if 'Seite' in row: idx = row.index('Seite') remove_cols.append(idx) if 'Plant' in row: idx = row.index('Plant') remove_cols.append(idx) if 'Package' in row: idx = row.index('Package') remove_cols.append(idx) if 'Usage' in row: idx = row.index('Usage') remove_cols.append(idx) if 'Order' in row: idx = row.index('Order') remove_cols.append(idx) if item_index < 0 or sizes_index < 0: if item_index < 0 and sizes_index < 0: logging.error("Could not find Item No or Sizes columns.") elif sizes_index < 0: logging.error("Could not find Item No column.") else: logging.error("Could not find Sizes column.") # exit processing break new_row = list() for idx, c in enumerate(row): if not idx in remove_cols: new_row.append(c) csv_row = new_row + header_row csv_row.append('rabatt EK') csv_row.append('EK Minus 30') header_read = True writer.writerow(csv_row) 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_err = os.path.join(data_root, "error") path_log = os.path.join(data_root, "log") path_ref = os.path.join(data_root, "ref") files = os.listdir(path_in) num_files = len(files) print("Found {0} files...".format(num_files)) if num_files == 0: return ref_dict, header_row = read_ean_lookup(path_ref) if len(ref_dict) == 0: logging.error("No reference items found. Aborting.") return for f in files: base_name,extension = os.path.splitext(f) # we are only interested in XLSX files if extension.lower() != '.xlsx': continue log_file_name = base_name + '.log' err_file_name = base_name + '.err' out_file_name = base_name + '.csv' in_file = os.path.join(path_in, f) out_file = os.path.join(path_out, out_file_name) err_file = os.path.join(path_out, err_file_name) prc_file = os.path.join(path_prc, f) perr_file = os.path.join(path_err, f) log_file = os.path.join(path_log, log_file_name) if os.path.isfile(in_file): logging.basicConfig(filename=log_file,level=logging.DEBUG) logging.debug("Converting {0}.".format(in_file.encode("ascii","ignore"))) try: convert_excel(in_file, out_file, err_file, ref_dict, header_row) shutil.move(in_file, prc_file) except Exception as e: logging.error(e) shutil.move(in_file, perr_file) def main(): data_root = r"/ftproot/" # data_root = r"/home/holger/git/thelen/data/" inventory_data_root = os.path.join(data_root, "Matching_Sonderposten") process_files(inventory_data_root) if __name__ == "__main__": main()