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!

#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 ):
	'''
		argument:
			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:
			break
		if start:
			data.append(sub_list[:2])
		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)
			temp.append(update)
		data.append(temp)
	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 
		argument: 
			filepath --> a valid filepath to the directory containing ods documents 
			target -> a valid filepath to the directory for the generated excel sheets 
		returns:
			None
	'''
	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()
	else:
		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 )
	wb.save(workbook_path)
	logging.info("Complete")
	

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):
		if(filename.endswith(".ods")):
			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):
			os.mkdir(target)
		if os.path.exists(source):
			task(source, target)
		else:
			print("The source directory is invalid")
	else:
		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 <–