I hate copying and pasting, so might as well automate it

9:43:24 PM Trying to complete my research paper.. …

The script below helped me with some research data for my Research Module (wish me good luck!)

If you have any question, do ask in the comment section. See you!

This allows for transforming my .ods file to .xlsx
//Note --> You will have to tweak this to your needs. This is a personalised solution
#USAGE python getdata.py 

from pyexcel_ods import get_data
import os , re, json, openpyxl, logging, typing, sys

logging.basicConfig(level=logging.DEBUG, format="%(asctime)s - %(levelname)s - %(message)s")

def get_rows_xy(sheet: typing.List ):
			sheet : a multidimensional list
		Gets all the xy data 
		returns : list 
	start = False #when true, starts appending to the data list
	data = []
	for sub_list in sheet:
		if len(sub_list) == 0:
		if start:
		if "XYDATA" in sub_list: #searches for XYDATA string in a sublist
			start = True 
	return comma_to_dot(data)

def comma_to_dot(rows: typing.List ):
		transform a German format number to US format e.g. 100, 36 to 100.36
		arguments : rows -> list 
		returns:  data -> transformed rows (all values are numbers with "," replaced with ".")
	data = []
	pattern = re.compile(r"(\d+),(\d+)")
	for sub_row in rows:
		temp = []
		for value in sub_row:
			if type(value) == int: #my data was weird.
				#u = float(value)  / 1000
				#u = str(u)
				#print(str(value) + " = " + u)
				value = float(value)  / 1000
				value = str(value)
			update = re.sub(pattern.pattern, r"\1.\2", str(value))
			update = float(update)
	return data		

def add_rows_to_sheet(rows: typing.List, sheet):
		Adds data to worksheet 
		arguments :
			rows: list of data 
			sheet: worksheet 
		returns : 
			worksheet (updated with rows)
	for row in range(len( rows )):
		sub_row = rows[row]
		for col in range(len(sub_row) ):
			sheet.cell(row=row + 1, column=col + 1, value= sub_row[col] )
	return sheet
def ods_to_excel(filepath, target)->None:
		Generates an excel workbook with ods data 
			filepath --> a valid filepath to the directory containing ods documents 
			target -> a valid filepath to the directory for the generated excel sheets 
	filename = os.path.basename(filepath)
	filename = filename.split(".")[0]
	data = get_data(filepath)
	json_data = json.dumps(data)
	json_data = json.loads(json_data)
	workbook_path = os.path.join( target, filename +  ".xlsx"   )
	if not os.path.exists(workbook_path):
		wb = openpyxl.Workbook()
		wb = openpyxl.load_workbook(workbook_path)
	for sheetname in json_data:
		ws = wb.active
		ws.title = sheetname[:21]
		data_update = get_rows_xy(json_data[sheetname])
		ws = add_rows_to_sheet(data_update, ws)
		logging.info("Adding data to " + sheetname )

if __name__ == "__main__":
	if len(sys.argv) == 2:
		path = sys.argv[1]
		filepath = os.path.join(path, filename)
		if os.path.exists(filepath):
			ods_to_excel(filepath)<span data-mce-type="bookmark" style="display: inline-block; width: 0px; overflow: hidden; line-height: 0;" class="mce_SELRES_start"></span>

This on the other hand, allows me to transform all ods files in a directory and save the newly generated excel workbooks in a given target path

#USAGE : python pode.py <source path> <target path>
import getdata, os, sys 

def task(source , target ):
	for filename in os.listdir(source):
			filepath = os.path.join(source, filename)
			getdata.ods_to_excel(filepath, target)

if __name__ == "__main__":
	if len( sys.argv ) == 3:
		source = os.path.abspath(sys.argv[1])
		target = os.path.abspath(sys.argv[2])
		if not os.path.exists(target):
		if os.path.exists(source):
			task(source, target)
			print("The source directory is invalid")
		print("Requires source directory and target directory")<span data-mce-type="bookmark" style="display: inline-block; width: 0px; overflow: hidden; line-height: 0;" class="mce_SELRES_start"></span>

Newer Versions <–


Please follow and like us:

Enjoy this blog? Please spread the word :)

error: Content is protected !!