def tasksfinal(data, test=False): ms_data = excel_list(mapfile, 'Milestones', folder) ms_map = excel_list(mapfile, 'Milestone Fields', folder) milestonedics = tasks.ms_details_dic(ms_data) newmsdic = milestonedics[2] data = tasks.addmilestones(data, newmsdic) mergedic = tasks.mergemilestones(data, ms_map) newdata = tasks.nslist(data, ms_map, mergedic, milestonedics, test) # final = columnsused(newdata) final = newdata return final
def projectSFIDdic(): folder = 'C:\\Users\jfew\OneDrive - Allbridge\\Netsuite Projects\\' idlist = excel_list('Lookups for Projects.xlsx', 'External ID', folder) dic = {} for row in idlist[1:]: proj = row[0] id = row[1] dic[proj] = id return dic
def mainoptions(): data = excel_list(mapfile, 'Data Selection', folder) head = data[0] dic = {} for row in data[1:]: crt = row[0] for n in range(0, len(head)): cat = head[n] if row[n] == True: if cat not in list(dic.keys()): dic[cat] = [crt] else: dic[cat].append(crt) return dic
def customerlookup(): folder = 'C:\\Users\jfew\OneDrive - Allbridge\\Netsuite Projects\\' nsextract = excel_list('Lookups for Projects.xlsx', 'Customers', folder) head = nsextract[0] internal = head.index('Internal ID') custnum = head.index('SF Customer Number') account = head.index('Name') dic = {} numv = None for row in nsextract[1:]: id = row[internal] num = row[custnum] act = row[account] if num != None: dic[num] = id if act != None: dic[act] = id # if cnum != None: # try: # numv = dic[cnum] # except KeyError: # numv = None # # try: # aval = dic[aname] # except KeyError: # aval = None # # if numv != None: # ans = numv # else: # if aval != None: # ans = aval # else: # ans = error # # if test == True: # ans = 16163 return dic
def projectsfinal(data, test=False): mapdic = proj.pmapdic(excel_list(mapfile, 'Project Fields', folder)) final = proj.ptrans(data, mapdic, test) return final
pdata = [projhead] tdata = [taskhead] pi = projhead.index('Case Record Type') ti = taskhead.index('Case: Case Record Type') for row in projfull[1:]: if row[pi] in select: pdata.append(row) for row in taskfull[1:]: if row[ti] in select: tdata.append(row) return pdata, tdata if __name__ == '__main__': test = True projext = excel_list(extractfile, 'Projects', folder) taskext = excel_list(extractfile, 'Milestones', folder) select = optionselect() re = removedata(projext, taskext, select) project = projectsfinal(re[0], test) task = tasksfinal(re[1], test) excelcreate(project, writefile, 'Projects', folder) excelcreate(task, writefile, 'Milestones', folder)
rows.append(r) return rows if __name__ == '__main__': folder = 'C:\\Users\\jfew\\OneDrive - Allbridge\\NetSuite Integration\\Sales Orders 1.6.20\\' mapfile = 'Scenarios 1-7 Missing Fields.xlsx' writefile = 'Scenarios 1 3 and 8 - 1.7.20 Add Missing Fields.xlsx' sheet = 'Scenario 1&3' writefile = 'Scenario 7 - Transform 1.4.20 Add Missing Fields.xlsx' sheet = 'MASTER' data = excel_list(writefile, sheet, folder) mdic = mapdic(excel_list(mapfile, 'Map', folder)) ext = excel_list(mapfile, 'Extract', folder) extdic = extractdic(ext, mdic) full = appenddata(data, extdic, mdic) print(full) wdic = {'Sheet1': full, 'Sheet2': [['Jackson Test']]} newfile = 'Scenario 7 New Fields.xlsx' txt = open('7.txt', 'w') textlist = textfile(full) txt.writelines(textlist) # txt.write('Jackson')
savefiles = {'Regular':'Regular Items 10.21.19.xlsx', 'Serial':'Serial Items 10.22.19.xlsx', \ 'Assembly':'Non-Serial Assembly 10.22.19.xlsx', 'Serial Assembly':'Serial Assembly 10.23.19.xlsx'} print('Please Select Item Type') for x in itemtypes: n = str(itemtypes.index(x)) + ': ' + x print(n) i = int(input()) item = itemtypes[i] file = savefiles[item] print(file) gla = gla_dic(excel_list('GLA MOD.xlsx', 'Mod', folder)) extid = extid_dic(excel_list('External_ID.xlsx', 'Sheet1', folder)) units = unit_dic( excel_list('Units of Measure 10.22.19.xlsx', 'Summary', folder)) bins = binloc( excel_list('Bins and Locations 10.22.19.xlsx', 'Summary', folder)) ven = vendors(excel_list('Vendors 10.22.19.xlsx', 'Vendors', folder)) itemdic = assItemdic(excel_list(assfile, 'ItemDic', folder)) guide = excel_list(mapfile, item, folder) extract = excel_list(extractfile, item, folder) full = ns_list(guide, extract, gla, extid,
name = 'Project Manager' row[head.index('Milestone Owner')] = name row[head.index('Default Resource')] = name return data if __name__ == '__main__': folder = 'C:\\Users\jfew\OneDrive - Allbridge\\Netsuite Projects\\' mapping = 'Project and Milestone Mapping.xlsx' # extract = 'Extract 12.31.19.xlsx' extract = 'Extract 1.17.20.xlsx' writefile = 'Milestone Transform.xlsx' data = excel_list(extract, 'Milestones', folder) ms_data = excel_list(mapping, 'Milestones', folder) ms_map = excel_list(mapping, 'Milestone Fields', folder) milestonedics = ms_details_dic(ms_data) newmsdic = milestonedics[2] print(milestonedics[0]) print(milestonedics[1]['Headend']['PTR']) test = False projlookup = projectInternalID( excel_list('NetSuite Projects.xlsx', 'Projects', folder), test) data = addmilestones(data, newmsdic, projlookup) mergedic = mergemilestones(data, ms_map)
def createdic(data): full = [] for row in data: t = row[-1] i = data.index(row) if t == 'Topic': topici = data.index(row) elif t == 'Question': qi = data.index(row) row.append(data[topici][1]) else: data[qi].append(row) for row in data: try: if row[2] == 'Question': full.append(row) except IndexError: print(row, data.index(row)) return full if __name__ == '__main__': raw = excel_list('AdminTestQuestions.xlsx', 'Sheet1') reform = reformatlist(raw) data = createdic(reform) print(data)
nrow[s] = item[0] nrow[qty_i] = item[4] # nrow[pack_i] = item[5] if nrow[s] == None: try: nrow[s] = sodic[so][0] except KeyError: nrow[s] = 'Open' if nrow[s] != 'Cancelled': full.append(nrow) return full if __name__ == '__main__': folder = 'C:\\Users\\jfew\\OneDrive - Allbridge\\NetSuite Integration\\Sales Orders 1.6.20\\' file = 'Scenario 8.xlsx' sfdata = excel_list('Ascent SO Lines Scenario 8.xlsx', 'Ascent SO', folder) kvdata = excel_list(file, 'Master', folder) pdic = productdic(sfdata) cdic = casedic(sfdata) sodic = sostatus(sfdata) final = combine(kvdata, cdic, pdic, sodic) excelcreate(final, file, 'Combine', folder)
def customname(data): id = data[0].index('Identifier') bom = data[0].index('BOM') dic = {} for row in data[1:]: dic[row[bom]] = row[id] return dic if __name__ == '__main__': folder = 'C:\\Users\\jfew\\OneDrive - Allbridge\\NetSuite Integration\\Items into Production 10.21.19' file = 'Assembly Components 12.23.19.xlsx' data = excel_list(file, 'Extract', folder) nsdic = nsitems(excel_list(file, 'Item IDs', folder)) custBOM = customname(excel_list(file, 'Custom Names', folder)) t = transform(data, custBOM) bomdic = t[0] itemdic = t[1] full = AssCompForm(data, nsdic, error=None, iid=True) bomlist = dictolist(bomdic) mem = 'Excel' bom = BOM(bomlist, memo=mem) rev = BOMrev(full, bomdic, memo=mem)
dic = {} for row in ext[1:]: op = row[head.index('Sales Order: Sales Order #')] item = row[head.index('Product 18 Char ID')] status = row[head.index('Status')] key = (op, item) dic[key] = status return dic if __name__ == '__main__': folder = 'C:\\Users\\jfew\\OneDrive - Allbridge\\NetSuite Integration\\Sales Orders 1.6.20\\' extfile = 'SO Line Status Extract 1.14.20.xlsx' sheet = 'Scenario 9' ext = excel_list(extfile, sheet, folder) sdic = statusdic(ext) file = 'SO Line Status Data 1.14.20.xlsx' data = excel_list(file, sheet, folder) full = statusupdate(data, sdic) writefile = 'SO Line Status Update 1.14.20.xlsx' excelcreate(full, writefile, sheet, folder)
n = row[0] elif row[1] != None: n = row[1] elif row[2] != None: n = row[2] elif row[3] != None: n = row[3] for x in row[5:]: if x != None and x != ':(': new.append([None, n, x]) return new if __name__ == '__main__': folder = 'C:\\Users\\jfew\\OneDrive - Allbridge\\NetSuite\\' # file = 'NetSuite Permissions 12.2.19.xlsx' # # # extract = excel_list(file, 'Extract', folder) # # data = perm_format(extract) # # print(data) # # excelcreate(data, file, 'Extract Table', folder) role = 'Operations Roles.xlsx' roledata = excel_list(role, 'Assign Roles', folder) newdata = roles(roledata) excelcreate(newdata, 'Operations Roles 12.9.19.xlsx', 'Operations', folder) print(newdata)
full.append(nrow) return full if __name__ == '__main__': folder = 'C:\\Users\\jfew\\OneDrive - Allbridge\\NetSuite Integration\\Sales Orders 1.6.20\\' # file = 'Scenario 7 1.16.20 Part 1.xlsx' file = 'Scenario 7 1.16.20 Split.xlsx' multi = True num = 5 if multi == True: comsheet = 'Sheet' + str(num) tsheet = 'Transform' + str(num) else: comsheet = 'Combine' tsheet = 'Transform' print('Start - ', dt.datetime.now()) mapping = pmapdic(excel_list('SO Mapping.xlsx', 'Mapping', folder)) print('Mapping - ', dt.datetime.now()) data = excel_list(file, comsheet, folder) print('Data - ', dt.datetime.now()) tran = transform(data, mapping) print('Transform - ', dt.datetime.now()) excelcreate(tran, file, tsheet, folder) print('Finish - ', dt.datetime.now())
def dictolist(dic): full = [] for x in list(dic.keys()): row = dic[x] row.insert(0, x) full.append(row) return full if __name__ == '__main__': folder = 'C:\\Users\\jfew\\OneDrive - Allbridge\\NetSuite\\' file = 'MPN Dupe Check.xlsx' report = excel_list(file, 'Report', folder) dupes = Dupes(report) itemdic = dupes[0] mpndic = dupes[1] nompn = dupes[2] mpn = dictolist(mpndic) sheetdic = {'MPN': mpn, 'No MPN': nompn} multiexcelwrite(file, sheetdic, folder) # excelcreate(mpn, file, 'MPN', folder)
data[0].append('Opp 18 ID') head = data[0] opid = head.index('Opportunity ID Ref') opp = head.index('Opportunity') full = [head] for row in data[1:]: nrow = row.copy() try: id18 = opdic[(row[opid], row[opp])] except KeyError: id18 = None nrow.append(id18) full.append(nrow) return full if __name__ == '__main__': folder = 'C:\\Users\jfew\OneDrive - Allbridge\\Netsuite Projects\\' file = 'Projects SO Update.xlsx' data = excel_list(file, 'Case Extract', folder) opp = oppdic(excel_list(file, 'Opp Extract', folder)) final = addopp(data, opp) excelcreate(final, file, 'Transform', folder)
sdic['Predecessor Sublist: Task'] = Taskdetail( )[old_crt][old_pretask][0] except KeyError: sdic['Predecessor Sublist: Task'] = None sdic['Predecessor Sublist: Type'] = None #Not Sure sdic['Task Length in Days'] = task_len(tstart, tend) sdic['PJ Task Subtab Link'] = tname for r in mapping[1:]: field = r[0] i = newhead.index(field) include = r[mapping[0].index(tname)] if include == True: if field in special: newrow[i] = sdic[field] else: sf = r[1] sf_i = datahead_dic[sf] newrow[i] = row[sf_i] datanew.append(newrow) return datanew if __name__ == '__main__': tmap = excel_list('Milestone Field Mapping.xlsx', 'COMM') data = excel_list('COMM Task Test 10.16.19.xlsx', 'Sheet1') newdata = dateconvert(task_transform(data, tmap)) excelcreate(newdata, 'COMM Task Test 10.16.19.xlsx', 'Sheet2')
full[ri][fi] = n full = dependmod(full, mapdic) return full if __name__ == '__main__': folder = 'C:\\Users\jfew\OneDrive - Allbridge\\Netsuite Projects\\' mapping = 'Project and Milestone Mapping.xlsx' extract = 'Extract 12.31.19.xlsx' writefile = 'Milestone Transform.xlsx' writesheet = 'Projects' test = False mapdic = pmapdic(excel_list(mapping, 'Project Fields', folder)) ext = excel_list(extract, 'Projects', folder) final = ptrans(ext, mapdic, test) # print(mapdic) print(final) excelcreate(final, writefile, writesheet, folder) # print(customerlookup('C67375', None, 'Missing'))
newhead = [] for f in head: if dic[f] != 0: newhead.append(f) newdata = [newhead] for row in full[1:]: newrow = [None] * len(newhead) for x in row: i = row.index(x) field = head[i] if dic[field] != 0: newi = newhead.index(field) newrow[newi] = x newdata.append(newrow) return newdata if __name__ == '__main__': folder = 'C:\\Users\jfew\OneDrive - Allbridge\\Netsuite Projects\\' file = 'System Types.xlsx' data = excel_list(file, 'Sheet4', folder) moddata = systemtypeTEST(data) excelcreate(moddata, file, 'Sheet4', folder)
transfile = 'Adjustment Transform.xlsx' adjtype = ['Regular', 'Serial'] sheetdic = { 'Regular': ['Bin', 'NoBin'], 'Serial': ['BinSerial', 'NoBinSerial'] } print('Please Select an Item Type') for x in adjtype: msg = str(adjtype.index(x)) + ': ' + x print(msg) i = int(input()) selec = adjtype[i] units = unit_dic( excel_list('Units of Measure 10.22.19.xlsx', 'Summary', folder)) bins = binloc( excel_list('Bins and Locations 10.22.19.xlsx', 'Summary', folder)) items = nsitems(excel_list(nsfile, 'Items', folder)) extract = excel_list(extractfile, selec, folder) guide = excel_list(mapfile, selec, folder) adj = Adj(extract, guide, items, bins, units) # full = adj[0] yesbin = finaladj(adj[1], None) nobin = finaladj(adj[2], None) # excelcreate(full, transfile, 'Full', folder) excelcreate(yesbin, transfile, sheetdic[selec][0], folder) excelcreate(nobin, transfile, sheetdic[selec][1], folder)