Python Forum

Full Version: Issue with a script to convert xls to json
You're currently viewing a stripped down version of our content. View the full version with proper formatting.
Hi there,

It seems that I have an issue with this python 2 script (from https://github.com/paceaux/xlsx-to-json) translated to py3.

Any idea on how I could improve it?
It return the following
Quote:Enter the path to the filename -> C:\......\MS1.xls
Traceback (most recent call last):
File "C:\......xls2json.py", line 69, in <module>
main()
File "C:\......\xls2json.py", line 63, in main
output.write(json.dumps(workbookdata, sort_keys=True, indent=2, separators=(',', ": ")))
TypeError: a bytes-like object is required, not 'str'


import xlrd
import xlwt
import json
import os.path
import datetime

def getColNames(sheet):
	rowSize = sheet.row_len(0)
	colValues = sheet.row_values(0, 0, rowSize )
	columnNames = []

	for value in colValues:
		columnNames.append(value)

	return columnNames

def getRowData(row, columnNames):
	rowData = {}
	counter = 0

	for cell in row:
		# check if it is of date type print in iso format
		if cell.ctype==xlrd.XL_CELL_DATE:
			rowData[columnNames[counter].lower().replace(' ', '_')] = datetime.datetime(*xlrd.xldate_as_tuple(cell.value,0)).isoformat()
		else:
			rowData[columnNames[counter].lower().replace(' ', '_')] = cell.value
		counter +=1

	return rowData

def getSheetData(sheet, columnNames):
	nRows = sheet.nrows
	sheetData = []
	counter = 1

	for idx in range(1, nRows):
		row = sheet.row(idx)
		rowData = getRowData(row, columnNames)
		sheetData.append(rowData)

	return sheetData

def getWorkBookData(workbook):
	nsheets = workbook.nsheets
	counter = 0
	workbookdata = {}

	for idx in range(0, nsheets):
		worksheet = workbook.sheet_by_index(idx)
		columnNames = getColNames(worksheet)
		sheetdata = getSheetData(worksheet, columnNames)
		workbookdata[worksheet.name.lower().replace(' ', '_')] = sheetdata

	return workbookdata

def main():
	filename = input("Enter the path to the filename -> ")
	if os.path.isfile(filename):
		workbook = xlrd.open_workbook(filename)
		workbookdata = getWorkBookData(workbook)
		output = \
		open((filename.replace("xlsx", "json")).replace("xls", "json"), "wb")
		output.write(json.dumps(workbookdata, sort_keys=True, indent=2,  separators=(',', ": ")))
		output.close()
		print("%s was created" %output.name)
	else:
		print("Sorry, that was not a valid filename")

main()
At line 62, try to replace the "wb" mode with "w".
worked like a charm. thanks.