## Smart Columns

Posted on February 26, 2021

# Introduction

Smart columns are a great way for you to quickly calculate sums on your entire dataset, and have them just as editable as any other column!

# Caveats

The editor does not function like a traditional grid based editor in that your file is not kept in memory, this allows you to edit and insert rows/columns in multi gigabyte files with zero latency, however there are limitations imposed on the smart functions:

• The formula can only lookup values in the current row.
• Smart columns evaluate from left to right. If you want to reference another smart column, the referenced column should be to the left. This is to ensure there are no circular dependencies.
• Deleting a source column will result in a #REF lookup error.
• Saving the file will commit the smart columns, only then can you delete the source columns.

# Operators

The formula syntax supports these basic operations, the syntax is parsed in a left to right approach (not BODMAS). Please use brackets to achieve desired functionality.

Operator
Description
+
-
Subtract
/
Divide
*
Multiply
%
Modulus
&
Concatenate (Strings)
(
Start group
)
End group
A-Z
Reference column
12.3
Decimal
FUNC(a,b)
Functions (see below)

# List of functions

The formula box provides access to many functions that can be useful

##### Decimal Functions
Function
Description
CONCAT(a,b)
Appends b onto a
LEFT(a,x)
First x characters of a
RIGHT(a,b)
Last x characters of a
SUBSTITUTE(a, b, c)
Replaces all instances of b with c located in a
Adds b to a (equivalent of +)
SUBTRACT(a,b)
Subtracts b from a (equivalent of -)
MULTIPLY(a,b)
Times a and b (equivalent of *)
DIVIDE(a,b)
Divides a by b (equivalent of /)
MOD(a,b)
Remainder of a by b (equivalent of %)
MAX(a,b)
Greater of a or b
MIN(a,b)
Lesser of a or b
ROUND(a,x)
Rounds a to x decimal places
POW(a, b)
Takes a to the power of b
IF(a, b, c)
Checks a is true, returns b otherwise returns c
COL(x)
Returns the Xth column
AND(a,b)
Checks if a and b are both true
OR(a,b)
Checks if a or b are either true
XOR(a,b)
Checks if one of a or b is true
EQUALS(a,b)
Checks if a and b are the same value
NOTEQUALS(a,b)
Checks if a and b are different values
NOT(a)
Checks if a is true, returns false, vice versa
GT(a,b)
Checks if a is greater than b
LT(a,b)
Checks if a is less than b
##### String Functions
Function
Description
Concat(string1, string2)
Appends string2 onto string1 (equivalent of &)
Left(string, No characters)
First X characters of the string
Right(string, No characters)
Last X characters of the string
Substitute(string, old, new)
Replaces all instances of old with new located in string
Len(string)
Returns the number of characters in a string
Regex(string,pattern,group)
Return the selected regex group from a pattern match
IsMatch(string, pattern)
Returns true if the regex pattern was found in the string
Reverse(string)
Reversed the string
##### Conditional Functions
Function
Description
IF(condition, a, b)
Checks the condition and returns A if true or B otherwise
##### Lookup Functions
Function
Description
Col(index)
Returns the value in the specified column for each row (equivalent of A, B, C etc)

# Examples

##### Prefix a £ symbol to A column values
`\'£\'&A `
##### Find the Sum of columns A B and C, and prefix a £ symbol
`\"£\"&A+B+C `
##### Find the average of columns A, B and C
`(A+B+C)/3 `
##### Calculate column A with an additional 20% tax (rounded)
`ROUND(A*1.2, 2) `
##### Extract the first name from a first/last pair using regex
`REGEX(B,'^(.+)\\s(.+)',1)`
##### Print even or odd if there are an even or odd number of characters in a column
`IF(LEN(B) % 2, 'Even', 'Odd')`
##### Swapping Columns A and B

You can add two new columns C and D,

C will reference B

`B`

and D will reference A:

`A`

Now save the file to commit both smart columns

Once the file reloads you can delete Colums A and B to leave only the reversed columns:

Posted on February 24, 2020

Hello once again,

Welcome to the new look, the new site has been written from scratch using .Net Core, it's been a bit of a rushed experiment and there are a few teething issues.

Over the coming weeks I'll endeavour to move the old site's content across and fix all the broken images and links.

## Big CSV Editor

Posted on November 7, 2019

I'm pleased to announce the new CSV Editor is now available on the Microsoft Store.

Available on the Microsoft Store

CSV Editor is a full featured large comma separated list file editing program.

Using sophisticated skim reading and data streaming huge files can not only be viewed, but edited too, even if there would not normally be enough RAM.

• Filtering can be done on a per-column basis
• Sorting can be done numerically, alphabetically and by currency
• Does not mangle leading zeros like many other programs.

Get what you need fast

## Shed Championships

Posted on May 15, 2019

Shed championships is an reimagining of the classic card game Shit Head, one of my favourite card games from my school days

Available on the Microsoft Store

This story based card game follows the young James and his friend on night they"ll never forget

Features

• 10 Levels
• Tutorial Mode
• and a Quick Play

Shed was developed using game maker studio, assets were developed in inkscape.

## Gallery for Wordpress

Posted on November 17, 2018

### Want to manage your blogs photos directly from your desktop, phone or tablet?

Gallery for WordPress is a great tool to Upload, Update and post Images, it integrates into windows share so you can show the world your photo or latest creation with a single click.

• Displays your blog as a "share" destination in any supported windows app.

• Compose and publish image posts directly to your blog feed.

• Supports multiple blogs simultaneously.

• Automatic image resizing for the web

• Compatible with wordpress.com

## Alphabetical Sorting without eating RAM

Posted on February 26, 2018

I was looking for a method to sort a really large data-set alphabetically without eating all a user’s RAM, or using disk space, nor have a user sit and wait for N² comparisons.

I found the answer in the form of a Trie-tree, a neat data structure that flattens ASCII characters into... well a tree.

Lets say we have the following words:

• Ape
• App
• Apple
• Orange
• Ore
• Pear

A Trie Tree would represent the data in the following way:

Each node represents a character which can have 26 children (or more depending on the character set). A node can be marked as an endpoint (in red) to signal the completion of a character string. Duplicate starting characters in similar words only use one node between them, as seen with A P for ape, apple and app, this has the wonderful side affect of compressing the sort data-structure. Our input data-set contained 24 characters, the tree only contains 17 characters.

To retrieve an alphabetically sorted list of words, you can traverse the tree taking the left most node, checking if its an endpoint (marked in red) if it is - return this, if not - explore the left most child, unless there is no more children, then travel up to the parent and explore the next adjacent child.

Some caveats,

1. It"s not going to be great for supporting the full unicode character set. A binary tree would probably work better.
2. There is a overhead traversing the structure to add and retrieve the items, but compared to other sorts this isn"t extreme.
3. In this implementation only one copy of each word is returned. This can easily be fixed by keeping a counter at each endpoint for duplicates.
4. Features like #3 eat ram in every node. This adds overhead to the tree and the compression is less efficient.

## Mini Golf Mod

Posted on September 5, 2017

For the past 6 months I"ve been working part time to port my golf mod from CS:S to  Counter Strike Global Offensive

As of today, on the 10 year anniversary of my delving into modding counter strike the mod is released.

Released along side is the first map to work with the plugin - Go Golf, a 18 hole course set in rural Massachusetts:

# Released on Steam

https://steamcommunity.com/sharedfiles/filedetails/?id=711504961

# Website

https://files.jackdye.co.uk/gogolf

# Screenshots

## Love Bites

Posted on February 12, 2016

Jess is on a girl's night out, when suddenly she is bombarded by stage 5 clinger zombies. Will she fend of the creepers or fall dead over heels?

Love bites is a take on the classic arcade game Tapper with a dose of clinger zombies.

Features an arcade style game-play with internet enabled high scores

Credits

Made in 48 hours as part of #indiesvsgamers

Programming: Jack D
Art/Story: Krystyne M

## Fix YouTube Restricted mode in Windows 10

Posted on September 7, 2015

If Youtube is hiding videos when searching due to "Restricted Mode", Like this:

## Fix

You can try turning it off at the bottom of the Youtube page by clicking on the "Restricted Mode" button.

## Still not working?

If this is not saving and restricted mode keeps turning back "ON" & you have a Windows 10 PC - you might have family mode enabled, To turn this off, type settings in the start menu and open the main settings dialog (See Picture Below). Click Accounts (the picture with the person)

In the accounts dialog you"ll need to click "Sign out of Microsoft Account and use Local Account Instead". You"ll need to provide your Microsoft account password and then make a new password for your PC.

This will disconnect you from all family settings

Now try turning off restricted mode again (from the bottom of the Youtube page), and you"ll see the setting now saves!

## Generating a Bitcoin address in C#

Posted on November 24, 2013

I recently needed to write a C# app to convert a Bitcoin public key to a human usable addresses to send coins to, using the example on

I hate using external libraries, so my main aim with this was to not require any 3rd party libraries, although you"ll need .Net framework 4.5 for the BigInteger classes.

Please note the full process of generating a valid bitcoin address is defined as:

Private Key > Public Key > Address

This tutorial concentrates on the last step.

If you want to generate a Private/Public pair you can use a bitcoin wallet or look into using http://www.bouncycastle.org/csharp/ to create a valid pair within C#.

# 9 Steps verbose

Here we have all 9 steps as per the technical background document written in C#. Each step is written to the console to show the progress of making the address.

```static void Main()
{

byte[] PubKey = HexToByte(HexHash);
Console.WriteLine("Public Key:" + ByteToHex(PubKey)); //Step 1
byte[] PubKeySha = Sha256(PubKey);
Console.WriteLine("Sha Public Key:" + ByteToHex(PubKeySha)); //Step 2
byte[] PubKeyShaRIPE = RipeMD160(PubKeySha);
Console.WriteLine("Ripe Sha Public Key:" + ByteToHex(PubKeyShaRIPE)); // Step 3
byte[] PreHashWNetwork = AppendBitcoinNetwork(PubKeyShaRIPE, 0); // Step4
byte[] PublicHash = Sha256(PreHashWNetwork);
Console.WriteLine("Public Hash:" + ByteToHex(PublicHash)); // Step5
byte[] PublicHashHash = Sha256(PublicHash);
Console.WriteLine("Public HashHash:" + ByteToHex(PublicHashHash));  //Step 6
Console.WriteLine("Checksum:" + ByteToHex(PublicHashHash).Substring(0, 4));  //Step 7
}```

# Shorthand Function

The entire function can be written in short for live (non debug) use:

```public static string CreateAddress(string PublicKey)
{
byte[] PreHashQ = AppendBitcoinNetwork(RipeMD160(Sha256(HexToByte(PublicKey))), 0);
}
```

I've used some helper functions along the way, they are very verbose (Some unnecessary) but it make it a lot easier to read and understand.

HexToByte

Hex to Byte converts the Hexidecimal string to a byte array, it"s simple as taking the 2 characters and converting it into base 256.

`public static byte[] HexToByte(string HexString) {    if (HexString.Length % 2 != 0)        throw new Exception("Invalid HEX");    byte[] retArray = new byte[HexString.Length / 2];    for (int i = 0; i < retArray.Length; ++i)    {        retArray[i] = byte.Parse(HexString.Substring(i*2, 2), NumberStyles.HexNumber, CultureInfo.InvariantCulture);    }    return retArray; }`

ByteToHex

Byte to hex converts a byte into a hexadecimal string

`byte[] data = { 1, 2, 4, 8, 16, 32 }; string hex = BitConverter.ToString(data);`

## SHA256

Sha256 uses Microsoft"s security cryptography include, and by default takes a byte array (this function is bad in the sense It creates duplicate instances of the sha manager, which we probably shouldn"t do as we could reuse the same instance for the 3 times)

`public static byte[] Sha256(byte [] array) {     SHA256Managed hashstring = new SHA256Managed();     return hashstring.ComputeHash(array); }`

## RipeMD160

Again this function uses Microsoft"s security cryptography include and is pretty much identical to the Sha256 function.

`public static byte[] RipeMD160(byte[] array) {     RIPEMD160Managed hashstring = new RIPEMD160Managed();     return hashstring.ComputeHash(array); }`

## Append Bitcoin Network

AppendBitcoinNetwork simply pre-appends a byte onto the beginning of an array of bytes

`public static byte[] AppendBitcoinNetwork(byte[] RipeHash, byte Network) {     byte[] extended = new byte[RipeHash.Length + 1];     extended[0] = (byte)Network;     Array.Copy(RipeHash, 0, extended, 1, RipeHash.Length);     return extended; }`

Concat address appends the last 4 bytes of the hash onto the end of the RipeMD160 value

`public static byte[] ConcatAddress(byte[] RipeHash, byte[] Checksum) {     byte[] ret = new byte[RipeHash.Length + 4];     Array.Copy(RipeHash, ret, RipeHash.Length);     Array.Copy(Checksum, 0, ret, RipeHash.Length, 4);     return ret; }`

## Base58

I"ve hacked together my own c# Base58 encoder based off the C++ code by Satoshi, notice it uses BigInterger, this is a new Microsoft class in C# that allows math operation on arbitrary big numbers, so we"re no longer limited to a 32 or 64 bit integer, see more here:

http://msdn.microsoft.com/en-us/library/system.numerics.biginteger(v=vs.110).aspx
```public static string Base58Encode(byte [] array) {     const string ALPHABET = "123456789ABCDEFGHJKLMNPQRSTUVWXYZabcdefghijkmnopqrstuvwxyz";     string retString = string.Empty;     BigInteger encodeSize = ALPHABET.Length;     BigInteger arrayToInt = 0;     for (int i = 0; i < array.Length; ++i)     {         arrayToInt = arrayToInt * 256 + array[i];     }
while (arrayToInt > 0)     {         int rem = (int)(arrayToInt % encodeSize);         arrayToInt /= encodeSize;         retString = ALPHABET[rem] + retString;     }
for (int i = 0; i < array.Length && array[i] == 0; ++i)       retString = ALPHABET[0] + retString;     return retString; }```
Note: You"ll have to manually add references to the Microsoft.Numerics library to your project to get the BigInteger class.