Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Экспорт из MS SQL 2008 в Excel через OpenDataSource. Проблема с форматами.  [new]
Drac013
Member

Откуда:
Сообщений: 45
Добрый день!

На сервере не установлен Excel, ибо он там не нужен в принципе. Но на его стороне выполняется задача сбора данных из таблицы, загрузка их в Excel и отправка.

Выгружаю данные в Excel следующим образом(имена полей, таблиц, файлов условные):

INSERT INTO OpenDataSource( 'Microsoft.ACE.OLEDB.12.0','Data Source=vFileName;Extended properties=""Excel 8.0""')...[Sheet_1$]
	SELECT 
               Field_1,
               Field_2,
                ...
               Field_N
	FROM #TTable


Все проходит ровно. Но вот числовая колонка выгружается в строковом формате. Файл Excel создан заранее и просто копируется. В нем указаны форматы колонок и заголовки таблицы.

Из решений нашел два варианта:
добавить фиктивную строку с даными для форматирования. Но проблема в том, что ее нельзя удалить силами SQL, только очистить данные в ячейках. Не подходит.

Добавить в Extended parameters "HDR=NO", а вместо заголовка написать число, чтобы колонка выгрузилась нужного формата. Все ок. А после этого хочу заменить данные в этой ячейке на имя через UPDATE, например:

UPDATE OpenDataSource('Microsoft.ACE.OLEDB.12.0','Data Source=vFileName; Extended properties=""Excel 8.0;HDR=NO""')...[Sheet_1$]
SET
F14 = 'ColumnHeader'
WHERE F14 = 1


Однако получаю ошибку "OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Bookmark is invalid."."

Какие вообще могут быть варианты решения данной проблемы? Заранее спасибо.
30 янв 14, 14:56    [15494959]     Ответить | Цитировать Сообщить модератору
 Re: Экспорт из MS SQL 2008 в Excel через OpenDataSource. Проблема с форматами.  [new]
Glory
Member

Откуда:
Сообщений: 104751
Drac013
В нем указаны форматы колонок и заголовки таблицы

Формат _отображения_ никак не задает тип данных
Определение типа данных происходит на основе значений в нескольких первых записях

http://microsoft-ssis.blogspot.com/2011/06/mixed-data-types-in-excel-column.html
30 янв 14, 15:01    [15494991]     Ответить | Цитировать Сообщить модератору
 Re: Экспорт из MS SQL 2008 в Excel через OpenDataSource. Проблема с форматами.  [new]
Drac013
Member

Откуда:
Сообщений: 45
Glory,

это понятно. Данных изначально нет, поэтому числовые он выгружает все равно как текст. Как это обойти не нашел, только такие суррогатные решения как выше описанные.
30 янв 14, 15:06    [15495026]     Ответить | Цитировать Сообщить модератору
 Re: Экспорт из MS SQL 2008 в Excel через OpenDataSource. Проблема с форматами.  [new]
Glory
Member

Откуда:
Сообщений: 104751
Drac013
Данных изначально нет, поэтому числовые он выгружает все равно как текст

Если данных нет, то как они могут быть числовыми или нечисловыми ?
30 янв 14, 15:08    [15495042]     Ответить | Цитировать Сообщить модератору
 Re: Экспорт из MS SQL 2008 в Excel через OpenDataSource. Проблема с форматами.  [new]
Drac013
Member

Откуда:
Сообщений: 45
Glory,

данных нет в файле Excel. Как я понял, провайдер определяет именно по эксельным данным формат. Данные в скульной выборке точно числовые. Но ему наплевать.
30 янв 14, 15:10    [15495057]     Ответить | Цитировать Сообщить модератору
 Re: Экспорт из MS SQL 2008 в Excel через OpenDataSource. Проблема с форматами.  [new]
Drac013
Member

Откуда:
Сообщений: 45
В общем, необходимо решение одной из двух проблем:

или заставить провайдер грузить данный в скульном формате.

или сделать UPDATE одной ячейки, чтобы заменить вспомогательные данные в шапке на реальный заголовок.
30 янв 14, 15:25    [15495193]     Ответить | Цитировать Сообщить модератору
 Re: Экспорт из MS SQL 2008 в Excel через OpenDataSource. Проблема с форматами.  [new]
Glory
Member

Откуда:
Сообщений: 104751
Выгрузите все в текстовый файл с расширением CSV.
Excel его открывает на раз.
30 янв 14, 15:28    [15495228]     Ответить | Цитировать Сообщить модератору
 Re: Экспорт из MS SQL 2008 в Excel через OpenDataSource. Проблема с форматами.  [new]
Drac013
Member

Откуда:
Сообщений: 45
Glory,

потребителю нужен именно Excel. Так что это неполноценное решение, например, если в шаблон добавят макросы для конечного пользователя. Хочется решить задачу максимально корректно.
30 янв 14, 15:32    [15495264]     Ответить | Цитировать Сообщить модератору
 Re: Экспорт из MS SQL 2008 в Excel через OpenDataSource. Проблема с форматами.  [new]
Glory
Member

Откуда:
Сообщений: 104751
Drac013
Так что это неполноценное решение,

linked server что ли полноценное решение ?
Используйте тогда SSIS.
30 янв 14, 15:34    [15495283]     Ответить | Цитировать Сообщить модератору
 Re: Экспорт из MS SQL 2008 в Excel через OpenDataSource. Проблема с форматами.  [new]
Drac013
Member

Откуда:
Сообщений: 45
Glory,

нет. Но работа со скулем идет через ADODB из другого приложения -_-
30 янв 14, 15:45    [15495390]     Ответить | Цитировать Сообщить модератору
 Re: Экспорт из MS SQL 2008 в Excel через OpenDataSource. Проблема с форматами.  [new]
Glory
Member

Откуда:
Сообщений: 104751
Drac013
Но работа со скулем идет через ADODB из другого приложения -_-

А нафига тогда OpenDataSource ?
Что мешает сразу формировать нужный файл в приложении ? на ADODB легче работать с Excel
30 янв 14, 17:52    [15496263]     Ответить | Цитировать Сообщить модератору
 Re: Экспорт из MS SQL 2008 в Excel через OpenDataSource. Проблема с форматами.  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 35376
Блог
эксель -> данные -> из других источников -> с сервера SQL Server
30 янв 14, 17:56    [15496286]     Ответить | Цитировать Сообщить модератору
 Re: Экспорт из MS SQL 2008 в Excel через OpenDataSource. Проблема с форматами.  [new]
DaniilSeryi
Member

Откуда:
Сообщений: 1965
Drac013,

Числовая колонка не float случайно?
Мне в своё время перед выгрузкой в Excel приходилось писать что-то вроде
replace(convert(nvarchar(100),[ИмяСтолбца]), '.',',') as 'НовоеИмя'

Ну и для дат:
convert(nvarchar(10),[ИмяСтолбца2],104) as 'НовоеИмя2'
30 янв 14, 17:58    [15496301]     Ответить | Цитировать Сообщить модератору
 Re: Экспорт из MS SQL 2008 в Excel через OpenDataSource. Проблема с форматами.  [new]
Drac013
Member

Откуда:
Сообщений: 45
Glory, Критик,

Drac013
На сервере не установлен Excel, ибо он там не нужен в принципе. Но на его стороне выполняется задача сбора данных из таблицы, загрузка их в Excel и отправка.


DaniilSeryi,
у меня данные целочисленные. И в таком виде мне в Excel и нужны.
31 янв 14, 09:51    [15498428]     Ответить | Цитировать Сообщить модератору
 Re: Экспорт из MS SQL 2008 в Excel через OpenDataSource. Проблема с форматами.  [new]
Glory
Member

Откуда:
Сообщений: 104751
Drac013
На сервере не установлен Excel,

"работа со скулем идет через ADODB из другого приложения" - у вас приложение работает на сервере что ли ?
31 янв 14, 10:08    [15498531]     Ответить | Цитировать Сообщить модератору
 Re: Экспорт из MS SQL 2008 в Excel через OpenDataSource. Проблема с форматами.  [new]
Drac013
Member

Откуда:
Сообщений: 45
Glory,

угу. 1С 8.3. Клиент-серверное приложение. Задача выполняется соответственно на серверной части.
31 янв 14, 10:10    [15498541]     Ответить | Цитировать Сообщить модератору
 Re: Экспорт из MS SQL 2008 в Excel через OpenDataSource. Проблема с форматами.  [new]
Glory
Member

Откуда:
Сообщений: 104751
Drac013
угу. 1С 8.3. Клиент-серверное приложение. Задача выполняется соответственно на серверной части.

Уточню - у вас на одной машине установлены 1С и MSSQL ?
Т.е. пользователь заходит на сервер с MSSQL и там запускает exe файл ?
31 янв 14, 10:13    [15498556]     Ответить | Цитировать Сообщить модератору
 Re: Экспорт из MS SQL 2008 в Excel через OpenDataSource. Проблема с форматами.  [new]
user89
Member

Откуда:
Сообщений: 2083
Drac013,

мне когда-то помогло шаманство с IMEX (может быть 0,1,2)
Extended Properties="Excel 8.0;IMEX=1"

http://support.microsoft.com/kb/194124/en-us
31 янв 14, 11:49    [15499330]     Ответить | Цитировать Сообщить модератору
 Re: Экспорт из MS SQL 2008 в Excel через OpenDataSource. Проблема с форматами.  [new]
Drac013
Member

Откуда:
Сообщений: 45
Glory
Drac013
угу. 1С 8.3. Клиент-серверное приложение. Задача выполняется соответственно на серверной части.

Уточню - у вас на одной машине установлены 1С и MSSQL ?

Да, сервер 1С и Сервер MS SQL на одном серваке.

Glory
Drac013
угу. 1С 8.3. Клиент-серверное приложение. Задача выполняется соответственно на серверной части.

Т.е. пользователь заходит на сервер с MSSQL и там запускает exe файл ?

Нет, пользователь запускает 1С у себя на локальном компе (толстый, тонкий либо ВЕБ-клиент). У 1С уже давненько реализована классическая 3-х звенка.
Задача будет выполняться регламентным заданием, которое, что логично, работает только на сервере.

user89,
спасибо. Попробую указывать 0 или 2.
31 янв 14, 13:45    [15500303]     Ответить | Цитировать Сообщить модератору
 Re: Экспорт из MS SQL 2008 в Excel через OpenDataSource. Проблема с форматами.  [new]
Drac013
Member

Откуда:
Сообщений: 45
user89,

странно. При IMEX = 2, ругается, что объект открыть только на чтение, как при IMEX = 1. А не должен... Но указание IMEX = 0 проблему тоже не решает.
31 янв 14, 13:56    [15500389]     Ответить | Цитировать Сообщить модератору
 Re: Экспорт из MS SQL 2008 в Excel через OpenDataSource. Проблема с форматами.  [new]
user89
Member

Откуда:
Сообщений: 2083
Drac013,

а Excel-файл нигде не открыт? Ничем не захвачен?
31 янв 14, 14:42    [15500747]     Ответить | Цитировать Сообщить модератору
 Re: Экспорт из MS SQL 2008 в Excel через OpenDataSource. Проблема с форматами.  [new]
Glory
Member

Откуда:
Сообщений: 104751
Drac013
Нет, пользователь запускает 1С у себя на локальном компе (толстый, тонкий либо ВЕБ-клиент). У 1С уже давненько реализована классическая 3-х звенка.
Задача будет выполняться регламентным заданием, которое, что логично, работает только на сервере.

Поставьте ReportingServices. И насочинайте там отчетов. И сделайте их регулярную рассылку в нужным вам форматах
31 янв 14, 15:00    [15500898]     Ответить | Цитировать Сообщить модератору
 Re: Экспорт из MS SQL 2008 в Excel через OpenDataSource. Проблема с форматами.  [new]
Drac013
Member

Откуда:
Сообщений: 45
user89, нет, все нормально с файлами.

Glory, не подойдет. Поверьте, я вычленил конкретные проблемы из объемной задачи. Поменять условия и инструменты нельзя. Относительно этого предложения: после отправки происходит еще запись данных в 1С по результатам ее отправки.

Хм, а как будет себя вести система, если сделать не INSERT , а UPDATE данных при заполненной второй строке?.. Надо проверить.
31 янв 14, 17:04    [15501753]     Ответить | Цитировать Сообщить модератору
 Re: Экспорт из MS SQL 2008 в Excel через OpenDataSource. Проблема с форматами.  [new]
Glory
Member

Откуда:
Сообщений: 104751
Drac013
Glory, не подойдет. Поверьте, я вычленил конкретные проблемы из объемной задачи. Поменять условия и инструменты нельзя

Смешно. Хочу построить современное здание. Но можно использовать только кирпичи и лом.
Ну ладно, выбор ваш
31 янв 14, 17:09    [15501812]     Ответить | Цитировать Сообщить модератору
 Re: Экспорт из MS SQL 2008 в Excel через OpenDataSource. Проблема с форматами.  [new]
Glory
Member

Откуда:
Сообщений: 104751
http://msdn.microsoft.com/en-us/library/aa338205(v=office.12).aspx
http://blogs.msdn.com/b/brian_jones/archive/2005/06/27/433152.aspx
31 янв 14, 17:17    [15501889]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить