Data Export: From SQL Query to Spreadsheet

How do you export data from an SQL database to Excel or another spreadsheet? In this article, I’ll look at various methods and share a few tips for when things go wrong.

SQL is the most widely used method of extracting data from a database. However, it’s not the only gadget in an analyst’s toolbox. Once the information is extracted, you’ll often want to import it into a spreadsheet – maybe to do further analysis or just to make it look pretty so you can present it to management. SQL extracts data as plain text, so how can you get it into a spreadsheet?

As a data analyst, you’ll need both spreadsheet and SQL skills. Spreadsheets are widely used and taught in schools, so I’ll assume that you’re up to speed with using them.

If you’re not yet an SQL expert, you may want to read The Best Way to Learn SQL, which will give you some ideas on how to get started. I’d also recommend  LearnSQL.com’s SQL for Data Analysis track. Specially designed for data analysts, this track consists of four courses that take you from a beginner level to being proficient in data analysis with SQL. It takes about 48 hours to complete, and it will teach you to solve the kind of problems you’ll encounter in analysis work.

If you already know a little SQL but would like a bit of help, here’s a free SQL for Data Analysis cheat sheet.

SQL Reporting with Excel

In this article, I’ll mainly concentrate on how to export SQL data to spreadsheets, but I’d like to first take a few minutes to look at why you’d want to do that. Let’s look at a few scenarios:

You Work in Marketing

You’ve used SQL to extract demographics and learn what kind of products were bought by customers in different areas and age groups over the past two years. Now you have to do a presentation with charts and tables to justify the campaign you have in mind for next year.

You can use a spreadsheet to create these charts and tables and make them look professional. Spreadsheets have a range of tools useful to data analysts, including:

• Charts and graphs.
• Pivot tables.
• Power maps.
• Sorting and filtering op
• Functions that perform complex calculations.

You Work in a Bank

You suspect there has been an ongoing fraud. You’ve used SQL to pull out details of the suspicious transactions, but you need to experiment with filtering and sorting the transactions and comparing them to other events during the period. You’d probably use a spreadsheet to do this because it simplifies looking at data in many different ways.

You Have to Submit a Report to Your Manager

Every month, you have to create a report showing the total sales made to each customer. Your SQL query produces this:

However, you’d like to format it into an attractive report for your manager, like this:

There will always be a demand for your skills if you can work with Excel spreadsheet data,  import data from SQL, and produce attractive and meaningful reports.

Exporting SQL Data to Spreadsheets

Ok, let’s move on to the point of the article: how to get your data from SQL query into a spreadsheet. There are many database providers, each with a slightly different working environment, and many different types of spreadsheets. This makes it difficult to write a comprehensive SQL query export guide, but I’ll show you how to use three popular databases …

• Google BigQuery,
• Microsoft SQL Server, and
• MySQL

 … with three different spreadsheets:

• Excel,
• Libre Calc from the Libre Office suite, and
• Google Sheets.

I’m also assuming you’re on a Windows PC. Linux and Mac users will need to use the equivalents to right-clicking and Crtl+ keyboard shortcuts.

I’ll look at several methods and see how they can be used with the software I’ve mentioned. You’ll learn enough to export and import data anywhere, since most databases and spreadsheets are very similar.

I’ll be showing examples using BigQuery’s public data, Microsoft’s sample AdventureWorks database, and MySQL’s sample Employee database.

I’ll start with the simplest method.

Method 1: Copy and Paste

This is the quickest way to export an SQL query to a spreadsheet, and I use it 99% of the time. Obviously, you first need to run an SQL query to provide the data.

You shouldn’t use this method for very large datasets, as it has to store the clipboard data in memory. It’s possible to freeze up your computer if the dataset is too big. Later in this article, I’ll look at some of the problems you may encounter with this method, and how to solve them.

In the image below, I’ve written a query to extract all customers whose birthdays are in January. The customer relations department can use this to send them birthday emails. I’m using SQL Server Management Studio to run the query, and this is the result:

You can copy data in two ways. First, you highlight the data. Then you can either click Edit → Copy from the menu, or you can use the keyboard shortcut by pressing CTRL+C.

This copies the rows you’ve highlighted, plus the headings, as tab-separated data to the clipboard.

Another option, if you want to copy all the rows into your spreadsheet, is to click the blank square at the top, as indicated by the red arrow in the image below. All rows will be highlighted; you can then copy the data to the clipboard.

Right-clicking the same blank square gives a menu that gives you the choice of copying with or without headers.

Copying Data from MySQL

In the screenshot below, you can see my query to extract all employees who will be over 65 at the beginning of 2024; the company can use this information to consider certain employees for retirement. I’m using MySQL Workbench to run the query.

Again, there are a few ways you can copy to the clipboard: Edit → Copy from the menu or CTRL+C. This copies the data without headers and it’s tab-delimited.

If you click the blank cell at the top indicated in the image below, it will select all rows. If you then copy it to the clipboard, it will include the headers.

Copying Data from BigQuery

In the screenshot below, I’ve written a query to extract a list of banks registered with the FDIC. This is a database in BigQuery’s public data. I’m using BigQuery Studio to run the query.

Although you can theoretically highlight rows and use CTRL+C or your browser’s clipboard facility, this doesn’t work well in practice – the columns tend to go out of sync. The only good way to copy to the clipboard is to use the Save Results feature highlighted in the image below:

This gives you a menu:

If you choose Copy to Clipboard, it will copy the data as tab-delimited with headers. Note: You can only copy 1 MB of data this way.

Pasting Into Excel

A simple Paste operation – using either the clipboard icon on the Home menu or by typing CTRL+V – brings the data into Excel, beginning at the position of the cursor. You’ll usually have to do some formatting to make the data look nice: widening columns, adding meaningful headings, etc.

If the data doesn’t come across as you expected, there’s a section at the end of this article on fixing common issues with exporting SQL data to spreadsheets.

Pasting Into Libre Calc

In Libre Calc, you initiate the paste operation either by typing CTRL+V or choosing Edit → Paste from the Home menu. The program then lets you set some options to control how the data is imported. A pop-up menu appears:

If you simply click OK, it will use defaults to import the data. This usually works fine, but you may have to set some options if it doesn’t.

The most common change you’d make would be to set the data type of a column that’s importing incorrectly. You do this by clicking on the column in the data preview window, which then selects the column. You can then set the data type by clicking the Column Type box just above the preview section and selecting a data type:

Sometimes, for reasons best known to the byte gremlins hidden in all computers, this dialogue box doesn’t pop up and the data just pastes in using defaults. This is usually fine. But if you need to set any of the options, you can force the dialogue box to pop up by choosing Edit → Paste Special → Paste Special from the Home menu:

This will pop up another menu:

Choose Use text import dialog. This will then pop up the same dialog box we looked at earlier.

Pasting Into Google Sheets

In Google Sheets, simply position your cursor and either choose Edit → Paste or press CTRL+V. There are no options that allow you to control how the data is pasted; it simply uses its own defaults.

Method 2: Using CSV Files to Transfer Data

CSV (comma-separated values) files are a popular way of transferring data from one application to another.

You’d use them in preference to copy–paste when:

• The data is too large to conveniently fit on the clipboard. Typically, you wouldn’t want to use copy–paste for more than about ten thousand rows.
• Your spreadsheet is on a different machine than the one you’re using to extract the data
• You want to circulate the data to allow someone else to import it into their own spreadsheet.

Columns in your query result will be separated by commas in the CSV file.

Extracting a CSV File from SQL Server

In the Query result window, right-click the blank cell at the top left of the data. This will pop up the following menu.

Choose Save Results as. It then gives you a standard file save dialog box so you can give the CSV file a name and navigate to where you want to store it.

Extracting a CSV File from MySQL

Just above the query results window, there’s a facility to export results to an external file. This is highlighted in the image below.

If you click this, you’ll get the File Save dialog box, which allows you to name the file and navigate to where you want to store it.

Extracting a CSV File from BigQuery

In BigQuery Studio, there’s a Save Results button just above the results window. If you click this, you’ll get the following menu:

You can then choose either:

• CSV Local File to save to your computer. This will open the usual Save File dialog box.
• CSV (Google Drive). This will save to your Google Drive, using (in my opinion) a rather unfriendly file name. Luckily, it then pops up a link as per the image below. If you click the file, you’ll be able to do useful things with it – such as renaming it, sharing it, or opening it with Google Sheets.

Opening a CSV File in Excel

You can open a CSV file from the File → Open menu. When it pops up the File Open dialog box, browse to where you saved the file. In some versions of Excel, CSV files will automatically be listed. In others, you’ll have to set it to show All Files, as you’ll see in the image below:

You can then select the file and open it.

You’ll probably need to do some formatting on it to make it look nice. Always use Save As to save it as an Excel workbook, because CSV files can’t include any formatting.

Opening a CSV File in Libre Calc

In Libre Calc, choose File → Open and navigate to where you saved the file. Libre Calc automatically shows all files, so you should have no trouble finding it.

Once you’ve selected the file and opened it, Libre Calc will pop up the same text import dialog box that you’ve already seen when pasting query results.

Opening a CSV File in Google Sheets

Open a new blank sheet, then choose File → Import.

This will give you several choices as to where you want to import the file from:

• If you saved the file to your Google Drive, choose My Drive and select the file you want.
• If the file is on your computer, choose Upload. This will allow you to drag your file into the window. Or you can choose Browse to go to the File Open dialog box.

You will then see the following options:

Normally, you can simply click Import Data and use the default settings. But you may like to change the Replace Spreadsheet option. Clicking it will give you several useful choices.

If you untick Convert text to numbers, dates, and formulas, all data will be imported as text. This can be useful, since sometimes dates and text fields that contain numbers can come across incorrectly. For example, a bank may have a 16-digit numeric account number. Since Google Sheets has a limit of 15 significant digits on numeric fields, the account number can be corrupted by rounding.

Method 3: Export Facilities in RDBMS Software

Most relational database management system (RDBMS) software includes facilities for exporting query results. I’ll briefly look at a few of them.

SQL Server

SQL Server has two utilities that are useful for SQL-to-Excel exports. Both are fairly complicated, so I’ll just describe what they do and give links to the relevant Microsoft documentation.

• Bulk Copy Program (BCP). The bulk copy program is designed to copy data from SQL Server in various formats. You can use it to create CSV files. And since it’s possible to schedule it to run regularly, it’s a good way to automate SQL to Excel or any other spreadsheet. You can learn how to use BCP here.
• SQL Server Import and Export Wizard. This can export the results of a query in various formats, including Excel. You can learn how to use the wizard to export from the SQL database to Excel here. Since both Libre Calc and Google Sheets can open Excel files, you can also use this method for exporting to those programs.

MySQL

In the previous section, we saw how to use MySQL Workbench to output the data as a CSV file. The Export resultset to an external file option can also be used to output other file types.

As you can see, one of the choices you have is to export to an Excel spreadsheet. This exports the data as a spreadsheet XML file, which is easily and accurately imported into Excel. Libre Office will also successfully open files exported this way, but Google Sheets won’t.

BigQuery

The Save result button in BigQuery can be used to export the data directly to Google Sheets, as you can see from the options below. This is the easiest and most efficient way of importing data from BigQuery into Google Sheets. Since Google Sheets allows you to export to Excel and Libre Calc can open Excel spreadsheets, you can also use this method to bring your data into either of these packages.

Method 4: Using Other Data Analysis Tools to Create Spreadsheets

There are many data analysis tools on the market and almost all of them allow you to use SQL to extract data from a database. Several also have the option to save the results as a spreadsheet. Since most tools can schedule reports regularly, they can provide a good way to automatically produce spreadsheets when needed. Some of the tools that allow data to be extracted with SQL and saved as spreadsheets are:

• Tableau
• Crystal Reports
• Power BI
• Zoho Analytics
• Jasper Reports

Common Problems Encountered with Exporting SQL to Spreadsheets

Most of the time, your data will import correctly into spreadsheets, but occasionally you will encounter problems. Here are some common problems and their solutions.

All Data Appears in a Single Column

You paste your results into Excel. But instead of each field being in a separate column, they all appear in one column. This usually happens when Excel ‘remembers’ (from a previous paste operation) that it should use something other than tabs for delimiters. The data may look like this:

 To fix it go to the Data tab on the ribbon. Make sure Choose Text to Columns is selected:

From the pop-up box, choose Delimited, then click Next. In the next dialog box, tick Tabs under Delimiters, then Next. You’ll now have the opportunity to choose data types for each column.

If you need to change any of them, click the column and select its type from the radio buttons at the top. This is especially useful if you want dates to appear in a different format or numbers to be treated as text.

If this keeps happening to you, what can you do? The next time you want to paste data, click the arrow under the Paste button. It will give you the option to use the text import wizard, i.e. the dialog boxes we saw in the previous paragraph. This will allow you to choose Tab as the delimiter; Excel will ‘remember’ this choice for future paste operations.

Your Dates Are the Wrong Way Round

This can happen when pasting or importing from a CSV. Sometimes it’s just a formatting issue: you can select the column and use the Format command to make the dates look the way you want them to.

But sometimes it can be more serious. The American short date format, for example, is mm/dd/yyyy, whereas the British short date format is dd/mm/yyyy. If your default date format in the spreadsheet is different from the format in your database application, you could end up interpreting a date that should represent January 4th as April 1st because the days and months are switched.

The best way to prevent this is to do some formatting in SQL to make sure the dates are in the format expected by the spreadsheet.

Your Numbers Are in Scientific Notation

If you’re seeing numbers in your spreadsheet that look like 123456E+14, they’re in scientific notation. This could be because your column is too narrow; you can solve it by widening the column. However, the problem is likely to be that you have a number that is more than 15 digits long. All three spreadsheets we’ve looked at can’t handle numbers larger than that. They automatically round them and show them in scientific notation.

This is OK for science applications, but it’s not so great in accounting – and it’s definitely NOT OK if the number represents a 16-digit account number!

You can get the numbers to display correctly by treating them as text, not numbers. You won’t, however, be able to do arithmetic with them in that format.

If you used cut and paste to import the data, use the Paste Special → Text Import Wizard to change the data type of that column to text.

If the data came from a CSV file, the easiest way to get it to import correctly is to open the file in Notepad (or any other basic text editor, like Online Notepad), select and copy all the data, and use Paste Special → Text Import Wizard to specify the data types.

If your data is too large for a single copy/paste operation, you may have to do the copy/paste operation in smaller chunks.

Your ID Column Has Dropped Leading Zeros or Lost a Digit

You may have a column that contains a numeric identifier and the numbers may be very large. Leading zeros are important here, as they are part of the ID.

The solution is the same as for numbers that appear in scientific notation: Treat the column as text, not numeric data.

Build Your SQL to Spreadsheet Skills

As we’ve seen, SQL and spreadsheets can easily be used together to harness the power of both applications. SQL is your gateway to the world of data, and it’s well worth your time to increase your skills in this area.

If you haven’t learned SQL, our SQL Basics course is a great place to start. And if you want to really invest in your future with SQL, the All Forever package gives you access to all our courses – including the ones we haven’t written yet! This means you’ll always be able to stay on top of any new features that are added to SQL in the future.

If you can use SQL and spreadsheets – and make the two work together – your skills will always be in demand in today’s data-driven world. So take the first step today! Start learning, start experimenting, and take charge of your future!