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