How to read the text in excel cell and replace with someother value in json output using python?

Question

My python code reads the excel sheet and converts it into a json file output. I have a column in the excel sheet, where the values are either "Planned" or "Unplanned".

1)In the json output, I want the Planned to be replaced with "1" and Unplanned to be replaced with "2" without changing anything in the excel file. 2)In the output I dont want "data" to appear. 3)In the excel, my Start time column value is like this "2018-11-16 08:00:00". I want the output to be "2018-11-16T08:00:00Z". Currently i am getting some garbage value. Below is my code.

import xlrd, json, time, pytz, requests
from os import sys
from datetime import datetime, timedelta
from collections import OrderedDict

def json_from_excel():
    excel_file = 'test.xlsx'
    jsonfile = open('ExceltoJSON.json', 'w')
    data = []
    datestr = str(datetime.now().date())
    loaddata = OrderedDict()

    workbook = xlrd.open_workbook(excel_file)
    worksheet = workbook.sheet_by_name('OMS-GX Data Extraction')
    sheet = workbook.sheet_by_index(0)

for j in range(0, 6):
    for i in range(1, 40):
        temp = {}
        temp["requestedStart"] = (sheet.cell_value(i,0))      #Start Time
        temp["requestedComplete"] = (sheet.cell_value(i, 1))  #End Time
        temp["location"] = (sheet.cell_value(i, 3))           #Station
        temp["equipment"] = (sheet.cell_value(i, 4))          #Device Name
        temp["switchOrderTypeID"] = (sheet.cell_value(i, 5))  #Outage Type
        data.append(temp)
        loaddata['data'] = data



    json.dump(loaddata, jsonfile, indent=3, sort_keys=False)
    jsonfile.write('\n')
    return loaddata



 if __name__ == '__main__':
    data = json_from_excel()

Below is my sample output:

 {
   "data": [
      {
         "requestedStart": testtime,
         "requestedComplete": testtime,
         "location": "testlocation",
         "equipment": "testequipment",
         "switchOrderTypeID": "Planned"
      },
      {
         "requestedStart": testtime,
         "requestedComplete": testtime,
         "location": "testlocation",
         "equipment": "testequipment",
         "switchOrderTypeID": "Unplanned"
      }
   ]
}

Mudra · Accepted Answer · 2019-04-02T06:00:06.347

1

Answer to the 1st question: You may use conditional assignment.

temp["switchOrderTypeID"] = (1 if sheet.cell_value(i, 5) == "Planned" else 0)

Answer to the 2nd question: Use loaddata = data which will be an array of the jsons without data as json key.

Answer to 3rd question:

from dateutil.parser import parse t = "2018-11-16 08:00:00" parse(t).strftime("%Y-%m-%dT%H:%M:%SZ")

edited Apr 02 '19 at 06:00

answered Apr 02 '19 at 05:27

Mudra

36
8

Thanks it worked. Could you help for the second one? – Sidharth Gokul Apr 02 '19 at 05:31
1

Try `loaddata = data`. It will be an array of json if that works. Because currently, `data` is a json key which holds your rest of the data. Or let me know what is the expected output format. – Mudra Apr 02 '19 at 05:39
I have added a third question. Would you please help with that too? – Sidharth Gokul Apr 02 '19 at 05:51
For the answer to the 3rd question, Could you please let me know how to change in the line here, temp["requestedStart"] = (sheet.cell_value(i,0)) – Sidharth Gokul Apr 02 '19 at 06:16
replace t with your cell value. `temp["requestedStart"] = parse(sheet.cell_value(i,0)).strftime("%Y-%m-%dT%H:%M:%SZ")` – Mudra Apr 02 '19 at 06:44
This is the error for the above changes, TypeError: Parser must be a string or character stream, not float – Sidharth Gokul Apr 02 '19 at 06:53
could you tell me what is the value of sheet.cell_value(i,0) and type of the same? – Mudra Apr 02 '19 at 07:01
In excel sheet, the value is "2018-11-16 08:00:00" . But in json output, for sheet.cell_value(1, 0) is 43420.333336 – Sidharth Gokul Apr 02 '19 at 07:08
If sheet.cell_value(i,0) is string then it has to be parsed and then use strftime `parse(t).strftime("format")`. Othewise if it is a datetime object already, directly use strftime `t.strftime("format"). – Mudra Apr 02 '19 at 07:09
Refer this: You will have to change the way of fetching date from excel. https://stackoverflow.com/questions/1108428/how-do-i-read-a-date-in-excel-format-in-python – Mudra Apr 02 '19 at 07:11

How to read the text in excel cell and replace with someother value in json output using python?

1 Answers1