#!/usr/bin/env python #pylint: disable=logging-format-interpolation,global-variable-undefined,global-statement import codecs import csv import fnmatch import logging import os import shutil import sys import tempfile import time import zipfile import math import xml.etree.ElementTree as ET # Utility classes for XML parsing class Item: ean = None ide_size_name = None ide_size_index = None sup_size_name = None pkg_unit = None purchase_price = None retail_price = None nos_flag = None net_flag = None class Color: sup_color_number = None ide_color_code = None sup_color_name = None picture = None items = None class Model: catalog_name = None model_number = None brand_number = None description = None long_description = None fedas = None vat = None colors = None class CatalogParser: def __init__(self, eans_set): self.current_catalog_name = None self.current_model = None self.current_color = None self.current_item = None self.current_tag = None self.items_read = 0 self.inventory_items = [] self.file_time = None self.eans = eans_set def start(self, tag, _): self.current_tag = tag if tag == 'Model_Type_1': self.current_model = Model() self.current_model.catalog_name = self.current_catalog_name elif tag == 'Colors': if self.current_model: self.current_model.colors = [] elif tag == 'Color': self.current_color = Color() elif tag == 'Items': if self.current_color: self.current_color.items = [] elif tag == 'Item': self.current_item = Item() def end(self, tag): if tag == 'Item': if self.current_color and self.current_item: self.current_color.items.append(self.current_item) self.current_item = None if tag == 'Color': if self.current_color and self.current_model: self.current_model.colors.append(self.current_color) self.current_color = None if tag == 'Model_Type_1': if self.current_model: self.inventory_items.append(self.current_model) self.current_model = None self.items_read += 1 # print(self.items_read) def data(self, data): if not data: return elem_text = data.strip() # print(self.current_tag, elem_text) if self.current_tag == 'CAT_NAME': if not self.current_catalog_name: self.current_catalog_name = elem_text elif self.current_tag == 'MODEL_NR': if self.current_model: self.current_model.model_number = elem_text elif self.current_tag == 'BRAND_NR': if self.current_model: self.current_model.brand_number = elem_text elif self.current_tag == 'Description': if self.current_model: self.current_model.description = self.strip_cdata(elem_text) elif self.current_tag == 'LongDescription': if self.current_model: self.current_model.long_description = self.strip_cdata(elem_text) elif self.current_tag == 'fedas': if self.current_model: self.current_model.fedas = elem_text elif self.current_tag == 'VAT': if self.current_model: self.current_model.vat = elem_text elif self.current_tag == 'SUP_COLOR_NR': if self.current_color: self.current_color.sup_color_number = elem_text elif self.current_tag == 'IDE_COLOR_CODE': if self.current_color: self.current_color.ide_color_code = elem_text elif self.current_tag == 'SUP_COLOR_NAME': if self.current_color: self.current_color.sup_color_name = elem_text elif self.current_tag == 'Picture': if self.current_color: self.current_color.picture = elem_text elif self.current_tag == 'EAN': if elem_text not in self.eans and self.current_item: # print('Adding EAN') self.eans.add(elem_text) self.current_item.ean = elem_text else: # We already have this one self.current_item = None elif self.current_tag == 'IDE_SIZE_NAME': if self.current_item: self.current_item.ide_size_name = self.strip_cdata(elem_text).replace(",",".") elif self.current_tag == 'IDESizeIndex': if self.current_item: self.current_item.ide_size_index = elem_text elif self.current_tag == 'SUP_SIZE_NAME': if self.current_item: self.current_item.sup_size_name = self.strip_cdata(elem_text) elif self.current_tag == 'PKG_UNIT': if self.current_item: self.current_item.pkg_unit = elem_text elif self.current_tag == 'PURCHASE_PRICE': if self.current_item: self.current_item.purchase_price = float(elem_text) elif self.current_tag == 'RETAIL_PRICE': if self.current_item: self.current_item.retail_price = float(elem_text) elif self.current_tag == 'NOS_FLAG': if self.current_item: self.current_item.nos_flag = elem_text elif self.current_tag == 'NET_FLAG': if self.current_item: self.current_item.net_flag = elem_text def close(self): pass def strip_cdata(self, text): if text: if text[0:9] == '': return text[9:-3] return text return '' def get_csv(self, brand_dict, brand_subst_dict, add_header): rows = [] if add_header: rows.append(['Artikelnummer', 'CAT_NAME', 'MODEL_NR', 'BRAND_NR', 'Description', 'LongDescription', 'fedas', 'VAT', 'SUP_COLOR_NR', 'IDE_COLOR_CODE', 'SUP_COLOR_NAME', 'Picture', 'EAN', 'IDE_SIZE_NAME', 'IDESizeIndex', 'SUP_SIZE_NAME', 'PKG_UNIT', 'PURCHASE_PRICE', 'RETAIL_PRICE', 'Preis_2', 'Preis_3', 'NOS_FLAG', 'NET_FLAG', 'Hersteller', 'FPB', 'Gewicht', 'Versandkosten', 'Brand','Variantennummer']) for model in self.inventory_items: if model.colors: for color in model.colors: if color.items: for color_item in color.items: if model.brand_number in brand_dict: brand = brand_dict[model.brand_number].strip() else: brand = '#########' brand_prefix = brand_subst_dict.get(model.brand_number, None) if not brand_prefix: brand_prefix = "SV24_" model_desc = "{} {} - {}".format( brand, (model.description.replace(';', '') if model.description else '').title(), color.sup_color_name.lower()) model_long_desc = model.long_description.replace(';', '') \ if model.long_description else '' if color_item.retail_price > 0: price = color_item.retail_price else: price = color_item.purchase_price * 2.0 # Artikelnummer;CAT_NAME;MODEL_NR;BRAND_NR;Description;LongDescription;fedas; # VAT;SUP_COLOR_NR;IDE_COLOR_CODE;SUP_COLOR_NAME;Picture;EAN;IDE_SIZE_NAME; # IDESizeIndex;SUP_SIZE_NAME;PKG_UNIT;PURCHASE_PRICE;RETAIL_PRICE;Preis_2; # Preis_3;NOS_FLAG;NET_FLAG;Nr;Bezeichnung;Hersteller;FPB;Gewicht;Versandkosten item_number = brand_prefix + model.model_number + '_' + color.sup_color_number row = [item_number, model.catalog_name, model.model_number, model.brand_number, model_desc, model_long_desc, model.fedas, model.vat, color.sup_color_number, color.ide_color_code, color.sup_color_name.lower(), color.picture, color_item.ean, color_item.ide_size_name, color_item.ide_size_index, color_item.sup_size_name, color_item.pkg_unit, color_item.purchase_price, price, round(price + 4.20, 2), math.floor(price + 4.20) + 0.95, color_item.nos_flag, color_item.net_flag, brand, '3.61', '1500', '0', brand, item_number + '#' + color_item.ide_size_name] rows.append(row) return rows def get_csv1(self, add_header): rows = [] if add_header: rows.append(['MODEL_NR', 'IDE_COLOR_CODE', 'SUP_COLOR_NR', 'SUP_COLOR_NAME', 'EAN', 'IDE_SIZE_NAME', 'PKG_UNIT', 'PURCHASE_PRICE', 'RETAIL_PRICE', 'Preis_3', 'NOS_FLAG', 'FPB', 'Gewicht', 'Versandkosten']) for model in self.inventory_items: if model.colors: for color in model.colors: if color.items: for color_item in color.items: # if model.brand_number in brand_dict: # brand = brand_dict[model.brand_number] # else: # brand = '#########' # model_desc = model.description # if not model_desc: # model_desc = '' # model_long_desc = model.long_description # if not model_long_desc: # model_long_desc = '' if color_item.retail_price > 0: price = color_item.retail_price else: price = color_item.purchase_price * 2.0 # Artikelnummer;CAT_NAME;MODEL_NR;BRAND_NR;Description;LongDescription; # fedas;VAT;SUP_COLOR_NR;IDE_COLOR_CODE;SUP_COLOR_NAME;Picture;EAN; # IDE_SIZE_NAME;IDESizeIndex;SUP_SIZE_NAME;PKG_UNIT;PURCHASE_PRICE; # RETAIL_PRICE;Preis_2;Preis_3;NOS_FLAG;NET_FLAG;Nr;Bezeichnung; # Hersteller;FPB;Gewicht;Versandkosten row = [model.model_number, color.ide_color_code, color.sup_color_number, color.sup_color_name, color_item.ean, color_item.ide_size_name, color_item.pkg_unit, color_item.purchase_price, price, math.floor(price + 4.20) + 0.95, color_item.nos_flag, '3.61', '1500', '0'] rows.append(row) return rows # Global vars logger = None in_folder = None out_folder = None out2_folder = None processed_folder = None error_folder = None ref_folder = None def _get_files_by_extension(root_folder, file_ext_regex): matches = [] for root, _, file_names in os.walk(root_folder): for filename in fnmatch.filter(file_names, "*." + file_ext_regex): matches.append(os.path.join(root, filename)) if matches: matches.sort() return matches def get_zip_files(root_folder): return _get_files_by_extension(root_folder, "[zZ][iI][pP]") def get_xml_files(root_folder): return _get_files_by_extension(root_folder, "[xX][mM][lL]") def setup_logging(log_file): global logger logger = logging.getLogger() logger.setLevel(logging.DEBUG) formatter = logging.Formatter('%(asctime)s - %(name)s - %(levelname)s - %(message)s') std_out = logging.StreamHandler(sys.stdout) std_out.setLevel(logging.DEBUG) std_out.setFormatter(formatter) logger.addHandler(std_out) file_handler = logging.FileHandler(log_file) file_handler.setFormatter(formatter) file_handler.setLevel(logging.DEBUG) logger.addHandler(file_handler) def teardown_logger(): # global logger handlers = logger.handlers for handler in handlers: logger.removeHandler(handler) def unzip_file(zip_file, temp_folder): # global logger success = True try: if zipfile.is_zipfile(zip_file): current_zip = zipfile.ZipFile(zip_file, 'r') current_zip.extractall(temp_folder) current_zip.close() else: logger.warning("Skipped {0}. Not a ZIP file.".format(zip_file)) success = False except Exception as ex: logger.error("Error occurred unzipping {0}".format(zip_file)) logger.error(ex) success = False return success def read_brand_ref(file_name): ref_dict = {} # Expects csv file with no header in this format: # ; with codecs.open(file_name, "rb", encoding='utf-8') as f: reader = csv.reader(f, delimiter=';') for row in reader: if len(row) >= 2: ref_dict[row[0]] = row[1] return ref_dict def read_brand_substitution_ref(file_name): ref_dict = {} # Expects csv file with no header in this format: # ; with codecs.open(file_name, "rb", encoding='utf-8') as f: reader = csv.reader(f, delimiter=';') for row in reader: if len(row) >= 2: ref_dict[row[0]] = row[1] return ref_dict def read_xml(in_file): with codecs.open(in_file, "r", encoding='utf-16le') as f: lines = f.read() return lines def write_csv(file_name, mode, rows): logger.debug("Writing CSV file %s", file_name) with codecs.open(file_name, mode, encoding='utf-8') as of: writer = csv.writer(of, delimiter=';') for row in rows: writer.writerow(row) def convert_xml_to_csv(xml_text, ean_set, brand_dict, brand_subst_dict, add_header): catalog_parser = CatalogParser(ean_set) parser = ET.XMLParser(target=catalog_parser, encoding='utf-16le') parser.feed(xml_text) return catalog_parser.get_csv(brand_dict, brand_subst_dict, add_header), catalog_parser.get_csv1(add_header) def sorted_ls(path): return list(sorted(os.listdir(path), reverse=True)) def prepend_year_filename(xml_folder): for xml_filename in os.listdir(xml_folder): try: year = str(2000 + int(os.path.splitext(xml_filename)[0][-2:])) except Exception as ex: year = '0000' new_xml_filename = year + xml_filename try: print("Original {}, New {} ".format(xml_filename, new_xml_filename)) shutil.move(os.path.join(xml_folder, xml_filename), os.path.join(xml_folder, new_xml_filename)) except Exception as ex: print(ex) logging.error(ex) def process_xml_files(temp_folder, csv_file_name, csv_in_file_name, csv1_file_name, brand_dict, brand_subst_dict): first_file = True mode = "w" ean_set = set() # Prepend year to filename to allow sorting from newest to oldest prepend_year_filename(temp_folder) # Get a list of all XML files in temp folder. xml_file_list = sorted_ls(temp_folder) for xml_file in xml_file_list: logger.debug("Reading XML file {0}".format(xml_file)) # current_folder = None # read the file and extract the xml fields into lists xml_content = read_xml(os.path.join(temp_folder, xml_file)) csv_content, csv_content1 = convert_xml_to_csv(xml_content, ean_set, brand_dict, brand_subst_dict, first_file) write_csv(csv_file_name, mode, csv_content) write_csv(csv_in_file_name, mode, csv_content) write_csv(csv1_file_name, mode, csv_content1) # # Don't write the header again if first_file: first_file = False mode = "a" def delete_temp_files(folder): # global logger print('Deleting temp files') for current_file in os.listdir(folder): current_file_path = os.path.join(folder, current_file) try: if os.path.isfile(current_file_path): os.unlink(current_file_path) except Exception as ex: logger.error(ex) def process(): global logger, in_folder, out_folder, out2_folder, process_folder, error_folder, ref_folder zip_file_list = get_zip_files(in_folder) brand_dict = read_brand_ref(os.path.join(ref_folder, 'REF_Brands.csv')) brand_subst_dict = read_brand_ref(os.path.join(ref_folder, 'REF_Brandsersetzung.csv')) csv_file_name = os.path.join(out_folder, 'ArtikelAnlage_Intersport.csv') csv1_file_name = os.path.join(out_folder, 'ArtikelAnlage_Intersport1.csv') current_folder = None # Create temp directory temp_folder = tempfile.mkdtemp() # Unzip and process files one by one for source_file in zip_file_list: try: logger.debug("Started processing {0}".format(source_file)) # Figure out what subfolder we're in rel_path = os.path.relpath(source_file, in_folder) subfolder_name = os.path.split(rel_path)[0] if not subfolder_name: # We're in IN subfolder_name = 'DUMMY' if not current_folder: current_folder = subfolder_name if current_folder and (subfolder_name != current_folder): if current_folder != 'DUMMY': dest_folder = os.path.join(out_folder, current_folder) else: dest_folder = out_folder if not os.path.exists(dest_folder): try: os.makedirs(dest_folder) except Exception as ex: logging.error(ex) # Abort raise Exception("Could not create error folder {0}".format(error_folder)) csv_file_name = os.path.join(dest_folder, 'ArtikelAnlage_Intersport.csv') csv_in_file_name = os.path.join(out2_folder, subfolder_name + '_ArtikelAnlage_Intersport.csv') csv1_file_name = os.path.join(dest_folder, 'ArtikelAnlage_Intersport1.csv') process_xml_files(temp_folder, csv_file_name, csv_in_file_name, csv1_file_name, brand_dict, brand_subst_dict) delete_temp_files(temp_folder) current_folder = subfolder_name # first_file = True # Extract contents to temp folder. Function returns False if it failed. if not unzip_file(source_file, temp_folder): continue if current_folder and source_file == zip_file_list[-1]: # Process last file if current_folder != 'DUMMY': dest_folder = os.path.join(out_folder, current_folder) else: dest_folder = out_folder if not os.path.exists(dest_folder): try: os.mkdir(dest_folder) except Exception as ex: logging.error(ex) # Abort raise Exception("Could not create error folder {0}".format(error_folder)) csv_file_name = os.path.join(dest_folder, 'ArtikelAnlage_Intersport.csv') csv_in_file_name = os.path.join(out2_folder, subfolder_name + '_ArtikelAnlage_Intersport.csv') csv1_file_name = os.path.join(dest_folder, 'ArtikelAnlage_Intersport1.csv') process_xml_files(temp_folder, csv_file_name, csv_in_file_name, csv1_file_name, brand_dict, brand_subst_dict) delete_temp_files(temp_folder) # Move the zip file to the archive folder new_file_name = os.path.join(process_folder, source_file[len(in_folder) + 1:]) if not os.path.exists(os.path.dirname(new_file_name)): os.makedirs(os.path.dirname(new_file_name)) shutil.move(source_file, new_file_name) except Exception as ex: logger.error("Failed processing file {0} in process()".format(source_file)) logger.error(ex) err_file_name = os.path.join(error_folder, source_file[len(in_folder) + 1:]) if not os.path.exists(os.path.dirname(err_file_name)): os.makedirs(os.path.dirname(err_file_name)) if os.path.exists(source_file): shutil.move(source_file, err_file_name) try: # remove temp dir and content if temp_folder and os.path.exists(temp_folder): shutil.rmtree(temp_folder) except Exception as ex: logger.error(ex) def main(): global in_folder, out_folder, out2_folder, process_folder, error_folder, ref_folder # data_root = r"/home/holger/git/bitbucket/thelen/data/" data_root = r"/ftproot/" # data_root = r"c:/temp/thelen/ " project_folder = os.path.join(data_root, "Matching_Intersport_Stammdaten") # setup sub folder paths in_folder = os.path.join(project_folder, "in") out_folder = os.path.join(project_folder, "out") out2_folder = os.path.join(data_root, "Matching_Plenty_ID_Barcode", "in") process_folder = os.path.join(project_folder, "processed") error_folder = os.path.join(project_folder, "error") log_folder = os.path.join(project_folder, "log") ref_folder = os.path.join(project_folder, "ref") files = os.listdir(in_folder) num_files = len(files) print("Found {0} files...".format(num_files)) if num_files == 0: return log_filename = 'convert_intersport-' + time.strftime("%Y%m%d-%H%M%S") + '.log' log_file = os.path.join(log_folder, log_filename) setup_logging(log_file) process() print("Done.") if __name__ == "__main__": main()