Read data from Excel and convert them to JSON!

In this example, we're going to read an Excel spreadsheet and convert into JSON format.

For this purpose, we have an Excel spreadsheet with four columns.

With following script in Python, we can load the spreadsheet and convert it to JSON format:

import xlrd from collections import OrderedDict import simplejson as json   # Open the workbook and select the first worksheet wb = xlrd.open_workbook('excel-xlrd-sample.xls') sh = wb.sheet_by_index(0)   # List to hold dictionaries cars_list = []   # Iterate through each row in worksheet and fetch values into dict for rownum in range(1, sh.nrows): cars = OrderedDict() row_values = sh.row_values(rownum) cars['car-id'] = row_values[0] cars['make'] = row_values[1] cars['model'] = row_values[2] cars['miles'] = row_values[3]   cars_list.append(cars)   # Serialize the list of dicts to JSON j = json.dumps(cars_list)   # Write to file with open('data.json', 'w') as f: f.write(j)

Read data from Excel

This example shows how to read data from an Excel file.

Note: Xlrd is for reading data and formatting information from older Excel files (ie: .xls)

import xlrd   # Open the workbook wb = xlrd.open_workbook('excel-xlrd-sample.xls')   # Print the sheet names print wb.sheet_names()   # Get the first sheet either by index or by name sh = wb.sheet_by_index(0)   # Iterate through rows, returning each as a list that you can index: for rownum in range(sh.nrows): print sh.row_values(rownum)   # If you just want the first column: first_column = sh.col_values(0) print first_column   # Index individual cells: cell_c4 = sh.cell(3, 2).value # Or you can use: #cell_c4 = sh.cell(rowx=3, colx=2).value print cell_c4   # Let's say you want the same cell from x identical sheets in a workbook: x = 2 while x >= 0: sh = wb.sheet_by_index(x) cell_x = sh.cell(2, 3).value print cell_x x = x - 1

You may use xlsxwriter to read the xlsx or the files generated by Excel 2010

Getting arguments from command line in python

Example

To read arguments from command line use following script

import sys

# main
param_1= sys.argv[1] 
param_2= sys.argv[2] 
param_3= sys.argv[3]  
print 'Params=', param_1, param_2, param_3