247 lines
8.0 KiB
Python
247 lines
8.0 KiB
Python
#################################################################################
|
|
#
|
|
# P4U Script for converting Baskets from Digikey / Mouser to P4U format
|
|
# Created by Jonas Lingua & Noah Piqué
|
|
# Date: 02.10.2025
|
|
# Version 1.6
|
|
#
|
|
################################################################################
|
|
|
|
|
|
from xlrd import open_workbook
|
|
from openpyxl import load_workbook
|
|
import sys
|
|
import csv
|
|
import os
|
|
|
|
printout = ""
|
|
def prints(string):
|
|
global printout
|
|
printout += str(string) + "<br />"
|
|
print(string)
|
|
|
|
# converting mouser xls file to array
|
|
def load_mouser(file_mouser, fast=False):
|
|
try:
|
|
wb = open_workbook(file_mouser)
|
|
except:
|
|
prints("Error(Mouser): could not open the file")
|
|
return 0
|
|
|
|
prints('Reading File Succeded')
|
|
|
|
sh = wb.sheet_by_index(0)
|
|
|
|
col = sh.col_values(0)
|
|
offset_mouser = col.index(1)
|
|
if offset_mouser == -1:
|
|
prints('Error(Mouser): did not find a part')
|
|
return 0
|
|
|
|
nparts = col.index('', offset_mouser) - offset_mouser
|
|
if nparts <= -1:
|
|
prints('Error(Mouser): did not the end of the file')
|
|
return 0
|
|
|
|
vertrag = 'Mouser Electronics, Elsenheimerstrasse 11, 80687, München, DE (EUR)'
|
|
if fast:
|
|
vertrag = 'Mouser Electronics (EUR) (Fasttrack)'
|
|
part_mouser = ['', '', vertrag, 'Mouser Electronics', '', '', '', '', '', 820, 0, 'Stück', '', '', '', '', '', '8.1', 0, 'EUR']
|
|
parts_mouser = []
|
|
|
|
for part in range(nparts):
|
|
parts_mouser.append(part_mouser.copy())
|
|
|
|
for x, y in enumerate(range(offset_mouser, offset_mouser + nparts)):
|
|
parts_old = sh.row_values(y, 0, 11)
|
|
parts_mouser[x][0] = str(int(parts_old[0]))
|
|
parts_mouser[x][1] = parts_old[1]
|
|
parts_mouser[x][4] = parts_old[3]
|
|
parts_mouser[x][5] = parts_old[2]
|
|
parts_mouser[x][6] = parts_old[3] + " " + parts_old[2]
|
|
parts_mouser[x][7] = parts_old[5]
|
|
parts_mouser[x][10] = int(parts_old[8])
|
|
w = parts_old[9][-1]
|
|
if w != '€':
|
|
prints('Error: please change mouser currency to euro and retry')
|
|
return 0
|
|
try:
|
|
p = float(parts_old[9][0:-2].replace(',', '.'))
|
|
except ValueError:
|
|
prints("Error: mouser price is not in right format")
|
|
return 0
|
|
parts_mouser[x][18] = p
|
|
|
|
#prints(parts_mouser)
|
|
|
|
return parts_mouser
|
|
|
|
|
|
# converting digikey csv file to array
|
|
def load_digikey(file_digikey, fast=False):
|
|
try:
|
|
# Use utf-8-sig to tolerate a BOM and newline='' for csv correctness
|
|
with open(file_digikey, 'r', newline='', encoding='utf-8-sig') as CSV:
|
|
read_csv = csv.reader(CSV, delimiter=",")
|
|
read_csv = list(read_csv)
|
|
except Exception as e:
|
|
prints("Error: could not open the file: " + str(e))
|
|
return 0
|
|
|
|
prints('Reading File Succeded')
|
|
|
|
offset_digikey = -1
|
|
for i, row in enumerate(read_csv):
|
|
# guard against empty rows
|
|
if row and row[0] == '1':
|
|
offset_digikey = i
|
|
break
|
|
|
|
if offset_digikey <= -1:
|
|
prints('Error(Digikey): did not find the start of the file')
|
|
return 0
|
|
|
|
# Collect data rows after the offset, skipping empty/trailing blank rows
|
|
data_rows = [r for r in read_csv[offset_digikey:] if r and any((c.strip() if isinstance(c, str) else True) for c in r)]
|
|
nparts = len(data_rows)
|
|
|
|
vertrag = 'Digi-Key Corporation, Brooks Avenue South 701, 56701, Thief River Falls, US (CHF)'
|
|
if fast:
|
|
vertrag = 'Digi-Key Corporation (CHF) (Fasttrack)'
|
|
# Index,"Menge","Teilenummer","Hersteller-Teilenummer","Beschreibung","Kundenreferenz","Verfügbar","Lieferrückstände","Stückpreis","Gesamtpreis EUR"
|
|
# Position Artikelnummer Vertrag Lieferant Hersteller Herstellernummer Kurztext Langtext Materialnummer Standardwarengruppe Menge Einheiten ppppp MwSt-Satz Preis Währung
|
|
part_digikey = ['', '', vertrag, 'Digi-Key Corporation', '', '', '', '', '', 820, 0, 'Stück', '', '', '', '', '', '8.1', 0, 'CHF']
|
|
parts_digikey = []
|
|
|
|
for part in range(nparts):
|
|
parts_digikey.append(part_digikey.copy())
|
|
|
|
for x, parts_old in enumerate(data_rows):
|
|
# basic validation: ensure we have enough columns expected by the parser
|
|
if len(parts_old) < 10:
|
|
prints(f"Error(Digikey): malformed data row {x} (expected >=10 columns), got {len(parts_old)}")
|
|
return 0
|
|
|
|
try:
|
|
parts_digikey[x][0] = str(int(parts_old[0]))
|
|
except Exception:
|
|
prints(f"Error(Digikey): invalid index value on data row {x}: '{parts_old[0]}'")
|
|
return 0
|
|
|
|
parts_digikey[x][1] = parts_old[2]
|
|
parts_digikey[x][5] = parts_old[3]
|
|
parts_digikey[x][6] = parts_old[3]
|
|
parts_digikey[x][7] = parts_old[4]
|
|
|
|
try:
|
|
parts_digikey[x][10] = int(parts_old[1])
|
|
except Exception:
|
|
prints(f"Error(Digikey): invalid quantity on data row {x}: '{parts_old[1]}'")
|
|
return 0
|
|
|
|
w = parts_old[9][-3:]
|
|
if w != 'Fr.':
|
|
prints('Error: please change digikey currency to chf and retry')
|
|
return 0
|
|
|
|
try:
|
|
p = float(parts_old[8].replace(',', '.'))
|
|
except ValueError:
|
|
prints(f"Error: digikey price is not in right format on data row {x}: '{parts_old[8]}'")
|
|
return 0
|
|
parts_digikey[x][18] = p
|
|
|
|
# prints(parts_digikey)
|
|
|
|
return parts_digikey
|
|
|
|
load_functions = {'mouser': load_mouser, 'digikey': load_digikey}
|
|
fasttrack = ['mouser', 'digikey']
|
|
|
|
def p4u(args):
|
|
global printout
|
|
printout = ""
|
|
|
|
if len(args) <= 2:
|
|
prints("Error: please specify a type(mouser/digikey) and a file")
|
|
return 0
|
|
else:
|
|
file_type = args[1]
|
|
filename = os.path.basename(args[2])
|
|
file = os.path.join('uploads', filename)
|
|
fast = ''
|
|
if len(args) == 4:
|
|
fast = args[3]
|
|
|
|
if not os.path.exists(file):
|
|
prints('Error: could not open the file. Tried: ' + file + '.')
|
|
prints('Please upload the file through the website so it appears in the uploads/ folder.')
|
|
return 0
|
|
|
|
parts = []
|
|
|
|
if file_type in load_functions.keys():
|
|
prints("Found load function for " + file_type)
|
|
# Call the loader and validate its return value instead of blindly
|
|
# extending, which raises when the loader returns 0 (an int).
|
|
try:
|
|
if fast == 'fasttrack' and file_type in fasttrack:
|
|
prints('Fasttrack Order found')
|
|
result = load_functions[file_type](file, True)
|
|
else:
|
|
prints('No Fasttrack')
|
|
result = load_functions[file_type](file)
|
|
except Exception as e:
|
|
prints("Error: loader raised an exception: " + str(e))
|
|
return 0
|
|
|
|
if not result:
|
|
prints('Error: loader returned no data (check earlier messages)')
|
|
return 0
|
|
|
|
if not isinstance(result, list):
|
|
prints('Error: loader returned unexpected type: ' + str(type(result)))
|
|
return 0
|
|
|
|
parts.extend(result)
|
|
else:
|
|
prints("Error: wrong type of file(mouser/digikey)")
|
|
return 0
|
|
|
|
if not parts:
|
|
prints('Error: no data recieved (empty files?)(wrong filename?)')
|
|
return 0
|
|
|
|
new_file = './Warenkorb_' + file_type + '.xlsx'
|
|
prints('Writing to new File: ' + new_file)
|
|
|
|
try:
|
|
wb_neu = load_workbook('./templates/Warenkorb.xlsx')
|
|
except:
|
|
prints("Error: template Warenkorb.xlsx is missing, please put it back in the templates folder")
|
|
return 0
|
|
|
|
sh_neu = wb_neu.active
|
|
|
|
offset = 0
|
|
|
|
for i, row in enumerate(sh_neu.values):
|
|
if (row[0]) == 'Position':
|
|
offset = i + 1
|
|
|
|
for i in range(len(parts)):
|
|
for j in range(len(parts[i])):
|
|
sh_neu.cell(offset + i + 1, j + 1, parts[i][j])
|
|
|
|
wb_neu.save('./exports/' + new_file)
|
|
|
|
prints('Finished')
|
|
return new_file
|
|
|
|
if __name__ == '__main__':
|
|
|
|
if(not p4u(sys.argv)):
|
|
print(printout)
|
|
sys.exit(1)
|
|
print("Done")
|
|
#print(printout) |