#!/usr/bin/env python __author__ = 'Holger Knust' 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.eans = eans_set def start(self, tag, attrib): 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) 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] else: return text else: return '' def get_csv(self, brand_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']) 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 = ['SV24_' + model.model_number + '_' + color.sup_color_number, model.catalog_name, model.model_number, model.brand_number, model_desc.replace(';',''), model_long_desc.replace(';',''), model.fedas, model.vat, color.sup_color_number, color.ide_color_code,color.sup_color_name,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 + 2.92,2), math.floor(price + 2.92) + 0.95, color_item.nos_flag, color_item.net_flag, brand, '2', '299', '0', brand] rows.append(row) return rows def get_csv1(self, brand_dict, 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 + 2.92) + 0.95, color_item.nos_flag, '2', '299', '0'] rows.append(row) return rows # Global vars logger = None in_folder = None out_folder = None processed_folder = None error_folder = None ref_folder = None def _get_files_by_extension(root_folder, file_ext_regex): matches = [] for root, dir_names, 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: # print(row) 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): 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, 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, add_header), \ catalog_parser.get_csv1(brand_dict, add_header) def process(): global logger, in_folder, out_folder, process_folder, error_folder, ref_folder zip_file_list = get_zip_files(in_folder) ean_dict = set() first_file = True mode = "w" brand_dict = read_brand_ref(os.path.join(ref_folder, 'REF_Brands.csv')) csv_file_name = os.path.join(out_folder, 'ArtikelAnlage_Intersport.csv') csv1_file_name = os.path.join(out_folder, 'ArtikelAnlage_Intersport1.csv') # 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) if len(os.path.split(rel_path)) == 2: # We're in a subfolder subfolder_name = os.path.split(rel_path)[0] dest_folder = os.path.join(out_folder, subfolder_name) 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') csv1_file_name = os.path.join(dest_folder, 'ArtikelAnlage_Intersport1.csv') else: # We're in in csv_file_name = os.path.join(out_folder, 'ArtikelAnlage_Intersport.csv') csv1_file_name = os.path.join(out_folder, 'ArtikelAnlage_Intersport1.csv') # Create temp directory temp_folder = tempfile.mkdtemp() # Extract contents to temp folder. Function returns False if it failed. if not unzip_file(source_file, temp_folder): continue # Get a list of all CSV files in temp folder. xml_file_list = get_xml_files(temp_folder) for xml_file in xml_file_list: logger.debug("Reading XML file {0}".format(xml_file)) # read the file and extract the xml fields into lists xml_content = read_xml(xml_file) csv_content,csv_content1 = convert_xml_to_csv(xml_content, ean_dict, brand_dict, first_file) write_csv(csv_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" # 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)) shutil.move(source_file, err_file_name) finally: # remove temp dir and content if temp_folder and os.path.exists(temp_folder): shutil.rmtree(temp_folder) def main(): global in_folder, out_folder, process_folder, error_folder, ref_folder # data_root = r"/home/holger/git/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") 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_file = os.path.join(log_folder, 'convert_intersport-' + time.strftime("%Y%m%d-%H%M%S") + '.log') setup_logging(log_file) process() print("Done.") if __name__ == "__main__": main()