SELECT FROM DB.Table(SQL Server) TO Excel
Всем привет, подскажите пожалуйста, как запросом можно перенести данные из сервера в excel.xlsx файл? Сейчас делаю это руками через import/export wizard.
Рассмотрим два случая:
- Когда нам нужно генерировать все данные для файла каждый раз заново
- Когда нам нужно один раз записать данные в файл и потом их периодически обновлять
Первый случай
Чистим файл перед записью и записываем новые данные
Это решение хоть и работает, но работает иногда криво — бывает, что новые данные инсертятся после очищенных строк. Поэтому я предпочитаю работать так:
Храню шаблон файла и каждый раз перед записью удаляю старый файл и создаю новый по шаблону.
Второй случай
Обновляем данные в файле, идентифицируя строки по (в данном случае) артикулу.
При этом в обоих случаях должны быть выполнены следующие условия:
- Файл должен быть закрыт.
- Колонки в файле должны называться именно так, как в запросе и идти в том же порядке.
P.S.
Microsoft.ACE.OLEDB — 64-битный провайдер. В случае, если нужен 32-битный, следует использовать Microsoft.Jet.OLEDB.
The main difference between Microsoft.Jet.OLEDB.4 & Microsoft.ACE.OLEDB.12 is that the Microsoft.Jet.OLEDB.4.0 provider is used with 32-bit SQL Server for Excel 2003 files & the Microsoft.ACE.OLEDB.12.0 provider is used with 64-bit SQL Server for any Excel files or 32-bit SQL Server for Excel 2007 files.
Тут можно скачать драйверы для провайдера.
Узнать версию своего сервера SELECT @@VERSION .
How to Export Data from SQL Server to Excel
If you want to easily let your non-technical users update and manage SQL Server data from Excel, click here to download the SQL Spreads Excel Add-In.
In a previous article, SQL and Excel: Why you need both , we showed that for many businesses the use of both SQL Server and Excel is an optimum approach to data management. For organizations that use SQL Server and Excel extensively, there is often a need to export data from SQL Server to Excel. The most common reasons to get the data into Excel are to:
- produce data visualizations such as charts and graphs for use in reports
- aggregate or analyze data in pivot charts
- collate data from multiple sources for further transformation
- make the data available to non-database users to view or process
In this article, we’re going to look at how to export data from SQL Server to Excel. The first method uses the SQL Server Import and Export Wizard, which can be used to export data to a number of destination types, not just Excel. The second method uses native functionality in Excel. The third method uses the SQL Spreads Add-In for Excel, which provides functionality beyond the export process. There are pros and cons of each method, which we’ll summarise at the end of the article.
Table of contents
- 1. Method (1): Exporting using SQL Server Import and Export Wizard
- 2. Method (2): Exporting using native functionality in Excel
- 3. Method (3): Exporting using SQL Spreads Add-In for Excel
- 4. Summary
Method (1): Exporting using SQL Server Import and Export Wizard
The SQL Server Import and Export Wizard can be launched from within SQL Server Management Studio or as a stand-alone application.
From within SQL Server Management Studio, click on the database from which you want to export data. We’re using the demo database in our example, so click on that and then right-click and select Tasks > Export Data.
The Welcome page is displayed, and you can go ahead and click ‘Next’.
On the next screen, select the data source. To export from SQL Server, select ‘SQL Server Native Client’.
Once you’ve selected ‘SQL Server Native Client’, the details about the data source will be populated by the wizard.
Click ‘Next’ to go to the next screen, where you going to choose the destination for the export, which for us is Excel.
When you select Excel as the choice of destination, you then need to provide the location of the Excel file. When you then click ‘Next’, you may see the following message:
The operation could not be completed.
ADDITIONAL INFORMATION:
The ‘Microsoft.ACE.OLEDB.16.0’ provider is not registered on the local machine. (System.Data)
The reason this message appears is that when you launch the SQL Server Import and Export Wizard from SSMS, a 32-bit version of the wizard is launched (because SSMS is 32-bit) and your operating system is 64-bit. There is more information on this here .
To get around this, you need to close SSMS and go to the Windows Start menu and search for the 64-bit version of the SQL Server Import and Export Wizard from the Start menu. Note that this version will only be installed on your computer if you have installed SQL Server.
Once the stand-alone version of the Wizard has started, you can go through the steps as discussed so far, which was to specify the source and destination of the data copy. We now need to define what we want to copy, and (optionally) configure the copy operation.
After clicking ‘Next’ on the ‘Choose a Destination’ screen, you specify whether you want to export one or more complete tables or views, or a subset of data via a SQL query. In our case, we are exporting from a single table, so we can leave the default option selected.
Next, we’re going to edit the column mappings by clicking the ‘Edit Mappings…’ button. The Column Mappings screen allows you to make changes to the name and data type of the columns that will be created in Excel by the copy operation. In our example, we’re going to change the data type for the ‘order_date’ column from VarChar to DateTime. We’ll leave the others as the default set by the wizard. Click ‘Next’. The next screen in the wizard summarizes that the source is a SQL table called sales and the destination will be to a sheet called ‘sales’ in the Excel workbook. You can change the name of the sheet as needed.
Once done, click ‘Ok’ to close the dialog and then ‘Next’.
If any of the mappings that we specified may not succeed in the export process the SQL Server Import and Export Wizard shows the Review Data Type Mapping page. This page will highlight the conversions that the wizard needs to perform to handle the mappings that you have specified. It also indicates what the wizard will do if a there is an error during the conversion. It can either ignore the error or cause the export to fail. In our example, several columns have been flagged: the ones where there is a conversion from nvarchar(max) to its Excel equivalent, Long Text, and also the conversion from VarChar to DateTime for the ‘order_date’ column. The default setting is for the wizard to fail if there is an error, so we’ll leave that option in place.
Note: This SQL Import and Export Wizard uses SSIS under the hood; in other words, each time you run the wizard, you are creating an SSIS package. This is why you are given the option of saving it for future use.Click ‘Next’. The wizard now offers you the chance to save the Wizard configuration as an SSIS package in addition to the default option of running the copy immediately.
We are just going to leave the default option ticked and then click the ‘Next’ button.
The wizard now displays a summary of the steps that we have configured.
Click ‘Finish’, and the wizard will now show the final screen, which is the result of the execution. In our case, the execution was successful, and we can see that 5484 records were copied to the ‘Query’ sheet in our Excel workbook.
We can check that the data has been exported from SQL Server to Excel by opening up the file that we specified and checking the ‘sales’ sheet.
Now that the data is in Excel, we can create some visualizations or aggregations, or whatever other processing we want to perform. For example, we could create a pivot chart to display the Total per item per region.
Method (2): Exporting using native functionality in Excel
Although the method itself is much the same, Microsoft has made improvements in the last few releases to the way you get data from other sources into Excel. The main change was the addition of the Power Query data import wizards and tools as a native component in Excel 2016. These are accessed from the Power Query Editor, although it’s not displayed as such on the Excel Ribbon – instead, it is branded as Get & Transform Data group on the Data tab of the Excel ribbon.
The steps described below are valid for Excel 2016 and beyond.
To get started with the export from SQL Server to Excel, click on Data > Get Data > From Database > From SQL Server Database.
You are next prompted to enter the database server to connect to and, optionally, specify a database. If you don’t specify a database, you can select from the available ones on the next screen.
Click ‘Ok’. You’ll then be prompted to provide authentication details. The default is Windows, so you can go ahead and click ‘Connect’.
The next screen allows you to navigate through the databases and tables on the database server that you specified. Click on the ‘demo’ database to expand it and then click on the ‘sales’ table. The data for the selected table is displayed in the right-hand window. We could now click the ‘Load’ button to get the data into Excel immediately. Alternatively, we can click on ‘Transform Data’ to perform transformations on the data (eg change data types, clean up some data, perform computations). For this example, we’re just going to load the data as it is, so go ahead and click ‘Load’.
The data is now exported from the table in SQL Server and loaded into a new sheet in Excel.
That is all we need to do to perform a simple export of a full table from SQL Server to Excel. Aside from the simple procedure we’ve described here, the Power Query data import wizards and tools provide ways to ‘shape’ the data from external sources. For example, you can remove a column, change a data type, merge tables to meet your specific needs.
Method (3): Exporting using SQL Spreads Add-In for Excel
If you don’t already have the SQL Spreads Add-In for Excel installed, you can download a copy here .
Once SQL Spreads is installed, you’ll see that it has been added as a new ribbon tab; go here and click the Design Mode button.
In the SQL Spreads Designer panel on the right side, click the Edit button to open the SQL Server connection dialog.
Enter the name of your SQL Server into the SQL Server name field. You also need to choose an authentication method: Windows-login (Windows Authentication) or SQL Server Authentication (using a name and password set up in SQL Server). Windows authentication is the more secure of the two options.
Click OK. SQL Spreads will try to connect to the database. If the connection is successful, your databases will show up in the SQL Spreads Designer panel.
We can now expand the demo database and select the sales table.
When you click on the table, the data is immediately exported from SQL Server and copied to a sheet called ‘sales’ in Excel.
That’s it! We’ve managed to export data from SQL Server to Excel with just a couple of clicks.
There are several more options available to us in the SQL Spreads Designer. For example, we can:
- filter the data that is exported from SQL Server
- set whether certain columns are displayed and/or read-only
- use a look-up value to display text instead of an id for a column
- sort data
- make updates in Excel and save changes to SQL Server
There is more information on these features in the SQL Spreads knowledgebase here .
Summary
Being able to export data from SQL Server to Excel is a common requirement in most businesses. We’ve looked at three methods for performing this day-to-day task. The first uses the SQL Server Import and Export Wizard, which is native functionality in SQL Server. The second method uses the native ‘Get & Transform’ tools in Excel. The third uses the SQL Spreads Add-In for Excel.
So which method should you use?
If you normally work with databases, and SSMS is your second home, you’ll probably feel that the SQL Server Import and Export Wizard is the best option for you. If you are an Excel user, then it makes sense to work from within Excel itself to export data from SQL Server. Both of the two Excel options that we’ve looked at are robust and easy to use, but SQL Spreads has a distinct advantage because it allows users to make updates to data directly in Excel and then save the data back to SQL Server. This is a huge advantage for those organizations that like the best of both worlds: the ease of use and familiarity of Excel, with the power and data integrity associated with SQL Server.
Try SQL Spreads to see how quick and easy it is to export data from SQL Server to Excel; download a free trial version today.
*This article was originally published on August 26, 2021 and was updated on July 4, 2022 to include some new screenshots.
Andy McDonald
Andy has worked 20+ years in the Engineering, Financial, and IT sectors with data analysis and presentation using tools such as SQL Server, Excel, Power Query and Power BI.
Writes for SQL Spreads about Excel and SQL Server and how to tie those two together.
Подключение к источнику данных средствами MS Excel
Бывают ситуации, когда на рабочей станции отсутствуют такие средства взаимодействия с БД как: MS SQL Server Management Studio, Aquafold Aqua Data Studio, DBeaver и т.п., а вероятность их установки в краткосрочной перспективе близка к нолю. В то же время, присутствует острая необходимость подключения к этой самой БД и работы с данными. Как оказалось, на помощь может прийти старый добрый MS Excel.
В моем случае требовалось подключиться к MS SQL Server, однако, MS Excel умеет устанавливать соединение не только с ним, но и с большинством современных БД: MySQL, PostgreeSQL, IBM DB2 и даже Oracle и Teradata, а также с файлами данных CSV, XML, JSON, XLS(X), MDB и другими.
Теперь немного о действиях, совершенных мной с целью подключения к базе:
В новой книге на ленте выбираем «(1) Данные» -> «(2) Получение внешних данных» -> «(3) Из других источников» -> «(4) С сервера SQL Server».
Далее, в окне Мастера подключения к данным, заполняем «(1) Имя сервера» -> «(2) Учетные сведения»[ -> «(3) Имя пользователя» и «Пароль»]. Таким образом, мы сообщаем MS Excel, с каким сервером мы хотим установить соединение и какой метод аутентификации хотим использовать. Я использовал «проверку подлинности Windows», но возможно также указать учетные данные отличные от установленных в Windows.
Выбираем целевую «(1) Базу данных» -> «(2)(3) Определенную таблицу» или «Несколько таблиц» или же базу в целом (тогда оба «чекбокса» оставляем пустыми).
После всех проделанных манипуляций, Мастер подключения предложит сохранить файл подключения. Потребуется задать «(1) Имя файла». Желательно также указать «(2) Описание» и «(3) Понятное имя файла», чтобы спустя время было понятно какой файл подключения к какой базе или таблице обращается.
Теперь выбрать созданное подключение можно будет следующим образом: «(1) Данные» -> «(2) Получение внешних данных» -> «(3) Существующие подключения».
Открыв только что созданное подключение, в случае если вы соединялись с базой в целом, MS Excel опять предложит выбрать одну или несколько конкретных таблиц:
Определив таблицы, MS Excel предложит выбрать «(1) Способ представления данных» и «(2) Куда следует поместить данные». Для простоты я выбрал табличное представление и размещение на уже имеющемся листе, чтобы не плодить новые. Далее следует нажать на «(3) Свойства».
В свойствах подключения, нужно перейти на вкладку «(1) Определение». Здесь можно выбрать «(2) Тип команды». Даже если требуется выгружать лишь одну таблицу без каких-либо связей, настоятельно рекомендую выбрать SQL команду, чтобы иметь возможность ограничить размер выгружаемой таблицы (например, с помощью TOP(n)). Так, если вы попытаетесь выгрузить целиком таблицу базы, это может привести в лучшем случае к замедлению работы MS Excel, а в худшем к падению программы, к тому же – это необоснованная нагрузка на сам сервер базы данных и на сеть. После того как «(3) Текст команды» будет введен и нажата кнопка «ОК», MS Excel предложит сохранить изменения запроса – отвечаем положительно.
3 Ways To Export an SQL Query to an Excel File using SQL Management server
Export SQL Data to Excel from Microsoft SQL Server
Let’s go over three ways to export an SQL Query to an Excel File Using MSSQL
Despite the pushback from Database aficionados, sometimes it makes sense to export data from SQL to an Excel file. It really depends on who is the audience of the information. Not everyone is great with SQL Analysis. Few people require access to the database.
And lots of times the boss just needs to see the information in Excel.
So, if Excel is the Output required this article is here to help you Export SQL Queries to Excel.
Here are three ways to Export a SQL query to Excel format. The last one can be kind of tricky. I’ll provide a condensed version followed by a detailed version with screenshots.
Three quick ways to Export SQL Data to Excel:
Choose the Summarized Version and then scroll to further down to use the SQL Export Version to Excel that works best for you.
Method Number 1 – Copy Grid results and Paste into Excel
Under Query, Make sure results to Grid are selected.
After Running your query, right-click the top right corner of the grid.
Copy With Headers.
Paste into an Excel File
Possible Issues:
I’ve seen formatting issues with this strategy. For Example, there are situations where the spreadsheet drops preceding zeroes after the copy-paste.
I’ve also noticed lengthy fields, (think of a really long free text field) end up running into the next line.
For the reasons above, I prefer this next method for a clean Excel file.
Method Number 2: Export the Query Results as a Task
In the object explorer window, find the database you want to export from.
Right Click the Database, Click Tasks, and Export Data
The Wizard walks you through the rest of the steps but I have included screenshots below.
Method Number 3 – Use Good Old fashioned TSQL to send the data to an excel file
For those who value speed above all use the following script format.
Possible Issues – Configuring this might not be your type of fun and getting this straight deserves its own article.
Step by Step instructions with screenshots
Method Number 1 – Copy Grid results and paste into Excel
After ensuring results to grid turned on, Execute your query, right-click the top left-hand corner of the results grid.
Choose Copy with Headers and then you are ready to paste in Excel with Ctrl + C
Method 2 – Export Via the Export Wizard
Right-click on the database you want to export from. Then Select tasks and “Export Data”.
The SQL Server Wizard will startup. Click Next through the prompts.
Select the appropriate Native client, your server name, and database and choose “Next”.
Next, Select Microsoft Excel and the file path where you want to import the data. The .xls file that you name will be created by this process.
Now you can choose to export a whole table or a query. For the purpose of this exercise, we are creating a query.
Paste the query into the SQL Statement field. Make sure every output field has a unique name.
Click Next on the “Select Source Tables and Views” screen.
I use the default settings on the “conversion issues and data type mapping screen”
Now you can choose to Run immediately or Save an SSIS Package for later reuse.
Double Check your settings and click finish.
Make sure there were no errors in the Export.
Now Go to the directory you choose earlier and make sure you have a sweet-looking Excel File at that location!
Method Number 3 – Use TSQL to send the data to an excel file
This method is the quickest once you get it set up but the configuration is the tricky part. Permissions can be a limiting factor.
Also with the script below, you have to make sure the file exists before you run the query for it to import properly.