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.

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,

This is a very simple and very effective procedure. Log on to your server and then log on to your database with the usual command..

mysql -u root -p

and enter your password.

Select your database using the command

USE your_database_name;

Then, use the following commands..

To copy the table and the data…

CREATE TABLE your_table_backup SELECT * FROM your_table;

To copy the table structure only, no data, use…

CREATE TABLE your_table_backup SELECT * FROM your_table LIMIT 0;

Nice.

Simple command that you sometimes need. Obviously this runs from the mysql client prompt, so open a command line and type

mysql -u root -p

and then enter the root password for your mySQL database.

Then, switch to your database…

USE yourdatabasename;

An now, all you need is..

SHOW columns FROM your_table;

OR

SELECT COLUMN_NAME' FROM 'INFORMATION_SCHEMA'.'COLUMNS'
WHERE 'TABLE_SCHEMA'='YOUR_DATABASE'
AND 'TABLE_NAME'='YOUR_TABLE';

Nice.

Very simple. Use the following commands..

Backup to an external file.

On the *nix command line, just run..

mysqldump -u root -p database_name > /path/to/database_dump_file.sql

Punch in the password for root (in this case) and the backup is output to the file database_dump_file.sql. It’s ASCII so it can be edited if needed.

Restore from an external file..

mysql -u root -p database_name < /path/to/database_dump_file.sql

Typed the password and that's it, finished.

Need the database names quickly?

Log in to the database as ususal with the command at the *nix command line.

mysql -u root -p

And then issue the following at the prompt..

mysql> show databases;

and you get this....

+----------------------+
| Database             |
+----------------------+
| information_schema   |
| largedump            |
| dspam                |
| mysql                |
| performance_schema   |
| testdata             |
| testdata_1           |
| dataset_4            |
| dataset_2            |
+----------------------+
9 rows in set (0.01 sec)