import custom_funcs as cf
import pandas as pd
path = cf.LookUp('simaris').raw()
path_processed = cf.LookUp('simaris').processed()
# list all files into the folder
from os import walk
raw_files = []
for (dirpath, dirnames, filenames) in walk(path):
raw_files.extend(filenames)
break
raw_files = raw_files[:-1] # remove 01_discounts.xlsx from the list
print(raw_files)
for file_to_clean in raw_files:
df = pd.read_excel(path + file_to_clean)
df.dropna(axis=1, how='all', inplace=True)
df.to_csv(path_processed + file_to_clean[:-4] + 'csv',
index=False,
encoding='utf-8')
print(file_to_clean + ' was processed and converted to csv format. [OK]')
import custom_funcs as cf
import pandas as pd
from sqlalchemy import create_engine
# list all files to upload
from os import walk
path = cf.LookUp('sap').processed()
file_name_list = []
for (dirpath, dirnames, filenames) in walk(path):
file_name_list.extend(filenames)
break
print(file_name_list)
# insert a table on sap DB
db_dir_sap = cf.LookUp('sap').db_path()
conn_sap = create_engine('sqlite:///' + db_dir_sap)
for file_name in file_name_list:
file_path = path + file_name
df = pd.read_csv(file_path, encoding='utf-8', sep=',')
df.to_sql(file_name[:-4], con=conn_sap, if_exists="replace", index=False)
#conn_sap.execute("SELECT * FROM '01_discount'").fetchall()
print(file_name[:-4] + ' uploaded to db complete! [OK]')
import custom_funcs as cf
import pandas as pd
path = cf.LookUp('sap').raw()
path_processed = cf.LookUp('sap').processed()
# list all files into the folder
from os import walk
raw_files = []
for (dirpath, dirnames, filenames) in walk(path):
raw_files.extend(filenames)
break
print(raw_files)
# ------------- KASTA ML Prep. ------------------------ #
nxair_w_1 = pd.read_excel(path + raw_files[2], header=0)
nxair_w_1.drop(nxair_w_1.columns[[2,8,10,12,13]], axis=1, inplace=True)
columns = ['include','level','material','pur_ty','cost_block','rev','qty','mu','description']
nxair_w_1.columns = columns
nxair_w_1['level'] = nxair_w_1['level'].str.replace('.','')
#nxair_w_1.drop(nxair_w_1.index[0], inplace=True)
nxair_w_1.to_csv(path_processed + raw_files[2][:-5] + '.csv', index=False, encoding='utf-8')
raw_files[3]
# ------------- ME80FN Prep. ------------------------ #
compras_sap = pd.read_excel(path + raw_files[3], header=0)
compras_sap.to_csv(path_processed + raw_files[3][:-5] + '.csv' ,index=False, encoding='utf-8')
import custom_funcs as cf
import pandas as pd
from sqlalchemy import create_engine
db_dir = cf.LookUp('simaris').db_path()
connection = create_engine('sqlite:///' + db_dir)
connection.table_names()
query = "SELECT * FROM '02_siemens_devices' WHERE articlenumber LIKE '6SL3202%'"
pd.read_sql(query, connection).head()
# ex = "SELECT * FROM '06_single_parts' WHERE articlenumber LIKE '8PQ%' AND import = 1"
# pd.read_sql(ex, connection).head()
# df = pd.read_sql(ex, connection)
# df['currency_sign'].unique()
# df[df['currency_sign'] == 'USD']
#script_test.py
import sys, os
cwd = os.getcwd() + '/material-cost-monitor/src'
sys.path.insert(1, cwd)
#import main custom functions
import custom_funcs as cf
import database as db
#---------------------------
# - just a test to call a module --- #
cf.SendMessage()
# - test to return an output from module --- #
dir = cf.LookUp('simaris').raw() #< -- localiza diretório da pasta do ex. simaris
dir
db_dir = cf.LookUp('nxtools').db_path()
db_dir
# - test to connect with DB and execute commands --- #
conn = db.conn(db_dir)
c = conn.cursor()
c.execute("select name from sqlite_master where type = 'table'")
##c.execute("select * from '01_discount where 'articlenumber' = '8PQ%'")
print(c.fetchall())
conn.commit()
conn.close()
import custom_funcs as cf
import pandas as pd
from sqlalchemy import create_engine
# list all files to upload
from os import walk
path = cf.LookUp('simaris').processed()
file_name_list = []
for (dirpath, dirnames, filenames) in walk(path):
file_name_list.extend(filenames)
break
print(file_name_list)
# insert a table on Simaris DB
db_dir_simaris = cf.LookUp('simaris').db_path()
conn_simaris = create_engine('sqlite:///' + db_dir_simaris)
for file_name in file_name_list:
file_path = path + file_name
df = pd.read_csv(file_path, encoding='utf-8', sep=',')
df.to_sql(file_name[:-4], con=conn_simaris, if_exists="replace", index=False)
#conn_simaris.execute("SELECT * FROM '01_discount'").fetchall()
print(file_name[:-4] + ' uploaded to db complete! [OK]')
import custom_funcs as cf
import pandas as pd
#check data (PMD vs. SCF) for materials below.
#3WL1116-2CB68-4GA4-ZK07+R16+R21+R30
#3WL1225-2EB78-4GA4-ZK07+R16+R21+R30
#3WL1340-4EB68-4GA4-ZK07+R16+R21+R30
#Note: consedering ICBs from LP the landed factor should be include.
dir = cf.LookUp('simaris').processed()
devices = pd.read_csv(dir + '02_siemens_devices.csv', encoding='utf-8')
#devices[devices['articlenumber'].str.contains('3WL1116-2')].to_excel('simulatio.xlsx')
discount = pd.read_csv(dir + '01_discount.csv', encoding='utf-8')
discount[discount['discount_origin'].str.contains('3_W_L')]
import custom_funcs as cf
import pandas as pd
from sqlalchemy import create_engine
# list all files to upload
from os import walk
path = cf.LookUp('nxtools').processed()
file_name_list = []
for (dirpath, dirnames, filenames) in walk(path):
file_name_list.extend(filenames)
break
print(file_name_list)
# insert a table on nxtools DB
db_dir_nxtools = cf.LookUp('nxtools').db_path()
conn_nxtools = create_engine('sqlite:///' + db_dir_nxtools)
for file_name in file_name_list:
file_path = path + file_name
df = pd.read_csv(file_path, encoding='utf-8', sep=',')
df.to_sql(file_name[:-4], con=conn_nxtools, if_exists="replace", index=False)
#conn_nxtools.execute("SELECT * FROM '01_discount'").fetchall()
print(file_name[:-4] + ' uploaded to db complete! [OK]')
