Category Archives: Easy Data Transform

Summerfest 2022

Easy Data Transform and Hyper Plan Professional edition are both on sale for 25% off at Summerfest 2022. So now might be a good time to give them a try (both have free trials). There is also some other great products from other small vendors on sale, including Tinderbox, Scrivener and Devonthink. Some of the software is Mac only, but Easy Data Transform and Hyper Plan are available for both Mac and Windows (one license covers both). Sale ends 12th July.

Why isn’t there a decent file format for tabular data?

Tabular data is everywhere. I support reading and writing tabular data in various formats in all 3 of my software application. It is an important part of my data transformation software. But all the tabular data formats suck. There doesn’t seem to be anything that is reasonably space efficient, simple and quick to parse and text based (not binary) so you can view and edit it with a standard editor.

Most tabular data currently gets exchanged as: CSV, Tab separated, XML, JSON or Excel. And they are all highly sub-optimal for the job.

CSV is a mess. One quote in the wrong place and the file is invalid. It is difficult to parse efficiently using multiple cores, due to the quoting (you can’t start parsing from part way through a file). Different quoting schemes are in use. You don’t know what encoding it is in. Use of separators and line endings are inconsistent (sometimes comma, sometimes semicolon). Writing a parser to handle all the different dialects is not at all trivial. Microsoft Excel and Apple Numbers don’t even agree on how to interpret some edge cases for CSV.

Tab separated is a bit better than CSV. But can’t store tabs and still has issues with line endings, encodings etc.

XML and JSON are tree structures and not suitable for efficiently storing tabular data (plus other issues).

There is Parquet. It is very efficient with it’s columnar storage and compression. But it is binary, so can’t be viewed or edited with standard tools, which is a pain.

Don’t even get me started on Excel’s proprietary, ghastly binary format.

Why can’t we have a format where:

  • Encoding is always UTF-8
  • Values stored in row major order (row 1, row2 etc)
  • Columns are separated by \u001F (ASCII unit separator)
  • Rows are separated by \u001E (ASCII record separator)
  • Er, that’s the entire specification.

No escaping. If you want to put \u001F or \u001E in your data – tough you can’t. Use a different format.

It would be reasonably compact, efficient to parse and easy to manually edit (Notepad++ shows the unit separator as a ‘US’ symbol). You could write a fast parser for it in minutes. Typing \u001F or \u001E in some editors might be a faff, but it is hardly a showstopper.

It could be called something like “unicode separated value” (hat tip to @fakeunicode on Twitter for the name) or “unit separated value” with file extension .usv. Maybe a different extension could used when values are stored in column major order (column1, column 2 etc).

Is there nothing like this already? Maybe there is and I just haven’t heard of it. If not, shouldn’t there be?

And yes I am aware of the relevant XKCD cartoon ( https://xkcd.com/927/ ).

** Edit 4-May-2022 **

“Javascript” -> “JSON” in para 5.

It has been pointed at the above will give you a single line of text in an editor, which is not great for human readability. A quick fix for this would be to make the record delimiter a \u001E character followed by an LF character. Any LF that comes immediately after an \u001E would be ignored when parsing. Any LF not immediately after an \u001E is part of the data. I don’t know about other editors, but it is easy to view and edit in Notepad++.

Making explainer videos for your software

If you want to find out how to do something, such as do a mail merge in Word or fix a leaky valve on a radiator, where do you look first? Probably Youtube. Videos are an excellent way to explain something. More bandwidth than text and more scaleable than a 1-to-1 demo.

I’ve done explainer videos for all 3 of my products. But I found it a real struggle. I would write a script and then try to read the script and do the screencast at the same time and do it all in one take. I would stutter and stumble and it would take multiple attempts. It took ages and results were passable at best. I got some better software to edit the stumbles out, so I didn’t have to do it in one go. But it still took me a fair few attempts and quite a bit of editing. It became one of my least favourite things to do and so I did less and less of it.

Recently, I came across these slides on video by Christian Genco. These and subsequent Twitter exchanges with Christian convinced me that I should stop being a perfectionist about video and just start cranking them out on the grounds that a ‘good enough’ video is better than no video at all (‘the perfect is the enemy of the good’) and I would get better at it over time. As Stalin supposedly said “Quantity has a quality all of it’s own”.

So I have ditched the scripts and the perfectionism and I’ve managed to create 13 short Easy Data Transform explainer videos in the last week or so. And I am getting faster at it and (hopefully) a bit more polished. I’m definitely not an expert on this (and probably never will be) but here are some tips I have picked up along the way:

  • Get some decent software. I use Camtasia on Windows and it seems pretty good.
Camtasia
  • Try to talk slower.
  • Try to sound upbeat (not easy if you are British and could voice double for Eeyore).
  • Try not to move the mouse and talk at the same time. This makes editing a lot easier. Some people like to do the audio and the visual separately, but that seems like too much hassle.
  • If you stumble, just take a deep breath, say it again and then edit the stumble out later.
  • Get a reasonable mic. I have a snowball mic on a cantilevered stand. I covered it with a thin cloth to try to reduce pops.
  • The occasional ‘um’ is fine.
  • Have a checklist of things to do for each video, so you don’t forget anything (such as disabling your phrase expander software or muting the phone).
My setup. Note the high tech use of rubber bands.

I’m lucky to have a very quiet office, so I don’t have much background noise to contend with.

Using Camtasia I can easily add intos and outros, edit out stumbles and add various effects, such a mouse position highlighting and movement smoothing. I just File>Save as the previous project so that I don’t have to re-add the intro and outro. Unsurprisingly, Camtasia have lots of explainer videos. I wish there was a way to automatically ‘ripple delete’ any sections where there is no audio and no mouse movement (if there is, I haven’t found it). Some people recommend descript.com. It looks interesting, but I haven’t tried it.

I did an A/B test of recordings with my Senheiser headset mic against my Snowball mic and the consensus was that the headset was ok but the the Snowball mic sound quality was better.

Some people prefer to use synthetic voices, instead of their own voice. While these synthetic voices have improved a lot, they never sound quite right to me. Also it must be time consuming to type out all the text. Or you can pay to have a professional voiceover done, but this is surprisingly expensive (around $100 per minute, last time I checked) and almost certainly more time consuming than doing it yourself.

Some people aren’t confident about speaking on videos because they are not native speakers of that language and have an accent. Personally accents don’t bother me at all. In fact I like hearing English spoken with a foreign accent, as long as I can understand it. Also I think there is an authenticity to hearing a creator talk about their product in their own voice.

I’m not a big fan of music on explainer videos, so I don’t add any.

I let Youtube generate automatic captions for people that want them (which could be people in busy offices and on trains and planes, as well as the hearing impaired). They aren’t perfect, but they are good enough.

My videos are aimed at least as much at finding new users as helping existing users. So I make sure I research keyword terms (mostly in Google Adwords) before I decide which videos to make and what to title them. Currently I am targetting very specific keyword searches, such as How to convert CSV to Markdown. Easy Data Transform can do a lot more than just format conversion, but from an SEO point of view it is better to target the phrases that people are actually searching for.

I upload the videos as 1080P (1920 x 1080 pixels) on to the Easy Data Transform Youtube channel and onto my screencast.com account (which I pay a yearly fee for). I then embed the screencast.com videos on relevant easydatatransform.com pages using IFRAME embed codes created by screencast.com. I don’t use the Youtube videos on my website, because I don’t want people to be distracted by Youtube ads and ‘you may also like’ recommendations. They might be showing a competitor! I don’t host the videos on the website itsself as I worry that might slow down the website. I also link to the videos in screencast.com from my help documentation, as appropriate.

Some people like to embed video of themselves in screencasts, in the hope of making it more engaging. But personally I want people to concentrate on my software, rather than being distracted by the horror of my face. And not having to comb my hair or look smart was part of what got me into running my own software business.

In the next few months I will be checking my analytics to see how many views these videos get and whether they increase the time on page and reduce the bounce rate.

If you can spare a few seconds to go to my Youtube page and ‘like’ a video ot two or subscribe, that would be a big help!

Note that some of the above doesn’t apply when you are creating a demo video for your home page, rather than an explainer video. Your main demo video should be slick and polished.

WinterFest 2021

Winterfest 2021

Easy Data Transform and Hyper Plan Professional edition are both on sale for 25% off at Winterfest 2021. There is also some other great products from other small vendors on sale, including Tinderbox, Scrivener and Devonthink. Some of the software is Mac only, but Easy Data Transform and Hyper Plan are available for both Mac and Windows (one license covers both). Sale ends 11th January.

Creating a forum for your product

I started selling software online 16 years ago. Until this year I never had a forum for any of my products. I handled customer support for PerfectTablePlan and Hyper Plan by email and kept customers up-to-date with an opt-in email newsletter. But I rethought this position with my latest product, Easy Data Transform and started a forum at forum.easydatatransform.com in December 2020.

My ISP offered various forum software packages, but I really wanted Discourse, as I consider it head and shoulders above all the other forum software I have interacted with as a user (even if I find the badge system a bit patronising). I didn’t want the hassle of setting up and patching a Discourse server, so created the forum through www.communiteq.com (previously discoursehosting.com). It was suprisingly easy to set-up. And it gives the option to export everything, in case I want to part ways with them. The sheer number of options in Discourse are quite daunting, but I stuck with the defaults for the most part.

Some people use Facebook Groups for their product forums. Ugh. You have almost no control of such a forum. Facebook could even be showing ads for your competitors on your forums. Or they could just decide to shut you down and delete all the content. That is before we get on to the fact that Facebook make their money monetising hatred and abusing our privacy at an industrial scale. No thanks.

The advantages of a forum are:

  • Letting customers talk to each other, and post content helps to create a community around the product. Which, in turn, can add a lot of value to your product.
  • Customers can help each other with support questions. Sometimes they will answer before you are able to or will give a different perspective. Or even give a better answer.
  • If a customer asks a question that has already been asked, you can send them a link to the appropriate forum page.
  • It is a quick and easy channel to communicate with customers. I can post a link to a new snapshot release in a few minutes. This is much quicker than sending out an email newsletter. It is also more interactive as customers can respond on the forum and see each other’s responses.
  • A lively forum is ‘social proof’ that your product is worth buying.
  • A forum with lots of content should have a large SEO footprint.

The disadvantages of a forum are:

  • The time to maintain it. A forum that is broken or full of spam and unanswered questions is worse than no forum.
  • Disgruntled customers potentially airing their grievances in public.
  • The cost of the forum.
  • An empty forum looks bad.
  • Bad actors can be a pain. For example, people posting links to spam or competing products.

It probably only takes me 1-2 hours per week to post on the forum at present. Some of that is time I would have spent answering support emails. If that rises substantially then I may have to delegate it.

I try very hard to provide a good product, with good support and haven’t had any issues with negativity, so far. But I know from my experiences moderating Joel Spolsky’s Business of Software forum that moderating a busy forum can be tricky, time-consuming and emotionally draining.

The cost of the forum is currently around $20 per month, so pretty low. That may climb, but hopefully sales will be climbing as well.

I was a bit worried about whether the forum was going to look empty. I warned customers that the forum was an experiment and would be closed if there wasn’t enough activity, to manage their expectations. I also created a ‘sock puppet’ account and ‘seeded’ the forum with a few support questions that I had been previously asked by email (with the permission of those that asked) and then posted answers. But I only did this a handful of times and then the forum started to take off.

I have heard stories of people getting 1000+ spam posts a day on their forum. But I haven’t had any issues with bad actors, so far. I’m not sure how much of that is down to Discourse and how much of it is down to luck. But, no doubt issues will occur at some point.

I still have my product newsletter, which I send out every few weeks when there is a new production release.

Overall I am pretty happy with how the forum is going. Should you have a forum for your product? As always, it depends. I think you should consider it if:

  • Your customer base isn’t tiny.
  • You want to interact with your customers and get feedback. This might be less the case with mature products.
  • You have the time and energy to police and maintain it.
  • Your product is relatively open ended or complex. For example, if your product just checks whether website are up or down, there is probably a very limited amount you can discuss.

Adding colour schemes to Easy Data Transform

Easy Data Transform user interface.

The colours used in Easy Data Transform make no difference to the output. But the colours are an important part of a user interface, especially when you using a tool for significant amounts of time. First impressions of the user interface are also important from a commercial point of view.

But colour is a very personal thing. Some people are colour-blind. Some people prefer light palettes and others dark palettes. Some people like lots of contrast and other don’t. So I am going to allow the user to fully customize the Center pane colours in Easy Data Transform.

I also want to include some standard colour schemes, to get people started. Looking around at other software it seems that the ‘modern’ trend is for pastel colours, invisible borders and subtle shadows. This looks lovely, but it is a bit low contrast for my tired old eyes. So I have tried to create a range of designs in that hope that everyone will like at least one. Below are the standard schemes I have come up with so far. They all stick with the convention pink=input, blue=transform, green=output.

Which is your favourite (click the images to enlarge).

Is there a tool that you use day to day that has particular nice colour scheme?

I hope to also add an optional dark theme for the rest of the UI in due course (Qt allowing).

Stalking website visitors with Microsoft Clarity

Microsoft Clarity is a new service that allows you to see, in detail, how visitors are interacting with your website. It includes:

  • heat maps, showing where visitors are clicking or touching or how far they are scrolling
  • recordings of visitor sessions, including mouse movement, clicks, touches and scrolling

You just need to get a Javascript snippet from clarity.microsoft.com (for which you need a Microsoft login) and paste it into the header of each page. You can then login to clarity.microsoft.com at a later time to see your results. The service is free.

I tried it on my www.easydatatransform.com website. Here you can see a click heatmap for the buy page:

People are clicking all over non-hyperlinked text. Hmm. Perhaps they somehow couldn’t the see the effing enormous blue button next to it? Notice that numbers are starred out to avoid personal information, such as credit card numbers.

You can also see how far visitors scroll down the page with scroll heatmaps:

So I can see that the buy button is appearing well above the fold.

You can also watch recodings of people interacting with the website, showing their mouse movements, clicks, touches and scrolling. This is where things start to feel a bit stalkerish. You don’t get any identifiable information on the visitor beyond their country, browser and their operating system and I’m ok with people watching me interact with their websites like this. But it still feels a bit voyeuristic. The results are also a bit strange. Some people just click all over the place and highlight random text (touches are tracked separately from clicks). There is a distinct danger that you could watch hours of sessions and come away without much actionable information.

You can filter the information in various ways, including by country or referring website. You can even filter to see sessions with ‘Rage clicks’ (where the user has clicked or tapped repeatedly in the same area).

Watching a few sessions with ‘rage clicks’ I could see that some people indeed seem completely unable to see the effing enormous blue ‘buy now’ button on the buy page. So I have also added a text hyperlink where most people are clicking in the text and will probably try changing the button colour. Perhaps to shocking pink!

Running Pingdom Website Speed Test on the Easy Data Transform home page, both with and without Clarity a few minutes apart, I can see that it had some effect on speed, but not too much.

Without Clarity script: 175.2kb of scripts, 7 script requests.

With Clarity script: 194.3kb of scripts, 9 script requests.

The load time was actually 0.1s faster with Clarity. That is probably just an anomaly.

I have disabled Clarity for now. But may reenable it after I have made some changes to the website, to see the effect of the changes. Overall I was quite impressed with the service and it was surprisingly easy to set-up. But the cynic in me does wonder what exactly Microsoft is getting out of it.

Easy Data Transform v1.6.0

I have been working hard on Easy Data Transform. The last few releases have included:

  • a 64 bit version for Windows
  • JSON, XML and vCard format input
  • output of nested JSON and XML
  • a batch processing mode
  • command line arguments
  • keyboard shortcuts
  • various improvements to transforms

Plus lots of other improvements.

The installer now includes 32 and 64 bit version of Easy Data Transform for Windows and installs the one appropriate to your operating sytem. This is pretty easy to with the Inno Setup installer. You just need to use Check: Is64BitInstallMode, for example:

[Files]
Source: "..\binaries\windows\program\{#MyAppExeName}"; DestDir: "{app}"; Flags: ignoreversion; Check: not Is64BitInstallMode
Source: "..\binaries\windows\program64\{#MyAppExeName}"; DestDir: "{app}"; Flags: ignoreversion; Check: Is64BitInstallMode

But it does pretty much double the size of the installer (from 25MB to 47MB in my case).

The 32 bit version is restricted to addressing 4GB of memory. In practise, this means you may run out of memory if you get much above a million data values. The 64 bit version is able to address as much memory as your system can provide. So datasets with tens or hundreds of millions of values are now within reach.

I have kept the 32 bit version for compatibility reasons. But data on the percentage of customers still using 32 bit Windows is surprisingly hard to come by. Some figures I have seen suggest <5%. So I will probably drop 32 bit Windows support at some point. Apple, being Apple, made the transition to a 64 bit OS much more aggressively and so the Mac version of Easy Data Transform has always been 64 bit only.

I have also been doing some benchmarking and Easy Data Transform is fast. On my development PC it can perform an inner join of 2 datasets of 900 thousand rows x 14 columns in 5 seconds. The same operation on the same machine in Excel Power Query took 4 minutes 20 seconds. So Easy Data Transform is some 52 times faster than Excel Power Query. Easy Data Transform is written in C++ with reference counting and hashing for performance, but I am surprised by just how much faster it is.

The Excel Power Query user interface also seems very clunky by comparison. The process to join two CSV files is:

  • Start Excel.
  • Start Power Query.
  • Load the two data files into Excel power query.
  • Choose the key columns.
  • Choose merge.
  • Choose inner join. Wait 4 minutes 20 seconds.
  • Load the results back into Excel. Wait several more minutes.
  • Save to a .csv file.
  • Total time: ~600 seconds

Whereas in Easy Data Transform you just:

  • Start Easy Data Transform.
  • Drag the 2 files onto the center pane.
  • Click ‘join’.
  • Select the key columns. Wait 5 seconds.
  • Click ‘To file’ and save to a .csv file.
  • Total time: ~30 seconds

join-op

If you have some data to transform, clean or analyze please give Easy Data Transform a try. There is a fully functional free trial. Email me if you have any questions.

 

Analyzing COVID19 data with Easy Data Transform

I have continued to make lots of improvements to Easy Data Transform, including:

Here is a video of me using Easy Data Transform to analyze the data.europa.eu COVID19 dataset. Hopefully it gives an idea of what the software is capable of.

covid19-data-wrangling

 

 

Easy Data Transform v1.1.0

I released v1.1.0 of Easy Data Transform this week. It is a big upgrade, with some major new features.

easy-data-transform-v110

There is a new Javascript transform. This allows you to create custom transforms for anything that is too specialist to do with the other 37 built-in transforms. I’m not a fan of Javascript, with its horrible scoping and typing, and I would have preferred Python or Lua. But there is a Javascript engine built into Qt, so this was the easiest way to add scripting. Now if you want to multiply two columns of your data together in Easy Data Transform, you can just do this:

javascript-data-transform-v110

You can also access Javascript maths, date and string functions. So you can do some pretty complicated stuff. Hopefully the built-in transforms are enough to cover 95% of data transformations. But the new Javascript transform adds some serious flexibility for the remainder. The Qt Javascript engine is also pretty fast. In testing I was able to multiply values from 2 columns together across 10,000 rows in less than 0.03 seconds.

There is a new Lookup transform. This allows you to lookup values for one dataset in another dataset. For example, if you have a dataset with a column for country code and another dataset with columns for the country code and tax rate, you can look up the tax rate by country code.

Previously you could only output your data in Excel and delimited text formats (including CSV and TSV). The new release also adds output to JSON, HTML, Markdown, vCard, YAML and XML formats.

I have improved the speed of the Join transform significantly using hashing. This makes a big difference with large datasets.

To save time, Easy Data Transform guesses the likely columns you want to use as keys when you Join, Intersect, Lookup or Subtract two datasets. For example if 2 datasets both have colummns called ‘ID’ with lots of unique values that are common to both columns, it will choose those two columns as the default key columns. I have improved the heuristic used to set the default columns.

You can now add comments to input, transform and output nodes as a note to a colleague or your future self.

You can now snap your input, transform and output nodes to a grid, so you can keep your layout all lovely and neat.

I have also made some bug fixes and minor improvements.

Haven’t tried Easy Data Transform yet? Got some table or list data that you need to wrangle into a more useful form? Take the free trial for a spin.