0

I have a json file that I have attached below. I have to read the json file in python. This file containes the path of my xls file containing multiple sheets, which needs to be cleaned and output each sheet as separate csv files. Any idea on how I 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 to few links that I have attached below, I'm still in vain!
Reading JSON from a file?
How can i split an Excel (.xls) file that contains multiple sheets into separate excel files?
Save each sheet in a workbook to separate CSV files

Cœur
  • 37,241
  • 25
  • 195
  • 267
anusha acharya
  • 11
  • 3

1 Answers1

0

How about this?

Use Python and xlrd & xlwt. See http://www.python-excel.org

The following script should do what you 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 the rightmost 1 value in the first row
    split_pos = max(
        colx for 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 the common cells
    for rowx in xrange(in_sheet.nrows):
        row_vals = in_sheet.row_values(rowx, end_colx=split_pos)
        for colx in xrange(split_pos):
            out_sheet.write(rowx, colx, row_vals[colx])
    out_num = 0
    # for each output file ...
    for out_col in range(split_pos, in_sheet.ncols):
        out_num += 1
        # ... overwrite the `split_pos` column
        for rowx, value in enumerate(in_sheet.col_values(colx=out_col)):
            out_sheet.write(rowx, split_pos, value)
        # ... and save the file.
        out_book.save("%s_%03d.xls" % (out_stem, out_num))

raj_split(*sys.argv[1:3])