This came up the other day, how do you add a disk to VirtualBox that physically exists? E.G. you have created a test vbox and would like to plug in your old laptop hard drive and have a look at the contents..

Here goes..

sudo lsblk

And get the following similar output..

NAME                       MAJ:MIN RM   SIZE RO TYPE  MOUNTPOINT
sda                          8:0    0 465.8G  0 disk  
|-sda1                       8:1    0   243M  0 part  /boot
|-sda2                       8:2    0     1K  0 part  
|-sda5                       8:5    0 465.4G  0 part  
  +-sda5_crypt (dm-0)      252:0    0 465.4G  0 crypt 
    +-mint--vg-root (dm-1) 252:1    0 465.4G  0 lvm   /
sdb                          8:16   1   7.5G  0 disk  
|-sdb1                       8:17   1   459M  0 part  /media/your-username/usbstick
sdc                          8:32   0 465.8G  0 disk  
|-sdc1                       8:33   0   243M  0 part  
|-sdc2                       8:34   0     1K  0 part  
|-sdc5                       8:37   0 111.6G  0 part  

Examine the tree and find out what your old hardware is called, e.g. /dev/sdc in this case.

Now create a directory for the virtual disk..

mkdir -p /home/your-username-here/VirtualBox/spare_disks

And use vBox’s commandline tools to create the image, changing /dev/sdX for your device location, prolly /dev/sdc.

sudo VBoxManage internalcommands createrawvmdk -filename "/home/your-username-here/VirtualBox/spare_disks/500GB-hard-disk.vmdk" -rawdisk /dev/sdX

Now chown and chmod the varioius relevant locations and that’s it..

sudo chown your-username-here:your-username-here /home/your-username-here/VirtualBox/spare_disks/500GB-hard-disk.vmdk
sudo chmod 777 "/home/your-username-here/VirtualBox/spare_disks/500GB-hard-disk.vmdk"
sudo chmod 777 /dev/sdX

Now open VirtualBox and add in the disk in the usual way (Select your box, then –> settings –> storage –> select disk controller –> click ‘adds hard disk’ icon –> Select vmdk file and that’s it. Fire up your machine and voila, the machine sees the external disk. Nice.

Every time I want to do this it seems to take waaay longer than it should. Here then, the instructions in n steps under Linux.

sudo lsblk

Check and see which device is your USB stick. In the commands below, the device is listed as /dev/sdaX, but in reality it will be /dev/sdc probably. Be careful. If you mis-select the device you could potentially DESTROY all your data, in which case you will have to go through all five stages of data recovery grief, namely..

Denial = “It wasn’t me”

Anger = “How the fuck did I just do that? What was I thinking, cutting and pasting commands off the interwebs??”

Bargaining = “If I reboot the machine, the data will probably come back. Actually, thats almost a certainty.”

Depression = “Who suggested rebooting? What a stupid idea that was. I’m completely screwed. Why didn’t I do routine backups, especially of my thesis, my research, my entire music collection and all my photos since 2003.”

Acceptance = “Either fork out for a data recovery company or start fresh with nothing.”

Consider yourself warned. And also, make regular backups.

So, unmount the USB drive in question..

sudo umount /dev/sdX

Format it as FAT32

sudo mkdosfs -n 'usb-bootable' -I /dev/sdX -F 32

Run isohybrid..

sudo isohybrid /home/username/Downloads/systemrescuecd-x86-4.7.1.iso

Now burn the iso image to USB

sudo dd if=/home/username/Downloads/systemrescuecd-x86-4.7.1.iso of=/dev/sdX bs=4k

Flush data to the USB drive..

sudo sync

And eject the USB drive..

sudo eject /dev/sdX

Finished. Your USB pendrive is bootable and ready to go.

I’ve just found a fantastic new set of tools for handling CSV data.  CSVkit allows you to chop, change, splice and cut any csv file you like without all the Excel style overhead. All command line driven on Linux, this is a really fast tool for preparing CSV data for upload to a database.  Perhaps even cooler, once you have your data nicely prepared, you can even generate the SQL statements to create the database on your database server.  Very very sweet and massively time saving too.

As it’s a python too, get these in first..

sudo apt-get install python-dev python-pip python-setuptools build-essential

Then install with pip

sudo pip install --upgrade setuptools

sudo pip install --upgrade csvkit

I just downloaded a huge excel spreadsheet from the LSE with the list of all company names and details on the exchange.

Let’s convert that Excel file into csv

in2csv list-of-all-companies.xls > list-of-all-companies.csv

Nice. The first six or so lines are fluff, so let’s remove them

sed -e '1,6d' < list-of-all-companies.csv > list-of-all-companies-trimmed.csv

OK, great. Now there are a lot more things you could do, such as select the columns you want etc, but I’m just going to create the MySQL table for the CSV file. It can convert to any of these database formats – access, sybase, sqlite, informix, firebird, mysql, oracle, maxdb, postgresql & mssql. Sick.

Right, let’s create the table.

csvsql -i mysql list-of-all-companies-trimmed.csv

produces..

CREATE TABLE `list-of-all-companies-trimmed` (
	`List Date` FLOAT NOT NULL, 
	`Company` VARCHAR(35) NOT NULL, 
	`Group` FLOAT, 
	`Sector` VARCHAR(38) NOT NULL, 
	`Sub Sector` VARCHAR(39) NOT NULL, 
	`Country of Incorporation` VARCHAR(2) NOT NULL, 
	`Market` VARCHAR(11) NOT NULL, 
	`Mkt Cap £m` FLOAT, 
	`International Main Market` BOOL NOT NULL, 
	`FCA Listing Category` VARCHAR(8), 
	CHECK (`International Main Market` IN (0, 1))
);

Cut and paste this to your favourite SQL editor and execute on the server and voilà, instant data table. You can also have csvsql upload the table and data direct to the server too. Crazy sweet. A brilliant tool.

Time to read the documentation and tutorial for more info. Worth every minute.

On your workstation host, open VirtualBox. Select the host you want to set up shared folders on, right click and click ‘Settings’.

In the Settings panel (shown above), click ‘Shared Folders’ in the left pane.

Click ‘Machine Folders’ in the right pane and then the new path folder icon on the extreme right hand side.

Add a folder path. This location is on the host workstation and will be made available to the virtual machine, for example…

Folder Path : /home/username/VirtualBox/shared_folders/linux_mint
Folder Name : linux_mint

Click OK. Now start the client OS.

Install VBox Addidtions

Install VBox Additions by now switching to the VM you have just started.

In the window of this device, click on the Devices menu, Insert Guest Additions CD Image. That mounts the Additions CD.

Now open a terminal prompt. Switch directories to the CD. It’s auto-mounted at

/media/username/VBOXADDITIONS_4.3.18_96516

or similar, depending on revision / edition etc. so cd into it thus..

cd /media/username/VBOXADDITIONS_4.3.18_96516

Now run the command below..

sudo ./VBoxLinuxAdditions.run

It’s installed.

Now, switch back to the host workstation. Open a command line and type the following…

sudo mkdir -p /mnt/hostOS

sudo mount -t vboxsf linux_mint /mnt/hostOS/ -o rw,exec,uid=1000,gid=1000,dev

Finished.

This is quite sweet. You need to get some table data straight out to a CSV for manipulating in OpenOffice Calc, or similar products. But how?

Connect to your SQL server using a command line, ususally

mysql -u root -p

enter your password. Show your databases using

SHOW DATABASES;

Switch to your database using

USE my_database_name;

Now show your tables if you like..

SHOW TABLES;

Now get your CSV out from the table results of a query thus..

SELECT Name, Surname, Address, Postcode, Telephone
FROM Customers
INTO OUTFILE '/tmp/customers.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Et voila, data dumped to /tmp/customers.csv! Nice.

Perhaps my favourite XKCD cartoon from xkcd.com

OK, funny / cool but how do I get .CSV file to my PC?

Understood.  I use Linux Mint, but in any Debian, Ubuntu, Mint system, open your file manager (or Thunar in Mint-speak) and put

sftp://your_username@your.server.name.com/tmp/

Enter any password required for the target server and voilà, you’re on the /tmp directory on the target server!  From there, you can copy / paste the file as you wish,

All pictures taken with digital cameras add information to images they take. A lot of information. To see it, install imagemagick using this command on the command line..

sudo apt-get install imagemagick

Then, just use the identify command thus..

identify -verbose /home/me/pics/some_picture.jpg

and it should pump out a heap of data.

Personally, if ever uploading photos to the internet (never) or emailing them to friends (rarely), I make sure to strip all the data from them. For this, use mogrify, also from the imagemagick suite…

mogrify -strip /home/me/pics/some_picture.jpg

Now the exif data has been removed. Nice!