Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Экспорт в EXCEL  [new]
ученик19
Guest
Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) Mar 23 2007 16:28:52 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)

Подскажите плиз как вставить данные в таблицу EXCEL, я смотрел на форуме как получить из EXCEL понятно, все получается получаю их преобразую учитываю, а вот изменённые данные вставить не могу , и в BOLе примеров не нашел

пробую вот так не работает

INSERT INTO OPENROWSET('MSDASQL','Driver={Microsoft Excel Driver (*.xls)};DefaultDir=C:\результат.xls','SELECT * FROM [результат$])')
SELECT * FROM #Результат


ругается значит через OPENROWSET я могу только прочитать а вставить тогда как ? или что неправильно написал ?
Msg 7390, Level 16, State 2, Line 35
The requested operation could not be performed because OLE DB provider "MSDASQL" for linked server "(null)" does not support the required transaction interface.
16 июл 08, 13:37    [5941589]     Ответить | Цитировать Сообщить модератору
 Re: Экспорт в EXCEL  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74930
Ну так не поддерживает выбранные Вами провайдер необходимого интерфейса.

ЗЫ. У Excel превосходные возможности по импорту данных. Почему ими не хотите воспользоваться?
16 июл 08, 13:45    [5941675]     Ответить | Цитировать Сообщить модератору
 Re: Экспорт в EXCEL  [new]
ученик19
Guest
pkarklin
Ну так не поддерживает выбранные Вами провайдер необходимого интерфейса.

ЗЫ. У Excel превосходные возможности по импорту данных. Почему ими не хотите воспользоваться?


Скажите плз с помощью какого провайдера я могу вставить данные в EXCEL
16 июл 08, 14:25    [5942133]     Ответить | Цитировать Сообщить модератору
 Re: Экспорт в EXCEL  [new]
-=MIX=-
Member

Откуда: Днепропетровск
Сообщений: 783
а чем SSIS неподходит?
16 июл 08, 15:01    [5942510]     Ответить | Цитировать Сообщить модератору
 Re: Экспорт в EXCEL  [new]
ученик19
Guest
Да там такая задача ... людям просто надо сказать вот скрипт, нажмите кнопочку F5 и что бы они получили EXCELевский файл. я вот наверно научу в EXCEL их , брать с сикула данные. НО раз уж такая задача хотелось бы знать имеет ли оно решение , могу ли я с помощью какого либо провайдера вставлять данные в EXCEL....
16 июл 08, 15:24    [5942768]     Ответить | Цитировать Сообщить модератору
 Re: Экспорт в EXCEL  [new]
Glory
Member

Откуда:
Сообщений: 104751
ученик19
pkarklin
Ну так не поддерживает выбранные Вами провайдер необходимого интерфейса.

ЗЫ. У Excel превосходные возможности по импорту данных. Почему ими не хотите воспользоваться?


Скажите плз с помощью какого провайдера я могу вставить данные в EXCEL

Microsoft.Jet.OLEDB разумеется
16 июл 08, 15:53    [5943037]     Ответить | Цитировать Сообщить модератору
 Re: Экспорт в EXCEL  [new]
ученик19
Guest
Пробую как сказал Glory
INSERT INTO  OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;IMEX=1; Database=C:\результат.xls',[Лист1$])
SELECT * FROM #результат

Опять ругается
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Обновление невозможно. База данных или объект доступны только для чтения."The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider indicates that the user did not have the permission to perform the operation..
атрибут "только для чтения" на файле EXCEL не стоит
права на full control у него для SYSTEM, administrators, и моя доменная учетка
скрипт запускаю под админом
сикул запушен под Local System
что надо ещё сделать что бы заработало ?
16 июл 08, 16:39    [5943471]     Ответить | Цитировать Сообщить модератору
 Re: Экспорт в EXCEL  [new]
Glory
Member

Откуда:
Сообщений: 104751
insert into OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
  'Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...sheet1$ ...  


Сообщение было отредактировано: 16 июл 08, 16:50
16 июл 08, 16:48    [5943580]     Ответить | Цитировать Сообщить модератору
 Re: Экспорт в EXCEL  [new]
ученик19
Guest
Glory
insert into OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
  'Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...sheet1$ ...  


спасибо все получилось ,все работает, тока вот в БОЛе я не нашел описание этого примера
16 июл 08, 18:04    [5944238]     Ответить | Цитировать Сообщить модератору
 Как избавиться от апострофа EXCEL  [new]
Шамиль Фаридович
Member

Откуда: Саратов
Сообщений: 487
При варианте экспорта в Exel с помощью OpenDatasource возникает следующая проблема:
данные типа bigint экспортируются с апострофом впереди, то есть 123 преобразуется в '123. Тоже самое происходит и с исходными данными типа decimal (9,2), кроме того разделителем дробной части является ".", а в Exel ",".
Как убрать ненужный апостроф и возможно ли sql-командой задать тип, в котором данные будут записаны в Exel?
22 авг 08, 15:04    [6099238]     Ответить | Цитировать Сообщить модератору
 Re: Как избавиться от апострофа EXCEL  [new]
Glory
Member

Откуда:
Сообщений: 104751
Шамиль Фаридович
данные типа bigint экспортируются с апострофом впереди, то есть 123 преобразуется в '123. Тоже самое происходит и с исходными данными типа decimal (9,2), кроме того разделителем дробной части является ".", а в Exel ",".
Как убрать ненужный апостроф и возможно ли sql-командой задать тип, в котором данные будут записаны в Exel?

- а в EXCEL разве есть тип bigint ?
- апостроф в EXCEL является признаком символьных данных
- разделитель целой и дробной частей в EXCEL не фиксированный а берется из региональных установок учетной записи, которая открывает файл
22 авг 08, 15:14    [6099328]     Ответить | Цитировать Сообщить модератору
 Re: Экспорт в EXCEL  [new]
Шамиль Фаридович
Member

Откуда: Саратов
Сообщений: 487
Не знаю есть ли в Exel тип bigint, но если воспользоваться DTSWizard (MS SQL Server 2005), то все данные экспортируются корректно, даже разделители. Вопрос в том, как добиться того же результата sql командой? Пока она выглядит так:
insert into OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
  'Data Source="\\Comp_Name\Folder\Book1.xls";Extended properties="Excel 8.0;HDR=Yes"')...лист1$
(Col1, Col2, Col3, Col4)
select Col1, Col2, Col3, Col4 from SQL_Table
Col1, Col2, Col3 имеют тип bigint, col4 decimal (9,2)
22 авг 08, 15:45    [6099614]     Ответить | Цитировать Сообщить модератору
 Re: Экспорт в EXCEL  [new]
Glory
Member

Откуда:
Сообщений: 104751
Шамиль Фаридович
Не знаю есть ли в Exel тип bigint, но если воспользоваться DTSWizard (MS SQL Server 2005), то все данные экспортируются корректно, даже разделители. Вопрос в том, как добиться того же результата sql командой?

Преобразовать типы данных MSSQL к тем типам, которые есть в Excel
22 авг 08, 17:49    [6100542]     Ответить | Цитировать Сообщить модератору
 Re: Экспорт в EXCEL  [new]
Шамиль Фаридович
Member

Откуда: Саратов
Сообщений: 487
Glory
DTSWizard (SSIS) как раз это и делает, то есть преобразует все значения типа bigint в decimal(19,0). С decimal(9,2) и DateTime ни делает ничего(!). Однако в результате все экспортируется абсолютно корректно.
Проводим аналогичные действия с помощью OPENDATASOURCE:
insert into OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
  'Data Source="\\Comp_Name\Folder\Book1.xls";Extended properties="Excel 8.0;HDR=Yes"')...лист1$
([Столбец 1], [Дата], [Столбец 3])
select convert(decimal(19,0),BigInt_Col) as [Столбец 1], CustDateTime as Дата, 
       Decimal_9_2_Col as [Столбец 3] from SQL_Table
и в результате получаем столбец Дата в формате mon dd yyyy hh:miAM (or PM), хотя по умолчанию установлен совсем формат dd.mm.yyyy (и именно так осуществляет экспорт DTSWizard), а в самом sql-сервере даты храняться в виде yyyy-mm-dd hh:mi:ss.mmm (тип DateTime).
Возникает проблема с разделителем для столбца Decimal_9_2, а именно он остается таким же, как в sql-сервере, то есть '.', хотя по умолчанию установлена ',', вследствие чего число воспринимается как строка. Опять же DTSWizard разделитель заменяет.
Почему??? И как добиться такого же результата исправить?
P.S. Худо-бедно удалось поменять разделитель, изменив в команде Decimal_9_2_Col на replace (Decimal_9_2_Col,'.',','), хотя теперь в Exel Столбец 3 воспринимается как строковый, стоит ли говорить про DTSWizard...
26 авг 08, 16:05    [6110698]     Ответить | Цитировать Сообщить модератору
 Re: Экспорт в EXCEL  [new]
Glory
Member

Откуда:
Сообщений: 104751
Шамиль Фаридович
хотя по умолчанию установлен совсем формат dd.mm.yyyy (и именно так осуществляет экспорт DTSWizard), а в самом sql-сервере даты храняться в виде yyyy-mm-dd hh:mi:ss.mmm (тип DateTime).

Выставлен где ? Какое отношение этот формат имеет к mssql ?
В mssql тип datetime хранится вовсе не в виде yyyy-mm-dd hh:mi:ss.mmm
26 авг 08, 16:28    [6110886]     Ответить | Цитировать Сообщить модератору
 Re: Экспорт в EXCEL  [new]
Шамиль Фаридович
Member

Откуда: Саратов
Сообщений: 487
Glory
Выставлен где ?
В Exel.
Glory
Какое отношение этот формат имеет к mssql ?
Именно в этом формате производит экспорт DTSWizard.
Glory
В mssql тип datetime хранится вовсе не в виде yyyy-mm-dd hh:mi:ss.mmm
Действительно, он хранится в виде десятичного числа, но так DateTime отображается в MS Managment Studio.
Теперь прошу прощения. Поле CustDateTime имеет тип SmallDateTime, а DTSwizard преобразует его к DateTime. Однако подобное преобразование (convert(DateTime,CustDateTime,104) ничего принципиально не меняет, даты экспортируется в виде "июл 1 2008 12:00AM", то есть точно так же, как без "конверта".
З.Ы. Процедура sp_makewebtask при абсолютно-идентичном запросе выдает приемлимый результат без проблем с разделителями и датами, экспортируя их в виде dd.mm.yyyy hh:mm
26 авг 08, 17:01    [6111187]     Ответить | Цитировать Сообщить модератору
 Re: Экспорт в EXCEL  [new]
Glory
Member

Откуда:
Сообщений: 104751
Шамиль Фаридович
Glory
Выставлен где ?
В Exel.

Установки Excel никак не влияют на преобразование данных. Потому что отвечают за отображение

Шамиль Фаридович

Именно в этом формате производит экспорт DTSWizard.

DTSWizard - это клиентское приложение. Которое использует mssql как источник данных

Шамиль Фаридович

Теперь прошу прощения. Поле CustDateTime имеет тип SmallDateTime, а DTSwizard преобразует его к DateTime. Однако подобное преобразование (convert(DateTime,CustDateTime,104) ничего принципиально не меняет, даты экспортируется в виде "июл 1 2008 12:00AM", то есть точно так же, как без "конверта".

Сколько не преобразуй datetime к datetime ничего не изменится. Потому что нужно строковое представление даты

Сообщение было отредактировано: 26 авг 08, 17:07
26 авг 08, 17:06    [6111241]     Ответить | Цитировать Сообщить модератору
 Re: Экспорт в EXCEL  [new]
Шамиль Фаридович
Member

Откуда: Саратов
Сообщений: 487
Есть сдвиг!
С датами этот фокус прошел:
convert(varchar(25),CustDateTime,104)
выдает в формате "10.07.2008", что и требовалось.
Но что делать с полем Decimal_9_2, которое после
convert(varchar(20),Replace(Decimal_9_2,'.',','))
воспринимается как строковое, а по нему может потребоваться суммирование?
26 авг 08, 17:39    [6111513]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: Экспорт в EXCEL  [new]
Александр Бердышев
Member

Откуда: Санкт-Петербург
Сообщений: 401
Возникла проблема:

Выполняю:

insert into OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
  'Data Source="D:\Book1.xls";Extended properties="Excel 8.0;HDR=Yes"')...Лист1$
(ID, ID_ГТД)
select ID, ID_ГТД from Счет


Говорит:
Msg 207, Level 16, State 1, Line 4
Invalid column name 'ID'.
Msg 207, Level 16, State 1, Line 4
Invalid column name 'ID_ГТД'.

При том, что
select ID, ID_ГТД from Счет

выполняется нормально.

Не могу понять, в чём дело.
2 июн 12, 20:42    [12656458]     Ответить | Цитировать Сообщить модератору
 Re: Экспорт в EXCEL  [new]
Glory
Member

Откуда:
Сообщений: 104751
Александр Бердышев
Не могу понять, в чём дело.

Поля ID, ID_ГТД упоминаются не только в select
3 июн 12, 12:41    [12657440]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить