def _postprocessTazdata(self, src_dbf_file, append_dbf_file, dat_file): """ This hurts, it really does. But group quarters needed to come from the zmast file, and HHPOP came from sfzones, and the TazDataProcessory couldn't handle combining those to make the total POP so we have to do so here. Some day I hope this is cleaner -- maybe if group quarters population came out of UrbanSim, or the TazDataProcessor were a bit smarter... """ shutil.move(src_dbf_file, src_dbf_file + ".bak") dbfin = dbf.Dbf(src_dbf_file + ".bak", readOnly=1, ignoreErrors=True) dbfin2 = dbf.Dbf(append_dbf_file, readOnly=1) if len(dbfin) != len(dbfin2): raise StandardError, "%s and %s have different number of records" % ( src_dbf_file, append_dbf_file) # read the append dbf file dist22 = {} dist40 = {} for rec in dbfin2: dist22[rec["SFTAZ"]] = rec["DIST22"] dist40[rec["SFTAZ"]] = rec["DIST40"] dbfin2.close() # add the two new fields dbfout = dbf.Dbf(src_dbf_file, new=True) datout = open(dat_file, 'w') fields = [] for field in dbfin.fieldDefs: if field.name == "DIST51": dbfout.addField(("DIST22", field.typeCode, field.length, field.decimalCount)) dbfout.addField(("DIST40", field.typeCode, field.length, field.decimalCount)) fields.append("DIST22") fields.append("DIST40") dbfout.addField( (field.name, field.typeCode, field.length, field.decimalCount)) fields.append(field.name) # update the dbf for rec in dbfin: newrec = dbfout.newRecord() for field in fields: if field == "POP": newrec[field] = rec["GQPOP"] + rec["HHPOP"] elif field == "DIST22": newrec[field] = dist22[rec["SFTAZ"]] elif field == "DIST40": newrec[field] = dist40[rec["SFTAZ"]] else: newrec[field] = rec[field] if field != fields[0]: datout.write(" ") datout.write(str(newrec[field])) newrec.store() datout.write("\n") dbfout.close() datout.close() dbfin.close()
def test(self): test_filename = "test.dbf" db = dbf.Dbf(test_filename, new=True) rec_fields = ['NAME', 'SURNAME', 'INITIALS', 'BIRTHDATE'] db.addField( ("NAME", "C", 15), ("SURNAME", "C", 25), ("INITIALS", "C", 10), ("BIRTHDATE", "D"), ) ## fill DBF with some records expected_data = [ ("John", "Miller", "JM", (1980, 1, 2), datetime.date(1980, 1, 2)), ("Andy", "Larkin", "AL", datetime.date(1981, 2, 3), datetime.date(1981, 2, 3)), ("Bill", "Clinth", "", datetime.date(1982, 3, 4), datetime.date(1982, 3, 4)), ("Bobb", "McNail", "", "19830405", datetime.date(1983, 4, 5)), ] for name, surname, initials, birthdate, _ in expected_data: rec = db.newRecord() rec["NAME"] = name rec["SURNAME"] = surname rec["INITIALS"] = initials rec["BIRTHDATE"] = birthdate rec.store() db.close() ## read DBF and print records db = dbf.Dbf(test_filename) data = list(db) self.assertEqual(len(data), len(expected_data)) for exp_rec, _rec in zip(expected_data, data): exp_rec = exp_rec[:-2] + exp_rec[-1:] rec = tuple(_rec[f] for f in rec_fields) self.assertEqual(exp_rec, rec) ## change record rec = db[2] rec["INITIALS"] = "BC" rec.store()
def DBFConverter(self, ifile, ofile, conv_type): # create the DBF reader object db = dbf.Dbf(ifile) # create the file to output CSV data to fpath = ofile f = open(fpath,'w') # initiat the header container and CSV writer hdr = [] c = csv.writer(f) # loop through all the field names and create the header row for fieldName in db.fieldNames: hdr += [fieldName] # write the head to the CSV file c.writerow(hdr) # loop through all the records and write each line to the CSV file for rec in db: # using asList makes a list as opposed to asDict c.writerow(rec.asList()) if conv_type in ['pandas']: # take the csv file and open it as a pandas dataframe from_csv = pd.read_csv(fpath) # return a dataframe object return from_csv if conv_type in ['csvfileobject']: return f # close the CSV file f.close() db.close()
def process_referencefile(file_name): reference_db = dbf.Dbf(file_name) reference_new_file_name = file_name[:-4] + "_new.csv" with open(reference_new_file_name, "w") as output_handle: writer = csv.writer(output_handle) origin_fields = reference_db.fieldNames if "ID_X" in origin_fields: origin_fields.remove("ID_X") if "ID_Y" in origin_fields: origin_fields.remove("ID_Y") new_fields = [ "ele_new", "loc_usr", "loc_ur", "region", "state_name", 'new_location' ] fields = origin_fields + new_fields writer.writerow(fields) for rec in reference_db: new_row = [] for field in origin_fields: new_row.append(rec[field]) new_row.append(elevation_new_process(rec["ELEVATION"])) new_row.append(location_usr_process(rec['NLCD_2011_'])) new_row.append(location_ur_process(rec['NLCD_2011_'])) new_row.append(region_process(rec['STATECODE'])) new_row.append( state_name_process(rec['STATECODE'], state_name_dict)) new_row.append( new_location_process(rec['NLCD_2011_'], rec['LOCATION'])) writer.writerow(new_row) reference_db.close()
def estrazione_dati_da_dbf_dello_shape_e_conversione_pandas(): only_dbf_da_shp = [ f for f in listdir(countries_dir) if isfile(join(countries_dir, f)) ] df_pop = pd.DataFrame(columns=[ 'iso3', 'adm0_code', 'adm0_name', 'adm1_code', 'adm1_name', 'adm2_code', 'adm2_name', 'hectares', 'area_sqkm', 'area_sqft', 'pop' ]) contatore = 0 for fileggio in sorted(only_dbf_da_shp): fileName, fileExtension = path.splitext(fileggio) if fileExtension == '.dbf': db = dbf.Dbf(countries_dir + "\\" + fileggio) for rec in db: df_pop.at[contatore, 'iso3'] = rec["ISO3"] df_pop.at[contatore, 'adm0_code'] = rec["ADM0_CODE"] df_pop.at[contatore, 'adm0_name'] = rec["ADM0_NAME"] df_pop.at[contatore, 'adm1_code'] = rec["ADM1_CODE"] df_pop.at[contatore, 'adm1_name'] = rec["ADM1_NAME"] df_pop.at[contatore, 'adm2_code'] = rec["ADM2_CODE"] df_pop.at[contatore, 'adm2_name'] = rec["ADM2_NAME"] df_pop.at[contatore, 'hectares'] = rec["HECTARES"] df_pop.at[contatore, 'area_sqkm'] = rec["AREA_SQKM"] df_pop.at[contatore, 'area_sqft'] = rec["AREA_SQFT"] df_pop.at[contatore, 'pop'] = rec["SUM"] contatore += 1 return df_pop
def listpath(e, dirpath, dirpath1): tStart = time.time() if ".dbf" not in dirpath1 and ".DBF" not in dirpath1: wx.MessageBox(u"不存在DBF檔 請重新選取", u"提示訊息") else: db = dbf.Dbf(dirpath1) for record in db: # print record['Plt_no'], record['Vil_dt'], record['Vil_time'],record['Bookno'],record['Vil_addr'],record['Rule_1'],record['Truth_1'],record['Rule_2'],record['Truth_2'],record['color'],record['A_owner1'] filename = record['Plt_no'] + "." + record[ 'Vil_dt'] + "." + record['Vil_time'] + u'-1' #檔名 piece1.append(filename) #檔名 piece1.append(record['Plt_no']) #車牌 piece1.append(record['Vil_dt']) #日期 piece1.append(record['Vil_time']) #時間 piece1.append(record['Bookno']) #冊頁號 piece1.append(record['Vil_addr']) #違規地點 piece1.append(record['Rule_1']) #法條1 piece1.append(record['Truth_1']) #法條1事實 piece1.append(record['Rule_2']) #法條2 piece1.append(record['Truth_2']) #法條2事實 piece1.append(record['color']) #車顏色 piece1.append(record['A_owner1']) #車廠牌 record.store() print('Mission accomplished') tEnd = time.time() print("Spend " + str((tEnd - tStart) // 1) + " second")
def set_records(values, errores): from dbfpy import dbf import os path_dbf = os.path.join(request.folder, 'static', 'economia', 'ban1.dbf') db = dbf.Dbf(path_dbf) try: for rec in db: found = False for v in values: if rec['NUM_IDEPER'] == v['NUM_IDEPER']: rec["DIR_PERSO1"] = v["DIR_PERSO1"].upper() rec["DIR_PERSO2"] = v["DIR_PERSO2"].upper() rec.store() if v["APELLIDO_1"]: rec["APELLIDO_1"] = v["APELLIDO_1"] rec.store() if v["APELLIDO_2"]: rec["APELLIDO_2"] = v["APELLIDO_2"] rec.store() rec["NOMBRE"] = v["NOMBRE"] rec["NOMB_APELL"] = rec["NOMBRE"] + ' ' + rec[ "APELLIDO_1"] + ' ' + rec["APELLIDO_2"] rec.store() found = True break if not found: errores.append({ 'type': 'not_found', 'value': (rec["NUM_IDEPER"], rec["NOMB_APELL"]) }) except Exception, e: errores.append((str(e), v))
def make_example_file(filename, fields, records, delete_last_record=False): field_names = [field[0] for field in fields] print('Creating', filename) print(' Fields:', ', '.join(field_names)) print(' ', len(records), 'records') db = dbf.Dbf(filename, new=True) db.addField(*fields) for data in records: record = db.newRecord() for name, value in zip(field_names, data): record[name] = value record.store() if delete_last_record: # Delete the last one of those records. record.delete() record.store() try: db.close() except AttributeError: # This ignores the following error: # self.memo.flush() # AttributeError: 'NoneType' object has no attribute 'flush' pass
def _read_DBF(src_file, dictionary): dataset = dbf.Dbf(src_file) for record in dataset: if (record['UP'] is None or record['UP'] < dictionary.arcMinDate): continue dictionary.addAccrualSize(record['TN']) dataset.close()
def summarizeDbf(dbfList, idField, sumVals=-1): results = {} expectedLength = 0 if sumVals == -1: sumVals = [["SUM"]] * len(dbfList) else: sumVals = sumVals #[sumVals] * len(dbfList) for dFileIdx in range(0, len(dbfList)): dFile = dbfList[dFileIdx] dbfData = dbf.Dbf(dFile) for idx in range(0, len(dbfData)): curResults = [] for cVal in sumVals[dFileIdx]: curResults.append(dbfData[idx][cVal]) if dFile == dbfList[0]: results[dbfData[idx][idField]] = curResults else: try: #For the 2nd dbf and onward, append the results to the existing data #Check to make sure the current data are of the proper length, if not, pad with zeroes curRes = results[dbfData[idx][idField]] if len(curRes) != expectedLength: results[dbfData[idx][idField]].extend( [0] * (expectedLength - len(curRes))) results[dbfData[idx][idField]].extend(curResults) except: #If the key is not in the output, add a bunch of zeroes at the start, then append results[dbfData[idx][idField]] = [0] * expectedLength results[dbfData[idx][idField]].extend(curResults) expectedLength = expectedLength + len(sumVals[dFileIdx]) return (results)
def convert_dbfs_to_csvs(self, logger=defaultLogger): for [band_name, band_folder] in self.band_parameters: self.string_args['band'] = band_name for dbfFilename in (glob(BANDS_DBF_FOLDER.format(**self.string_args) + '*.dbf') + glob(TEMP_DBF_FOLDER.format(**self.string_args) + '*.dbf') + glob(SAMPLE_DBF_FOLDER.format(**self.string_args) + '*.dbf') + glob(SEL_POINTS_FOLDER.format(**self.string_args) + '*.dbf') + glob(SEL_TEMP_DBF_FOLDER.format(**self.string_args) + '*.dbf')): ### Code for dbfpy module csvFilename = dbfFilename.replace('.dbf', '.csv') with open(csvFilename, 'wb') as outCSV: inDBF = dbf.Dbf(dbfFilename) #outCSV = open(csvFilename, 'wb') csvWriter = csv.writer(outCSV) names = [field.name for field in inDBF.header.fields] csvWriter.writerow(names) for rec in inDBF: csvWriter.writerow(rec.fieldData) inDBF.close() if os.path.exists(csvFilename): try: os.remove(dbfFilename) except OSError: pass try: os.remove(dbfFilename + '.xml') except OSError: pass return None
def writeClimData(climData, stationId, outDir): for ghcndVar in climData.keys(): if ghcndVar == 'TMP': outFile = outDir + '/' + stationId + 'tmp.dbf' else: outFile = outDir + '/' + stationId + varMap[ghcndVar].lower() + '.dbf' db = dbf.Dbf(outFile, new=True) if ghcndVar == 'TMP': db.addField( ('DATE', 'D')\ , ('MAX','N', 5, 1)\ , ('MIN','N', 5, 1)\ ) else: db.addField( ("DATE", "D")\ , (varMap[ghcndVar],'N', 5, 1) ) varData = climData[ghcndVar] for row in range(0,varData.shape[0]): rec = db.newRecord() rec["DATE"] = (int(varData[row,0]), int(varData[row,1]), int(varData[row,2])) if ghcndVar == 'TMP': rec['MAX'] = varData[row,3] rec['MIN'] = varData[row,4] else: rec[varMap[ghcndVar]] = varData[row,3] rec.store() db.close() del db
def hr_department(src_path, dst_path, dictionary): dst_file = dst_path + 'hr_department.csv' try: dataset = dbf.Dbf(src_path + 'PDR.DBF') f = open(dst_file, 'w+') f.write( 'ID;code;name;parentUnitID;state;fullName;description;nameGen;fullNameGen;nameDat;nameOr;' + 'fullNameGen;fullNameDat;dateFrom;dateTo\n') ID = 0 for record in dataset: ID += 1 code = record['ID'] name = record['NM'] parentUnitID = record['ID_PARENT'] and dictionary.get_DepartmentID( record['ID_PARENT']) or '' state = 'ACTIVE' fullName = record['NMF'] description = name + ' (' + code + ')' nameGen = '' fullNameGen = '' nameDat = '' nameOr = '' fullNameGen = '' fullNameDat = '' dateFrom = record['BEG'] dateTo = record['END'] and record['END'] or '' f.write('%d;%s;%s;%s;%s;%s;%s;%s;%s;%s;%s;%s;%s;%s;%s\n' % (ID, code, name, parentUnitID, state, fullName, description, nameGen, fullNameGen, nameDat, nameOr, fullNameGen, fullNameDat, dateFrom, dateTo)) dictionary.set_DepartmentID(code, ID) dataset.close() except: print 'Error making ', dst_file, sys.exc_info()[1]
def prepare_drought_tables(paese): direttorio_radice = r"C:\data\tools\sparc\projects\drought" direttorio = direttorio_radice + "\\" + paese_ricerca import pycountry iso_paese = pycountry.countries.get(name=paese_ricerca).alpha3 lista = [] for direttorio_principale, direttorio_secondario, file_vuoto in os.walk( direttorio): if direttorio_principale != direttorio: name_adm = direttorio_principale.split("\\")[7].split("_")[0] code_adm = direttorio_principale.split("\\")[7].split("_")[1] files_dbf = glob.glob(direttorio_principale + "/*.dbf") for file in files_dbf: fileName, fileExtension = os.path.splitext(file) if 'stat' in fileName: try: if str(fileExtension) == '.dbf': temporal_string = fileName.split("\\")[-1].split( "_")[1] temporal_value = ''.join(x for x in temporal_string if x.isdigit()) in_dbf = dbf.Dbf(fileName + fileExtension) for rec in in_dbf: stringola = (paese, iso_paese, name_adm, code_adm, temporal_value, rec['VALUE'], rec['SUM']) lista.append(stringola) in_dbf.close() except: pass return lista
def DBFtoCSV(): '''Convert every DBF table into CSV table. ''' # Set new workplace where tables are located env.workspace = pathlist[1] # list tables in file tablelist = arcpy.ListTables() # iterate through every table for table in tablelist: #make sure you are just working with .dbf tables if table.endswith('.dbf'): #name csv the same as the .dbf table just with .csv at the end csv_fn = table[:-4] + ".csv" with open(pathlist[2] + csv_fn, 'wb') as csvfile: in_db = dbf.Dbf(pathlist[1] + table) out_csv = csv.writer(csvfile) #copy row names and items in rows from dbf to csv names = [] for field in in_db.header.fields: names.append(field.name) out_csv.writerow(names) for rec in in_db: out_csv.writerow(rec.fieldData) in_db.close() #keep track of processing print "\n Processing ", table[:-4] + ".csv table complete."
def _read_DBF(src_file, entity, f, dictionary): dataset = dbf.Dbf(src_file) balance = Balance() for record in dataset: if (dictionary.isSkipEmployee(record['TN'])): continue if (record['UP'] is not None and record['UP'] < dictionary.arcMinDate): continue if (record['CD'] == 'Ќачальное—альдо'): try: employeeNumberID = record['TN'] periodCalc = record['UP'] sumFrom, sumTo = balance.get((employeeNumberID, periodCalc)) sumFrom = record['SM'] balance.set((employeeNumberID, periodCalc), (sumFrom, sumTo)) prevPeriodCalc = (periodCalc + timedelta(days=-1)).replace(day=1) sumFrom, sumTo = balance.get( (employeeNumberID, prevPeriodCalc)) sumTo = record['SM'] balance.set((employeeNumberID, prevPeriodCalc), (sumFrom, sumTo)) except: print 'Error accrual balance tabNum:', record[ 'TN'], sys.exc_info()[1] dataset.close() balance.write(f, dictionary)
def dbf_to_list(dbf_file, field_name): if os.path.splitext(dbf_file)[1] == '.shp': dbf_file = os.path.splitext(dbf_file)[0] + '.dbf' #The next exception that is handled is handled within an if loop #This exception would occur if a non .dbf file was entered #First it finds wither the extension is not a .dbf by splitting the extension out if os.path.splitext(dbf_file)[1] != '.dbf': #Then the user is prompted with what occured and prompted to exit as above print 'Must input a .dbf file' print 'Cannot compile ' + dbf_file raw_input('Press enter to continue') sys.exit() #Finally- the actual function code body #First the dbf file is read in using the dbfpy Dbf function db = dbf.Dbf(dbf_file) #Db is now a dbf object within the dbfpy class #Next find out how long the dbf is rows = len(db) #Set up a list variable to write the column numbers to out_list = [] #Iterate through each row within the dbf for row in range(rows): #Add each number in the specified column number to the list out_list.append(db[row][field_name]) db.close() #Return the list #This makes the entire function equal to the out_list return out_list
def _read_DBF(src_file, accrual, f, dictionary, suffix): dataset = dbf.Dbf(src_file) for record in dataset: if (record['CD'] == 'НачальноеСальдо'): continue if (dictionary.isSkipEmployee(record['TN'])): continue if (record['UP'] is not None and record['UP'] < dictionary.arcMinDate): continue code = record['CD'] + suffix accrual.ID += 1 accrual.periodCalc = record['UP'] accrual.periodSalary = record['RP'] accrual.tabNum = str(record['TN']) accrual.taxCode = dictionary.get_TaxCode(accrual.tabNum) accrual.employeeNumberID = accrual.tabNum = str(record['TN']) accrual.payElID = dictionary.get_PayElID(code) accrual.paySum = record['SM'] != 0 and str(record['SM']) or '0' accrual.days = record['DAYS'] != 0 and str(record['DAYS']) or '' accrual.hours = record['HRS'] != 0 and str(record['HRS']) or '' accrual.calculateDate = '' accrual.flagsRec = str( 8 | (record['STOR'] > 0 and 512 or 0)) # 8 - import, 512 - storno accrual.dateFrom = record['PR_BEG'] accrual.dateTo = record['PR_END'] accrual.write_record(f) dataset.close()
def convert_dbf2csv(dbfpath, csvpath, footer): print "Converting %s to csv" % dbfpath writingddata = [] in_db = dbf.Dbf(dbfpath) decimalCounts = [] index_array = [0] * len(Constants.USE_HEADER) src_index = 0 for field in in_db.header.fields: if field.name in Constants.USE_HEADER: index = Constants.USE_HEADER.index(field.name) index_array[index] = src_index decimalCounts.append(field.decimalCount) src_index = src_index + 1 for rec in in_db: row = [normalize_boolean(x) for x in rec.fieldData] row = [ format_float(row[x], decimalCounts[x]) for x in xrange(len(row)) ] row = format_sms(row, index_array, footer) if remove_nulldata(row): writingddata.append(row) in_db.close() #並び替える writingddata.sort(key=lambda x: x[3] + x[0] + x[2]) with open(csvpath, 'wb') as csvfile: out_csv = csv.writer(csvfile) for row in writingddata: out_csv.writerow(row) print " Done..."
def post_func(directory, dbname, userr, pas, hostt, portt): ank = dbf.Dbf(directory + "zoleav.dbf") f_ank = open(directory + 'oleav.csv', 'w') f_ank.write("ID; ORGBASE_RN; FCAC_RN; DAYTYPE_RN; STARTDATE; ENDDATE\n") #print len(ank) for i in ank: if i.deleted or i["OLEAV_RN"] == None or i["FCAC_RN"] == None or i[ "DAYTYPE_RN"] == None or i["ORGBASE_RN"] == None or i[ "STARTDATE"] == None or i["ENDDATE"] == None: continue if not i["ORGBASE_RN"] or not i["DAYTYPE_RN"] or not i[ "OLEAV_RN"] or not i[ "FCAC_RN"] or parus_id_to_odoo.parusIndexToOdoo( i["FCAC_RN"].decode('cp1251').encode('utf-8').decode( 'utf-8')) in [ 33, 1939, 1317, 3132, 3372, 3127, 257, 313 ]: continue if i["STARTDATE"] < datetime.date(2014, 12, 01): continue f_ank.write("\"" + str( parus_id_to_odoo.parusIndexToOdoo(i["OLEAV_RN"].decode( 'cp1251').encode('utf-8').decode('utf-8'))) + "\"" + "; ") f_ank.write("\"" + str( parus_id_to_odoo.parusIndexToOdoo(i["ORGBASE_RN"].decode( 'cp1251').encode('utf-8').decode('utf-8'))) + "\"" + "; ") f_ank.write("\"" + str( parus_id_to_odoo.parusIndexToOdoo(i["FCAC_RN"].decode( 'cp1251').encode('utf-8').decode('utf-8'))) + "\"" + "; ") f_ank.write("\"" + str( parus_id_to_odoo.parusIndexToOdoo(i["DAYTYPE_RN"].decode( 'cp1251').encode('utf-8').decode('utf-8'))) + "\"" + "; ") f_ank.write("\"" + str(i["STARTDATE"]) + "\"" + "; " + "\"" + str(i["ENDDATE"]) + "\"" + "\n")
def hr_employeeAccrual(src_path, dst_path, dictionary): dst_file = dst_path + 'hr_employeeAccrual.csv' try: dataset = dbf.Dbf(src_path + 'NCH.DBF') f = open(dst_file, 'w+') f.write('ID;employeeID;tabNum;taxCode;employeeNumberID;payElID;dateFrom;dateTo;accrualSum;accrualRate;' + 'orderNumber;orderDatefrom;taxCode\n') ID = 0 for record in dataset: if (dictionary.isSkipEmployee(record['TN'])): continue if (record['DATK'] is not None and record['DATK'] < dictionary.arcMinDate): continue ID += 1 employeeID = str(record['TN']) # str(record['ID']) tabNum = str(record['TN']) taxCode = dictionary.get_TaxCode(tabNum) employeeNumberID = str(record['TN']) payElID = dictionary.get_PayElID(record['CD']) dateFrom = record['DATN'] and record['DATN'] or '' dateTo = record['DATK'] and record['DATK'] or '9999-12-31' accrualSum = str(record['SM']) accrualRate = str(record['PRC']) orderNumber = '' # record['CDPR'] orderDatefrom = '' taxCode = dictionary.get_TaxCode(tabNum) f.write('%d;%s;%s;%s;%s;%s;%s;%s;%s;%s;%s;%s;%s\n' % (ID, employeeID, tabNum, taxCode, employeeNumberID, payElID, dateFrom, dateTo, accrualSum, accrualRate, orderNumber, orderDatefrom, taxCode)) dataset.close() except: print 'Error making ', dst_file, sys.exc_info()[1]
def person_func(directory, dbname, userr, pas, hostt, portt): ank = dbf.Dbf(directory + "zpost.dbf") f_ank = open(directory + 'post.csv', 'w') f_ank.write("ID; STARTDATE; ENDDATE; POST_NUM; NAME\n") #print len(ank) for i in ank: if i.deleted or i["POST_CODE"] == None or i["ENDDATE"] == None or i[ "STARTDATE"] == None: continue f_ank.write("\"" + str( parus_id_to_odoo.parusIndexToOdoo(i["POST_RN"].decode( 'cp1251').encode('utf-8').decode('utf-8'))) + "\"" + "; ") f_ank.write("\"" + str(i["STARTDATE"]) + "\"" + "; ") f_ank.write("\"" + str(i["ENDDATE"]) + "\"" + "; ") f_ank.write("\"" + i["POST_NUM"] + "\"" + "; ") f_ank.write("\"" + i["POST_CODE"].decode('cp1251').encode('utf-8') + "\"" + "\n") f_ank.close() print "zpost.dbf to post.csv [ ok ]" #CONNECT TO DATABASE con = psycopg2.connect(database=dbname, user=userr, password=pas, host=hostt, port=portt) cur = con.cursor() #cur.execute ("DELETE from tabel_post;") #OPEN CSV FILE GENERATED BY syncronize.py script my_file = open(directory + 'post.csv') #CREATE TEMP TABLE cur.execute( "CREATE TEMP TABLE tmp_z (ID int unique, STARTDATE date, ENDDATE date, NUM int, NAME text);" ) cur.copy_expert("COPY tmp_z FROM STDIN WITH DELIMITER ';' CSV HEADER;", my_file) #cur.execute ("DELETE from tabel_fcac;") #UPDATE DATA cur.execute( "UPDATE tabel_post SET STARTDATE=tmp_z.STARTDATE, ENDDATE=tmp_z.ENDDATE, NUM=tmp_z.NUM, NAME=tmp_z.NAME FROM tmp_z WHERE tabel_post.id = tmp_z.id;" ) #cur.execute("SELECT G.id, G.ANK_RN, G.POST_RN, G.SUBDIV_RN, G.VIDISP_RN, G.STARTDATE, G.ENDDATE FROM (SELECT T.id, T.ANK_RN, T.POST_RN, T.SUBDIV_RN, T.VIDISP_RN, T.STARTDATE, T.ENDDATE FROM tmp_z AS T LEFT JOIN tabel_fcac AS P ON T.id = P.id WHERE P.id IS NULL) AS G, tabel_ank AS H where G.ank_rn = H.id ;") #INSERT DATA add something which lacks cur.execute( "INSERT INTO tabel_post (id, startdate, enddate, num, name) SELECT T.id, T.STARTDATE, T.ENDDATE, T.NUM, T.NAME FROM tmp_z AS T LEFT JOIN tabel_post AS P ON T.id = P.id WHERE P.id IS NULL ;" ) #rows = cur.fetchall() #for i in rows: # print i #DROP TEMP TABLE or auto drop after session cur.execute("DROP TABLE tmp_z;") #CLOSE CONNECTION con.commit() cur.close() con.close() print "sql requests for table post [ ok ]"
def convertDbf2Csv(dbf_file, csv_file): if os.path.exists(dbf_file): in_db = dbf.Dbf(dbf_file) fieldnames_list = [] for field in in_db.header.fields: fieldnames_list.append(field.name) if six.PY2: out_csv = csv.writer(open(csv_file, 'wb')) out_csv.writerow(fieldnames_list) for rec in in_db: out_csv.writerow(rec.fieldData) else: with open(csv_file, 'w', newline='') as csvfile: writer = csv.DictWriter(csvfile, fieldnames=fieldnames_list) writer.writeheader() for rec in in_db: fieldData_dico = {} for index in range(len(fieldnames_list)): name_col = fieldnames_list[index] value = rec.fieldData[index] fieldData_dico[name_col] = value writer.writerow(fieldData_dico) in_db.close() else: print(cyan + "convertDbf2Csv() : " + endC + bold + yellow + "Impossible de lire le fichier dbf : " + dbf_file + " . Fichier inexistant!" + endC) return
def hr_position(src_path, dst_path, dictionary): dst_file = dst_path + 'hr_position.csv' try: f = open(dst_file, 'w+') position = Position() position.write_header(f) dataset = dbf.Dbf(src_path + 'PRK.DBF') position_list = set() for record in dataset: department_code = record['PDR'] if (department_code): department_id = dictionary.get_DepartmentID(department_code) dictPosition_id = record['DOL'] if (department_id and dictPosition_id): position.ID = int(department_id) * 10000 + dictPosition_id if (position.ID in position_list): continue position_list.add(position.ID) position.code = str(dictPosition_id) position.name = dictionary.get_DictPositionName( position.code) position.psCategory = '' position.positionType = '' position.description = '' position.write_record(f) dataset.close() except: print 'Error making ', dst_file, sys.exc_info()[1]
def hr_employeeNumber(src_path, dst_path, dictionary): dst_file = dst_path + 'hr_employeeNumber.csv' try: dataset = dbf.Dbf(src_path + 'LS.DBF') f = open(dst_file, 'w+') f.write( 'ID;employeeID;taxCode;tabNum;dateFrom;dateTo;description;payOutID;personalAccount\n' ) for record in dataset: if (dictionary.isSkipEmployee(record['TN'])): continue ID = str(record['TN']) # str(record['ID']) employeeID = str(record['TN']) taxCode = record['NLP'] tabNum = str(record['TN']) dateFrom = record['BEG'] and record['BEG'] or '' dateTo = record['END'] and record['END'] or '' description = record['FIO'] + ' (' + str(record['TN']) + ')' payOutID = '' personalAccount = record['BANKRAH'] f.write('%s;%s;%s;%s;%s;%s;%s;%s;%s\n' % (ID, employeeID, taxCode, tabNum, dateFrom, dateTo, description, payOutID, personalAccount)) dataset.close() except: print 'Error making ', dst_file, sys.exc_info()[1]
def convert_dbf_to_csv(folder): for dbfFilename in (glob(folder + '*.dbf')): inDBF = dbf.Dbf(dbfFilename) csvFilename = dbfFilename.replace('.dbf', '.csv') outCSV = open(csvFilename, 'wb') csvWriter = csv.writer(outCSV) names = [] for field in inDBF.header.fields: names.append(field.name) csvWriter.writerow(names) for rec in inDBF: csvWriter.writerow(rec.fieldData) inDBF.close() outCSV.close() if os.path.exists(csvFilename): try: os.remove(dbfFilename) except OSError: pass try: os.remove(dbfFilename + '.xml') except OSError: pass return None
def __init__(self, filename, delimiter=',', quotechar='"', has_headers=True): from dbfpy import dbf self.delimiter = delimiter self.quotechar = quotechar self.filename = filename self.data = [] self.width = 0 self.headers = [] db = dbf.Dbf(self.filename, True) self.headers = db.fieldNames # using the default `for row in db` will cause a crash on invalid # fields produced by QGIS for i in range(0, len(db)): row_dict = {} for key in self.headers: header = db.header[key] try: record = db[i].rawFromStream(db, i) val = header.decodeFromRecord(record) except ValueError: val = None row_dict[key] = val self.data.append(Row(row_dict)) # Cleaner method, whenever this issue is fixed in DBFpy """
def file_to_dict(self, clear_null=0, clear_num_key='trade_date', file_type='Excel', del_key='', multifile_mode=False): def find_the_column(table, col_name, found_col_num="NA"): for i in range(table.ncols): if table.cell(0, i).value.encode('utf-8') == col_name: found_col_num = i break if found_col_num == "NA": raise Exception, "Invalid Column Name!" return found_col_num def dict_null_clear(dict, key): for i in range(len(dict[key])): if dict[key][i] == "": for k in dict.keys(): del dict[k][i] data_dict = {} file_li = [] if multifile_mode == True: filename = os.listdir(os.getcwd()) for fn in filename: if fn.startswith(self.filename): file_li.append(fn) if file_type == 'Excel': data = xlrd.open_workbook(self.filename) table = data.sheet_by_index(0) for key in self.file_db_relation.keys(): data_dict[key] = table.col_values( find_the_column(table, self.file_db_relation[key]))[1:] #[1:] cause first row is always title. if clear_null == 1: dict_null_clear(data_dict, clear_num_key) elif file_type == "DBF": dbffile = DBF(self.filename) frame = DataFrame(iter(dbffile)) for k in self.file_db_relation.keys(): data_dict[k] = list(frame[self.file_db_relation[k]]) elif file_type == "DBF2": #It's available when multifile_mode=True for file in file_li: print "Reading the file %s,waiting..." % file dbffile = dbf.Dbf(file, readOnly=True) for fn in self.file_db_relation.keys(): li = [] for i in range(0, len(dbffile)): li.append( str(dbffile[i][self.file_db_relation[fn]]).strip()) data_dict[fn] = data_dict.setdefault(fn, []) + li else: raise Exception, 'Invalid file type!' if not del_key == '': print data_dict del data_dict[del_key] return data_dict
def pfcac_func(directory, dbname, userr, pas, hostt, portt): ank = dbf.Dbf(directory + "zdaytype.dbf") f_ank = open(directory + 'daytype.csv', 'w') f_ank.write("ID; NICK; NAME;\n") #print len(ank) for i in ank: if i.deleted or i["NICK"] == None or i["NAME"] == None or i[ "DAYTYPE_RN"] == None: continue f_ank.write("\"" + str( parus_id_to_odoo.parusIndexToOdoo(i["DAYTYPE_RN"].decode( 'cp1251').encode('utf-8').decode('utf-8'))) + "\"" + "; ") f_ank.write("\"" + str(i["NICK"].decode('cp1251').encode('utf-8')) + "\"" + ";") f_ank.write("\"" + str(i["NAME"].decode('cp1251').encode('utf-8')) + "\"" + "\n") print "daytype.dbf to daytype.csv [ ok ]" f_ank.close() #CONNECT TO DATABASE con = psycopg2.connect(database=dbname, user=userr, password=pas, host=hostt, port=portt) cur = con.cursor() #cur.execute ("DELETE from tabel_fcacwth;") #OPEN CSV FILE GENERATED BY syncronize.py script my_file = open(directory + 'daytype.csv') #CREATE TEMP TABLE cur.execute( "CREATE TEMP TABLE tmp_z (ID int unique, NICK text, NAME text);") cur.copy_expert("COPY tmp_z FROM STDIN WITH DELIMITER ';' CSV HEADER;", my_file) #UPDATE DATA cur.execute( "UPDATE tabel_daytype SET NICK=tmp_z.NICK, NAME=tmp_z.NAME FROM tmp_z WHERE tabel_daytype.id = tmp_z.id;" ) #cur.execute("SELECT G.id, G.ANK_RN, G.POST_RN, G.SUBDIV_RN, G.VIDISP_RN, G.STARTDATE, G.ENDDATE FROM (SELECT T.id, T.ANK_RN, T.POST_RN, T.SUBDIV_RN, T.VIDISP_RN, T.STARTDATE, T.ENDDATE FROM tmp_z AS T LEFT JOIN tabel_fcac AS P ON T.id = P.id WHERE P.id IS NULL) AS G, tabel_ank AS H where G.ank_rn = H.id ;") #INSERT DATA add something which lacks. #add those records that are in the table tmp.z and not in the table fcacwth. And check that all the fields in the table fcac_rn existed fcac cur.execute( "INSERT INTO tabel_daytype (id, nick, name) SELECT G.id, G.NICK, G.NAME FROM (SELECT T.id, T.NICK, T.NAME FROM tmp_z AS T LEFT JOIN tabel_daytype AS P ON T.id = P.id WHERE P.id IS NULL) AS G ;" ) #rows = cur.fetchall() #for i in rows: # print i #DROP TEMP TABLE or auto drop after session cur.execute("DROP TABLE tmp_z;") #CLOSE CONNECTION con.commit() cur.close() con.close() print "sql requests for table daytype [ ok ]"
def CustomizeSummary(name, year, day): db = dbf.Dbf(name) for rec in db: zone = rec["FIPS"] count = rec["COUNT"] summ = rec["SUM"] mean = rec["MEAN"] stddev = rec["STD"] self.InsertDB(year, day, zone, count, mean, stddev, summ)