--- a/data/xls2csv.py +++ b/data/xls2csv.py @@ -1,1 +1,31 @@ +''' 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(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) + 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) +