Category: Code

Sub-Categories:

Snippets of useful code I’ve found or written. I don’t vouch for the general usefulness of any of this. These are almost exclusively solutions to one off problems I’ve encountered that I figure I might need again.

XPS to XLSX

A tool for converting XPS files to XLSX files. Specifically, this maps out the location of data within the XPS and collates based on X and Y coordinates. This is useful for automating data capture fom XPS documents without having to manually copy and paste. Specific areas of the page can be targeted and content pulled.

Combine XLSX Files

A script to combine all XLSX files in a given folder into a single file. Each original XLSX file becomes a worksheet or worksheets in the final file. ##The Basics import xlrd import csv import os import re import pandas as pd def dfs_from_excels(folder, v = True): if v: print(“Parsing “, folder) output = {} for file in os.listdir(folder): if file.endswith(“.xlsx”) or file.endswith(“.XLSX”): if v: print(“Parsing:”, file) name = os.path.splitext(file)[0] wb = xlrd.open_workbook(os.path.join(folder,file)) for sheet in wb.sheet_names(): if v: print(‘Sheet:’, sheet) content = pd.read_excel(open(os.path.join(folder,file), ‘rb’),sheet_name=sheet) index = name + “-” + sheet output[index] = content if len(output) > 0: return […]

Write to CSV File

Writes a line of CSV to a file If the file specified does not already exists, it will be created Otherwise the file will be appended to include the new line Accepts a path to a CSV file, and a CSV line data. def updateCSV(aFilePath, lineData): aFolder, aFile = os.path.split(aFilePath) if os.path.exists(aFilePath): with open(aFilePath, “a+”, newline=”) as csvfile: fieldnames = lineData.keys() writer = csv.DictWriter(csvfile, fieldnames=fieldnames) writer.writerow(lineData) else: if not os.path.exists(aFolder): os.makedirs(aFolder) with open(aFilePath, “x”, newline=”) as csvfile: fieldnames = lineData.keys() writer = csv.DictWriter(csvfile, fieldnames=fieldnames) writer.writeheader()

FileMatch

A function to check all files in a folder against a given pattern. Uses RegEx for the file pattern. Returns “true” or “false” for each file. Usage: FileMatch(“/path/to/folder”, “[a-z0-9]\.exe”, [bool], [bool]) Attributes: Folder path, pattern, recurse, display result # Import the basics import io import os import re # r=True will recursively search sub folders as well # v=True will print the results of the search def FileMatch(aFolder, aPattern, r=False, v=False): validArgs = True if not os.path.isdir(aFolder): if v: print(aFolder, “is not a valid directory”) validArgs = False try: cPattern = re.compile(aPattern) except Exception as e: if v: print(e) validArgs […]

XLSX to CSV

A simple script to create CSV versions of any XLSX files in a given folder. #The Basics import xlrd import csv import os import re # Create empty variables and hash tables we’ll need later Location = {} # Location to output CSV file when all is done Location[‘input’] = input(‘Folder: ‘) Location[‘output’] = os.path.join(Location[‘input’],’csv’) def csv_from_excel(file): print(“Parsing “, file) wb = xlrd.open_workbook(file) for sheet in wb.sheet_names(): print(‘Exporting Sheet:’, sheet) sh = wb.sheet_by_name(sheet) fileout = os.path.splitext(file)[0] + ” ” + sheet + “.csv” with open(fileout, “w”, newline=””) as csvfile: wr = csv.writer(csvfile, quoting=csv.QUOTE_ALL) for rownum in range(sh.nrows): wr.writerow(sh.row_values(rownum)) csvfile.close() # […]