#!/usr/bin/env python import codecs import csv import logging import os import shutil import time import zipfile def read_item_lookup(ref_path): ref_dict = dict() header_read = False header_names = None full_ref_path = os.path.join(ref_path, "Ref_SSW_Update_Item.csv") with codecs.open(full_ref_path, "rU", encoding='utf-8') as f: reader = csv.reader(f, delimiter=';') item_idx = -1 url_idx = -1 for row in reader: if not row: continue if header_read: item_id = row[item_idx].strip() if url_idx > -1: urls = row[url_idx] url_list = urls.split(',') row = row[:url_idx] + row[url_idx+1:] for n in range(5): if n < len(url_list): row.insert(url_idx + n, url_list[n]) else: row.insert(url_idx + n, '') if item_id in ref_dict: msg = "Duplicate Item Id : {}".format(item_id) print(msg) logging.error(msg) else: ref_dict[item_id] = row[item_idx + 1:] else: item_idx = row.index("ItemID") url_idx = row.index("ItemImageURL") if url_idx > -1: row = row[:url_idx] + row[url_idx+1:] for n in range(5): row.insert(url_idx + n, "ItemImageUrl_{}".format(n)) if item_idx > -1: header_names = row[item_idx + 1:] header_read = True return ref_dict, header_names def convert_file(in_file, out_file, ref_dict, header_names): header_read = False ean_index = -1 item_id_index = -1 variation_index = -1 item_text_name_index = -1 price_index = -1 item_price_index = -1 if "ItemPurchasePrice" in header_names: item_price_index = header_names.index("ItemPurchasePrice") else: raise Exception("Could not find column ItemPurchasePrice") if "ItemTextName" in header_names: item_text_name_index = header_names.index("ItemTextName") else: raise Exception("Could not find column ItemTextName") with codecs.open(in_file, "rU", encoding='utf-8') as f, \ codecs.open(out_file, "w", encoding='utf-8') as of: reader = csv.reader(f, delimiter=';') writer = csv.writer(of, delimiter=';', quoting=csv.QUOTE_NONNUMERIC) line_counter = 1 for row in reader: if not row: continue if header_read: # ean = row[ean_index] item_id = row[item_id_index] if item_id in ref_dict: ref_values = ref_dict[item_id] purch_price_text = row[price_index] purch_price_value = 0.0 if purch_price_text: try: purch_price_value = float(purch_price_text.replace(',', '.')) except ValueError: pass if purch_price_value < 0.01: item_purch_price_text = ref_values[item_price_index] try: purch_price_value = float(item_purch_price_text .replace(',', '.')) except ValueError: purch_price_value = 0.0 row[price_index] = purch_price_value variation = row[variation_index] try: var, size = variation.split(":") row[variation_index] = var row.insert(variation_index+1, size) except ValueError: msg = "Value '%s' in column VariationString did not have a colon (:) in row %d" print(msg % (variation, line_counter)) logging.error(msg, variation, line_counter) # Skip to next line continue item_name_and_size = ref_values[item_text_name_index] + " Gr. " + size writer.writerow(row + ref_values + [item_name_and_size, "2,50", "2,32", "2,99"]) else: row.append("ERROR") writer.writerow(row) else: header_read = True if "EAN" in row: ean_index = row.index("EAN") if "ItemID" in row: item_id_index = row.index("ItemID") if "PurchasePrice" in row: price_index = row.index("PurchasePrice") if "VariationString" in row: variation_index = row.index("VariationString") row.insert(variation_index+1, "Groesse") if "VariationIDString" in row: variation_index = row.index("VariationIDString") row.insert(variation_index+1, "Groesse") if ean_index < 0 or item_id_index < 0: raise Exception("Could not find needed columns") writer.writerow(row + header_names + ["ItemTextName_gr", "versand_dhl", "versand_dpd", "versand_brutto"]) line_counter += 1 def process(data_root): path_ref = os.path.join(data_root, "ref") path_in = os.path.join(data_root, "in") path_out = os.path.join(data_root, "out") path_proc = os.path.join(data_root, "processed") path_err = os.path.join(data_root, "error") path_log = os.path.join(data_root, "log") files = os.listdir(path_in) num_files = len(files) print("Found {0} files...".format(num_files)) if num_files == 0: return ref_dict, header_names = read_item_lookup(path_ref) for f in files: log_file_name = time.strftime("%Y%m%d-%H%M%S") + '.log' in_file = os.path.join(path_in, f) out_file_name = (os.path.splitext(f)[0] + '.csv').upper() zip_file_name = (os.path.splitext(f)[0] + '.zip').upper() if "_IN" in out_file_name: out_file_name = out_file_name.replace("_IN", "_OUT") out_file = os.path. join(path_out, out_file_name) zip_file = os.path. join(path_out, zip_file_name) proc_file = os.path.join(path_proc, f) err_file = os.path.join(path_err, f) log_file = os.path.join(path_log, log_file_name) logging.basicConfig(filename=log_file, level=logging.DEBUG) logging.debug("Matching item ids for %s...", in_file) if os.path.isfile(in_file): try: print("Converting {0} to {1}...".format(in_file, out_file)) convert_file(in_file, out_file, ref_dict, header_names) zf = zipfile.ZipFile(zip_file, mode='w', compression=zipfile.ZIP_DEFLATED) try: zf.write(out_file, os.path.basename(out_file)) os.remove(out_file) finally: zf.close() shutil.move(in_file, proc_file) except Exception as e: print(e) logging.error(e) shutil.move(in_file, err_file) os.remove(out_file) def main(): #data_root = r"/home/holger/git/bitbucket/thelen/data/" data_root = r"/ftproot/" #data_root = r"c:/temp/thelen/" project_data_root = os.path.join(data_root, "Matching_SSW_Update") process(project_data_root) print("Done.") if __name__ == "__main__": main()