DeflateAwning / LookupClosestLatLong.bas

Created April 23, 2021 21:16

An Excel VBA macro used to jump to a row of a spreadsheet containing a latitude and a longitude column, where that row is closest to a target lat/long.

	Option Explicit ' require strict variable 'dim' statements to avoid typos

	Public Sub LookupClosestLatLong()
	' Runs a procedure that prompts the user a few times, and takes them to the row of a pipe tally closest to some input coords.
	' @since 2021-04-15

	' Note to self: Range.Cells(row number, col number) is 0-index. Cells(2,1) = B3. Good luck.

	Dim LatColNum As Long
	Dim LonColNum As Long

DeflateAwning / MakeSQLInsertStatements.m

Last active April 23, 2021 21:13

PowerQuery custom step to generate a new column containing SQL Insert Statements for the entire table. Useful for loading Temp Tables.


	= Table.AddColumn(#"Changed Type", "SQL", each "INSERT INTO TableName (" & Text.Combine(Table.ColumnNames(#"Changed Type"), ", ") & ") VALUES (" & Text.Combine(List.Transform(Record.FieldValues(_), each "'" & Text.From(_) & "'") , ", ") & ")")

DeflateAwning / DBeaver Properties Changes.md

Created March 1, 2021 21:29

DBeaver is good, but not perfect. Here are the changes to make to make it good.

DBeaver Properties Changes

Allow line breaks in queries

File > Properties > Editors > SQL Editor > SQL Processing
Uncheck "Blank line is statement delimeter" in Delimeters section.

DeflateAwning / Find Non-Distinct Pandas Columns.py

Created February 8, 2021 03:05

Have a table with a ton of columns? Know what you want your primary key to be? Use this method to find which columns have different values per row within each target primary key.

	import pandas as pd
	import BOE_SQLAccess as boesql

	sqlEngine = boesql.createSQLAlchemyEngine() # or equivalent SQLEngine object

	tableName = 'petrinex_ab_well_licence'
	pk = 'Licence Number' # target column to check for duplicates as the primary key

	df_in = pd.read_sql(tableName, con=sqlEngine)
	df = df_in.copy() # make a copy for safety, because downloads can be expensive

DeflateAwning / ButterflyButtonModule.bas

Created November 19, 2020 21:25

Add this VBA Macro to your PERSONAL.XLSB workbook. Run it while selecting a cell within a table to save that table to a CSV file.

	Option Explicit

	Sub ButterflyButton()

	Dim tbl As ListObject
	Dim csvFilePath As String
	Dim fNum As Integer
	Dim i As Long
	Dim tblArr
	Dim rowArr

DeflateAwning / CheckInternetConnection.sh

Last active July 9, 2020 16:25

Check Internet Connection

	# Unix/Max
	ping www.google.com -i 1 \| while read pong; do echo "$(date +%Y-%m-%d_%H:%M:%S): $pong"; done \| grep --line-buffered timeout

	# Windows (through cygwin or Git Bash)
	ping www.google.com -n 500000 \| while read pong; do echo "$(date +%Y-%m-%d_%H:%M:%S): $pong"; done \| grep --line-buffered timed

DeflateAwning / UCalgary Class Watcher.py

Last active May 2, 2018 16:07

Tool for watching class availability for non-waitlistable UCalgary classes

	#!/usr/bin/env python3

	import urllib.request
	import xml.etree.ElementTree as ET

	import time, random, math

	import smtplib
	from email.message import EmailMessage