# import libraries import pandas as pd import os import numpy as np from dbfread import DBF from pandas import DataFrame # get a list of tables to iterate through path = '//166.2.126.25/teui1/4_Derek/Salmon_Challis_LTA_Mapping/Zonal_Stats' out_path = '//166.2.126.25/teui1/4_Derek/Salmon_Challis_LTA_Mapping/Zonal_Stats/' for root, dirs, files in os.walk(path): for file in files: if file.endswith(".dbf"): print(file) # convert the dbf to pandas dataframe tempTable = DBF(os.path.join(root, file)) tempFrame = DataFrame(iter(tempTable)) file_name = os.path.join(root, file) data = tempFrame #pd.read_csv(file_name) df = data.iloc[:, 1:] for column in df.columns[0:]: #print(tempFrame[column]) df.loc[:, column] /= 4046.86 #print(tempFrame[column]) out_name = file.split('.')[0] df.to_csv(path_or_buf=out_path + out_name + '_acres.csv', sep=',') path = '//166.2.126.25/teui1/4_Derek/Salmon_Challis_LTA_Mapping/Zonal_Stats' out_path = '//166.2.126.25/teui1/4_Derek/Salmon_Challis_LTA_Mapping/Zonal_Stats/' for root, dirs, files in os.walk(path):
def import_account(self, file_dir, files, journal_data): """Import accounts from *_acf*.dbf files. The data in those files are the type, name, code and currency of the account as well as wether it is used as a default central account for partners or taxes. :return: (account_data, account_central, account_deprecated_ids, account_tax) account_data is a dictionary whose keys are the Winbooks account references and the values are the account ids in Odoo. account_central is a dictionary whose keys are the Winbooks central account references and the values are the account ids in Odoo. account_deprecated_ids is a recordset of account that need to be deprecated after the import. account_tax is a dictionary whose keys are the Winbooks account references and the values are the Winbooks tax references. """ def manage_centralid(account, centralid): "Set account to being a central account" property_name = None account_central[centralid] = account.id if centralid == 'S1': property_name = 'property_account_payable_id' model_name = 'res.partner' if centralid == 'C1': property_name = 'property_account_receivable_id' model_name = 'res.partner' if centralid == 'V01': property_name = 'property_tax_receivable_account_id' model_name = 'account.tax.group' if centralid == 'V03': property_name = 'property_tax_payable_account_id' model_name = 'account.tax.group' if property_name: self.env['ir.property']._set_default(property_name, model_name, account, self.env.company) _logger.info("Import Accounts") account_data = {} account_central = {} account_tax = {} recs = [] grouped = collections.defaultdict(list) AccountAccount = self.env['account.account'] ResCurrency = self.env['res.currency'] AccountGroup = self.env['account.group'] account_types = [ {'min': 100, 'max': 160, 'id': 'account.data_account_type_equity'}, {'min': 160, 'max': 200, 'id': 'account.data_account_type_non_current_liabilities'}, {'min': 200, 'max': 280, 'id': 'account.data_account_type_non_current_assets'}, {'min': 280, 'max': 290, 'id': 'account.data_account_type_fixed_assets'}, {'min': 290, 'max': 400, 'id': 'account.data_account_type_current_assets'}, {'min': 400, 'max': 401, 'id': 'account.data_account_type_receivable', 'reconcile': True}, {'min': 401, 'max': 420, 'id': 'account.data_account_type_current_assets'}, {'min': 420, 'max': 440, 'id': 'account.data_account_type_current_liabilities'}, {'min': 440, 'max': 441, 'id': 'account.data_account_type_payable', 'reconcile': True}, {'min': 441, 'max': 490, 'id': 'account.data_account_type_current_liabilities'}, {'min': 490, 'max': 492, 'id': 'account.data_account_type_current_assets'}, {'min': 492, 'max': 500, 'id': 'account.data_account_type_current_liabilities'}, {'min': 500, 'max': 600, 'id': 'account.data_account_type_liquidity'}, {'min': 600, 'max': 700, 'id': 'account.data_account_type_expenses'}, {'min': 700, 'max': 822, 'id': 'account.data_account_type_revenue'}, {'min': 822, 'max': 860, 'id': 'account.data_account_type_expenses'}, ] for file_name in files: for rec in DBF(join(file_dir, file_name), encoding='latin').records: recs.append(rec) for item in recs: grouped[item.get('TYPE')].append(item) rec_number_list = [] account_data_list = [] journal_centered_list = [] is_deprecated_list = [] account_deprecated_ids = self.env['account.account'] for key, val in grouped.items(): if key == '3': # 3=general account, 9=title account for rec in val: account = AccountAccount.search( [('code', '=', rec.get('NUMBER')), ('company_id', '=', self.env.company.id)], limit=1) if account: account_data[rec.get('NUMBER')] = account.id rec['CENTRALID'] and manage_centralid(account, rec['CENTRALID']) if not account and rec.get('NUMBER') not in rec_number_list: data = { 'code': rec.get('NUMBER'), 'name': rec.get('NAME11'), 'group_id': AccountGroup.search([('code_prefix_start', '=', rec.get('CATEGORY'))], limit=1).id, 'currency_id': ResCurrency.search([('name', '=', rec.get('CURRENCY'))], limit=1).id } if rec.get('VATCODE'): account_tax[rec.get('NUMBER')] = rec.get('VATCODE') try: account_code = int(rec.get('NUMBER')[:3]) except Exception: _logger.warning(_('%s is not a valid account number for %s.') % (rec.get('NUMBER'), rec.get('NAME11'))) account_code = 300 # set Current Asset by default for deprecated accounts for account_type in account_types: if account_code in range(account_type['min'], account_type['max']): data.update({ 'user_type_id': self.env.ref(account_type['id']).id, 'reconcile': account_type.get('reconcile', False) }) break # fallback for accounts not in range(100000,860000) if not data.get('user_type_id'): data['user_type_id'] = self.env.ref('account.data_account_type_other_income').id account_data_list.append(data) rec_number_list.append(rec.get('NUMBER')) journal_centered_list.append(rec.get('CENTRALID')) is_deprecated_list.append(rec.get('ISLOCKED')) if len(account_data_list) % 100 == 0: _logger.info("Advancement: {}".format(len(account_data_list))) account_ids = AccountAccount.create(account_data_list) for account, rec_number, journal_centred, is_deprecated in zip(account_ids, rec_number_list, journal_centered_list, is_deprecated_list): account_data[rec_number] = account.id # create the ir.property if this is marked as a default account for something journal_centred and manage_centralid(account, journal_centred) # we can't deprecate the account now as we still need to add lines with this account # keep the list in memory so that we can deprecate later if is_deprecated: account_deprecated_ids += account return account_data, account_central, account_deprecated_ids, account_tax
def import_vat(self, file_dir, files, account_central): """Import the taxes from *codevat.dbf files. The data in thos files are the amount, type, including, account and tags of the taxes. :return: a dictionary whose keys are the Winbooks taxes references and the values are the taxes ids in Odoo. """ _logger.info("Import VAT") vatcode_data = {} treelib = {} AccountTax = self.env['account.tax'] tags_cache = {} def get_tags(string): "Split the tags, create if it doesn't exist and return m2m command for creation" tag_ids = self.env['account.account.tag'] if not string: return tag_ids indexes = [i for i, x in enumerate(string) if x in ('+', '-')] + [len(string)] for i in range(len(indexes) - 1): tag_name = string[indexes[i]: indexes[i + 1]] tag_id = tags_cache.get(tag_name, False) if not tag_id: tag_id = self.env['account.account.tag'].search([('name', '=', tag_name), ('applicability', '=', 'taxes')]) tags_cache[tag_name] = tag_id if not tag_id: tag_id = self.env['account.account.tag'].create({'name': tag_name, 'applicability': 'taxes', 'country_id': self.env.company.country_id.id}) tag_ids += tag_id return [(4, id, 0) for id in tag_ids.ids] data_list = [] code_list = [] for file_name in files: for rec in DBF(join(file_dir, file_name), encoding='latin').records: treelib[rec.get('TREELEVEL')] = rec.get('TREELIB1') if not rec.get('USRCODE1'): continue tax_name = " ".join([treelib[x] for x in [rec.get('TREELEVEL')[:i] for i in range(2, len(rec.get('TREELEVEL')) + 1, 2)]]) tax = AccountTax.search([('company_id', '=', self.env.company.id), ('name', '=', tax_name), ('type_tax_use', '=', 'sale' if rec.get('CODE')[0] == '2' else 'purchase')], limit=1) if tax.amount != rec.get('RATE') if rec.get('TAXFORM') else 0.0: tax.amount = rec.get('RATE') if rec.get('TAXFORM') else 0.0 if tax: vatcode_data[rec.get('CODE')] = tax.id else: data = { 'amount_type': 'percent', 'name': tax_name, 'company_id': self.env.company.id, 'amount': rec.get('RATE') if rec.get('TAXFORM') else 0.0, 'type_tax_use': 'sale' if rec.get('CODE')[0] == '2' else 'purchase', 'price_include': False if rec.get('TAXFORM') or rec.get('BASFORM') == 'BAL' else True, 'refund_repartition_line_ids': [ (0, 0, {'repartition_type': 'base', 'factor_percent': 100.0, 'tag_ids': get_tags(rec.get('BASE_CN')), 'company_id': self.env.company.id}), (0, 0, {'repartition_type': 'tax', 'factor_percent': 100.0, 'tag_ids': get_tags(rec.get('TAX_CN')), 'company_id': self.env.company.id, 'account_id': account_central.get(rec.get('ACCCN1'), False)}), ], 'invoice_repartition_line_ids': [ (0, 0, {'repartition_type': 'base', 'factor_percent': 100.0, 'tag_ids': get_tags(rec.get('BASE_INV')), 'company_id': self.env.company.id}), (0, 0, {'repartition_type': 'tax', 'factor_percent': 100.0, 'tag_ids': get_tags(rec.get('TAX_INV')), 'company_id': self.env.company.id, 'account_id': account_central.get(rec.get('ACCINV1'), False)}), ], } if rec.get('ACCCN2'): data['refund_repartition_line_ids'] += [(0, 0, {'repartition_type': 'tax', 'factor_percent': -100.0, 'tag_ids': [], 'company_id': self.env.company.id, 'account_id': account_central.get(rec.get('ACCCN2'), False)})] if rec.get('ACCINV2'): data['invoice_repartition_line_ids'] += [(0, 0, {'repartition_type': 'tax', 'factor_percent': -100.0, 'tag_ids': [], 'company_id': self.env.company.id, 'account_id': account_central.get(rec.get('ACCINV2'), False)})] data_list.append(data) code_list.append(rec.get('CODE')) if len(data_list) % 100 == 0: _logger.info("Advancement: {}".format(len(data_list))) tax_ids = AccountTax.create(data_list) for tax_id, code in zip(tax_ids, code_list): vatcode_data[code] = tax_id.id return vatcode_data
num dicionário """ #início class MyFieldParser(FieldParser): def parse(self, field, data): try: return FieldParser.parse(self, field, data) except ValueError: return InvalidValue(data) for f1 in files: print(f1) dbf = DBF(f1, parserclass=MyFieldParser) for i, record in enumerate(dbf): for name, value in record.items(): if isinstance(value, InvalidValue): print('records[{}][{!r}] == {!r}'.format(i, name, value)) frame_global = DataFrame(iter(dbf)) di = {} try: for i in range(np.shape(frame_global['FLOW_M'].tolist())[0]): if i % 10000 == 0: print(i) di[frame_global['DATA'][i], frame_global['HORA_I'][i]] = frame_global['FLOW_M'][i]
def isNum(x): try: float(x) return True except ValueError: return False #Kv should be in rpms/V #current should be in amps #resistance should be in ohms #weight should be in ounces connection = sql.connect("components.db") motoCalcFile = DBF("Motors/MOTOR8.DBF") cursor = connection.cursor() cursor.execute("drop table Motors") # The database is refreshed every time cursor.execute("""CREATE TABLE Motors (id INTEGER PRIMARY KEY, name VARCHAR(40), kv FLOAT, gear_ratio FLOAT default 1.0, resistance FLOAT, no_load_current FLOAT default 0.0, weight FLOAT default -1.0);""") print("Reading MotoCalc Database") for record in motoCalcFile: print(record)
for j in os.listdir(n_path): if "dbf" in j:dbf_path.append(str(n_path + "/" +j)) # We will next read through dbf files and record everything in python lists. As shown below, this data includes 22 columns. # In[2]: # open shapefiles and save the data into python lists from dbfread import DBF import pandas as pd records = [] for n, i in enumerate(dbf_path): idr = i.split("/")[-1][:-4] if not "pt" in idr:continue for record in DBF(i): record.update({"id":idr}) records.append(record) columns = [] for n,r in enumerate(records): if n==1: for j in r: columns.append(j) print columns # In[3]: # convert the lists to Pandas DataFrame. We have 22 columns and 128930 rows recs = [] for n,r in enumerate(records): lst = []
def make_maps(force_download=False): CID_URL = 'http://www.datasus.gov.br/cid10/V2008/downloads/CID10CSV.zip' local_zipfile = download_zip(CID_URL, force_download) with ZipFile(local_zipfile) as zfile: l = zfile.namelist() with zfile.open('CID-10-SUBCATEGORIAS.CSV') as cid_file: cid_map = pd.read_csv(cid_file, sep=';', encoding='iso-8859-1', usecols=['SUBCAT', 'DESCRICAO'], index_col='SUBCAT', squeeze=True) cid_map = (cid_map.index + ': ' + cid_map).str.replace(', ', ' - ') TAB_SIH_URL = 'ftp://ftp.datasus.gov.br/dissemin/publicos/SIHSUS/200801_/Auxiliar/TAB_SIH.zip' local_zipfile = download_zip(TAB_SIH_URL, force_download) with ZipFile(local_zipfile) as zfile: l = zfile.namelist() # br_municip.cnv with zfile.open('br_municip.cnv') as file: mun_map = pd.read_table(file, sep=b'\s{2,}', engine='python', encoding='iso-8859-1', skiprows=1, header=None, skipfooter=3).applymap(func_decode) mun_map[1] = mun_map[1].str.split(' ', 1).str.get(1) mun_map = pd.Series(mun_map[1].tolist(), index=mun_map[2].tolist()) for i, mun in mun_map[mun_map.str.startswith('Município')].items(): for i2 in i.split(','): mun_map[str(i2).zfill(6)] = mun mun_map.drop(i, inplace=True) mun_map = mun_map.to_dict() for i in ['520000', '529999']: mun_map[i] = 'Município ignorado - GO' list_brasilia = [] for i in range(530000, 530009 + 1): list_brasilia.append(str(i)) for i in range(530011, 539999 + 1): list_brasilia.append(str(i)) list_brasilia.append('530010') for i in list_brasilia: mun_map[i] = 'Brasília' list_ign = [] for i in range(1, 9999): list_ign.append(str(i).zfill(6)) list_ign = list_ign + ['000000', '999999'] for i in list_ign: mun_map[i] = 'Ignorado ou exterior' # CARATEND.CNV with zfile.open('CARATEND.CNV') as file: caratend_map = pd.read_table(file, sep=b'\s{2,}', engine='python', encoding='iso-8859-1', skiprows=1, header=None).applymap(func_decode) caratend_map = { '01': 'Eletivo', '02': 'Urgência', '03': 'Acidente no local trabalho ou a serv da empresa', '04': 'Acidente no trajeto para o trabalho', '05': 'Outros tipo de acidente de trânsito', '06': 'Out tp lesões e envenen por agent quím físicos' } #fiz manualmente porque não funcionou o encoding e o decode # COMPLEX2.CNV with zfile.open('COMPLEX2.CNV') as file: comp_map = pd.read_table(file, sep=b'\s{2,}', engine='python', encoding='iso-8859-1', skiprows=1, header=None).applymap(func_decode) comp_map = { '00': 'Não se aplica', '01': 'Atenção básica', '02': 'Média complexidade', '03': 'Alta complexidade', '99': 'Não se aplica' } #fiz manualmente porque não funcionou o encoding e o decode # FINANC.CNV with zfile.open('FINANC.CNV') as file: df = pd.read_table(file, sep=b'\s{2,}', engine='python', encoding='iso-8859-1', skiprows=1, header=None).applymap(func_decode) temp = pd.DataFrame([{ 1: 'Não discriminado', 2: '00' }, { 1: 'Não discriminado', 2: '99' }, { 1: '04 Fundo de Ações Estratégicas e Compensações FAEC', 2: '04' }]) df = pd.concat([df, temp], ignore_index=True).drop(0, axis=1).drop([0, 3]) financ_map = pd.Series(df[1].tolist(), index=df[2].tolist()) # ETNIA with zfile.open('etnia.cnv') as et_file: df_et = pd.read_table(et_file, sep=b'\s{2,}', engine='python', encoding='iso-8859-1', skiprows=1, header=None).applymap(func_decode) ni = pd.DataFrame([{ 1: 'NÃO INFORMADO', 2: '0000' }, { 1: 'NÃO INFORMADO', 2: '9999' }]) df_et = pd.concat([df_et, ni], ignore_index=True).drop(0, axis=1).drop(0) et_map = pd.Series(df_et[1].tolist(), df_et[2].tolist()) # SEX with zfile.open('SEXO.CNV') as sex_file: df = pd.read_table(sex_file, sep=b'\s{2,}', engine='python', skiprows=1, header=None).applymap(func_decode) sex_map = { df.loc[0, 2][0]: df.loc[0, 1], df.loc[0, 2][2]: df.loc[0, 1], df.loc[1, 2]: df.loc[1, 1], df.loc[2, 2][0]: df.loc[2, 1], df.loc[2, 2][2]: df.loc[2, 1] } # UF with zfile.open('br_ufsigla.cnv') as uf: df = pd.read_table( uf, sep=b'\s{2,}', engine='python', skiprows=1, header=None).applymap(func_decode).dropna(axis=1) pe = pd.DataFrame([{1: 'PE', 2: '20'}, {1: 'PE', 2: '26'}]) df = pd.concat([df, pe], ignore_index=True) df.drop(12, inplace=True) uf_map = pd.Series(df[1].tolist(), df[2].tolist()) # CNES list_df = [] list_cnes = [item for item in l if re.search('\S+CNES\S+', item)] for file in list_cnes: zfile.extract(file) dbf = DBF(file, encoding='utf-8') cnes_temp = pd.DataFrame(list(dbf)) list_df.append(cnes_temp) os.unlink(file) cnes_br = pd.concat(list_df, sort=False).drop_duplicates() cnes_br['UF_ZI'] = cnes_br.UF_ZI.map(uf_map) cnes_map = pd.Series(cnes_br.NOMEFANT.tolist(), index=cnes_br.CNES.tolist()) # CBO.dbf file = 'CBO.dbf' zfile.extract(file) dbf = DBF(file, encoding='iso-8859-1') cbo_map = pd.DataFrame(list(dbf)) cbo_map = pd.Series(cbo_map.DS_CBO.tolist(), index=cbo_map.CBO.tolist()) os.unlink(file) # # group_cid_map # group_cid_map = {} # for key in MAP_CIDS: # for v in MAP_CIDS[key]: # group_cid_map[v] = key return sex_map, cnes_map, uf_map, cid_map, et_map, financ_map, cbo_map, comp_map, mun_map, caratend_map
def download_table_dbf(file_name): """ Realiza o download de um arquivo auxiliar de dados do SIA em formato "dbf" ou de uma pasta "zip" que o contém (se a pasta "zip" já não foi baixada), em seguida o lê como um objeto pandas DataFrame e por fim o elimina Parâmetros ---------- file_name: objeto str String do nome do arquivo "dbf" Retorno ------- df: objeto pandas DataFrame Dataframe que contém os dados de um arquivo auxiliar de dados originalmente em formato "dbf" """ if ((file_name == 'CADMUN') or (file_name == 'TABUF')): fname = file_name + '.DBF' ftp = FTP('ftp.datasus.gov.br') ftp.login() ftp.cwd('/dissemin/publicos/SIM/CID10/TABELAS/') ftp.retrbinary(f'RETR {fname}', open(fname, 'wb').write) elif file_name == 'rl_municip_regsaud': folder = 'base_territorial.zip' ftp = FTP('ftp.datasus.gov.br') ftp.login() ftp.cwd('/territorio/tabelas/') ftp.retrbinary(f'RETR {folder}', open(folder, 'wb').write) zip = ZipFile(folder, 'r') fname = file_name + '.dbf' zip.extract(fname) else: folder = 'TAB_SIA.zip' if not os.path.isfile(folder): ftp = FTP('ftp.datasus.gov.br') ftp.login() ftp.cwd('/dissemin/publicos/SIASUS/200801_/Auxiliar/') ftp.retrbinary(f'RETR {folder}', open(folder, 'wb').write) zip = ZipFile(folder, 'r') try: fname = file_name + '.DBF' zip.extract('TAB_DBF/' + fname) except: try: fname = file_name + '.dbf' zip.extract('TAB_DBF/' + fname) except: raise Exception(f'Could not access {file_name}.') if ((file_name == 'CADMUN') or (file_name == 'TABUF') or (file_name == 'rl_municip_regsaud')): dbf = DBF(fname) else: dbf = DBF('TAB_DBF/' + fname, encoding='iso-8859-1') df = pd.DataFrame(iter(dbf)) if ((file_name == 'CADMUN') or (file_name == 'TABUF') or (file_name == 'rl_municip_regsaud')): os.unlink(fname) else: os.unlink('TAB_DBF/' + fname) return df
import sys import csv from dbfread import DBF fname = sys.argv[-1] assert fname.lower().endswith('.dbf') outname = fname.lower()[:-4]+".csv" print("Converting",fname,"to",outname,".") table = DBF(fname) with open(outname,'w',newline='') as f: writer = csv.writer(f) writer.writerow(table.field_names) for record in table: writer.writerow(list(record.values()))
# In[21]: import dbfread # In[22]: from dbfread import DBF # In[23]: tab = DBF("/Users/margaretmccall/Downloads/Assignment Result 4/FAF4DATA_V43.DBF") # In[27]: frame= pd.DataFrame(iter(tab)) # In[28]: frame.head() # In[30]:
from dbfread import DBF import re import config client = config.client query = client.query("SELECT FROM Carrera") #for result in query: # print result table = DBF('KI9119B.dbf', load=True, encoding="latin-1") table2 = DBF('KI9119A.dbf', load=True, encoding="latin-1") #print list(set(table.field_names) - set(table2.field_names)) #print table.field_names #print table2.field_names r = re.compile("S\d") fields = filter(r.match, table.field_names) #print fields #DELETING ALL RECORDS #client.command("TRUNCATE CLASS Institucion UNSAFE") #client.command("TRUNCATE CLASS Empleado UNSAFE") #client.command("TRUNCATE CLASS Responsable UNSAFE") #client.command("TRUNCATE CLASS Direccion UNSAFE") #client.command("TRUNCATE CLASS Resultados911 UNSAFE") #DELETING ALL RECORDS #Check fields info_general = [ 'CARRERA', 'NOMBRECAR', 'NOMCARSINA', 'MODALIDAD', 'NOMMOD', 'PLAN_EST', 'DURACION', 'ESTRUCT', 'ESTATUS', 'MAT_2015', 'TIPO_SUB', 'SUBSISTEMA',
from dbfread import DBF import csv import sys import json from random import randrange import random # Script to generate a geoJSON with all municipalities of BL # Datenquellen: # Gemeindeliste: https://www.bfs.admin.ch/bfs/de/home/grundlagen/agvch.assetdetail.11467406.html # Geodaten: SwissBoundaries3D # not the best code ;) but it works # (c) 2020, RadioSYS GmbH, Oliver Wisler os = DBF('./data/PLZO_OS.dbf') osvb = DBF('./data/PLZO_OS.dbf') osname = DBF('./data/PLZO_OSNAME.dbf', encoding='utf-8') osnamepos = DBF('./data/PLZO_OSNAMEPOS.dbf', encoding='utf-8') plz = DBF('./data/PLZO_PLZ.dbf') print(plz.field_names) print(osvb.field_names) print(os.field_names) print(osname.field_names) print(osnamepos.field_names) sHoheitsgebiet = DBF('./data/swissBOUNDARIES3D_1_3_TLM_HOHEITSGEBIET.dbf', encoding='utf-8') print(sHoheitsgebiet.field_names) print('Lengths os:%s osname:%s osnamepos:%s plz:%s' %
from dbfread import DBF count = 0 sites = [] print list(DBF('../ttms/ttms.dbf'))[0].keys() for record in DBF('../ttms/ttms.dbf'): # print record.keys() # print record['LOCATION'] # print record['SECTION_'] # print record['Sitetype'] # if 'YEAR_' in record: # print record['YEAR_'] # print record['COMM'] cosite = record['Cosite'] if cosite[:2] == '75': # print record['YEAR_'], cosite print record.values() # count += 1 # if count == 100: # break
def WT_processing_pipeline(path, pipe_WT_TBD, assign_types=False, makecopy=True): # copy dbf to memory in pandas dataframe tic = time() dbf_ref = DBF(path, ignore_missing_memofile='yes') references_dataFrame = DataFrame(iter(dbf_ref)) if assign_types: references_dataFrame = assign_GW_ups_downs(references_dataFrame, pipe_WT_TBD) references_dataFrame['WT_ASS'] = 0.0 #pipe_WT_TBD = [] GW_WT_list = [] GW_index_list = [] sleeve = False assigned_value_list = [] clamp = False for index, row in references_dataFrame.iterrows(): # Check for sleeves, tees, installations if row['TYPE'] == "AS_Sleeve": sleeve = True if row['TYPE'] == "AE_Sleeve": sleeve = False if row['TYPE'] == "Clamp": clamp = True # Check for end of a GWs section and apply WT if row['TYPE'] == "GW_wt_dwn" or row['TYPE'] == "GW_wt_up" or row[ 'TYPE'] == "AE_Receiv": GW_WT_list_mean = np.mean(GW_WT_list) GW_WT_list_median = np.median(GW_WT_list) # Compare WTs to list if it exists assigned_value = compare_GW_WTs(pipe_WT_TBD, GW_WT_list_median) # track assigned values assigned_value_list.append(assigned_value) index_wta = references_dataFrame.columns.get_loc("WT_ASS") # Assign GW WT for GW in GW_index_list: references_dataFrame.iat[GW, index_wta] = assigned_value #references_dataFrame.iat[GW,5] = 55 # start new lists GW_WT_list = [] GW_index_list = [] if row['TYPE'] != "AE_Receiv": # populate with last gw up or down GW_WT_list.append(row['WT_CALC']) GW_index_list.append(index) # Create a list of a section of GWs if row['TYPE'] == "GirthWeld": # if sleeve or present do not append the GW WT calculated value just the index position if sleeve or clamp: GW_index_list.append(index) clamp = False else: GW_WT_list.append(row['WT_CALC']) GW_index_list.append(index) if makecopy: table_copy = make_dbf_copy(path) else: # overwriting references db table_copy = path write_to_table(table_copy, references_dataFrame) toc = time() print("Task took {0:.{1}f} seconds".format(toc - tic, 1))
""" Add custom field parsing by subclassing FieldParser. """ from dbfread import DBF, FieldParser class CustomFieldParser(FieldParser): def parseC(self, field, data): # Return strings reversed. return data.rstrip(' 0').decode()[::-1] for record in DBF('files/people.dbf', parserclass=CustomFieldParser): print(record['NAME'])
""" Return records as objects with fields as attributes. """ from dbfread import DBF class Record(object): def __init__(self, items): for name, value in items: setattr(self, name, value) for record in DBF('files/people.dbf', recfactory=Record, lowernames=True): print(record.name, 'was born on', record.birthdate)
asm.setid("animal", 100) asm.setid("owner", 100) asm.setid("ownerdonation", 100) asm.setid("ownerlicence", 100) asm.setid("adoption", 100) asm.setid("animalcontrol", 100) print "\\set ON_ERROR_STOP\nBEGIN;" print "DELETE FROM animal WHERE ID >= 100;" print "DELETE FROM animalcontrol WHERE ID >= 100;" print "DELETE FROM owner WHERE ID >= 100;" print "DELETE FROM ownerdonation WHERE ID >= 100;" print "DELETE FROM ownerlicence WHERE ID >= 100;" print "DELETE FROM adoption WHERE ID >= 100;" for p in DBF("%s/NAMES.DBF" % PATH): o = asm.Owner() owners.append(o) ppo[p["ID"]] = o o.OwnerForeNames = p["F_NAME"] o.OwnerSurname = p["L_NAME"] o.OwnerAddress = "%s %s\n%s" % (p["ADR_ST_NUM"], p["ADR_ST_NAM"], p["ADR_LINE2"]) o.OwnerTown = p["CITY"] o.OwnerCounty = p["STATE"] o.OwnerPostcode = p["ZIP"] o.HomeTelephone = p["H_PHONE"] o.WorkTelephone = p["W_PHONE"] comments = "ID: %s" % p["ID"] comments += "\n%s" % asm.nulltostr(p["NAMES_TXT"]) o.Comments = comments
def dbf_to_mysql_ValuationFile(filename): # set mysql connection conn = pymysql.connect(host='10.20.12.7', port=3306, user='******', passwd='HJam2017!', db='RiskMeasurement', use_unicode=True, charset="utf8") cur = conn.cursor() # load dbf and insert to db dbf_file = filename product = dbf_file[-12:-4] table = DBF(filename=dbf_file, load=True, encoding="gb2312") # get record for row in table.records: temp_row_list = [] # filter empty lines if row.get('A0') == '' or row.get('Ffdate') == '': continue # get values for each rows for key in row: x = row.get(key) if not (type(x) is type(0.0)): value1 = str(x).encode('utf-8').decode('utf-8') value = "'%s'" % value1 else: value = x temp_row_list.append(value) # mark product id product_id = "'%s'" % str(product).encode('utf-8').decode('utf-8') temp_row_list.append(product_id) # replace None with 0.0/0 for float/int field ## define a method first def replace_none_value(list, startpoint, endpoint, givenvalue): s = int(startpoint) if endpoint == 'null': # single item if list[s] == "'None'": list[s] = givenvalue return list else: e = int(endpoint) for i in list[s:e]: # multiple items if i == "'None'": list[list.index(i)] = givenvalue return list ## execute the method row_list = replace_none_value(temp_row_list,3,7,0.0) row_list = replace_none_value(temp_row_list,12,17,0.0) row_list = replace_none_value(temp_row_list,-2,'null',0) # format list to tuple insert_values = tuple(row_list) print(row_list) print(insert_values) # compile sql statement insert_statement = """insert into Valuation values(%s,%s,%s,%f,%f,%f,%f,%s,%s,%s,%s,%s,%f,%f,%f,%f,%f,%s,%s)""" % insert_values # execute sql insert_result = cur.execute(insert_statement) conn.commit() print(insert_result) # close mysql connection cur.close() conn.close()
return i_query if load.lower()=='y': files = os.listdir('./data') files = [x for x in files if not x.startswith('.')] files = [x for x in files if x.lower().endswith('.dbf')] files.sort() print(f"\nA total of {len(files)} tables will be loaded...\n") time.sleep(2) #loading tables into database for table in tqdm(files): #print(f'Loading {table}...') dbf = DBF(f'data/{table}') df = pd.DataFrame(iter(dbf)) if not df.empty: df.to_sql(name=table[:-4], con=conn, if_exists = 'replace', index=False) #creating table that gives you an over view of the database table_overview_ct=""" CREATE TABLE IF NOT EXISTS TABLE_OVERVIEW ( TABLE_NAME TEXT, COLUMN_NAME TEXT, DATA_TYPE TEXT, ROW_COUNT INT ); """ conn.execute(table_overview_ct)
asm.setid("animal", 100) asm.setid("owner", 100) asm.setid("ownerlicence", 100) asm.setid("ownerdonation", 100) asm.setid("animalcontrol", 100) print "\\set ON_ERROR_STOP\nBEGIN;" print "DELETE FROM animal WHERE ID >= 100;" print "DELETE FROM animalcontrol WHERE ID >= 100;" print "DELETE FROM owner WHERE ID >= 100;" print "DELETE FROM ownerlicence WHERE ID >= 100;" print "DELETE FROM ownerdonation WHERE ID >= 100;" # pre-load address and residences mappings addresses = {} for a in DBF("%s/ADDRESS.DBF" % PATH): addresses[a["ADDRESSNO"]] = a residences = {} for r in DBF("%s/RESIDENC.DBF" % PATH): residences[r["PERSONNO"]] = r for p in DBF("%s/PERSON.DBF" % PATH): if p["PERSONNO"] == "New": continue o = asm.Owner() owners.append(o) ppo[p["PERSONNO"]] = o o.OwnerForeNames = p["FIRSTNAME"] o.OwnerSurname = p["LASTNAME"] if p["AGENCY"] != "" and o.OwnerSurname == "": o.OwnerSurname = p["AGENCY"]
from dbfread import DBF import psycopg2 connection = psycopg2.connect(user="******", password="******", host="127.0.0.1", port="5432", database="astz") all_cnt = 0 all_rows = 0 errors = [] for record in DBF('/home/bastion/MD_MAINZ-20190827/MD_mainz.DBF'): all_rows += 1 if all_rows < 942877 + 1: if all_rows % 100000: print(all_rows) continue real_keys = [] values = [] for key in record.keys(): val = record[key] if key == 'IS': key = 'IS_' real_keys.append(key) values.append(val) cursor = connection.cursor() try: cursor.execute(
def __insert_hospital(self, file, request, tip_file): if tip_file == 1: self.OPER = False temp_oper.objects.filter(user=request.user.id).all().delete() for rec in DBF(file, char_decode_errors="ignore", encoding="utf-8", lowernames=True): dict_rec_r = dict(rec) kodan = str(dict_rec_r['kodan']).strip(' ') kodan = ''.join(kodan) try: temp_oper.objects.create(kod_op=dict_rec_r['kod_op'], dato=dict_rec_r['dato'], goc_o=dict_rec_r['goc_o'], py=dict_rec_r['py'], kodx=dict_rec_r['kodx'], kodxa=dict_rec_r['kodxa'], kodxa1=dict_rec_r['kodxa1'], obz=dict_rec_r['obz'], kodan=kodan, pr_osob=dict_rec_r['pr_osob'], k_mm=dict_rec_r['k_mm'], nib=dict_rec_r['nib'], user=request.user.id) self.OPER = True except KeyError: pass else: self.SLUCH = False temp_sluch.objects.filter(user=request.user.id).all().delete() for rec in DBF(file, char_decode_errors="ignore", encoding="cp866", lowernames=True): dict_rec_r = dict(rec) try: tm_let = dict_rec_r[ 'tm_let'] if dict_rec_r['tm_let'] != '' else None temp_sluch.objects.create( fam=dict_rec_r['fam'], im=dict_rec_r['im'], ot=dict_rec_r['ot'], pol=dict_rec_r['pol'], datr=dict_rec_r['datr'], udl=dict_rec_r['udl'], s_pasp=dict_rec_r['s_pasp'], n_pasp=dict_rec_r['n_pasp'], ss=dict_rec_r['ss'], c_oksm=dict_rec_r['c_oksm'], adr=dict_rec_r['adr'], m_roj=dict_rec_r['m_roj'], cod_adr=dict_rec_r['cod_adr'], cj=dict_rec_r['cj'], v_lgoty=dict_rec_r['v_lgoty'], in_t=dict_rec_r['in_t'], rab=dict_rec_r['rab'], r_n=dict_rec_r['r_n'], prof=dict_rec_r['prof'], vec=dict_rec_r['vec'], nib=dict_rec_r['nib'], datp=dict_rec_r['datp'], datv=dict_rec_r['datv'], goc=dict_rec_r['goc'], prpg=dict_rec_r['prpg'], vrez=dict_rec_r['vrez'], lpy=dict_rec_r['lpy'], ws=dict_rec_r['ws'], tm_otd=dict_rec_r['tm_otd'], otd=dict_rec_r['otd'], prof_k=dict_rec_r['prof_k'], icx=dict_rec_r['icx'], dsny=dict_rec_r['dsny'], dsk=dict_rec_r['dsk'], dskz=dict_rec_r['dskz'], dsc=dict_rec_r['dsc'], ds_osl=dict_rec_r['ds_osl'], dson=dict_rec_r['dson'], ksg_osn=dict_rec_r['ksg_osn'], ksg_sop=dict_rec_r['ksg_sop'], vid_hmp=dict_rec_r['vid_hmp'], metod_hmp=dict_rec_r['metod_hmp'], trs=dict_rec_r['trs'], tm_let=tm_let, pri=dict_rec_r['pri'], ds_let=dict_rec_r['ds_let'], wskr=dict_rec_r['wskr'], dspat=dict_rec_r['dspat'], rasxp=dict_rec_r['rasxp'], otd_y=dict_rec_r['otd_y'], vds=dict_rec_r['vds'], sctp=dict_rec_r['sctp'], nctp=dict_rec_r['nctp'], t_pol=dict_rec_r['t_pol'], ctkom=dict_rec_r['ctkom'], ksg_ts=dict_rec_r['ksg_ts'], t_trv=dict_rec_r['t_trv'], details=dict_rec_r['details'], trav_ns=dict_rec_r['trav_ns'], pmg=dict_rec_r['pmg'], user=request.user.id) self.SLUCH = True except KeyError: continue
# @File : dbf_cut.py # @desc : cut big dbf file to small excel files import xlsxwriter from dbfread import DBF print(""" 用于dbf文件自动下载失败 需要切割大dbf文件为xlsx 上传后用备用抓取下载入库 """) MAX = 5000 FILE_list = ['bond634.dbf', 'BOND634_O.dbf'] #input('file: ') #文件名称 for FILE in FILE_list: table = DBF(filename=FILE, encoding='GBK') def write_to_excel(data: [[]], path: str): """ Args: data: [[]] double list path: export file path """ workbook = xlsxwriter.Workbook(path) worksheet = workbook.add_worksheet('sheet1') x = 0 for d in data: y = 0 for value in d:
folder_geoparks = 'geoparks_world/' folder_geoparks_EA = 'geoparks_asia_europe/' #%% data World #change directory path = disk + folder + folder_world os.chdir(path) fileList = glob.glob("*.dbf") #create empty dataframe data_world = pd.DataFrame() for file in glob.glob("*.dbf"): # for index, file in enumerate(fileList): data = pd.DataFrame(iter(DBF((path + file), load=True))) data['name'] = file.replace(".dbf", "") data_world = pd.concat([data_world, data], axis=0, sort=True) data_world.replace(99999, np.nan, inplace=True) #replace 0 by nan data_worldM = pd.melt(data_world, id_vars=["name"]) #%% data Europe path = disk + folder + folder_europe os.chdir(path) fileList = glob.glob("*.dbf") #create empty dataframe data_europe = pd.DataFrame()
in_dbf = r"I:\Projects\Darren\PPA_V2_GIS\SACSIM Model Data\trans.link.all_{}.dbf".format( year) out_csv = r'I:\Projects\Darren\PPA_V2_GIS\SACSIM Model Data\transit_linkvol_{}.csv'.format( year) col_anode = 'A' col_bnode = 'B' grouby_cons_col = '{}_{}'.format(col_anode, col_bnode) val_cols = ['VOL', 'REV_VOL'] # --------------------------------------------------------- dbf = DBF(in_dbf) df_data = pd.DataFrame(dbf) input_cols = [col_anode, col_bnode] + val_cols df_data = df_data[input_cols] df_data[grouby_cons_col] = df_data[col_anode].map( str) + '_' + df_data[col_bnode].map(str) #df_data[grouby_cons_col] = df_data.swifter.apply(lambda x: '{}_{}'.format(x[group_cols[0]], x[group_cols[1]]), axis = 1) val_cols.append(grouby_cons_col) df_summed = df_data[val_cols].groupby([grouby_cons_col]).sum().reset_index() df_summed[val_cols].to_csv(out_csv, index=False)
from dbfread import DBF import re import datetime import config client = config.client nivel = ["secundaria"] subnivel = ["general"] table = DBF('SECUNI16.dbf', load=True, encoding="latin-1") #print table.field_names #print table.records[0] r = re.compile("V\d") fields = filter(r.match, table.field_names) #print fields info_general = [ 'CLAVECCT', 'N_CLAVECCT', 'DIRSERVREG', 'UNIDADRESP', 'PROGRAMA', 'SUBPROG', 'RENGLON', 'N_RENGLON', 'PERIODO', 'MOTIVO', 'DISPON' ] turno = ['TURNO'] ubicacion = [ 'N_ENTIDAD', 'MUNICIPIO', 'N_MUNICIPI', 'LOCALIDAD', 'N_LOCALIDA', 'DOMICILIO', 'ZONAESCOLA', 'SECTOR' ] sostenimiento = ['SOSTENIMIE'] dependencia_normativa = ['DEPNORMTVA', 'DEPADMVA'] servicio = ['SERVICIO'] renombres = { u'CLAVECCT': 'clave', u'N_CLAVECCT': 'nombre', u'TURNO': 'turno', u'N_ENTIDAD': 'nombre_entidad',
def _import_move(self, file_dir, files, scanfiles, account_data, account_central, journal_data, partner_data, vatcode_data, param_data): """Import the journal entries from *_act*.dfb and @scandbk.zip files. The data in *_act*.dfb files are related to the moves and the data in @scandbk.zip files are the attachments. """ _logger.info("Import Moves") recs = [] ResCurrency = self.env['res.currency'] IrAttachment = self.env['ir.attachment'] suspense_account = self.env['account.account'].search([('code', '=', self.suspense_code)], limit=1) if not self.only_open and not suspense_account: raise UserError(_("The code for the Suspense Account you entered doesn't match any account")) counter_part_created = False for file_name in scanfiles: with zipfile.ZipFile(join(file_dir, file_name), 'r') as scan_zip: scan_zip.extractall(file_dir) for file_name in files: for rec in DBF(join(file_dir, file_name), encoding='latin').records: if rec.get('BOOKYEAR') and rec.get('DOCNUMBER') != '99999999': recs.append(rec) result = [dict(tupleized) for tupleized in set(tuple(item.items()) for item in recs)] grouped = collections.defaultdict(list) for item in result: # Group by number/year/period grouped[item['DOCNUMBER'], item['DBKCODE'], item['DBKTYPE'], item['BOOKYEAR'], item['PERIOD']] += [item] move_data_list = [] pdf_file_list = [] reconcile_number_set = set() for key, val in grouped.items(): journal_id = self.env['account.journal'].browse(journal_data.get(key[1])) bookyear = int(key[3], 36) if not bookyear or (self.only_open and bookyear not in param_data['openyears']): continue perdiod_number = len(param_data['period_date'][bookyear]) - 2 period = min(int(key[4]), perdiod_number + 1) # closing is 99 in winbooks, not 13 start_period_date = param_data['period_date'][bookyear][period] if 1 <= period < perdiod_number: end_period_date = param_data['period_date'][bookyear][period + 1] + timedelta(days=-1) elif period == perdiod_number: # take the last day of the year = day of closing end_period_date = param_data['period_date'][bookyear][period + 1] else: # opening (0) or closing (99) are at a fixed date end_period_date = start_period_date move_date = val[0].get('DATEDOC') move_data_dict = { 'journal_id': journal_id.id, 'move_type': 'out_invoice' if journal_id.type == 'sale' else 'in_invoice' if journal_id.type == 'purchase' else 'entry', 'ref': '%s_%s' % (key[1], key[0]), 'company_id': self.env.company.id, 'date': min(max(start_period_date, move_date), end_period_date), } if not move_data_dict.get('journal_id') and key[1] == 'MATCHG': continue move_line_data_list = [] move_amount_total = 0 move_total_receivable_payable = 0 # Split lines having a different sign on the balance in company currency and foreign currency tmp_val = [] for rec in val: tmp_val += [rec] if rec['AMOUNTEUR'] * (rec['CURRAMOUNT'] or 0) < 0: tmp_val[-1]['CURRAMOUNT'] = 0 tmp_val += [rec.copy()] tmp_val[-1]['AMOUNTEUR'] = 0 val = tmp_val # Basic line info for rec in val: currency = ResCurrency.search([('name', '=', rec.get('CURRCODE'))], limit=1) if currency == self.env.company.currency_id: currency = self.env['res.currency'] partner_id = self.env['res.partner'].browse(partner_data.get(rec.get('ACCOUNTRP'), False)) account_id = self.env['account.account'].browse(account_data.get(rec.get('ACCOUNTGL'))) matching_number = rec.get('MATCHNO') and '%s-%s' % (rec.get('ACCOUNTGL'), rec.get('MATCHNO')) or False line_data = { 'date': rec.get('DATE', False), 'account_id': account_id.id, 'partner_id': partner_id.id, 'date_maturity': rec.get('DUEDATE', False), 'name': rec.get('COMMENT'), 'currency_id': currency.id, 'debit': rec.get('AMOUNTEUR') if rec.get('AMOUNTEUR') and rec.get('AMOUNTEUR') >= 0 else 0.0, 'credit': abs(rec.get('AMOUNTEUR')) if rec.get('AMOUNTEUR') and rec.get('AMOUNTEUR') < 0 else 0.0, 'amount_currency': rec.get('CURRAMOUNT') if currency and rec.get('CURRAMOUNT') else 0.0, 'amount_residual_currency': rec.get('CURRAMOUNT') if currency and rec.get('CURRAMOUNT') else 0.0, 'winbooks_matching_number': matching_number, 'exclude_from_invoice_tab': rec.get('DOCORDER') == 'VAT' or (account_id.user_type_id.type in ('receivable', 'payable') and journal_id.type in ('sale', 'purchase')), } if matching_number: reconcile_number_set.add(matching_number) if rec.get('AMOUNTEUR'): move_amount_total = round(move_amount_total, 2) + round(rec.get('AMOUNTEUR'), 2) move_line_data_list.append((0, 0, line_data)) if account_id.user_type_id.type in ('receivable', 'payable'): move_total_receivable_payable += rec.get('AMOUNTEUR') # Compute refund value if journal_id.type in ('sale', 'purchase'): is_refund = move_total_receivable_payable < 0 if journal_id.type == 'sale' else move_total_receivable_payable > 0 else: is_refund = False # Add tax information for line_data, rec in zip(move_line_data_list, val): if self.env['account.account'].browse(account_data.get(rec.get('ACCOUNTGL'))).user_type_id.type in ('receivable', 'payable'): continue tax_line = self.env['account.tax'].browse(vatcode_data.get(rec.get('VATCODE') or rec.get('VATIMPUT', []))) if not tax_line and line_data[2]['account_id'] in account_central.values(): # this line is on a centralised account, most likely a tax account, but is not linked to a tax # this is because the counterpart (second repartion line) line of a tax is not flagged in Winbooks try: counterpart = next(r for r in val if r['AMOUNTEUR'] == -rec['AMOUNTEUR'] and r['DOCORDER'] == 'VAT' and r['VATCODE']) tax_line = self.env['account.tax'].browse(vatcode_data.get(counterpart['VATCODE'])) except StopIteration: pass # We didn't find a tax line that is counterpart with same amount repartition_line = is_refund and tax_line.refund_repartition_line_ids or tax_line.invoice_repartition_line_ids repartition_type = 'tax' if rec.get('DOCORDER') == 'VAT' else 'base' line_data[2].update({ 'tax_ids': tax_line and rec.get('DOCORDER') != 'VAT' and [(4, tax_line.id)] or [], 'tax_tag_ids': [(6, 0, tax_line.get_tax_tags(is_refund, repartition_type).ids)], 'tax_repartition_line_id': rec.get('DOCORDER') == 'VAT' and repartition_line.filtered(lambda x: x.repartition_type == repartition_type and x.account_id.id == line_data[2]['account_id']).id or False, }) move_line_data_list = [i for i in move_line_data_list if i[2]['account_id'] or i[2]['debit'] or i[2]['credit']] # Remove empty lines # Adapt invoice specific informations if move_data_dict['move_type'] != 'entry': move_data_dict['partner_id'] = move_line_data_list[0][2]['partner_id'] move_data_dict['invoice_date_due'] = move_line_data_list[0][2]['date_maturity'] move_data_dict['invoice_date'] = move_line_data_list[0][2]['date'] if is_refund: move_data_dict['move_type'] = move_data_dict['move_type'].replace('invoice', 'refund') # Balance move, should not happen in an import from a complete db if move_amount_total: if not counter_part_created: _logger.warning('At least one automatic counterpart has been created at import. This is probably an error. Please check entry lines with reference: ' + _('Counterpart (generated at import from Winbooks)')) counter_part_created = True account_id = journal_id.default_account_id account_id = account_id or (partner_id.property_account_payable_id if rec.get('DOCTYPE') in ['0', '1'] else partner_id.property_account_receivable_id) account_id = account_id or suspense_account # Use suspense account as fallback line_data = { 'account_id': account_id.id, 'date_maturity': rec.get('DUEDATE', False), 'name': _('Counterpart (generated at import from Winbooks)'), 'credit': move_amount_total if move_amount_total >= 0 else 0.0, 'debit': abs(move_amount_total) if move_amount_total < 0 else 0.0, } move_line_data_list.append((0, 0, line_data)) # Link all to the move move_data_dict['line_ids'] = move_line_data_list attachment = '%s_%s_%s' % (key[1], key[4], key[0]) pdf_file = self.find_file(attachment, file_dir) pdf_file_list.append(pdf_file) move_data_list.append(move_data_dict) if len(move_data_list) % 100 == 0: _logger.info("Advancement: {}".format(len(move_data_list))) _logger.info("Creating moves") move_ids = self.env['account.move'].create(move_data_list) move_ids._post() _logger.info("Creating attachments") for move, pdf_file in zip(move_ids, pdf_file_list): if pdf_file: attachment_ids = [] for pdf in pdf_file: attachment_data = { 'name': pdf.split('/')[-1], 'type': 'binary', 'datas': base64.b64encode(open(pdf, "rb").read()), 'res_model': move._name, 'res_id': move.id, 'res_name': move.name } attachment_ids.append(IrAttachment.create(attachment_data)) move.message_post(attachments=attachment_ids) _logger.info("Reconcile") for matching_number in reconcile_number_set: lines = self.env['account.move.line'].search([('winbooks_matching_number', '=', matching_number), ('reconciled', '=', False)]) try: lines.with_context(no_exchange_difference=True).reconcile() except UserError as ue: if len(lines.account_id) > 1: _logger.warning('Winbooks matching number {} uses multiple accounts: {}. Lines with that number have not been reconciled in Odoo.'.format(matching_number, ', '.join(lines.mapped('account_id.display_name')))) elif not lines.account_id.reconcile: _logger.info("{} {} has reconciled lines, changing the config".format(lines.account_id.code, lines.account_id.name)) lines.account_id.reconcile = True lines.with_context(no_exchange_difference=True).reconcile() else: raise ue return True
import psycopg2 import datetime conn = psycopg2.connect( "host='localhost' dbname='odoo' user='******' password='******'") #conn = psycopg2.connect("host='192.168.1.27' dbname='odoo' user='******' password='******'") cursor = conn.cursor() datakosong = 0 listOfUkuran = [ '2', '3', '4', '5', '6', '26', '27', '28', '29', '30', '31', '32', '33', '34', '35', '36', '37', '38', '39', '40', '41', '42', '43', '44', '45' ] from dbfread import DBF # for record in DBF('/mnt/poserver/ICS/INV.DBF',encoding='iso-8859-1'): # print(record) for item in DBF('/mnt/poserver/ics/DAT/INV.DBF', encoding='iso-8859-1'): # print item[1] # print list(item.values()) # print len(item), # print item['CODE'],item['DESC1'],item['LQOH'] list = item['DESC1'].split(" ") print list[len(list) - 1] ukuran = list[len(list) - 1][-2:] if ukuran in listOfUkuran: print ukuran article = item['DESC1'].replace(ukuran, "").replace("'", "") else: ukuran = "" print "AllSize" article = item['DESC1'].replace("'", "")
def import_partner(self, file_dir, files, civility_data, category_data, account_data): """Import partners from *_csf*.dbf files. The data in those files is the partner details, its type, its category, bank informations, and central accounts. :return: a dictionary whose keys are the Winbooks partner references and the values are the partner ids in Odoo. """ _logger.info("Import Partners") partner_data = {} ResBank = self.env['res.bank'] ResCountry = self.env['res.country'] ResPartner = self.env['res.partner'] ResPartnerBank = self.env['res.partner.bank'] partner_data_dict = {} for file_name in files: for rec in DBF(join(file_dir, file_name), encoding='latin').records: if not rec.get('NUMBER'): continue partner = ResPartner.search([('ref', '=', rec.get('NUMBER'))], limit=1) if partner: partner_data[rec.get('NUMBER')] = partner.id if not partner: vatcode = rec.get('VATNUMBER') and rec.get('COUNTRY') and (rec.get('COUNTRY') + rec.get('VATNUMBER').replace('.', '')) if not rec.get('VATNUMBER') or not rec.get('COUNTRY') or not ResPartner.simple_vat_check(rec.get('COUNTRY').lower(), vatcode): vatcode = '' data = { 'ref': rec.get('NUMBER'), 'name': rec.get('NAME1'), 'street': rec.get('ADRESS1'), 'country_id': ResCountry.search([('code', '=', rec.get('COUNTRY'))], limit=1).id, 'city': rec.get('CITY'), 'street2': rec.get('ADRESS2'), 'vat': vatcode, 'phone': rec.get('TELNUMBER'), 'zip': rec.get('ZIPCODE') and ''.join([n for n in rec.get('ZIPCODE') if n.isdigit()]), 'email': rec.get('EMAIL'), 'active': not rec.get('ISLOCKED'), 'title': civility_data.get(rec.get('CIVNAME1'), False), 'category_id': [(6, 0, [category_data.get(rec.get('CATEGORY'))])] if category_data.get(rec.get('CATEGORY')) else False } if partner_data_dict.get(rec.get('IBANAUTO') or 'num' + rec.get('NUMBER')): for key, value in partner_data_dict[rec.get('IBANAUTO') or 'num' + rec.get('NUMBER')].items(): if value: # Winbooks has different partners for customer/supplier. Here we merge the data of the 2 data[key] = value if rec.get('NAME2'): data.update({ 'child_ids': [(0, 0, {'name': rec.get('NAME2'), 'title': civility_data.get(rec.get('CIVNAME2'), False)})] }) # manage the bank account of the partner if rec.get('IBANAUTO'): partner_bank = ResPartnerBank.search([('acc_number', '=', rec.get('IBANAUTO'))], limit=1) if partner_bank: data['bank_ids'] = [(4, partner_bank.id)] else: bank = ResBank.search([('name', '=', rec.get('BICAUTO'))], limit=1) if not bank: bank = ResBank.create({'name': rec.get('BICAUTO')}) data.update({ 'bank_ids': [(0, 0, { 'acc_number': rec.get('IBANAUTO'), 'bank_id': bank.id })], }) # manage the default payable/receivable accounts for the partner if rec.get('CENTRAL'): if rec.get('TYPE') == '1': data['property_account_receivable_id'] = account_data[rec.get('CENTRAL')] else: data['property_account_payable_id'] = account_data[rec.get('CENTRAL')] partner_data_dict[rec.get('IBANAUTO') or 'num' + rec.get('NUMBER')] = data if len(partner_data_dict) % 100 == 0: _logger.info("Advancement: {}".format(len(partner_data_dict))) partner_ids = ResPartner.create(partner_data_dict.values()) for partner in partner_ids: partner_data[partner.ref] = partner.id return partner_data
from dbfread import DBF ybraw=open('./output/yb18.csv',"w") table = DBF('./DBF/yb.dbf',load=True,ignore_missing_memofile=True) print('qhdm','sq','csdbrs','csdbhs','ncdbrs','ncdbhs',sep=',',file=ybraw) for record in range(0,len(table)): # print(table.records[record]['SYS_ZDM'][0:12],',',table.records[record]['SYS_ZDM'][12:14]) print(table.records[record]['SYS_ZDM'][0:12],',',table.records[record]['SYS_ZDM'][12:14],",",table.records[record]['YZH21110'],\ ",",table.records[record]['YZH21120'],",",table.records[record]['YZH21210'],",",table.records[record]['YZH21220'],\ sep="",file=ybraw) ybraw.close()