"""DCaaS Services XLS to CSV converter Converts the DCaaS XLS file to CSV while removing line breaks and other special characters Alex Sadleir """ from xlrd import * import unicodecsv import glob import codecs def cleanseValue(column,value): if isinstance(value, basestring): value = "".join([x if ord(x) < 128 else '' for x in value]) value = value.replace('<','>').replace('>','<').replace('\n','
').replace('\r','
') else: value = str(value) if column == 'ACNABN' and value != None: value = value.replace(' ','').replace(".0","").replace('"','').replace(",","") value = value.replace("ABN"," ").replace("ACN"," ").replace("/"," ").replace(":"," ") value = value.replace(" "," ").strip() valueParts = value.split(' ') if len(valueParts) == 2: if len(valueParts[0]) > len(valueParts[1]): value = valueParts[0] if len(valueParts[0]) < len(valueParts[1]): value = valueParts[1] if value == "351222885465": value = "35122885465" if value == "067060937171": value = "67060937171" if value == "148179846": value = "49148179846" if value == "": value = "62081001194" #verizon return value for filename in glob.glob("*.xls"): print filename b = open_workbook(filename) for sheet in b.sheet_names()[2:]: outputfile = filename.replace(".xls","") + sheet.replace(".","-").replace(" ","_").replace("&","and") + ".csv" print outputfile s = b.sheet_by_name(sheet) rbc = open(outputfile,'w') bcw = unicodecsv.writer(rbc,unicodecsv.excel) rowNum = 0 headerRow = [] for row in range(s.nrows): this_row = [] for col in range(s.ncols): this_row.append(cleanseValue(headerRow[col] if col < len(headerRow) else None , s.cell_value(row,col))) bcw.writerow(this_row) if rowNum == 0: headerRow = this_row rowNum = rowNum + 1