Anti-nuisance lawsuit warning: The purpose of these notes is to remind me, Zoegond, of stuff or to help me work stuff out. They may contain mistakes.

Quick

  • ($a, $b....) = unpack("A2A7...", $packed)
  • push( array, list )

Thursday, October 23, 2025

Perl File::Spec

File::Spec behaves a bit unexpectedly if you split a 'path' which is actually a bare filename, and then put it back together after changing the directory portion (originally "") to an actual directory or path.

In this scenario catpath will put the relative path into the root directory - prepending a \ .

($v, $d, $f) = File::Spec->splitpath("barefile.txt");
$d = File::Spec->catdir($d, "archive");
$j = File::Spec->catpath($v, $d, $f);
$j is "\archive\barefile.txt" not "archive\barefile.txt".

Additionally, splitpath can't tell with c:\dir1\dir10\dir100 that dir100 is a directory, it will assume it is a filename. Unless a trailing slash is supplied, or an extra parameter , 1 .

The splitpath method in File::Spec appears to work thusly under Win32:

  • Volume - drive letter and colon, or \\hostname\sharename
  • Path - either "", or \, or will begin with \ and end with \
    (path will be "" if it wasn't specified, eg c:blah.txt)
  • Filename - the base file name

NB You can't avoid the 'catpath adds an extra slash after dir' issue by splitting dir into volume and dir first.

Monday, November 4, 2024

Google Query empty dates

Some original research for a change! If you wish to match an empty date, eg Col4 = '' does not work.

The workaround I found was eg Col4 < date '1960-1-1' or whatever date you know is earlier than any date occurring in your data.

Tuesday, October 29, 2024

Google Query date specifications

(This is purely a rewriting of Ben Collins' excellent article so that I can grasp it easier)
  • Google Query has a keyword that introduces a date literal, date 'yyyy-mm-dd', and like other parts of GQ language it's limited and inflexible. You have to give it the format shown.
  • If your date criterion is a hard-coded date, you can just put that date in the string, eg Col2 >= date '1997-05-01'
  • But if as is more likely, your date comes from a spreadsheet cell, you have to manipulate it into yyyy-mm-dd format.
    • Spreadsheet function DATEVALUE(s) interprets s as a date in the same way that Google Sheets would if you literally typed s into a cell, ie using the locale settings you've got set for GS, and it returns a GS date number
    • Spreadsheet function TEXT(n, f) applies a format f to a number n to give a formatted string. Eg TEXT(0, "YYYY-MM-DD") = "1899-12-30"
  • So you simply apply DATEVALUE to your spreadsheet date, and TEXT( , "yyyy-mm-dd") to the result, and concatenate that into your query: "Col2 >= date '" & TEXT(DATEVALUE(thedate), "yyyy-mm-dd") & "'"
Thanks again, Ben.

Tuesday, October 8, 2024

Object oriented Autoit

Autoit3's maps and function pointers make it possible to put an OO style layer on it, in much the same way that you can with JS and Perl.

But the lack of a new keyword, and any kind of this mechanism, and namespaces, means more programmer discipline is required.

A class Doubler with a static method Doubler, and an object method DoubleMe:
https://pastebin.com/aETcFCYw

; Doubler.au3
#include-once

                                                       ;  class Doubler {
                                                       
func Doubler(byref $this, $n)                          ;  constructor(n) {
; properties
$this["n"] = $n                                        ;  this.n = n;
; methods
$this["Double"] = DoublerDouble                        ;  this.Double = DoublerDouble;
$this["DoubleMe"] = DoublerDoubleMe                    ;  this.DoubleMe = DoublerDoubleMe;
endfunc                                                ; 

func DoublerDouble(byref $this, $x)                    ;  Double(x) {
return $x*2                                            ;    return x*2;
endfunc                                                ;  }

func DoublerDoubleMe(byref $this)                      ;  DoubleMe() {
$this["n"] = $this["Double"]($this, $this["n"])        ;    this.n = this.double(this.n);
endfunc                                                ;  }

                                                       ; }
In use:
; main.au3                                             ;  let obj;
#include "Doubler.au3"

local $obj[]
$obj["constructor"] = Doubler                          ;
$obj["constructor"]($obj, 128)                         ;  obj = new Doubler(128);
MsgBox(0, "", $obj["Double"]($obj, 32))                ;  alert(obj.Double(32));
;
$obj["DoubleMe"]($obj)                                 ;  obj.DoubleMe();
MsgBox(0, "", $obj["n"])                               ;  alert(obj.n);

You don't have to use an include file btw, but it's the best way to manage code in larger projects.

As I say, discipline is required:

  • You have to prefix, with the class name, the base names of the functions that are assigned to be methods, because they're in the single namespace that Au3 has.
  • You have to supply the object's map variable as first parameter to all its methods. (Strictly speaking you needn't do that with the constructor, and static methods, but I find it easier to be consistent and always supply the map variable.)
  • The aforesaid parameter must be byref if you want the method to modify any properties. Again, it's easier to be consistent and use byref for all methods.
But if you have to use Au3 to implement something, this is a way to avoid spaghetti code and function/variable name collisions.

Map elements can be set to other maps, or arrays. So your object can 'has-a' another object. But take note that Au3 cannot do more than one layer of dereference:

; Not allowed!
; $obj["anotherObj"] = []
; $obj["anotherObj"]["constructor"] = OtherClass
; $obj["anotherObj"]["constructor"]( $obj["anotherObj"] )
;
; instead do it stepwise:
local $temp[]
$temp["constructor"] = OtherClass
$temp["constructor"]($temp)
$obj["anotherObj"] = $temp

It would be possible to write a 'Ootoit' preprocessor that translated a more sugary syntax into Au3 code that used these protocols. Rather in the style of the old RATFOR tool that put a C-style layer on top of Fortran.

Thursday, July 4, 2024

Perl rewind text file

You can rewind a text file with seek($fh, 0, 0), just as you can any other file.

But that doesn't reset $. - you do that manually with $. = 0 .

Thursday, March 31, 2022

robocopy /xd

You cannot give /xd a partial path like media\audio (like you can with rsync) as it interprets the \ as an escape character.

There doesn't seem to be any usable workaround.

Sunday, December 6, 2020

Google Sheets QUERY column headings

The big problem with QUERY is that it generates unwanted column headings in its results. But I noticed that it doesn't always do that, and reading this article explained why

https://davidmeindl.com/google-sheets-how-to-remove-headers-from-query-result/

To summarise what he says, there are 2 circumstances in which QUERY adds headers, and corresponding ways of getting rid of them:

  1. Including headers in the range specified as parameter 1 to QUERY
    • Specify 0 as parameter 3 to QUERY, so the headers aren't in the input
    • Specify OFFSET 1 in the query, so that the header row is not output
  2. Having an aggregation function in the query, eg SUM
    • Specify eg LABEL SUM(B) '' in the query (can never make that work myself)
    • Use an outer query with OFFSET 1 to skip the header row - QUERY(QUERY(range, "SELECT SUM(B)"), "SELECT * OFFSET 1")
    • NEW If your actual result set only has 1 row of data, use CHOOSEROWS to choose it as row 2 (row 1 being the headers) - CHOOSEROWS(QUERY(range, "SELECT SUM(B)"), 2)

Wednesday, November 25, 2020

Google Sheets AND/OR with ARRAYFORMULA

AND and OR don't work with ARRAYFORMULA because they already have potential array->scalar scope - AND(array) ANDs the whole array together and returns a single value, for instance.

Instead use multiplication (AND) or addition (OR).

Eg array-ise AND(condition1, condition2) as ARRAYFORMULA((condition1)*(condition2)>0)

This isn't a peculiarity of ARRAYFORMULA so much as a (possibly) unexpected APL-style extra functionality of AND and OR.

There is a similar problem with any function which can take a range or a single value - eg MAX - because it will get to the A4:A style reference before ARRAYFORMULA does. Eg if you have ARRAYFORMULA(MAX(A4:A, 4)) intending ARRAYFORMULA to use A4:A to iterate over, it won't work because MAX will take the maximum of A4:A.
In those cases I don't think there's a simple workaround.

Monday, September 21, 2020

Google Sheets VLOOKUP

VLOOKUP doesn't seem to work when the search key is numeric and all the values in the range are also numeric. Even if the key is a quoted numeric string eg "603".

I find I can get round this by adding "" to the key, eg VLOOKUP("603"+"", Db, 4, 0). Presumably this coerces the search comparison to work in string terms.

Also NB that if a key range is sorted with numbers first, eg {118, 151, "AB"}, vlookup won't work properly unless you set the is_sorted param to 0.

Thursday, July 23, 2020

Google Sheets 'Loading...'

If I have a Google Sheet with importrange() cells that occasionally get stuck on 'Loading...', I find I can force them to load by putting '=today()' into a blank cell on that sheet. Once they've loaded I just delete it again.

Saturday, June 20, 2020

Google Query NO_COLUMN

Sometimes you can cure a persistent NO_COLUMN by putting the Range supplied to QUERY in curly brackets:

query({NamedRange}, "select sum(Col4) where Col1='Insane'")

I don't know why that works, as surely NamedRange is an array already, but it does.

Wednesday, June 17, 2020

xfce slow login

If xfce takes ages to log you on, try this advice
https://forum.xfce.org/viewtopic.php?id=5174

In fact it was such a godsend I'm going to copy it out here:

  • command: rm -rf ~/.cache/sessions
  • GUI: Settings Manager >> Session and Startup >> Session tab >> "Clear saved sessions". And when you next go to shutdown/logout make sure you UNCHECK the box that asks to save the session.
Thanks guys!

Wednesday, February 12, 2020

Asset proportions

Suppose you have two assets a and b, each will form a proportion of the total a+b. So if you have 60 units of a and 40 units of b, a/(a+b)=0.6 and b/(a+b)=0.4.

You want to know how b's proportion will change if you add x units to a.

So you're asking how b/(a+b+x) will relate to b/(a+b).

To get b/(a+b+x), divide b/(a+b) by the proportion of the new total a+b+x to the old total a+b.

Eg with the example above, you add 100 units to a. a+b+x=200, a+b=100, the proportion is 200/100=2, divide 0.4 by 2 to get the new proportion for b, 0.2.

Check, b is still 40, a+b+x is 200, 40/200 = 0.2.

Thursday, July 25, 2019

s/// return value

s/// appears to have scalar context only, and it returns the number of times the substitution was made.

Followers

Blog Archive