| ''' 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) | |