In the previous article I told you why an analysts needs SQL. Obviously it is too hard to make high-grade data analysis only with the help of SQL. We always want to keep some temporary data and previous results. Also to work with them. So, main appropriation of the SQL is data aggregation at the large scale of data.

Most of the time, work with the database can be done using: IDE, BI tools or custom integration solution. But, sometimes, when you make your own research or company has a low budget, you could use common SQL console.

So, how we can dump data from MySQL and upload it to Excel or other spreadsheets? We will use 'SELECT INTO OUTFILE' command.

The principle of this command is very easy. Suppose we have a simple aggregate query:

SELECT 

    c.name, 

    max(p.price), 

    count(p.id) 

FROM products p 

JOIN categories c 

    ON c.id = p.category_id 

GROUP BY p.category_id 

It is enough to add next text after the list of fields:

INTO OUTFILE '/tmp/dump.csv' 

FIELDS TERMINATED BY ',' 

        ESCAPED BY '\\' 

        OPTIONALLY ENCLOSED BY '"' 

LINES TERMINATED BY '\n' 

Several notes about using of this command. If the dump was made in OS Linux you probably can write file only into /tmp/ directory. Also, if you use OS Windows you must use / as a path name separator character or replace \ with \\. If you set comma as decimal symbol, it will be better to use semicolon as CSV separator or remove OPTIONALLY keyword from OPTIONALLY ENCLOSED BY part of the command. Otherwise number will be separated into 2 columns. If you have double quotes in your text field, you should escape them. So, we add construction ESCAPED BY '"'.

Result query will be look like this:

SELECT 

    c.name, 

    max(p.price), 

    count(p.id) 

INTO OUTFILE '/tmp/dump.csv' 

FIELDS TERMINATED BY ',' 

    ESCAPED BY '"' 

    OPTIONALLY ENCLOSED BY '"' 

LINES TERMINATED BY '\n' 

FROM products p 

JOIN categories c 

    ON c.id = p.category_id 

GROUP BY p.category_id 

Result file can be open in the Microsoft Excel or LibreOffice. In the first case file will be instantly open correctly. In the second case you will see an import setting window, where you could choose field separator and text delimiter.

If we choose semicolon as a field separator, Excel can a little bit disappoint you. As a default separator Excel considers only comma. So, what we will do?

1. Open a new workbook.
2. Select Data tab.
3. Select “From Text” option.

4. Open our CSV file.
5. Press “Next”, we are not interested in the first step.

6. Disable every checkbox at the second step (delimiters block) and choose semicolon.
7. Press “Finish”.

8. In a new window press “Ok”.

9. Now we are happy owner of the spreadsheet with our data.