Intro

Once, the company I worked for had refactored its entire system with a new architecture and was looking for a way to import its legacy data into it.

When migrating data from one system to to another, you may find yourself in the need for mapping data structures. This is true especially when dealing with API's. Even when the data format is the same. The structure could be completely different.

Writing migration scripts can be cumbersome, but what if.. you wouldn't need to write any code at all!

When converting text data formats, such as CSV or JSON, Have you considered using nothing more than a Text Editor? There are many great text editors out there that can perform simple repeating task for you. There are many tools out there that support macros', But if you can be bothered with a bit of a learning curve, few compare to the power that VIM brings to the table imho.

TLDR;

Consider the following JSON structure.

[
  {
     “time”: 1409522400000,
  },
...

So this is basicly a JSON object that includes an array of objects.

Now to import this kind of data into the new system, the JSON needs to look like this:

[
  {
     “t”:” 2015-01-25T07:00:00″
  },
...
]

The mockdata used for this article can be found here.

Imagine 24 of these objects, as there are hours in a day. Then take a year or so of data and you will no doubt understand the need for this to be automated.

In the first excerpt you may notice the dates as a timestamp (nr. of milliseconds from 1st of jan 1970) while the second is formatted in the more readable ISO date.

So now we find ourselves in need of a way to automatically convert many lines of JSON from one format to the other calculating a timestamp into a date in every step of the way.

What makes VIM great is that it comes with some basic internal programming functions, including the strftime function.

Try entering the command :help functions in the command mode of VIM to see them all Strftime’s origins are in the C programming language. Its used for formatting timestamps into date formats, exactly what we are looking for.

As arguments strftime takes a date formatting string and a timestamp. By entering “man strftime” in a terminal, the documentation for the c implementation reveals that the formatting string “%FT%T” will output the desired notation in this case. Try executing

:echo strftime("%FT%T",0)

VIM will now neatly output the first of jan 1970 in the desired format: 1970-01-01T01:00:00

Macros

VIM allows you to record the editing you do on line(s) and repeat these steps. There are many great examples out there on how this feature can save you a ton of work. In this case we have repeating structure of data that we would like to convert into another representation of that data. VIM Macro’s are perfect for such tasks.

Just hit the ‘q’ key to start recording, followed by any key to bind the recording to that key. Hit ‘q’ again to stop recording. You can then execute the recording by hitting the ‘@’ key followed by the key used to store the recording. Then use “@@” to use the last recording or “@” and the key of your macro of choice to execute it.

At this point we could go in depth into the basics of VIM, explaining how to navigate to words in VIM and setting it into edit mode. But that's not really in scope of this article and others have covered this so much better than I ever could.

Skipping all that for now, to convert the "time" string to "t", we could enter commands keys in VIM that would look something like this in a row "j j w l dw". And that might seem weird if you are not used to VIM. But for now, lets pretend that was the easy part.

Registers

Every time a line or word in VIM gets removed or yanked, it can be pasted later using “p”. This is done because the removed selection is being kept in a buffer, also called a register.

VIM keeps internal registers for a lot of things, including the outcome of its internal functions such as strftime. The contents of these registers can actually be used as part of VIM commands. That makes them very useful.

Enter help:registers in vim to learn more.

What is of interest in this case is that deleted and yanked texts are stored in the unnamed register:

(yes, just the quotes), or:

To get the contents from the the unnamed register in the command line, open the command line by pressing “:” followed by pressing the “CTRL” and “r” keys at the same time.

A quote character will appear in the command line. By entering the “ character or “0” the previously deleted word will appear.

For more detailed information insert :help i_ctrl-r in VIM. This trick will enable the strftime command to use the deleted timestamp as part of its input.

When editing text in insert mode of VIM, the same key combination will work to paste a register value. A quote character will then appear at the position where you would like to paste the value inside the document.

However strftime’s output will be stored in something called an expression (“=”) register. VIM will first prompt for an expression to be entered from the command line before you can paste that register.

By deleting the timestamp in the file and use the unnamed register as input for strftime, then the outcome, from the expression register, can be inserted to the file.

delete the timestamp by using a command sequence like “dw”
enter edit mode by pressing "i" or "a"
then holding “ctrl” and “r”, followed by “=”

the command line will open starting with an “=”

by entering:

strftime("%FT%T",

followed by holding “ctrl”and “r” then “0”

the timestamp is pasted into the command line. And the function can be ended by entering “)” followed by hitting the enter key, the timestamp is now replaced with a date notation.

[edit] last time I tried this on my Mac, I had to add "/1000" before closing the parentheses to prevent encountering a year 2038 problem, where a 32-bit signed integer Unix timestamp will overflow and become negative. [edit]

Putting it all together

Performing these steps while still recording a macro, enables to repeat these steps converting all JSON objects in the file into the new format. Just navigate to the next opening bracket of the following object and stop recording. Then enter ‘@@’ to convert the next object in the list.

But entering a command for each object to be converted is repetitive work on its own, VIM can handle that as well. You can use macros within macros. So its not that hard to run one macro to convert the whole file. when the length of the file is known by running the function =line("$") (:help line) Its easy to calculate how many JSON objects are to be converted.

What made this case easier is that the original JSON objects was exactly 10 lines in length (it was simplified in the example). So after the file is stripped to be just the list of objects and is a 1000 lines long a command like ‘100@w’ can be run, if the macro to convert the object was bound to the “w” key.

But why do any work at all? If the line function will produce the number of lines, that value can be used to calculate the amount of times a macro is to be run. All that is needed now is a way to have vim run the value of line(“$”) times the macro.

normal

Whenever in command line mode wanting to write a command out, and execute it as if it were in normal mode, use the normal command. You guessed it, just type

:help normal

in the command line and learn all about it.

So by typing “=” in the command line, VIM will prompt for an expression to be entered, in this case line(“$”)/10 follow this by the :normal command and, in this case, the @W will result in the macro being repeated by a tenth of the number of lines

Storing macros

It’s quite annoying that VIM does not store these registers by default. After exiting VIM, your macro’s are gone. However, here is a little trick to get around that anyway.

Earlier on I explained a bit about ‘binding’ macro’s to keys. What actually happens here is that after the ‘q’ key is hit in normal mode, whatever is typed next gets stored in the register corresponding to that key.

use :registers to find out how your macro’s look like
then open the hidden vimrc file, if none is present create one in VIMS home directory
for POSIX (MAC, Linux) systems: ~/.vimrc
use the “let” command, this allows a register to be set to a particular value.
in this case the lines to add look something like.

let @w = 'jddldwiv^[wwi"^[A<80>kb",^[jdddddddwdwdwdwi"t": "^["=strftime("%FT%T",^R"<80>kb<80>kb<80>kb)^Mpa"^[lxjddddj’

let @e = '"=line('$')/10^M:normal ^R=^M@w^MdGGo]^[ggO[^['

let @r = '3dd@e^[:w^M'

By opening the file to convert in VIM the macro’s should be preloaded in the registers. In this case Executing “@r” will convert the entire file.

Using VIM macro's in favor of migration scripts.