Export DHCP leases to CSV format

RouterOS General
1

I needed to export DHCP leases to CSV format for further use. I found out some examples but not quite suiting my needs so I had to do some changes and updates due to ROS version.

Here is script as I guess someone else would need similar

{

  # Export DHCP to CSV

  :local outputFileName ("dhcp-leases-" . [/system identity get name] . ".csv.txt")
  :put ("exporting DHCP leases to " . $outputFileName)

  :local outputContent "address\tmac-address\tclient-id\tserver\thost-name\tcomment"

  /ip dhcp-server lease {
    :foreach i in=[find (!dynamic)] do={
      :local Address [get $i address]
      :local MacAddress [get $i mac-address]
      :local ClientID [get $i client-id]
      :local Server [get $i server]
      :local Hostname [get $i host-name]
      :local Comment [get $i comment]
      :local DHCPItem ""
       :set $DHCPItem ($Address . "\t" . $MacAddress . "\t" . $ClientID . "\t" . $Server . "\t" . $Hostname . "\t" . $Comment)
       :put $DHCPItem
       :set $outputContent ($outputContent . "\n" . $DHCPItem)
    }
  }

  :put "list formed"


  /file
  :if ([:len [find where name=$outputFileName] ] = 0) do={ 
    print file=$outputFileName; 
    :delay 2s; 
    set $outputFileName content=""
  }
  set $outputFileName contents=$outputContent
  }


}
2

Nice, thanks. :smile:

Though at first sight it seems more like Tab delimited, not comma separated.

3

Or in recent V7,

For CSV to terminal:

:put [:serialize to=dsv delimiter="," options=dsv.remap [/ip/dhcp-server/lease/print as-value]]

.id,address,blocked,disabled,dynamic,host-name,last-seen,mac-address,radius,server,status
*7,172.22.74.245,false,false,true,towerpower,11m9s,4C:5E:0C:0D:XX:XX,false,dhcp1,bound

For TSV (tabs) to file:

:serialize to=dsv delimiter="\t" options=dsv.remap [/ip/dhcp-server/lease/print as-value] file-name=leases.tsv
4

CSV is universally accepter file extension. Historically it started using comma as separator, but as comma can easy be found in data itself and make mess, other delimiters are allowed, like TAB, which actually became more common than comma.

This is first time in my 40 years of working in IT that I hear someone objects about naming CSV…

5

Thanks, did not know that.

6

That’s the good thing about life, everyday something new can happen.

CSV is RFC4180:
https://www.ietf.org/rfc/rfc4180.txt
and RFC7111:
https://www.rfc-editor.org/rfc/rfc7111.html
(not really standards/specifications, but anyway the separator is comma, nomen est omen)

TSV is IANA-TSV:
http://www.iana.org/assignments/media-types/text/tab-separated-values

TSV has its own dignity, distinct from CSV.

7

And MikroTik choose DSV as the catch-all (delimiter separated file), apparently keeps everyone happy , \t

Just to add couple notes to my example. This closer matches the OP’s post, including the column selection and file:

  :serialize to=dsv delimiter="\t" options=dsv.remap \
    [/ip/dhcp-server/lease/print detail as-value] \
    order=address,mac-address,client-id,server,host-name,comment \
    file-name="dhcp-leases-$[/system/identity get name].csv.txt"

  • order= let you “select columns”, which is how it “matches” OP output. Note order is tricky, since the column does have to exist in the output. So, for example, if no DHCP lease had a comment, then above command would fail - this because RouterOS print remove columns if there not used

  • print detail as-value is needed to make it an array needed by [:serialize], with the detail being that print detail is needed to get full set of attributes. The devil is in the details, as they say.

8

Good :smile:

And now the fun fact, one of the most used programs where you import (and can export) CSV is Excel and similar spreadsheets programs.
In Italian (and AFAIK many other European) Windows version Excel won’t normally parse commas as separators, nor will save CSV files with commas as separators, because what is used is instead the local system separator, which is “;” (semicolon).

In this sense pedjas is right that the C in CSV has become in the years more “CanUseAnythingIFancy” than “Comma”

9

For additional fun and games, many countries use comma instead of a period as the numeric decimal separator. This along with using semicolon as the field separator leads to Excel being completely unable to import csv’s created on different locale settings. (I don’t know what the current situation is, but it was like this for more than a decade.) This leads to a format that would seem the most universal for exchanging simple tabular numeric data being implemented as one of the worst in terms of compatibility.

Interestingly, Libre/OpenOffice seems to automagically figure out what it’s reading, and even when it doesn’t, there’s an intuitive little wizard for it.

EDIT: Oh, I almost forgot, there was a while when even if comma was used as the decimal separator, it was also used as the column/field separator as well, but every value was in quotes :slight_smile:

10

And this is one of the reasons why the good MS guys invented their own modification to the CSV format, adding a line at the beginning, like:
sep=,
or
sep=;
etcetera allows to tell Excel what field separators Is actually used in the file.

And now, only to confirm how right Is the classic xkcd:
https://xkcd.com/927/

a proposal for SSV:

11

Didn’t know about that one. Obligatory: https://xkcd.com/927/

12

Oops, cross-posting. :worried:

But since we are at it, there is also the curious case of CSV files that begin with “ID”, and Excel confuses them with SYLK files:

https://en.m.wikipedia.org/wiki/Symbolic_Link_(SYLK)