Removing SIM PIN using AT commands

Ever needed to use a sim card/modem in a headless device which doesnt support a PIN code? (cheap chinese gsm-enabled sensors, I’m looking at you!) You can remove it with a simple AT command:

#verify pin is enabled:
AT+CPIN?
 +CPIN: SIM PIN

#disable pin:
AT+CLCK="SC",0,"<PIN>"
 OK

#(eg: AT+CLCK="SC",0,"1234")

#verify:
AT+CPIN?
 +CPIN: READY

After that, the SIM card will work without a PIN code (yes, yes, security, I know, I know, but with prepaid cards, who cares)

Sound pitch fix with fast-forward in mplayer

Have you ever watched a video on Youtube using a faster play speed (1.25x, 1.5x,..) and the sound seemed “the same but faster”. Ever did the same (using [ and ] keys) in mplayer, and all the voices were higher (high-pitched), and hard to listen and understand? There’s a filter in mplayer that fixes that and it’s called scaletempo.

mplayer -af scaletempo /foo/video.mp4

Now you can change the playback speed without the annoying pitch changes!

Enabling SSH on a fresh Raspbian Jessie image

This post is mostly a reminder for myself, because I will probably forget this (again) soon.

Sometimes you want to use a RaspberryPi as a headless machine, so first you download the newest Rasbian image, ‘dd’ it to the SD card, insert the SD card into your RaspberryPi, connect the ethernet and power cables, check the IP in your DHCP server logs, run ‘ssh <ip>‘ and..

..connection refused!

I have no idea why, but the ssh server is disabled by default (probably a security feature, since the default password is easy to guess, but still..). Then you start googling, and first couple of links all say that you need to connect the keyboard, display, log in localy, etc. etc. But you’re lazy, and want to do it directly on the PC when you write the image to the SD card. So the easiest way is, after you’re done with ‘dd’-ing, you mount the first partition (a smaller, FAT partition containing the boot files), and just create an empty ‘ssh’ file (with eg: touch ssh) in that folder.

If you also wish to enable wifi (which is enabled by default, but no configurations are stored), you also need to mount the second (ext4) partition, containing the full system – there you can edit ‘./etc/wpa_supplicant/wpa_supplicant.conf‘ file, and add your wireless configuration

After that, sync, umount, insert the card, connect all the cables, and it works!

Mounting VirtualBox VDI images (and similar formats)

Sometimes you have a .vdi (or .vmdk or similar) virtual machine disk image, and you wish to access files inside, without starting the virtual machine itself and copying files via network or some ‘guest additions’ tools provided by the virtualization software.

To do this, you need nbd support enabled in the kernel (and module inserted) and qemu installed (which is actually another virtualization software, but we need it for the qemu-nbd utility).

In some distributions, the nbd module is enabled by default, on some you have to enable it when compiling the kernel sources.

  │ Symbol: BLK_DEV_NBD [=m]
  │ Type  : tristate
  │ Prompt: Network block device support
  │   Location:
  │     -> Device Drivers
  │       -> Block devices (BLK_DEV [=y])
  │   Defined at drivers/block/Kconfig:276
  │   Depends on: BLK_DEV [=y] && NET [=y]

Due to some weird reasons, the nbd module by default supports zero (0) partitions inside a block device:

# modinfo nbd
filename:       /lib/modules/4.9.6-gentoo-r1/kernel/drivers/block/nbd.ko
license:        GPL
description:    Network Block Device
depends:        
intree:         Y
vermagic:       4.9.6-gentoo-r1 SMP mod_unload 
parm:           nbds_max:number of network block devices to initialize (default: 16) (int)
parm:           max_part:number of partitions per device (default: 0) (int)

So when inserting the module, set the parameter to a larger number (eg. 4 or 16 or whatever)

modprobe nbd max_part=16

After that, you use the qemu-nbd utility to ‘connect’  the vdi image to the nbd block device (qemu-nbd actually works as a block device server), mount the partition, do stuff, umount the partition, and disconnect the qemu-nbd:

#connect the device:
qemu-nbd -c /dev/nbd0 /path/foo.vdi
#partitions appear as /dev/nbd0p1, /dev/nbd0p2...
mount /dev/nbd0p1 /mnt
#do stuff
umount /mnt
#and now disconnect the device:
qemu-nbd -d /dev/nbd0

 

Checking how much data still needs to be written when sync-ing on Linux

Sometimes on Linux, when you copy alot of data to a slow USB flash drive, the operation is “done” in a second, but when you umount the drive (or run ‘sync’) it takes forever to write the cache to the actual device.

So, how to check the status (how much time is left, how much does it still need to write)?

The answer hides in /proc/meminfo. Specifically the “Dirty” and “Writeback” lines in that virtual file. Very simplified, the “Dirty” value tells you how much data is waiting in cache, and the “Writeback” tells you how much data is being currently written to the device (chunk size).

You can check both values in semi-realtime with the following command:

watch "egrep -e '(Dirty:|Writeback:)' /proc/meminfo"

 

Measuring frequency with a BusPirate

I recently ordered a NE555 pulse generator from DX with some evil plans in mind, and I had to set the frequency to the desired value. Since calculating the frequency by hand and guessing the number of turns on the cheap pots was not an option, I had to measure it.

Since my multimeter only goes to 1000Hz, I decided to use my BusPirate (it goes to a couple of MHz). I’ve connected the GND and 5V leads to GND and VCC pins on the module, and the AUX lead to the modules output (BusPirate measures frequency on the AUX pin/lead).

BusPirate + ne555
BusPirate + ne555

After connecting the BusPirate to my computer via USB, and opening the serial port (“/dev/ttyUSB0”, 115200 8N1 in my case), i see the BusPirate interface. By default it stays in “HiZ” mode after being plugged in (high impedance mode on all pins – to protect the connected circuits). Pressing the ‘?’ key and Enter, you get a help printout:

HiZ> ?
General                                 Protocol interaction
---------------------------------------------------------------------------
?       This help                       (0)     List current macros
=X/|X   Converts X/reverse X            (x)     Macro x
~       Selftest                        [       Start
#       Reset                           ]       Stop
$       Jump to bootloader              {       Start with read
&/%     Delay 1 us/ms                   }       Stop
a/A/@   AUXPIN (low/HI/READ)            "abc"   Send string
b       Set baudrate                    123
c/C     AUX assignment (aux/CS)         0x123
d/D     Measure ADC (once/CONT.)        0b110   Send value
f       Measure frequency               r       Read
g/S     Generate PWM/Servo              /       CLK hi
h       Commandhistory                  \       CLK lo
i       Versioninfo/statusinfo          ^       CLK tick
l/L     Bitorder (msb/LSB)              -       DAT hi
m       Change mode                     _       DAT lo
o       Set output type                 .       DAT read
p/P     Pullup resistors (off/ON)       !       Bit read
s       Script engine                   :       Repeat e.g. r:10
v       Show volts/states               ;       Bits to read/write e.g. 0x55;2
w/W     PSU (off/ON)            <x>/<x= >/<0>   Usermacro x/assign x/list all

If i press the ‘f’ key and Enter the frequency measurement starts, but it measures 0Hz (since the power supply to the module is off). To turn the power supply on, i need to use the “W” key, but this doesnt work in HiZ mode:

HiZ> f
AUX Frequency:  autorange 0 Hz
HiZ> W
Command not used in this mode

To use the power supply, I had to select some other mode (not HiZ) by using the “m” key, so I picked UART (3), and some default speed settings (which don’t matter now, since I wont actually use UART).

In UART mode, I can now turn the 5V power on, and measure the frequency:

UART> W
Power supplies ON
UART> f
AUX Frequency:  autorange 57,751 Hz

Then I’ve tweaked the potentiometers, ran the measurement (“f” + Enter) again, and repeat until the desired frequency is set.

Browsing the Slovenian vehicle database

Slovenian “National Interoperability Framework” (NIO) published a database with data on every registered vehicle in Slovenia, so I decided to do some fun queries. (Query results are in Slovene, but i will try to translate the most interesting ones)

How many registered vehicles are in Slovenia?

+----------+
| count(*) |
+----------+
|  1516719 |
+----------+

What about different vehicle types?

+-------------------------------------+----------+
| J-Kategorija in vrsta vozila (opis) | count(*) |
+-------------------------------------+----------+
| osebni avtomobil                    |  1148673 | #(personal) cars
| traktor                             |   109677 | #tractors
| tovorno vozilo                      |    86719 | #trucks
| motorno kolo                        |    54778 | #motorcycles
| priklopno vozilo                    |    37240 | #trailers
| kolo z motorjem                     |    32215 | #motorized bicycles
| vlečno vozilo                       |    12266 |
| moped-dvokolo                       |    10833 |
| traktorski priklopnik               |     7252 |
| delovno vozilo                      |     6394 |
| štirikolo                           |     5391 |
| avtobus                             |     2687 |
| trikolo                             |     1471 |
| lahko štirikolo                     |      828 |
| Neznana vrednost                    |      235 |
| moped-trikolo                       |       19 |
| motorno kolo z visoko močjo         |       19 |
| kolo na motorni pogon               |        7 |
| dvokolesni moped                    |        6 |
| motorno kolo z nizko močjo          |        4 |
| motorno kolo s srednjo močjo        |        2 |
| gosenični traktor                   |        2 |
| NULL                                |        1 |
+-------------------------------------+----------+

Common manufacturers (cars only):

+-----------------------------+----------+
| D1-Znamka                   | count(*) |
+-----------------------------+----------+
| RENAULT                     |   195620 |
| VOLKSWAGEN                  |   167546 |
| OPEL                        |    90443 |
| CITROEN                     |    80923 |
| PEUGEOT                     |    72295 |
| FORD                        |    60756 |
| FIAT                        |    47954 |
| AUDI                        |    46237 |
| ŠKODA                       |    43864 |
| BMW                         |    37414 |
| HYUNDAI                     |    36942 |
| TOYOTA                      |    33153 |
| SEAT                        |    27598 |
| KIA                         |    26660 |
| MERCEDES BENZ               |    22120 |
| NISSAN                      |    20200 |
| MAZDA                       |    17181 |
| HONDA                       |    16269 |
| CHEVROLET                   |    14401 |
| SUZUKI                      |    13145 |
| VOLVO                       |    11045 |
| MERCEDES-BENZ               |     8233 |
| ALFA ROMEO                  |     8202 |
| DACIA                       |     7578 |
| MITSUBISHI                  |     7189 |
| DAEWOO                      |     7088 |
| SUBARU                      |     3546 |
| LAND ROVER                  |     2256 |
| LANCIA                      |     2248 |
| MINI                        |     1986 |
| ZASTAVA                     |     1770 |
| JEEP                        |     1498 |
| ROVER                       |     1438 |
| CHRYSLER                    |     1277 |
| SAAB                        |     1070 |
| SMART                       |     1051 |
| PORSCHE                     |      980 |
| UNIS                        |      963 |
| DAIHATSU                    |      789 |
| LADA                        |      611 |
| JAGUAR                      |      608 |
| LEXUS                       |      570 |
| SSANGYONG                   |      567 |
| VOLKSWAGEN, VW              |      461 |
| PROTON                      |      408 |
| FIAT-ADRIA                  |      400 |
| IMV                         |      380 |
| MCC                         |      349 |
| ADRIA                       |      292 |
| DODGE                       |      251 |
| CIMOS                       |      202 |
| MARUTI                      |      183 |
| AUSTIN                      |      113 |
| MG                          |      100 |
| IVECO                       |       96 |
| HYMER                       |       77 |
| IDA                         |       68 |
| CADILLAC                    |       65 |
| STEYR DAIMLER PUCH          |       60 |
| PUCH                        |       53 |
| TRIUMPH                     |       53 |
| SUN LIVING                  |       52 |
| WILLYS                      |       51 |
| MASERATI                    |       50 |
| BMW I                       |       49 |
| HUMMER                      |       47 |
| MAHINDRA                    |       46 |
| PONTIAC                     |       43 |
| FERRARI                     |       42 |
| TRABANT                     |       40 |
| TESLA                       |       37 |
| FORD-CHALLENGER             |       36 |
| AUTOCARAVANS RIMOR          |       36 |
| UAZ                         |       35 |
| CAPRON                      |       34 |
| NSU                         |       33 |
| TAM                         |       32 |
| RENAULT-ADRIA               |       31 |
| FSM                         |       29 |
| QUATTRO                     |       29 |
| CARTHAGO                    |       28 |
| ELNAGH                      |       27 |
| WARTBURG                    |       27 |
| INFINITI                    |       27 |
| BENIMAR                     |       27 |
| DETHLEFFS                   |       24 |
| TRIGANO                     |       23 |
| CHALLENGER                  |       22 |
| SIMCA                       |       21 |
| LINCOLN                     |       21 |
| KNAUS                       |       19 |
| DS                          |       19 |
| STEYR-PUCH                  |       19 |
| AUTOBIANCHI                 |       17 |
| GM DAEWOO                   |       17 |
| BUICK                       |       16 |
| SEA                         |       16 |
| PIAGGIO                     |       15 |
| ASTON MARTIN                |       15 |
| FIAT-JOINT                  |       14 |
| DANGEL                      |       14 |
| LOTUS                       |       13 |
| TATA                        |       13 |
| LAIKA                       |       12 |
| ISUZU                       |       12 |
| MORRIS                      |       12 |
| BENTLEY                     |       12 |
| STEYR-DAIMLER               |       12 |
| VAUXHALL                    |       11 |
| DAIMLER-ADRIA               |       11 |
| SHUANGHUAN                  |       11 |
| GMC                         |       11 |
| ROLLS ROYCE                 |       11 |
| HYNDAI                      |       10 |
| FIAT-ADRIA MOBIL            |       10 |
| DKW                         |       10 |
| INNOCENTI                   |        9 |
| OLDSMOBILE                  |        9 |
| VAZ                         |        9 |
| TALBOT                      |        9 |
| MOBILVETTA                  |        9 |
| EURA MOBIL                  |        8 |
| DAIMLER BENZ                |        8 |
| DAIMLERCHRYSLER             |        8 |
| WEINSBERG                   |        8 |
| ROLLER TEAM                 |        8 |
| RAPIDO                      |        8 |
| GM DAEWOO AUTO&TECHN        |        7 |
| GIOTTILINE                  |        7 |
| MORGAN                      |        7 |
| BUERSTNER GMBH              |        7 |
| CARAVANS INTERNATIONAL      |        6 |
| BUERSTNER                   |        6 |
| STEYR-DAIMLER-PUCH          |        6 |
| MCLOUIS                     |        6 |
| FORD-CHAUSSON               |        6 |
| HOBBY                       |        5 |
| PLYMOUTH                    |        5 |
| DAF                         |        5 |
| WESTFALIA                   |        5 |
| FIAT-CARTHAGO               |        5 |
| SUN ROLLER                  |        5 |
| BURSTNER                    |        5 |
| FIAT-TRIGANO                |        5 |
| AUTO UNION-DKW              |        5 |
| LUAZ                        |        5 |
| BEDFORD                     |        5 |
| PLA                         |        5 |
| SUNBEAM                     |        5 |
| CARAVAN INTERNATIONAL       |        5 |
| ARO                         |        4 |
| PANTHER                     |        4 |
| TATRA                       |        4 |
| ROLLS-ROYCE                 |        4 |
| DAIMLER                     |        4 |
| BERTONE                     |        4 |
| LAMBORGHINI                 |        4 |
| RIMOR                       |        4 |
| MC LOUIS                    |        4 |
| ELNAGH MARLIN               |        4 |
| BRAVIA                      |        4 |
| T.E.C.                      |        4 |
| JOINT                       |        3 |
| FIAT-CHAUSSON               |        3 |
| ASIA                        |        3 |
| ABARTH                      |        3 |
| ARCA                        |        3 |
| AUSTIN HEALEY               |        3 |
| BMW ALPINA                  |        3 |
| ALFA- ROMEO                 |        3 |
| RELIANT                     |        2 |
| BORGWARD                    |        2 |
| SCION                       |        2 |
| ADRIA CARAVAN               |        2 |
| FIAT-LAIKA                  |        2 |
| AERO                        |        2 |
| FIAT ARCA                   |        2 |
| GREAT WALL                  |        2 |
| TOMOS-CITROEN               |        2 |
| HYMERMOBIL                  |        2 |
| AUTO-UNION                  |        2 |
| ZAZ                         |        2 |
| CONCORDE                    |        2 |
| MERCURY                     |        2 |
| BINZ                        |        2 |
| JBA                         |        2 |
| MATRA SIMCA                 |        2 |
| KTM                         |        2 |
| CHRYSLER-SIMCA              |        2 |
| FSO                         |        2 |
| A.C. CARS                   |        1 |
| ALFA-ROMEO                  |        1 |
| CHAUSSON                    |        1 |
| PHOENIX                     |        1 |
| DONNET ZEDEL                |        1 |
| FIAT-AUTOCARAVANS RIMOR     |        1 |
| STEYER-PUCH                 |        1 |
| CALVY MITCHEL               |        1 |
| LMC                         |        1 |
| GREAT WALL MOTOR            |        1 |
| HUPMOBILE                   |        1 |
| PORCSHE                     |        1 |
| AC COBRA LTD                |        1 |
| BSA                         |        1 |
| CATERHAM                    |        1 |
| TOMOS CITROEN               |        1 |
| LDV                         |        1 |
| FORD-GB                     |        1 |
| LAFER                       |        1 |
| ITASCA                      |        1 |
| KUTSENITS                   |        1 |
| SHELBY                      |        1 |
| IVECO-LAIKA                 |        1 |
| ACURA                       |        1 |
| VOLKSWAGWN                  |        1 |
| GLAS-ISARIA                 |        1 |
| IRENAULT                    |        1 |
| STEYR-FIAT                  |        1 |
| API                         |        1 |
| VOLKSVAGEN                  |        1 |
| DAIMLER-LA STRADA           |        1 |
| JEPP                        |        1 |
| K1                          |        1 |
| AMC                         |        1 |
| V0LKSWAGEN                  |        1 |
| BOMBARDIER                  |        1 |
| LASTNA IZDELAVA             |        1 |
| FERGUSON                    |        1 |
| AHORN                       |        1 |
| GAZELLE                     |        1 |
| ROSENGART                   |        1 |
| OVERLAND                    |        1 |
| FORD-D                      |        1 |
| MOSKVICH                    |        1 |
| WILLIS                      |        1 |
| CIMOS CITROEN               |        1 |
| ROVER RH(600)               |        1 |
| VOLKWAGEN                   |        1 |
| VOLSKWAGEN                  |        1 |
| BUGATTI                     |        1 |
| LEA-FRANCIS                 |        1 |
| STANDARD                    |        1 |
| MAN                         |        1 |
| MERCEDES  BENZ              |        1 |
| ARSENAL                     |        1 |
| M-R                         |        1 |
| FORD GPW                    |        1 |
| MERCEDES                    |        1 |
| ADLER                       |        1 |
| AUSTIN MORIS                |        1 |
| LTI                         |        1 |
| NSU-FIAT                    |        1 |
| ITINEO                      |        1 |
| TRIGANO CHAUSSON            |        1 |
| MITSUBISHI-NL               |        1 |
| PHILLIPS                    |        1 |
| FORD-USA                    |        1 |
| FRANKIA                     |        1 |
| ASTRA                       |        1 |
| GAZ                         |        1 |
| ESSEX                       |        1 |
| AUTOMIRAGE                  |        1 |
| POESSL                      |        1 |
| VOLGA                       |        1 |
| REUTER                      |        1 |
| MAYBACH                     |        1 |
| DAIMLERCHRYSLER-ADRIA MOBIL |        1 |
| BLUCAMP                     |        1 |
| FORD-BURSTNER               |        1 |
| TOMOS                       |        1 |
| AMILCAR                     |        1 |
| ZAPOROŽEC-ZAZ               |        1 |
| FIAT-MOOVEO                 |        1 |
| STEMMER                     |        1 |
| PILGRIM                     |        1 |
| FENDT                       |        1 |
| GERMAN E CARS               |        1 |
| IFA                         |        1 |
| VW                          |        1 |
| WOLSELEY                    |        1 |
| OLTCIT                      |        1 |
| IKOR-B                      |        1 |
| AUTOZODIACO                 |        1 |
| FOELLNER                    |        1 |
+-----------------------------+----------+

Since data entry is done at registration points by hand, you see “MERCEDES BENZ” and “MERCEDES-BENZ”  etc. as different manufacturers.

How many Lamborghinis (all vehicle types, all spellings)?

+----------------+----------+
| D1-Znamka      | count(*) |
+----------------+----------+
| LAMBORDINI     |        1 |
| LAMBORDŽINI    |        1 |
| LAMBORGHINI    |     2417 |
| LAMBORGINI     |        5 |
| S. LAMBORGHINI |        1 |
+----------------+----------+

Nice! Must be alot of rich people here.. but lets check the vehicle type first:

+-------------------------------------+----------+
| J-Kategorija in vrsta vozila (opis) | count(*) |
+-------------------------------------+----------+
| traktor                             |     2419 | #tractors
| osebni avtomobil                    |        4 | #cars
| Neznana vrednost                    |        2 | #unknown
+-------------------------------------+----------+

Okay, maybe not. Lets check the not-tractors:

+-------------+-----------------+------------------------+
| D1-Znamka   | D2-Tovar oznaka | D3-Komerc oznaka       |
+-------------+-----------------+------------------------+
| LAMBORGHINI | 140-R-45-A-P    | GALLARDO               |
| LAMBORGHINI | 140-R-45-A-P    | GALLARDO               |
| LAMBORGHINI | NULL            | R 235                  | #unknown type, actually a tractor
| LAMBORGHINI | L147            | LAMBORGHINI MURCIELAGO |
| LAMBORGHINI | NULL            | LDA 90                 | #unknown type, actually a bulldozer-like vehicle
| LAMBORGHINI | 147F-704S       | MURCIELAGO /           |
+-------------+-----------------+------------------------+

Nice, 4 actual Lamborhini cars.

What about some demographic data – how old are the owners (Starost == age)?

+----------------------------+----------+
| C2-Starost lastnika vozila | count(*) |
+----------------------------+----------+
| NULL                       |   319475 | #company owned cars
| 1                          |        1 |
| 2                          |        3 |
| 3                          |        1 |
| 4                          |        2 |
| 5                          |        6 |
| 6                          |        9 |
| 7                          |        9 |
| 8                          |        3 |
| 9                          |        2 |
| 10                         |        7 |
| 11                         |        9 |
| 12                         |       14 |
| 13                         |       17 |
| 14                         |       24 |
| 15                         |       70 |
| 16                         |      173 |
| 17                         |      434 |
| 18                         |      965 |
| 19                         |     2360 |
| 20                         |     3890 |
| 21                         |     5298 |
| 22                         |     6746 |
| 23                         |     8147 |
| 24                         |     9243 |
| 25                         |    11035 |
| 26                         |    12429 |
| 27                         |    14028 |
| 28                         |    16177 |
| 29                         |    17011 |
| 30                         |    17357 |
| 31                         |    18643 |
| 32                         |    19350 |
| 33                         |    20415 |
| 34                         |    21860 |
| 35                         |    22956 |
| 36                         |    23990 |
| 37                         |    24682 |
| 38                         |    25084 |
| 39                         |    25136 |
| 40                         |    26056 |
| 41                         |    25898 |
| 42                         |    25784 |
| 43                         |    26283 |
| 44                         |    26737 |
| 45                         |    26501 |
| 46                         |    25600 |
| 47                         |    26802 |
| 48                         |    27546 |
| 49                         |    28138 |
| 50                         |    29551 |
| 51                         |    29234 |
| 52                         |    28672 |
| 53                         |    28220 |
| 54                         |    27573 |
| 55                         |    27435 |
| 56                         |    26243 |
| 57                         |    25566 |
| 58                         |    25066 |
| 59                         |    25115 |
| 60                         |    24906 |
| 61                         |    24058 |
| 62                         |    22796 |
| 63                         |    22614 |
| 64                         |    21458 |
| 65                         |    19926 |
| 66                         |    19542 |
| 67                         |    17366 |
| 68                         |    16118 |
| 69                         |    14913 |
| 70                         |    13200 |
| 71                         |     9067 |
| 72                         |    10557 |
| 73                         |    11165 |
| 74                         |    10985 |
| 75                         |     9805 |
| 76                         |     9143 |
| 77                         |     8248 |
| 78                         |     7308 |
| 79                         |     6522 |
| 80                         |     5949 |
| 81                         |     5125 |
| 82                         |     4345 |
| 83                         |     3621 |
| 84                         |     3138 |
| 85                         |     2508 |
| 86                         |     2121 |
| 87                         |     1461 |
| 88                         |     1162 |
| 89                         |      811 |
| 90                         |      574 |
| 91                         |      441 |
| 92                         |      230 |
| 93                         |      173 |
| 94                         |      120 |
| 95                         |       73 |
| 96                         |       39 |
| 97                         |       27 |
| 98                         |        6 |
| 99                         |        4 |
| 100                        |        1 |
| 101                        |        2 |
| 102                        |        4 |
| 103                        |        1 |
| 105                        |        1 |
| 108                        |        3 |
| 109                        |        1 |
+----------------------------+----------+

In Slovenia it is legal for a minor to own or inherit a vehicle, but to register it, an adult “user” with the appropriate licence must be assigned.

Which car does to youngest car owner have? (not sure if it’s inherited or a data entry error – age:=1)

+------------+--------------------+----------------------------+
| D1-Znamka  | D3-Komerc oznaka   | C2-Starost lastnika vozila |
+------------+--------------------+----------------------------+
| VOLKSWAGEN | TOURAN / 2.0 / TDI | 1                          |
+------------+--------------------+----------------------------+

How many cars are registered per administrative unit (geographical)?

+----------+--------------------------------------------+
| count(*) | C13-Upravna enota uporabnika vozila (opis) |
+----------+--------------------------------------------+
|   203373 | LJUBLJANA                                  |
|    74347 | MARIBOR                                    |
|    45983 | KRANJ                                      |
|    37767 | CELJE                                      |
|    37697 | PTUJ                                       |
|    36522 | NOVA GORICA                                |
|    36510 | NOVO MESTO                                 |
|    34164 | DOMŽALE                                    |
|    33235 | KOPER                                      |
|    29689 | MURSKA SOBOTA                              |
|    23703 | ŽALEC                                      |
|    22915 | ŠKOFJA LOKA                                |
|    22661 | VELENJE                                    |
|    22344 | GROSUPLJE                                  |
|    19867 | SLOVENSKA BISTRICA                         |
|    19278 | RADOVLJICA                                 |
|    19103 | KAMNIK                                     |
|    17929 | ŠMARJE PRI JELŠAH                          |
|    16023 | KRŠKO                                      |
|    15804 | SEŽANA                                     |
|    15752 | JESENICE                                   |
|    14860 | AJDOVŠČINA                                 |
|    13762 | VRHNIKA                                    |
|    13339 | BREŽICE                                    |
|    13236 | POSTOJNA                                   |
|    13144 | RAVNE NA KOROŠKEM                          |
|    12830 | SLOVENSKE KONJICE                          |
|    11957 | TREBNJE                                    |
|    11811 | LENDAVA                                    |
|    11739 | SLOVENJ GRADEC                             |
|    11409 | GORNJA RADGONA                             |
|    11220 | LITIJA                                     |
|    11185 | ŠENTJUR PRI CELJU                          |
|    10816 | LENART                                     |
|    10691 | PESNICA                                    |
|    10550 | TOLMIN                                     |
|    10140 | SEVNICA                                    |
|     9860 | ČRNOMELJ                                   |
|     9687 | PIRAN                                      |
|     9571 | LAŠKO                                      |
|     9395 | CERKNICA                                   |
|     9316 | LJUTOMER                                   |
|     9163 | IDRIJA                                     |
|     8950 | MOZIRJE                                    |
|     8845 | IZOLA                                      |
|     8838 | ORMOŽ                                      |
|     8783 | ZAGORJE OB SAVI                            |
|     8605 | RADLJE OB DRAVI                            |
|     8347 | KOČEVJE                                    |
|     8318 | ILIRSKA BISTRICA                           |
|     8170 | TRBOVLJE                                   |
|     8127 | TRŽIČ                                      |
|     7908 | RUŠE                                       |
|     7407 | LOGATEC                                    |
|     7364 | RIBNICA                                    |
|     4936 | DRAVOGRAD                                  |
|     4534 | HRASTNIK                                   |
|     4493 | METLIKA                                    |
|      701 | Sistem                                     |
+----------+--------------------------------------------+

 

What about fuel type (gasoline vs diesel vs others)?

+----------+-----------------------------+
| count(*) | P13-Vrsta goriva (opis)     |
+----------+-----------------------------+
|   621159 | Bencin                      | #gasoline
|   517443 | Dizel                       | #diesel
|     8692 | Bencin/Utek. naft. plin     | #gasoline + LPG
|      662 | Utekočinjeni naftni plin    | #LPG
|      353 | Ni goriva                   | #no gas? maybe electric
|       97 | Dizel/Biodizel in komb.     |
|       94 | Komprimiran zemeljski plin  |
|       68 | Bencin/Komp. zem. plin      |
|       57 | Mešanica                    |
|       16 | Bencin/Etanol               |
|       14 | Dizel/Utek. naft. plin.     |
|       10 | Vodik                       | #hydrogen
|        5 | Utekočinjeni naftni plin*   |
|        3 | Diesel, nafta, plinsko olje |
+----------+-----------------------------+

Most common engine size/displacement (ccm):

+-------------------------+----------+
| P11-Delovna prostornina | count(*) |
+-------------------------+----------+
| 1598                    |   113932 | 
| 1896                    |    85995 |
| 1149                    |    71910 |
| 1968                    |    60370 |
| 1390                    |    49126 |
| 1560                    |    48783 |
| 1461                    |    47872 |
| 1997                    |    39966 |
| 1995                    |    38076 |
| 49                      |    34013 | #most common for mopeds
| -1                      |    31052 |
| 1360                    |    29413 |
| 1242                    |    22095 |
| 1870                    |    19239 |
| 1587                    |    19183 |
| 1998                    |    18516 |
| 1197                    |    18451 |
| 1910                    |    16872 |
| 1396                    |    14560 |
| 0                       |    14301 |
| 1364                    |    13973 |
| 1596                    |    13717 |
| 998                     |    12881 |
| 2500                    |    12685 |
| 1398                    |    12437 |
| 999                     |    11584 |
| 1595                    |    11523 |
| 1199                    |    10962 |
| 1686                    |    10720 |
| 1248                    |    10545 |
| 1198                    |     9736 |
| 1399                    |     9205 |
| 2198                    |     8630 |
| 1124                    |     8099 |
| 1368                    |     8067 |
| 1299                    |     7689 |
| 1397                    |     7653 |
| 1229                    |     7485 |
| 2148                    |     7377 |
| 2360                    |     7296 |
| 1796                    |     7276 |
| 1388                    |     6722 |
| 1781                    |     6601 |
| 1108                    |     6599 |
| 1991                    |     6406 |
| 1498                    |     6333 |
| 1298                    |     6236 |
| 2993                    |     6025 |
| 1591                    |     5859 |
| 1582                    |     5673 |
+-------------------------+----------+

 

And colors?

+---------------------------------+----------+
| R-Barva vozila (opis)           | count(*) |
+---------------------------------+----------+
| kovinski - SIVA - SVETLA        |   299205 | #metallic gray light
| navaden - BELA - SREDNJA        |   135132 | #white
| navaden - RDEČA - SREDNJA       |   104414 | #red
| kovinski - SIVA - TEMNA         |    97520 | #metallic gray dark
| kovinski - SIVA - SREDNJA       |    85646 | #metallic gray medium
| kovinski - ČRNA - SREDNJA       |    66214 | #metallic black medium
| navaden - BELA - TEMNA          |    64781 | #white dark (???)
| kovinski - ČRNA - TEMNA         |    61789 | #metallic black dark(?)
| kovinski - MODRA - TEMNA        |    60884 | #metallic blue dark
| kovinski - RDEČA - TEMNA        |    45224 | #metallic red dark
| kovinski - MODRA - SVETLA       |    41295 | 
| navaden - RDEČA - TEMNA         |    37676 |
| navaden - BELA - SVETLA         |    37414 |
| kovinski - MODRA - SREDNJA      |    31500 |
| kovinski - RJAVA - SVETLA       |    30638 |
| navaden - ČRNA - SREDNJA        |    27241 |
| kovinski - ZELENA - TEMNA       |    24184 |
| navaden - ZELENA - SREDNJA      |    20368 |
| kovinski - ZELENA - SVETLA      |    20096 |
| kovinski - RDEČA - SREDNJA      |    17849 |
| navaden - MODRA - TEMNA         |    17846 |
| navaden - MODRA - SREDNJA       |    16393 |
| navaden - ČRNA - TEMNA          |    13610 |
| navaden - ORANŽNA - SREDNJA     |    11157 |
| navaden - ZELENA - SVETLA       |     9743 |
| kovinski - RJAVA - TEMNA        |     9408 |
| navaden - RUMENA - SREDNJA      |     8159 |
| kovinski - ZELENA - SREDNJA     |     7212 |
| navaden - RDEČA - SVETLA        |     7122 |
| kovinski - RJAVA - SREDNJA      |     7018 |
| navaden - MODRA - SVETLA        |     6953 |
| kovinski - BELA - SVETLA        |     6755 |
| navaden - SIVA - SREDNJA        |     6530 |
| navaden - ZELENA - TEMNA        |     6384 |
| kovinski - BELA - SREDNJA       |     5737 |
| kovinski - RUMENA - TEMNA       |     5470 |
| navaden - SIVA - TEMNA          |     5014 |
| kovinski - ČRNA - SVETLA        |     4996 |
| kovinski - BELA - TEMNA         |     4836 |
| navaden - RUMENA - SVETLA       |     4758 |
| kovinski - ORANŽNA - SREDNJA    |     4345 |
| navaden - RUMENA - TEMNA        |     3938 |
| kovinski - VIJOLIČNA - TEMNA    |     3741 |
| navaden - RJAVA - SVETLA        |     3471 |
| kovinski - RUMENA - SVETLA      |     3392 |
| navaden - SIVA - SVETLA         |     3067 |
| navaden - ORANŽNA - TEMNA       |     3023 |
| kovinski - ORANŽNA - TEMNA      |     2529 |
| navaden - ORANŽNA - SVETLA      |     2525 |
| kovinski - RUMENA - SREDNJA     |     2300 |
| navaden - RJAVA - SREDNJA       |     2204 |
| kovinski - RDEČA - SVETLA       |     2067 |
| NULL                            |     1501 |
| kovinski - ORANŽNA - SVETLA     |      905 |
| navaden - RJAVA - TEMNA         |      827 |
| kovinski - VIJOLIČNA - SREDNJA  |      701 |
| navaden - VIJOLIČNA - TEMNA     |      696 |
| kovinski - VIJOLIČNA - SVETLA   |      635 |
| navaden - ČRNA - SVETLA         |      360 |
| navaden - VIJOLIČNA - SREDNJA   |      210 |
| navaden - VIJOLIČNA - SVETLA    |      111 |
+---------------------------------+----------+

kovinski = metallic
temna = dark
srednja = medium
svetla = light/bright
siva = gray
bela = white
črna = black
rdeča = red
modra = blue
zelena = green
oranžna = orange
rjava = brown

 

I will probably add more queries/results, when I get some new ideas and/or questions.

Converting data from Microsoft Access (mdb) format to MySQL

Slovenian “National Interoperability Framework” (NIO) publishes some interesting data every now and then, but most data is usually in MS Access ‘mdb’ format and/or ‘csv’. Working with csv files is painful, since you have to do everything by hand (no selects, filters etc, you have to go through each and every line and check the conditions in a giant loop), and using Microsoft Access on Linux is an even bigger NO.

So, i decided to download some data (anonymized data on drivers licences in Slovenia), and convert it from mdb to MySQL (and insert it into the database). I could have done the same with parsing the csv file, but doing it from mdb autogenerates the database schema, (with csv, you have to do that yourself, by hand).

For converting the data i used mdbtools. The data i used can be found here (zipped mdb).

The first thing after downloading and unzipping the data, was to check which (and how many) tables are inside – you can do this with the “mdb-tables” command.

mulaz@x ~/vd $ mdb-tables Vozniki2013.mdb 
Vozniki_2013 

So, there is one table in the database named “Vozniki_2013” (old data, I know).

To generate the schema I used the “mdb-schema” command (with MySQL output):

mulaz@x ~/vd $ mdb-schema Vozniki2013.mdb mysql
-- ----------------------------------------------------------
-- MDB Tools - A library for reading MS Access database files
-- Copyright (C) 2000-2011 Brian Bruns and others.
-- Files in libmdb are licensed under LGPL and the utilities under
-- the GPL, see COPYING.LIB and COPYING files respectively.
-- Check out http://mdbtools.sourceforge.net
-- ----------------------------------------------------------

-- That file uses encoding UTF-8

CREATE TABLE `Vozniki_2013`
 (
        `VIR_PODATKOV`                  varchar (510), 
        `STAROST`                       varchar (510), 
        `SPOL`                  varchar (510), 
        `PODVRSTA_VD`                   varchar (510), 
        `DATUM_IZDAJE`                  varchar (510), 
        `DATUM_VELJAVNOSTI`                     varchar (510), 
        `OMEJITEV_220`                  varchar (510), 
        `OMEJITEV_70`                   varchar (510), 
        `OMEJITEV_71`                   varchar (510), 
        `AM_DATUM_PRIDOBITVE`                   varchar (510), 
        `AM_DATUM_VELJAVNOSTI`                  varchar (510), 
        `A1_DATUM_PRIDOBITVE`                   varchar (510), 
        `A1_DATUM_VELJAVNOSTI`                  varchar (510), 
        `AM2_DATUM_PRIDOBITVE`                  varchar (510), 
        `AM2_DATUM_VELJAVNOSTI`                 varchar (510), 
        `A_DATUM_PRIDOBITVE`                    varchar (510), 
        `A_DATUM_VELJAVNOSTI`                   varchar (510), 
        `B1_DATUM_PRIDOBITVE`                   varchar (510), 
        `B1_DATUM_VELJAVNOSTI`                  varchar (510), 
        `B_DATUM_PRIDOBITVE`                    varchar (510), 
        `B_DATUM_VELJAVNOSTI`                   varchar (510), 
        `C1_DATUM_PRIDOBITVE`                   varchar (510), 
        `C1_DATUM_VELJAVNOSTI`                  varchar (510), 
        `C1_95`                 varchar (510), 
        `C_DATUM_PRIDOBITVE`                    varchar (510), 
        `C_DATUM_VELJAVNOSTI`                   varchar (510), 
        `C_95`                  varchar (510), 
        `D1_DATUM_PRIDOBITVE`                   varchar (510), 
        `D1_DATUM_VELJAVNOSTI`                  varchar (510), 
        `D1_95`                 varchar (510), 
        `D_DATUM_PRIDOBITVE`                    varchar (510), 
        `D_DATUM_VELJAVNOSTI`                   varchar (510), 
        `D_95`                  varchar (510), 
        `BE_DATUM_PRIDOBITVE`                   varchar (510), 
        `BE_DATUM_VELJAVNOSTI`                  varchar (510), 
        `C1E_DATUM_PRIDOBITVE`                  varchar (510), 
        `C1E_DATUM_VELJAVNOSTI`                 varchar (510), 
        `C1E_95`                        varchar (510), 
        `CE_DATUM_PRIDOBITVE`                   varchar (510), 
        `CE_DATUM_VELJAVNOSTI`                  varchar (510), 
        `CE_95`                 varchar (510), 
        `D1E_DATUM_PRIDOBITVE`                  varchar (510), 
        `D1E_DATUM_VELJAVNOSTI`                 varchar (510), 
        `D1E_95`                        varchar (510), 
        `DE_DATUM_PRIDOBITVE`                   varchar (510), 
        `DE_DATUM_VELJAVNOSTI`                  varchar (510), 
        `DE_95`                 varchar (510), 
        `F_DATUM_PRIDOBITVE`                    varchar (510), 
        `F_DATUM_VELJAVNOSTI`                   varchar (510), 
        `G_DATUM_PRIDOBITVE`                    varchar (510), 
        `G_DATUM_VELJAVNOSTI`                   varchar (510), 
        `ID`                    int
);

Every field is “varchar (510)”, and that’s long. I’ve checked the data, and it fits into “varchar (50)”, so you can do a sed replace, to change the field size in the output. After that, you connect to the database, paste this output into the console (or pipe it directly from the command), and you will have an empty table created for your data.

This would also be a good time to create any indexes you need.

Now all we need is the actual data; we can get that using the “mdb-export” command (we have to tell it the table we want to export, and the output syntax).

mulaz@x ~/vd $ mdb-export -I mysql Vozniki2013.mdb Vozniki_2013
INSERT INTO `Vozniki_2013` (`VIR_PODATKOV`, `STAROST`, `SPOL`, `PODVRSTA_VD`, `DATUM_IZDAJE`, `DATUM_VELJAVNOSTI`, `OMEJITEV_220`, `OMEJITEV_70`, `OMEJITEV_71`, `AM_DATUM_PRIDOBITVE`, `AM_DATUM_VELJAVNOSTI`, `A1_DATUM_PRIDOBITVE`, `A1_DATUM_VELJAVNOSTI`, `AM2_DATUM_PRIDOBITVE`, `AM2_DATUM_VELJAVNOSTI`, `A_DATUM_PRIDOBITVE`, `A_DATUM_VELJAVNOSTI`, `B1_DATUM_PRIDOBITVE`, `B1_DATUM_VELJAVNOSTI`, `B_DATUM_PRIDOBITVE`, `B_DATUM_VELJAVNOSTI`, `C1_DATUM_PRIDOBITVE`, `C1_DATUM_VELJAVNOSTI`, `C1_95`, `C_DATUM_PRIDOBITVE`, `C_DATUM_VELJAVNOSTI`, `C_95`, `D1_DATUM_PRIDOBITVE`, `D1_DATUM_VELJAVNOSTI`, `D1_95`, `D_DATUM_PRIDOBITVE`, `D_DATUM_VELJAVNOSTI`, `D_95`, `BE_DATUM_PRIDOBITVE`, `BE_DATUM_VELJAVNOSTI`, `C1E_DATUM_PRIDOBITVE`, `C1E_DATUM_VELJAVNOSTI`, `C1E_95`, `CE_DATUM_PRIDOBITVE`, `CE_DATUM_VELJAVNOSTI`, `CE_95`, `D1E_DATUM_PRIDOBITVE`, `D1E_DATUM_VELJAVNOSTI`, `D1E_95`, `DE_DATUM_PRIDOBITVE`, `DE_DATUM_VELJAVNOSTI`, `DE_95`, `F_DATUM_PRIDOBITVE`, `F_DATUM_VELJAVNOSTI`, `G_DATUM_PRIDOBITVE`, `G_DATUM_VELJAVNOSTI`, `ID`) VALUES ("SVD","42","Ž","S","06.12.2002","21.04.2051",NULL,NULL,NULL,"02.06.1987","21.04.2051",NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,"21.12.1989","21.04.2051",NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,"02.06.1987","21.04.2051","02.06.1987","21.04.2051",1);
INSERT INTO `Vozniki_2013` (`VIR_PODATKOV`, `STAROST`, `SPOL`, `PODVRSTA_VD`, `DATUM_IZDAJE`, `DATUM_VELJAVNOSTI`, `OMEJITEV_220`, `OMEJITEV_70`, `OMEJITEV_71`, `AM_DATUM_PRIDOBITVE`, `AM_DATUM_VELJAVNOSTI`, `A1_DATUM_PRIDOBITVE`, `A1_DATUM_VELJAVNOSTI`, `AM2_DATUM_PRIDOBITVE`, `AM2_DATUM_VELJAVNOSTI`, `A_DATUM_PRIDOBITVE`, `A_DATUM_VELJAVNOSTI`, `B1_DATUM_PRIDOBITVE`, `B1_DATUM_VELJAVNOSTI`, `B_DATUM_PRIDOBITVE`, `B_DATUM_VELJAVNOSTI`, `C1_DATUM_PRIDOBITVE`, `C1_DATUM_VELJAVNOSTI`, `C1_95`, `C_DATUM_PRIDOBITVE`, `C_DATUM_VELJAVNOSTI`, `C_95`, `D1_DATUM_PRIDOBITVE`, `D1_DATUM_VELJAVNOSTI`, `D1_95`, `D_DATUM_PRIDOBITVE`, `D_DATUM_VELJAVNOSTI`, `D_95`, `BE_DATUM_PRIDOBITVE`, `BE_DATUM_VELJAVNOSTI`, `C1E_DATUM_PRIDOBITVE`, `C1E_DATUM_VELJAVNOSTI`, `C1E_95`, `CE_DATUM_PRIDOBITVE`, `CE_DATUM_VELJAVNOSTI`, `CE_95`, `D1E_DATUM_PRIDOBITVE`, `D1E_DATUM_VELJAVNOSTI`, `D1E_95`, `DE_DATUM_PRIDOBITVE`, `DE_DATUM_VELJAVNOSTI`, `DE_95`, `F_DATUM_PRIDOBITVE`, `F_DATUM_VELJAVNOSTI`, `G_DATUM_PRIDOBITVE`, `G_DATUM_VELJAVNOSTI`, `ID`) VALUES ("SVD","44","Ž","S","12.07.2000","29.10.2049",NULL,NULL,NULL,"12.07.2000","29.10.2049",NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,"12.07.2000","29.10.2049",NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,"12.07.2000","29.10.2049",2);
INSERT INTO `Vozniki_2013` (`VIR_PODATKOV`, `STAROST`, `SPOL`, `PODVRSTA_VD`, `DATUM_IZDAJE`, `DATUM_VELJAVNOSTI`, `OMEJITEV_220`, `OMEJITEV_70`, `OMEJITEV_71`, `AM_DATUM_PRIDOBITVE`, `AM_DATUM_VELJAVNOSTI`, `A1_DATUM_PRIDOBITVE`, `A1_DATUM_VELJAVNOSTI`, `AM2_DATUM_PRIDOBITVE`, `AM2_DATUM_VELJAVNOSTI`, `A_DATUM_PRIDOBITVE`, `A_DATUM_VELJAVNOSTI`, `B1_DATUM_PRIDOBITVE`, `B1_DATUM_VELJAVNOSTI`, `B_DATUM_PRIDOBITVE`, `B_DATUM_VELJAVNOSTI`, `C1_DATUM_PRIDOBITVE`, `C1_DATUM_VELJAVNOSTI`, `C1_95`, `C_DATUM_PRIDOBITVE`, `C_DATUM_VELJAVNOSTI`, `C_95`, `D1_DATUM_PRIDOBITVE`, `D1_DATUM_VELJAVNOSTI`, `D1_95`, `D_DATUM_PRIDOBITVE`, `D_DATUM_VELJAVNOSTI`, `D_95`, `BE_DATUM_PRIDOBITVE`, `BE_DATUM_VELJAVNOSTI`, `C1E_DATUM_PRIDOBITVE`, `C1E_DATUM_VELJAVNOSTI`, `C1E_95`, `CE_DATUM_PRIDOBITVE`, `CE_DATUM_VELJAVNOSTI`, `CE_95`, `D1E_DATUM_PRIDOBITVE`, `D1E_DATUM_VELJAVNOSTI`, `D1E_95`, `DE_DATUM_PRIDOBITVE`, `DE_DATUM_VELJAVNOSTI`, `DE_95`, `F_DATUM_PRIDOBITVE`, `F_DATUM_VELJAVNOSTI`, `G_DATUM_PRIDOBITVE`, `G_DATUM_VELJAVNOSTI`, `ID`) VALUES ("SVD","55","Ž","S","29.06.1994","05.11.2038",NULL,NULL,NULL,"07.06.1990","05.11.2038",NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,"07.06.1990","05.11.2038",NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,"07.06.1990","05.11.2038",3);
INSERT INTO `Vozniki_2013` (`VIR_PODATKOV`, `STAROST`, `SPOL`, `PODVRSTA_VD`, `DATUM_IZDAJE`, `DATUM_VELJAVNOSTI`, `OMEJITEV_220`, `OMEJITEV_70`, `OMEJITEV_71`, `AM_DATUM_PRIDOBITVE`, `AM_DATUM_VELJAVNOSTI`, `A1_DATUM_PRIDOBITVE`, `A1_DATUM_VELJAVNOSTI`, `AM2_DATUM_PRIDOBITVE`, `AM2_DATUM_VELJAVNOSTI`, `A_DATUM_PRIDOBITVE`, `A_DATUM_VELJAVNOSTI`, `B1_DATUM_PRIDOBITVE`, `B1_DATUM_VELJAVNOSTI`, `B_DATUM_PRIDOBITVE`, `B_DATUM_VELJAVNOSTI`, `C1_DATUM_PRIDOBITVE`, `C1_DATUM_VELJAVNOSTI`, `C1_95`, `C_DATUM_PRIDOBITVE`, `C_DATUM_VELJAVNOSTI`, `C_95`, `D1_DATUM_PRIDOBITVE`, `D1_DATUM_VELJAVNOSTI`, `D1_95`, `D_DATUM_PRIDOBITVE`, `D_DATUM_VELJAVNOSTI`, `D_95`, `BE_DATUM_PRIDOBITVE`, `BE_DATUM_VELJAVNOSTI`, `C1E_DATUM_PRIDOBITVE`, `C1E_DATUM_VELJAVNOSTI`, `C1E_95`, `CE_DATUM_PRIDOBITVE`, `CE_DATUM_VELJAVNOSTI`, `CE_95`, `D1E_DATUM_PRIDOBITVE`, `D1E_DATUM_VELJAVNOSTI`, `D1E_95`, `DE_DATUM_PRIDOBITVE`, `DE_DATUM_VELJAVNOSTI`, `DE_95`, `F_DATUM_PRIDOBITVE`, `F_DATUM_VELJAVNOSTI`, `G_DATUM_PRIDOBITVE`, `G_DATUM_VELJAVNOSTI`, `ID`) VALUES ("SVD","34","Ž","S","03.12.2007","17.03.2059",NULL,NULL,NULL,"15.11.1999","17.03.2059",NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,"15.11.1999","17.03.2059",NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,"15.11.1999","17.03.2059",4);
...many more lines (~1.3M)

All you have to do now, is to redirect this to a file, and copy it to te server with the database, and import it (or pipe it directly, if you can use the MySQL client on the machine you’re using now).

If you have a large table with alot of lines/INSERTs, you will notice that the import takes alot of time. The main slowdown for me was because MySQL commits the data after every INSERT statement, and that takes time. If you want to speed it up, you can disable the autocommit, and manually commit at the end. Since i’ve exported the data to a text file first, I just added the two lines in the beginning and the end of the file:

SET autocommit=0;
INSERT ....;
INSERT ....;
....
COMMIT;

And voila, you have all the data in your MySQL database now.