Category: Coding

LibreOffice – concatenate a string with seperator (Google Sheets join)

if you’re in need to join a list of strings with a separator, you can create this custom function in your sheet:
Function STRJOIN(range, Optional delimiter As String, Optional before As String, Optional after As String)
    Dim row, col As Integer
    Dim result, cell As String
    result = ""
    If IsMissing(delimiter) Then
        delimiter = ","
    End If
    If IsMissing(before) Then
        before = ""
    End If
    If IsMissing(after) Then
        after = ""
    End If
    If NOT IsMissing(range) Then
        If NOT IsArray(range) Then
            result = before & range & after
        Else
            For row = LBound(range, 1) To UBound(range, 1)
                For col = LBound(range, 2) To UBound(range, 2)
                    cell = range(row, col)
                    If cell <> 0 AND Len(Trim(cell)) <> 0 Then
                        If result <> "" Then
                            result = result & delimiter
                        End If
                        result = result & before & range(row, col) & after
                    End If
                Next
            Next
        End If
    End If
    STRJOIN = result
End Function

set this up by selecting: tools > macros > organise macros > libreoffice basic …
you can choose where to save the macro to the sheet, the application or others.

list of all eBay categories and eBay category ids

it’s strange that a company like eBay doesn’t make it easy for sellers to get the resources they need. have you tried getting a complete list of all eBay categories? maybe with the eBay category tree? – you will find that there’s not a convenient download location, albeit there should be!

the “trick” so I’ve learned is to get it yourself.

here is a very short python script that will get your categories:

#! /usr/bin/env python
import tools as t
import xmltodict as xml
import requests
url = 'http://open.api.ebay.com/Shopping?callname=GetCategoryInfo&appid={}&siteid={}&CategoryID={}&version=729&IncludeSelector=ChildCategories'
app_id = 'XXXX' #get your own eBay app id
site_id = '3' # 3 is UK
to_get = [-1] # start at the top
done = []
categories = {}
if __name__ == "__main__":
    while len(to_get) > 0:
        cid = to_get.pop()
        re = requests.get(url.format(app_id, site_id, cid))
        root = xml.parse(re.text)
        cats = root['GetCategoryInfoResponse']['CategoryArray']['Category']
        for cat in cats:
            if cat['CategoryID'] not in categories:
                print(cat.get('CategoryName', 0))
                c = {'category_id': cat.get('CategoryID', 0),
                    'category_level': cat.get('CategoryLevel', 0),
                    'category_name': cat.get('CategoryName', 0),
                    'category_parent_id': cat.get('CategoryParentID', 0),
                    'leaf_category': cat.get('LeafCategory', 0),
                    'category_name_path': cat.get('CategoryNamePath', 0)}
                categories[cat['CategoryID']] = c
            done.append(cid)
            if cat['CategoryID'] not in done  and cat['LeafCategory'] == 'false' :
                to_get.append(cat['CategoryID'])
    with open('eBay_categories.tsv', 'w', encoding="utf-8") as of:
        of.write("category_idtcategory_leveltcategory_nametcategory_parent_idtleaf_categorytCategoryNamePathn")
        for k,v in categories.items():
            of.write(str(v['category_id']) + "t" + str(v['category_level']) + "t" + str(v['category_name']) + "t" + str(v['category_parent_id']) + "t" + str(v['leaf_category']) + "t" + str(v['category_name_path']) + "n")
the above will then create a tab-delimited text file named ‘eBay_categories.tsv’.
please note you will need both the “requests” and the “xmltodict” modules. they can be easily obtained using pypi. this code is python 3

Keyword Categorisation for PPC Search Engine Marketing

sometimes it’s strange that you never get to think about creating a solution for a manual task that you have to do, even though it would be totally easy to do.
such for me was building a tool that categorises keywords… i have probably categorised keywords manually for way too long, probably 10s of thousands of keywords. often i cut corners, often i would just wrap keywords into an ad group by the way that i generated them, running a quick sense check if the keywords are in the right place.

it turns out that the way that i was doing this was actually quite straight forward: i look at the keyword and decide, based on a few key phrases, what category i would map that keyword too. let’s take an example. the company i (hypothetically) work for is an airline with the name paddyjet:

  • i have brand terms, ie: paddyjet, paddy jet and so on and so forth.
    everything that has these words or ‘paddy’ in them will be brand terms, with the possible exception of keywords that have the word ‘review’ or ‘compare’ in them. maybe i also want to exclude the words ‘contact’ and ‘customer service’, as they too should be in another category called ‘paddyjet cs terms’
  • i have competitor terms that i may or may not want to bid on: british airways, ba, delta, – i would probably want to create a rule with these other brand terms and put them in another category, again with the exception of ‘review’ and ‘compare’ terms
  • there are the ‘review’ and ‘compare’ type terms
  • budget type terms will probably have the terms ‘cheap’, ‘discount’, ‘bargain’, ‘low cost’ and so on
  • cs terms should be our brand name and the words like ‘customer service’, ‘contact’, ‘complaints’…
in short, there are rules in your mind that make you decide quite instantly where to put terms. these rules are:
  • must include this word
  • must exclude these words
  • must include these words and these words
so i wrote a little python script to categorise keywords based on your rules. click on the link to go to my website and get the file and also samples! 
it needs the list of keywords in a text file (default keywords.txt), a list of noise words to remove (default: noiseWords.txt – it is optional to provider content in this file, the file itself has to be there) and the categorisation rules file (default keywordCategories.txt). 
the rules file needs to be marked up and structured in an ordered way:
  • more important rules need to go at the top
    ie: paddyjet tickets – if you rather want this in the ‘brand terms’, then put the brand rules first. if you rather want this to be categorised as ‘ticket terms’, then put this one first
  • one line per rule – no exceptions
  • start the line with the category name, followed by a colon (:)
  • words that mean the keyword does not fit in this category need to be in curly brackets ({…})
  • words that need to in combination with the term need to be in hard brackets ([…])
  • you can create combinations of words that need to be included and the search term.
    ie: you have vanity urls that you wish to treat different from brand terms (‘flypaddyjet’, ‘premierpaddyjet’ or paddyjetbusiness). simply put a tilde at the required position. ie:
    vanity urls: [premier~, ~premier] paddyjey, tickets, bookings, paddy
    will create the word combinations:
    premierpaddyjet, premiertickets, premierbookings, premierpaddy, paddyjetpremier, ticketspremier, …
finally, anything that can’t be categorised as ‘unknown’.
at the end a file will be generated with three columns: the original keyword, the noise words removed and the category based on the rules. the default name of the file would be date-time stamped and look like: YYYYMMDD_HHMMSS_categorisedKeywords.txt
i hope this tool could be useful for some people in digital marketing who currently struggle to categorise thousands of keywords manually. contact me if you have any questions, i’m happy to help.
it’s a very quick and dirty solution, so if you have problems, find bugs or would like more features, feel free to contact me. 

Using AJAX to read XML

arguably not the wisest of all implementations of an AJAX, i have prepared a page on my personal website (http://www.matenaers.com/chris/chris-matenaers.html) to put my CV online – at least a basic version.

i finished with the code, but still have to fill in the rest of the xml file to complete this task. this will change the content of a div on hovering over another div…

finally, this here is code only, apologies. ask questions if you have to.

here is a copy of the solution:

xml part:

<?xml version=”1.0″ encoding=”UTF-8″?>
<lvl1>
    <lvl2 id=”aaa”>
        <lv3a attrib=”test_attribute”>node_text</lv3a>
        <lvl3b><![CDATA[
            Some data not parsed
            ]]></lvl3b>
    </lvl2>
</lvl1>

and the html & javascript:

<script>
function getXML(url) {
var xmlhttp;
var txt = “”;
var x,xx,i;
if (window.XMLHttpRequest) {
xmlhttp=new XMLHttpRequest();
}
else {
 xmlhttp=new ActiveXObject(“Microsoft.XMLHTTP”);
}
xmlhttp.onreadystatechange=function() {
if (xmlhttp.readyState==4 && xmlhttp.status==200) {
x = xmlhttp.responseXML.documentElement.getElementsByTagName(“lvl2”);
for (i=0;i<x.length;i++) {
txt = txt + “<br />Attribute lvl2 : ” + x[i].getAttribute(‘id’);
xx = x[i].getElementsByTagName(‘lv3a’);
txt = txt + “<br />Node value lvl3a : ” +  xx[0].firstChild.nodeValue;
txt = txt + “<br />Attribute lvl3a : ” +  xx[0].getAttribute(‘attrib’);
xx = x[i].getElementsByTagName(‘lvl3b’);
txt = txt + “<br />Node value lvl3b : ” +  xx[0].firstChild.nodeValue;
}
document.getElementById(“change_me”).innerHTML=txt;
}
}
xmlhttp.open(“GET”,url,true);
xmlhttp.send();
}
</script>
<h1>Chris’ Sandbox</h1>
<div id=”mainpage”>
<div id=”content” class=”text”>
<h4><a href=”#” onmouseover=”getXML(‘http://www.matenaers.com/chris/code/sample_xml.xml’)” onclick=”return false;”>Hover here!</a></h4>
<hr/>
<div id=”change_me”> test </div>
</div>
</div>

PHP Image Resizer Class

class imageResizer {
var $newImage;

function getSquareImage($size, $image) {
//getting the path and resetting to the new path of the image
$oldPath = explode(‘/’, $image);
$t = sizeof($oldPath) – 1;
$i = 0;
while ($i < $t) {
$newPath = $newPath . $oldPath[$i] . ‘/’;
$i++;
}
$filename = str_replace(‘.gif’, ”, end($oldPath));
$filename = str_replace(‘.jpg’, ”, $filename);
$filename = str_replace(‘.png’, ”, $filename);
$filename = str_replace(‘.JPG’, ”, $filename);
$filename = str_replace(‘.jpeg’, ”, $filename);
$newPath = $newPath . ‘cache/’ . $filename . ‘_sq’. $size .’.jpg’;

//if that image does not exist already (we haven’t resized it before):
if(!is_file($newPath)) {
list($width, $height) = getimagesize($image);

//check if the image has the right propotions or do:
if ($width > $size || $height > $size) {
if ($width > $height) $ratio = $size / $width;
else $ratio = $size / $height;

$new_width = round($width * $ratio);
$new_height = round($height * $ratio);

$newImg = imagecreatetruecolor($new_width, $new_height);
$imageTmp = imagecreatefromjpeg($image);
imagecopyresampled($newImg, $imageTmp, 0, 0, 0, 0, $new_width, $new_height, $width, $height);
imagejpeg($newImg, $newPath);

$this -> newImage = $newPath;
}
//If the original image is the correct size:
else $this -> newImage = $image;
}
//set this orignal imaget the image returned:
else  $this -> newImage =  $newPath;
return $this -> newImage;
}

function getImageDimensions($sizeX, $sizeY, $image) {
//getting the path and resetting to the new path of the image
$oldPath = explode(‘/’, $image);
$t = sizeof($oldPath) – 1;
$i = 0;
while ($i < $t) {
$newPath = $newPath . $oldPath[$i] . ‘/’;
$i++;
}
$filename = str_replace(‘.gif’, ”, end($oldPath));
$filename = str_replace(‘.jpg’, ”, $filename);
$filename = str_replace(‘.png’, ”, $filename);
$filename = str_replace(‘.JPG’, ”, $filename);
$filename = str_replace(‘.jpeg’, ”, $filename);
$newPath = $newPath . ‘cache/’ . $filename . ‘_’. $sizeX .’x’. $sizeY .’.jpg’;

//if that image does not exist already (we haven’t resized it befoe):
if(!is_file($newPath)) {
list($width, $height) = getimagesize($image);

//check if the image has the right propotions or do:
if ($width > $sizeX || $height > $sizeY) {
if ($width > $height) $ratio = $sizeX / $width;
else $ratio = $sizeY / $height;

$new_width = round($width * $ratio);
$new_height = round($height * $ratio);

$newImg = imagecreatetruecolor($new_width, $new_height);
$imageTmp = imagecreatefromjpeg($image);
imagecopyresampled($newImg, $imageTmp, 0, 0, 0, 0, $new_width, $new_height, $width, $height);
imagejpeg($newImg, $newPath);

$this -> newImage = $newPath;
}
//If the original image is the correct size:
else $this -> newImage = $image;
}
//set this orignal imaget the image returned:
else  $this -> newImage =  $newPath;
return $this -> newImage;
}

}

PHP Image Downloader Class

a simple PHP class with just one method to download images from a remote server. checks for supported types (gif, jpg, png) and will throw an exception if the file is not supported.

usage:

  1. create instance if ImageDownloader
  2. call function downloadImageFrom with parameters: url, destination in local file-system, image name, image type
explanation of download image:
  1. check if the image type is supported
  2. get width and height of original image
  3. create a new image on local machine with width and height
  4. check the filetype and load correct image from imagecreatefrom~ function, parsing the image remote url
  5. resample the image
  6. save the image as the correct filetype using img~ function, parsing the image in memory and the new filename

code:

class ImageDownloader {
var $supported = array(“png”,”jpg”,”gif”);
function downloadImageFrom($url, $to, $fn, $img_type) {
if (in_array($img_type, $this -> supported)) {
list($width, $height) = getimagesize($url);
$newImg = imagecreatetruecolor($width, $height);
$imageTmp = ”;
if ($img_type == ‘png’) {
$imageTmp = imagecreatefrompng($url);
}
elseif ($img_type == ‘jpg’) {
$imageTmp = imagecreatefromjpeg($url);
}
elseif ($img_type == ‘gif’) {
$imageTmp = imagecreatefromgif($url);
}
if ($imageTmp != ”) {
imagecopyresampled($newImg, $imageTmp, 0, 0, 0, 0, $width, $height, $width, $height);
$newPath = $to . $fn . ‘.’ . $img_type;
$this -> imgLoc = $newPath;
if ($img_type == ‘jpg’) {
imagejpeg($newImg, $newPath);
}
elseif ($img_type == ‘gif’) {
imagegif($newImg, $newPath);
}
elseif ($img_type == ‘png’) {
imagepng($newImg, $newPath);
}
}
}
else {
throw new Exception(‘Not supported file-type’);
}
}

Python on MS SQL (via pyodbc)

I have been struggling, albeit not very long, to set up a DB connection to a remote MS SQL server through Python. I thought that the process is not as straight forward as I’d like it to be, but I guess that’s okay because as far as I can see the best solution is to cobble it together a bit.

Basically it seems that the best solution is not to use a python end to end module as a driver, but to use features that are available otherwise, such as ODBC. Then use a ODBC driver for the MS SQL engine and then use ODBC within Python.
As a Linux person, I first had to install unixodbc:

sudo apt-get install unixodbc unixodbc-dev

This takes care of you being able to use ODBC to connect to ODBC database servers. The other part is having a ODBC driver for the connection to the MS SQL server. There are probably others and I shall list them in due time, but for MS SQL, I found it easiest to use FreeTDS (an implementation of the Tabular Data Stream for Linux).

To install:

sudo apt-get install freedts-dev tdsodbc

With this done, you can preceed to modifying the /etc/odbcinst.ini:

sudo nano /etc/odbcinst,ini

[ODBC]
Trace = Yes
TraceFile = /tmp/odbc.log

[FreeTDS]
Description = TDS driver (Sybase/MS SQL)
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
Setup = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so
CPTimeout =
CPReuse =
UsageCount = 1

Please note that the paths for libtdsodbc.so and libtdsS.so are not necessarily as the ones given in this example. For one, I’m using a 64bit version, so on 32 the path is probably different. Another possible location for the files would be: /usr/lib/odbc/.
Either search for the location or seek Google to find where your distribution saves these files.

You will also need to have the python-dev stuff installed:

sudo apt-get install python-dev 

Now that the system is installed, you will want to install the pydobc module. You can download the latest version of the pyodbc module here. Extract the file where it’s convenient for you and ‘cd’ into the folder.
To install simply do:

sudo python setup.py install

Auto Download File From FTP – then rename

A little while ago my friend asked me to help him with some Windows commandline scripting. Basically he was waiting for ages for the development team in his company to sort this simple task out and asked me how he can every day automatically download a file from FTP, then rename it with the current date.

I don’t do Windows very much and Linux is just much better for exactly these things, but this seemed like an easy enough challenge.

In the end I didn’t manage to do everything in one file, but I think that two should work just as well when you add them to the Windows task scheduler…

Here are the files with some explanation:

downloader.bat:

@ftp -i -s:”%~f0″&GOTO:EOF
open ftp.someserver.com
[username]
[password]
!:— FTP commands below here —
mget “*.*”
disconnect

The first line starts the windows command line FTP client and the parameters essentially say that everything else in the file should be ignored by the command line itself, but be parsed to the FTP client.
Line two opens the connection and line three and four will send the authentication details. Line six gets all files in the current directory. You could really do here anything you want on the remote and local server, eg: change the local directory path (lcd …), the remote one (cd …) send files (put) or even delete them (rm). At the end, I simply disconnect.
renamer.bat:

for /f “tokens=1-5 delims=/ ” %%d in (“%date%”) do rename “SomeFileName.txt” SomeFileName_%%f-%%e-%%d.txt

In one line: I’m starting a for-loop and I use a tokeniser to delimit the following string into 5 (1-5) sections. %%d specifies the beginning character used for the token (d) and all that follow will be e,f,g…
then I use the %date% (in (“%date%”) function to get the current date. Now, In the do part, I will rename the file from SomeFileName.txt to SomeFileName_yyyy-MM-dd.txt
I hope this makes sense and helps a few people. Best of luck…

Script to disable & enable touchpads

I constantly touch the touchpad when I type. I also normally use an external mouse when I’m on my laptop. I seldom really need it, but I will sometimes want to switch it on (or off).

First you need to find the name of your touchpad in the xinput list:

$ xinput list

⎡ Virtual core pointer                     id=2 [master pointer  (3)]

⎜   ↳ Virtual core XTEST pointer               id=4 [slave  pointer  (2)]

⎜   ↳ Razer Razer Imperator                   id=10 [slave  pointer  (2)]

⎜   ↳ Razer Razer Imperator                   id=11 [slave  pointer  (2)]

⎜   ↳ PS/2 Mouse                               id=13 [slave  pointer  (2)]

⎜   ↳ AlpsPS/2 ALPS GlidePoint                 id=14 [slave  pointer  (2)]

⎣ Virtual core keyboard                   id=3 [master keyboard (2)]

    ↳ Virtual core XTEST keyboard             id=5 [slave  keyboard (3)]

    ↳ Sony Vaio Keys                           id=6 [slave  keyboard (3)]

    ↳ Video Bus                               id=7 [slave  keyboard (3)]

    ↳ Power Button                             id=8 [slave  keyboard (3)]

    ↳ USB 2.0 Camera                           id=9 [slave  keyboard (3)]

    ↳ AT Translated Set 2 keyboard             id=12 [slave  keyboard (3)]

So in my case the touchpad is most likely: PS/2 Mouse on id 13.

You can easily identify if the device is “on” (enabled):

$ xinput list-props “PS/2 Mouse” 

Device ‘PS/2 Mouse’:

Device Enabled (144): 1

Coordinate Transformation Matrix (146): 1.000000, 0.000000, 0.000000, 0.000000, 1.000000, 0.000000, 0.000000, 0.000000, 1.000000

Device Accel Profile (267): 0

Device Accel Constant Deceleration (268): 1.000000

Device Accel Adaptive Deceleration (269): 1.000000

Device Accel Velocity Scaling (270): 10.000000

Evdev Axis Inversion (271): 0, 0

Evdev Axes Swap (273): 0

Axis Labels (274): “Rel X” (154), “Rel Y” (155)

Button Labels (275): “Button Left” (147), “Button Middle” (148), “Button Right” (149), “Button Wheel Up” (150), “Button Wheel Down” (151)

Evdev Middle Button Emulation (276): 0

Evdev Middle Button Timeout (277): 50

Evdev Wheel Emulation (278): 0

Evdev Wheel Emulation Axes (279): 0, 0, 4, 5

Evdev Wheel Emulation Inertia (280): 10

Evdev Wheel Emulation Timeout (281): 200

Evdev Wheel Emulation Button (282): 4

Evdev Drag Lock Buttons (283): 0

 Now create yourself a little file (eg: “touchpad”):

# toggle synaptic touchpad on/off

SYNSTATE=$(xinput list-props “PS/2 Mouse” | grep Enabled | grep -Eo ‘.$’)

if [ $SYNSTATE = 0 ]; then xinput set-int-prop “PS/2 Mouse” “Device Enabled” 8 1

else xinput set-int-prop “PS/2 Mouse” “Device Enabled” 8 0; fi

dengar@dengar-vaio:/usr/bin$ $ xinput list-props “PS/2 Mouse” 

This should sort you out. Don’t forget to  change the name of your actual device, remember mine is “PS/2 Mouse”

Next you need to make the file executable:

sudo chmod +x touchpad

Now, to have that file available from the command line, simply copy it to /usr/bin.