Chris A. Matenaers, Author at Chris A. Matenaers

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

Things I hate about Mac OSX

From my experience, these things are really annoying when it comes to the Apple Mac OS

Non-standard keyboard shortcuts
having used windows, apple macintosh (many years ago) and linux systems for most of my life and used certain standard keyboard shortcut combinations (ctrl+x, ctrl+c, return, delete or backspace), it is infuriating to see that mac at some point must have thought that it’s wise to deviate from everyone else and therefore make their computers more difficult to use.
there is no standard programme to open archives – not even zip
the standard is to unzip an archive. other than in the command line, there doesn’t seem to be a way of looking what’s inside a zip file. you have to extract it, then delete it. pointless.
not only that, but zip is really the only thing supported, so you end up installing a different archive manager for tar balls, rar, 7z or any other files.
the decision to not support ntfs or fat32 or other file systems out of the box, but just their own journaling file system and exfat
you cannot cut and paste into the file browser “finder”.

this list will grow over time

An easy way to enable VNC in Ubuntu 14.04

i’ve struggled today for a few hours to get XDMCP connections running on my new tv server. in the end i posted on a few forums and gave up. a bit frustrating!

but i still didn’t want to leave my seat without having any ability to log into my server without gui, so i opted for a simple vnc server solution:

you need to have the gnome-session-flashback package installed through apt-get
you need to log into at least one session that is a gnome (fallback) one.
go to applications > system tools > preferences > desktop sharing

and that solves the problem. you must have at least one desktop session running, logged into Gnome-flashback and this user must have the connection allowed.

Removing unused Kernel files via command line

regular kernel updates are great, but having 3 years of kernel updates on one hard disk is not. chances are you are never going to use any of the 10 or 20 that you installed last year. if everything continues to work fine, why would you?

you can obviously use a package manager, such as synaptic, to uninstall unwanted programmes. quite often you might find a few other programmes that you didn’t know you had still and want to get rid of. but it’s generally easier to just use the command line to remove things quickly.

use this command:

dpkg -l ‘linux-*’ | sed ‘/^ii/!d;/'”$(uname -r | sed “s/(.*)-([^0-9]+)/1/”)”‘/d;s/^[^ ]* [^ ]* ([^ ]*).*/1/;/[0-9]/!d’ | xargs sudo apt-get -y purge

i use this command in ubuntu and i’m pretty sure it will work in derived versions, but results may vary!

Bulk encode MP3s with Lame and avconv

to be honest, when i am in the car or listening to music at home, i don’t really hear the difference between “lossy” mp3s and lossless formats such as flac. flac is just really huge compared to MP3 and i wouldn’t care if hard disk space was unlimited.
even if buying 2-3 nat storage devices, i think i’d still run our of space eventually.

so i do compress flac files after a while. but doing a whole album that is correctly labelled and tagged can be tricky. you don’t want to use some software that renames the files or changes the tagging…

linux is very supportive for doing things like that via the command line:

what you need is avconv and lame. avconv is a very fast video and audio converter. it can use libmp3lame to encode to mp3. because avconv can also convert and optimise video files, you can use it the same way to strip mp3s from video files, such as music videos or stuff you downloaded from youtube. but this post is about mp3s.
lame ain’t an mp3 encoder, but it simply is the best of them.

the command line argument to convert all flac files to mp3s in one folder is:

for file in *.flac; do n=$(basename “$file” .flac); avconv -i “$file” -codec:a libmp3lame -qscale:a 2 “$n”.mp3; done

if you want to convert from a different format, simply exchange the “.flag” to a “.wav”, “.mp4”, “.swf” or whatever the source file might be.

Exporting Sound with XDMCP

Some of you might be connecting to your remote linux box using XDMCP. While that would export the display,what would one do if one wanted to play a song on the remote box but hear it on the present system? well that can be easily done using the arts server on the remote machine setup the arts daemon by typing

artsd -n -u -p 5001

once the server is started change to the X display of the remote machine and type in

export ARTS_SERVER=ipofcurrentsystem:5001

and then

xmms &

Voila you will have a complete remote desktop including sound just like in windows xp.

Excel reverse string function (VBA)

a very simple VBA funciton to reverse a string (text cell) in Excel:

Function Reverse(str As String) As String
Reverse = StrReverse(Trim(str))
End Function

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>