SELECT FROM DB.Table(SQL Server) TO Excel

#sql #sql_server #excel #sql_server_2008

Всем привет, подскажите пожалуйста, как запросом можно перенести данные из сервера
в excel.xlsx файл?
  Сейчас делаю это руками через import/export wizard.

источник данных: SQL Server Native client 10.0
назначение: Microsoft Excel

    

Ответы

Ответ 1

Рассмотрим два случая:


Когда нам нужно генерировать все данные для файла каждый раз заново
Когда нам нужно один раз записать данные в файл и потом их периодически обновлять


Первый случай

Чистим файл перед записью и записываем новые данные   

UPDATE t
  SET
      t.Артикул = '',
      t.Наименование = '',
      t.Цена = '',
      t.Дата = ''
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=YES;Database=filepath\sales.xlsx;',
'SELECT * FROM [Лист1$]') AS t;

INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.10.0', 'Excel 12.0;HDR=YES;Database=filepath\sales.xlsx;',
'SELECT * FROM [Лист1$]')
       SELECT s.code [Артикул],
              s.name [Наименование],
              s.price [Цена],
              s.date [Дата]
       FROM _table AS s;  


Это решение хоть и работает, но работает иногда криво - бывает, что новые данные
инсертятся после очищенных строк. Поэтому я предпочитаю работать так:   

EXEC master..xp_cmdshell
     'del filepath\sales.xlsx';
GO

EXEC master..xp_cmdshell
     'copy filepath\template.xlsx filepath\sales.xlsx';
GO

INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.10.0', 'Excel 12.0;HDR=YES;Database=filepath\sales.xlsx;',
'SELECT * FROM [Лист1$]')
       SELECT s.code [Артикул],
              s.name [Наименование],
              s.price [Цена],
              s.date [Дата]
       FROM _table AS s;


Храню шаблон файла и каждый раз перед записью удаляю старый файл и создаю новый по
шаблону.

Второй случай

Обновляем данные в файле, идентифицируя строки по (в данном случае) артикулу. 

UPDATE t
  SET
      t.Артикул = s.[Артикул],
      t.Наименование = s.[Наименование],
      t.Цена = s.[Цена],
      t.Дата = s.[Дата]
FROM OPENROWSET('Microsoft.ACE.OLEDB.10.0', 'Excel 12.0;HDR=YES;Database=filepath\sales.xlsx;',
'SELECT * FROM [Лист1$]') AS t
     JOIN
(
    SELECT code AS Артикул,
           name AS Наименование,
           price AS Цена,
           date AS Дата
    FROM _table
) AS s ON s.Артикул = t.Артикул;  




При этом в обоих случаях должны быть выполнены следующие условия:   


Файл должен быть закрыт.
Колонки в файле должны называться именно так, как в запросе и идти в том же порядке.


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.


Ответ 2

В Excel можно сделать подключаемый источник данных и настроить периодическую синхронизацию,
в т.ч. и с MS SQL Server.

Настройка зависит от вашей версии Office. На примере русской версии Excel 2013:


Создаем пустой документ Excel, переходим на вкладку "Данные",
нажимаем кнопку "Из других источников", выбираем пункт "С сервера SQL
Server". 
Указываем имя сервера, учетные данные для входа, нажимаем
"Далее".
Выбираем нужную базу данных, снимаем галку "Подключение к
определенной таблице", нажимаем "Готово".
При предложении заменить файл подключения нажимаем "Да". В появившемся окне "Выбор
таблицы" выбираем любую (использовать ее мы не будем), нажимаем "Ок".
В появившемся окне "Импорт данных" при необходимости указываем способ    представления
данных и куда нужно поместить данные - по умолчанию оставляем как есть, нажимаем кнопку
"Свойства".
В появившемся окне "Свойства подключения" переходим на вкладку "Определение", выбираем
тип команды "SQL", в поле "Текст команды" вставляем SQL-запрос для получения нужных
данных, нажимаем кнопку "ОК", на предложение изменить подключение нажимаем "Да", в
окне "Импорт данных" нажимаем "ОК".
Ждем окончания процесса загрузки - в зависимости от "тяжести" запроса и количества
данных время загрузки может сильно отличаться.
Если запрос необходимо изменить - на вкладке "Конструктор" из всплывающего меню рядом
с кнопкой "Обновить" выбираем пункт "Свойства подключения", на вкладке "Определение"
меняем запрос, применяем изменения - данные загрузятся повторно.