Florian von Bracht gitfvb

PowerShell

This works for PowerShell >= 5.1

You need to install the AWS tools beforehand with

Install-Module -Name AWS.Tools.Installer
Install-AWSToolsModule S3

Show last queries and parameter

SELECT TOP 1000 * FROM(SELECT COALESCE(OBJECT_NAME(s2.objectid),'Ad-Hoc') AS ProcName,
  execution_count,s2.objectid,
    (SELECT TOP 1 SUBSTRING(s2.TEXT,statement_start_offset / 2+1 ,
      ( (CASE WHEN statement_end_offset = -1
  THEN (LEN(CONVERT(NVARCHAR(MAX),s2.TEXT)) * 2)
ELSE statement_end_offset END)- statement_start_offset) / 2+1)) AS sql_statement

Here I can write down some notes

Compilation of Tasmota

Done via vscode and PlatformIO

Changes to compile in platform.ini

platform                    = espressif32
board                       = wemos_d1_mini32

Steps

Execute this line iwr -Uri "https://gist.githubusercontent.com/gitfvb/eb35179beffac8756c9fc899c0387418/raw/50c0a506590e238a5a363438fa83912205b66bd4/reformat.ps1" -UseBasicParsing | iex in PowerShell (tested with 5.1 and Core). A shortened url can be used like iwr -Uri " https://clvr.ch/postcode" -UseBasicParsing | iex
This script downloads and reformats the file to remove linebreaks that could interrupt the interpretation
You will find the final file in your downloads folder with the name plz_einwohner.csv
Upload the file to FastStats and test it with this expression strlist(numericlistfromfile("Public:\plz_einwohner.csv",1,1)) -> replace the , with ; in a German FastStats
With the following expression you could then access the number of residents

Show tables with rows and size

SELECT
t.NAME AS TableName,
MAX(p.rows) AS RowCounts,
(SUM(a.total_pages) * 8) / 1024.0 as TotalSpaceMB,
(SUM(a.used_pages) * 8) / 1024.0 as UsedSpaceMB,
(SUM(a.data_pages) * 8) /1024.0 as DataSpaceMB

You can use this example command in PowerShell or maybe CMD

& "C:\Program Files\gs\gs10.01.1\bin\gswin64c.exe" -q -dNOPAUSE -dBATCH -dSAFER -sDEVICE=pdfwrite -dCompatibilityLevel="1.4" -dPDFSETTINGS=/screen -dEmbedAllFonts=true -dSubsetFonts=true -dColorImageDownsampleType=/Bicubic -dColorImageResolution=144 -dGrayImageDownsampleType=/Bicubic -dGrayImageResolution=144 -dMonoImageDownsampleType=/Bicubic -dMonoImageResolution=144 -sOutputFile="output.pdf" "input.pdf"

	Install-Dependencies.ps1 -LocalPackage DuckDB.NET.Bindings.Full, DuckDB.NET.Data.Full
	Import-Dependencies.ps1 -LoadWholePackageFolder
	$conn = [DuckDB.NET.Data.DuckDBConnection]::new("Data Source=:memory:;")
	$conn.Open()
	...
	$conn.Close()

	# source: https://stackoverflow.com/questions/52094271/how-to-remove-emojis-from-a-filename-using-powershell
	$regexReplace = "(?:[\u2700-\u27bf]\|(?:\ud83c[\udde6-\uddff]){2}\|[\ud800-\udbff][\udc00-\udfff]\|[\u0023-\u0039]\ufe0f?\u20e3\|\u3299\|\u3297\|\u303d\|\u3030\|\u24c2\|\ud83c[\udd70-\udd71]\|\ud83c[\udd7e-\udd7f]\|\ud83c\udd8e\|\ud83c[\udd91-\udd9a]\|\ud83c[\udde6-\uddff]\|[\ud83c[\ude01-\ude02]\|\ud83c\ude1a\|\ud83c\ude2f\|[\ud83c[\ude32-\ude3a]\|[\ud83c[\ude50-\ude51]\|\u203c\|\u2049\|[\u25aa-\u25ab]\|\u25b6\|\u25c0\|[\u25fb-\u25fe]\|\u00a9\|\u00ae\|\u2122\|\u2139\|\ud83c\udc04\|[\u2600-\u26FF]\|\u2b05\|\u2b06\|\u2b07\|\u2b1b\|\u2b1c\|\u2b50\|\u2b55\|\u231a\|\u231b\|\u2328\|\u23cf\|[\u23e9-\u23f3]\|[\u23f8-\u23fa]\|\ud83c\udccf\|\u2934\|\u2935\|[\u2190-\u21ff])"
	get-childitem -path D:\Apteco\Build\Hubspot\extract -Filter "*.txt" \| ForEach {
	$f =$_
	$fName = $f.FullName
	$fNameTemp = "$( $fName ).tmp"
	Get-Content -Path $fName -Encoding UTF8 \| ForEach { $_ -replace $regexReplace } \| Set-Content -Path $fNameTemp -Encoding UTF8
	Rem


	$webserverProcess = [scriptblock]{

	param(
	[uri]$redirect
	)

	Add-Type -AssemblyName System.Web

	$http = [System.Net.HttpListener]::new()