i have json file have attached below. have read json file in python. file containes path of xls file containing multiple sheets, needs cleaned , output each sheet separate csv files. idea on how can go about?
{ "file":{ "path":"c:/.../xyz.xlsx", "sheetname":"sheet1" "clean":{ "1":"a", "2":"b", "3":"c" }, "delete":{ "1":"d", "2":"e" }, "outfile":"c:/.../out_xyz.csv" } } i referred few links have attached below, i'm still in vain!
reading json file?
how can split excel (.xls) file contains multiple sheets separate excel files?
save each sheet in workbook separate csv files
how this?
use python , xlrd & xlwt. see http://www.python-excel.org
the following script should want:
import xlrd, xlwt, sys def raj_split(in_path, out_stem): in_book = xlrd.open_workbook(in_path) in_sheet = in_book.sheet_by_index(0) first_row = in_sheet.row_values(0) # find rightmost 1 value in first row split_pos = max( colx colx, value in enumerate(first_row) if value == 1.0 ) + 1 out_book = xlwt.workbook() out_sheet = out_book.add_sheet("sheet1", cell_overwrite_ok=true) # copy common cells rowx in xrange(in_sheet.nrows): row_vals = in_sheet.row_values(rowx, end_colx=split_pos) colx in xrange(split_pos): out_sheet.write(rowx, colx, row_vals[colx]) out_num = 0 # each output file ... out_col in range(split_pos, in_sheet.ncols): out_num += 1 # ... overwrite `split_pos` column rowx, value in enumerate(in_sheet.col_values(colx=out_col)): out_sheet.write(rowx, split_pos, value) # ... , save file. out_book.save("%s_%03d.xls" % (out_stem, out_num)) raj_split(*sys.argv[1:3]) 
Comments
Post a Comment