1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 | ''' DCaaS Services XLS to CSV converter ''' Converts the DCaaS XLS file to CSV while removing line breaks and other special characters ''' Alex Sadleir <maxious@lambdacomplex.org> from xlrd import * import unicodecsv import glob import codecs def cleanseValue(value): if isinstance(value, basestring): value = "".join([x if ord(x) < 128 else '' for x in value]) value = value.replace('<','>').replace('>','<').replace('\n','<br/>').replace('\r','<br/>') else: value = str(value) 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) for row in range(s.nrows): this_row = [] for col in range(s.ncols): this_row.append(cleanseValue(s.cell_value(row,col))) bcw.writerow(this_row) |