| ''' 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 * | from xlrd import * |
| import unicodecsv | import unicodecsv |
| import glob | import glob |
| import codecs | import codecs |
| def cleanseValue(value): | def cleanseValue(value): |
| if isinstance(value, basestring): | if isinstance(value, basestring): |
| value = "".join([x if ord(x) < 128 else '' for x in value]) | value = "".join([x if ord(x) < 128 else '' for x in value]) |
| value = value.replace('<','>').replace('>','<').replace('\n','<br/>').replace('\r','<br/>') | value = value.replace('<','>').replace('>','<').replace('\n','<br/>').replace('\r','<br/>') |
| else: | else: |
| value = str(value) | value = str(value) |
| return value | return value |
| for filename in glob.glob("*.xls"): | for filename in glob.glob("*.xls"): |
| print filename | print filename |
| b = open_workbook(filename) | b = open_workbook(filename) |
| for sheet in b.sheet_names()[2:]: | for sheet in b.sheet_names()[2:]: |
| outputfile = filename.replace(".xls","") + sheet.replace(".","-").replace(" ","_").replace("&","and") + ".csv" | outputfile = filename.replace(".xls","") + sheet.replace(".","-").replace(" ","_").replace("&","and") + ".csv" |
| print outputfile | print outputfile |
| s = b.sheet_by_name(sheet) | s = b.sheet_by_name(sheet) |
| rbc = open(outputfile,'w') | rbc = open(outputfile,'w') |
| bcw = unicodecsv.writer(rbc,unicodecsv.excel) | bcw = unicodecsv.writer(rbc,unicodecsv.excel) |
| for row in range(s.nrows): | for row in range(s.nrows): |
| this_row = [] | this_row = [] |
| for col in range(s.ncols): | for col in range(s.ncols): |
| this_row.append(cleanseValue(s.cell_value(row,col))) | this_row.append(cleanseValue(s.cell_value(row,col))) |
| bcw.writerow(this_row) | bcw.writerow(this_row) |