Подключение к эксель через ado 1с
подключаюсь к Эксельке, выбираю запросом все строки, в переборе загружаю. Все вроде норм, но иногда встречаются числа типа 1.232323*Е22. В файлике загрузки все типы ячеек строковые. 1с не может определеить что это Число, при выполнении Число("1.22323*Е22"); аццки ругается. Можно как-то загружать с эксель числа (которые на самом деле текст) в норм виде без этих "Е" . В самом Эксель когда тыкаешь на ячейку с таким числом сверху вылазит полная его запись. Запрос щас выглядит как Select From [Все ячейки от А1 до AA99999], как то конкретно тип может задавать?
Управление записями в БД через интерфейс в Excel(ADODB + MS SQL Server)
Всем привет. Есть задача сделать пользовательский интерфейс для изменения данных в БД через.
ADODB.Connection средствами Excel
Всем здрасте! Подскажите как загрузить данные из .xls файла если количество строк и столбцов.
Работа с Adodb.recordset Excel. Выборка
Доброго всем дня. Вот мучаюсь уже некоторое время, никак не могу разобраться. Видимо не замечаю.
Обращение к данным в БД. Ошибка: Объект не является ни ADODB.RecordSet, ни ADODB.Record
при созданиие приложения в коде у меня возникла ошибка подскажите суть проблемы .
И читал построчно, но сейчас надо считать 15 000 строк. Все бы ничего, но после получасового ожидания пока все загрузится выскочила ошибка. кот надо отладить, пришлось искать другой способ , нашел вот такой
Ну на скрине я вижу Число и твои триллионы, так что всё нормально должно быть.
Давай тогда весь код и скрин ошибки
Действительно нету . просто грузить 15к строк, долго поэтому я открывал в либрОфисе, копипастил около 1к и вставлял в Эксель. Там то эти числа и появились, а так все норм. Еще вопрос остался с запросом, он щас читает все ячейки из таблицы с А[КолЯчеекШапки] по CZ9999999. Т.е. если в документе 10 строк и указать, что шапка равна 1 строке, запрос отработает, в обходе пробежит 9 строк, потом RecordSet.EOF() встанет в истину и цикл прервется, все отработает на ура. Но если указать в этом же документе, что шапка равна 20. (в доке 10 строк)
то RecordSet.EOF() будет ложью пока не дойдет до посл строки, что ужасно, при чем все строки будут пустые.
Общепринятые методы работы с файлами Excel устраивают до тех пор, пока не встречаешься
с файлами на 30 000 строк и несколькими десятками колонок. Время выполнения и обработки
такого файла растет до ужаса.Можно конечно написать :"а теперь можете пойти покурить" как
делают некоторые разработчики, но это не наши методы. Мы боремся за оптимизацию и
производительность. Чем больше элегантных методов мы используем ,тем выше
наш уровень мастерства.А теперь по сути: появилась необходимость загружать специфические
выгрузки, с сайта клиента в excel формате, в базу системы 1с:Предприятие
.Апробирования стандартного метода последовательного считывания ,показало что 30 000 строк
загружается за 12 с небольшим минут. После использования метода с ADO
скорость загрузки уменьшилась до 20 секунд.
После небольшого ознакомления с документацией по ADO ,чтением MSDN возникла идея
обратиться к таблице excel как к обыкновенной таблице какой-нибудь БД через запрос.
Единственное условиедля такого рода задачи таблица должна быть одна на листе.
Привожу код используемый в функции, в моей обработке пользовательсам выбирал
список колонок для загрузки, поэтому в запросе использовались системные имена,
вы можете скорректировать код под себя.Код хорошопрокомментирован,так что
суть должна быть понятна.
Опишу вкратце текущий алгоритм:
1.Создаю COM объект .
2.Получаю список листов файла.(будет использоваться в дальнейшем запросе).
3.Формирую строку необходимых полей(по выбранным пользователем колонкам)
5.Выполняю запрос к файлу.
6.Собираю таблицу для дальнейшей обработки.
// Создание объекта для установки связи с источником данных
Connection = Новый COM Объект ( "ADODB.Connection" );
// сформируем строку подключения
// Если необходимо в дальнейшем оперировать цифровыми значениями колонок , то ставим признак HDR = NO
// если необходимо производить загрузку с учетом псевдонимов в 1 й строке , то ставим HDR = YES
СтрокаПодключения = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source font-family: Courier; color: red;" lang="en-US">+ ИмяФайлаЗагрузки ;
СтрокаПодключения = СтрокаПодключения + "; Extended Properties font-family: Courier; color: red;" lang="en-US">+ """Excel 8.0" + ";HDR=NO;IMEX=1"";" ;
// подключение к источнику данных
Попытка
Connection . Open ( СтрокаПодключения );
Сообщить ( ОписаниеОшибки ());
Возврат ;
// создание объекта выполнения команды
Command = Новый COM Объект ( "ADODB.Command" );
// создание объекта подключения к файлу
axCatalog = Новый COM Объект ( "ADOX.Catalog" );
axCatalog . ActiveConnection = Connection ;
// получим листы документа ,
// для обработки колонок листа можно обратится к коллекции Лист .Columns, которую тоже можно обойти посредством цикла Для каждого
счЛиста = 1 ;
Для каждого Лист ИЗ axCatalog . Tables Цикл
Если счЛиста = ЭлементыФормы . НомерЛиста . Значение Тогда
// получим имя т аблицы , затем будем использовать в запросе
// ИмяТаблицы = Сред ( Строка ( Лист .Name),2, СтрДлина ( Строка ( >Лист .Name))-1);
ИмяТаблицы = Лист . Name ;
счЛиста = счЛиста + 1 ;
// отсортируем таблицу колонок , которые необходимо загрузить
// чтобы в дальнейшем при обработке RecordSet могли не беспокоится о последовательности обработки
ТаблицаИспользуемыхКолонок . Сортировать ( " НомерКолонки Возр " );
// соберем часть запроса , а именно имена используемых колонок
// по умолчанию имя колонки выглядит следющим образом F1,F2 и т . д
Для каждого СтрокаТаблицы ИЗ ТаблицаИспользуемыхКолонок Цикл
ТекстЗапросаКолонки = ТекстЗапросаКолонки + ?( ТекстЗапросаКолонки = "" , "F" + СтрокаТаблицы . НомерКолонки , ",F" + СтрокаТаблицы . НомерКолонки );
// добавим колонку в таблицу загрузки
ТаблицаЗагрузки . Колонки . Добавить ( СтрокаТаблицы . ИмяКолонки ); // соотв . колонки идут в порядке возрастания , т . к мы отсортировали таблицу ранее
// соберем строку используемых колонок для записи в регистр ЖурналОбновленийСерийныхНомеров , в конце загрузки
СтрокаКолонки = СтрокаКолонки + ?( СтрокаКолонки = "" , СтрокаТаблицы . ИмяКолонки , "," + СтрокаТаблицы . ИмяКолонки );
// Создание объекта набора записей
RecordSet = Новый COM Объект ( "ADODB.RecordSet" );
// Указание активного соединения
Command . ActiveConnection = Connection ;
// получим количество строк в документе
Command . CommandText = "SELECT COUNT(*)FROM [" + ИмяТаблицы + "]" ;
// определение типа команды
Command . CommandType = 1 ;
// Выполнение и получение набора данных
RecordSet = Command .Execute();
// первая запись это количество
КоличествоСтрок = RecordSet . Fields ( 0 ). Value ;
// опредлеление текста команды
Command . CommandText = "SELECT " + ТекстЗапросаКолонки + " FROM [" + ИмяТаблицы + "]" ;
// определение типа команды
Command . CommandType = 1 ;
// Выполнение и получение набора данных
Попытка
RecordSet = Command .Execute();
Исключение
Сообщить ( ОписаниеОшибки ());
КонецПопытки ;
RecordSet . MoveNext (); // т . к первая строка это заголовки таблиц , не будем ее учитывать
Пока RecordSet . EOF () = 0 Цикл
СтрокаТабличнойЧасти = ТаблицаЗагрузки . Добавить ();
сч = 0 ;
Для каждого Колонка ИЗ ТаблицаЗагрузки . Колонки Цикл
СтрокаТабличнойЧасти [ Колонка . Имя ] = СокрЛП ( Строка ( RecordSet . Fields ( сч ). Value ));
сч = сч + 1 ;
RecordSet . Close ();
Connection . Close ();
Итак на выходе мы получили ТаблицуЗагрузки ,которую можно обработать по своему желанию.Стоит
отметить что все значения получаются строкового типа, поэтому необходимо потом их правильно обработать.
Но овчинка стоит выделки, прирост скорости действительно существенный. Что самое интересное в процессе работы
с файлом excel как с COM объектом можно получать все необходимые свойства и обращаться к нужным листами.
Запрос можно построитьпотяжелее с фильтрами и объединениями, так что поле для действий огромно.
Скажу честно данный механизмсоветую использовать при работе исключительно с большими файлами,
Недавно столкнулся с необходимостью делать из 1С прямые запросы к базе данных Oracle, используя ADODB.Connection. В этой статье хочу поделиться опытом и рассказать про некоторые особенности с которыми пришлось столкнуться.
Провайдер
Первое, что необходимо для использования ADODB.Connection это наличие в системе провайдера (драйвера), который позволяет подключаться к источнику данных. Для баз данных Oracle существует два драйвера.
Один от компании Microsoft — MSDAORA и второй от Oracle — OraOLEDB. Возможно есть и другие, но мне попадались описания только этих двух. Сразу возникает вопрос как можно проверить установлен ли хоть один из этих драйверов на компьютере. Чтобы понять это можно воспользоваться файлом c расширением *.udl. Если открыть этот файл и перейти на первую закладку — Поставщик данных, то мы увидим перечень всех провайдеров. Как видно на картинке у меня установлен провайдер от Microsoft Microsoft OLE DB Provider for Oracle.
Но я при подключении использовала драйвер от Oracle. Просто из тех соображений, что родной драйвер должен работать лучше. При необходимости можно скачать его с сайта Oracle. Выбираем закладку Downloads, в разделе Enterprise Management пункт See All…
Здесь в разделе Drivers нас интересует пакет Oracle Data Access Components for Windows
Дальше разберетесь сами. Нужно только выбрать правильную разрядность. Не надо только качать пакет где в названии есть Xcopy. В нем используется какая-то полуавтоматическая установка через .bat файлы.
В процессе установки можно выбрать только то, что нас интересует Oracle Provider For OLE DB
Также в процессе установки желательно указать параметры для подключения к серверу с оракловыми базами данных. Или это можно сделать позднее, но для этого придется самостоятельно отредактировать файл tnsnames.ora
После установки откроем наш файл *.udl и теперь мы видим, что на закладке Поставщик данных появилась еще одна строчка с провайдером Oracle Provider for OLE DB
На какой рабочей станции устанавливать драйвер
Теперь немного порассуждаем на каком компьютере должен быть установлен провайдер. Это может отразиться и на выборе разрядности драйвера, о чем говорилось в предыдущем пункте. Раз уж вы читаете эту статью вряд ли вы используете файловый вариант базы 1С, т.к. базы данных Oracle используются как правило очень крупными организациями. Скорее речь идет о клиент-серверном варианте базы 1С. Таким образом мы имеем два варианта.
Либо устанавливать драйвер на каждой клиентской машине, либо один раз на сервере, где физически установлен сервер 1С:Предприятия. Понятно, что ставить драйвер на каждую клиентскую машину это не вариант, тем более, что у разных пользователей могут быть установлены операционные системы с разной разрядностью. Да и само количество пользовательских станций может быть большим. Поэтому устанавливать драйвер будем на сервере 1С:Предприятия. Напомню, что код на сервере 1С:Предприятия выполняется от имени служебного пользователя USR1CV8. Соответственно у этого пользователя должны быть права на использование провайдера.
Процедура подключения
Так как драйвер мы установили на сервере, то и установка соединения должна выполняться на сервере, для этого процедуру подключения к оракловой базе разместим в серверном общем модуле ОбщегоНазначенияСервер.
Теперь хочу сделать несколько пояснений.
- Если кто-то заметил, то псевдонимы полей в тексте запроса взяты в кавычки. Это особенность языка PL/SQL. Ни в языке запросов 1С ни в Transact-SQL эти кавычки не нужны.Поэтому, если будете использовать псевдонимы, помните про этот момент, иначе запрос вывалится с ошибкой.
- В строке запроса
Теперь проделаем обратную операцию. Вставим записи из таблицы значений в таблицу базы данных Oracle.
И опять несколько замечаний
-
В следующей строке
Без заключения текста запроса внутрь блока «begin……end;», запрос выполнялся с ошибкой, причем это только при вставке в таблицу. При выборке (как в предыдущем примере) этот блок не нужен. Обратите внимание, что после end стоит точка с запятой. Также точка с запятой стоит в конце предпоследней строки запроса
3 комментария на «“Запросы к базе Oracle через ADODB.Connection”»
Спасибо, очень полезно!
И ещё, вроде бы, в INSERT INTO запросе вылетит ошибка, т.к. отсутствует открывающая скобка для VALUES (очепятка) %)
Очень серьезный момент упустили:
Пока Выборка.EOF = 0 Цикл
……………
Выгружаем из 1С в EXCEL с помощью ADO. Часть I
Наверное уже многие знают что с помощью технологии ADO можно быстро читать большие объемы информации из книг excel. Но ведь с помощью данной технологии можно так же легко создавать и заполнять файлы excel прямо из 1С. Неверите ? Смотрите …
Итак, постановка задачи: Создать с помощью механизма ADO из 1С файл excel и выгрузить в него из справочника номенклатура код и наименование.
Все, готово !
Плюсы данного способа формирования файла excel:
- Большая скорость работы.
- Не требуется запуск самого процесса excel.exe, как это происходит в случае использования OLE-сервера excel.application, а следовательно на него не тратятся ресурсы. Кроме того нет необходимости отслеживать и перехватывать ошибки, связанные с процессом excel.exe, те кто использовал OLE-cоединение — тот поймет о чем я говорю, мало радости когда в случае программной или пользовательской ошибки процесс excel.exe остается висеть в памяти.
- Не требуется самого приложения EXCEL, достаточно что бы на компьютере был установлен OLEDB провайдер Microsoft.Jet.OLEDB.4.0 (он входит в состав практически всех ОС семейства Windows) Это особенно актуально, когда данный код выполняется на сервере 1С:Предприятия, например в качестве регламентного задания.
Минусы тоже имеются:
- Нельзя красиво оформить ячейки — шрифт, рамки и т.д.
Возможен «комбинированный» способ: с помощью ADO формируется файл, а затем с помощью OLE «наводится марафет». Читайте продолжение «Выгружаем из 1С в EXCEL с помощью ADO. Часть II»
Если публикация помогла Вам справится с проблемой, решить задачу или расширить свои знания - поддержите автора.
Комментарии:
Пишет ошибку: Ошибка при вызове метода контекста (Execute): Произошла исключительная ситуация (Microsoft JET Database Engine): Ошибка синтаксиса (пропущен оператор) в выражении запроса ‘с выключателем’. Command.Execute();
Опубликованный выше пример полностью рабочий, ищите ошибку у себя в коде. Позже выложу готовую обработку, доступную для скачивания.
при открытии в версии 8.2 (файл/открыть) пишет «Ошибка загрузки документа. Внешняя обработка не может быть прочитана текущей версией программы»…
Откройте сначала в Конфигураторе 8.2 обработку, переконвертируйте (совместимость с 8.1 пропадет!), и если без управляемых форм (в Толстом клиенте) — открывайте переконвертированную обработку уже в Предприятии 8.2.
Код рабочий. Одна проблема — если в наименовании присутствует апостроф — система падает. Как его можно сохранить? (Вариант — вырезать возможен, но некрасиво). Спасибо
Насколько я помню в SQL апостроф экранируется обратным слэшом, таким образом можно попробовать заменить все ‘ в наименовании на \’
Сам не пробовал, но на мой взгляд решение данной проблемы следует искать в этом направлении.
Добрый день, большое спасибо за статью, очень помогла.
Есть ньюанс — в файле Экселя, в начале каждого значения присутствует апостроф ‘ — можно ли от него избавиться ?
1) при повторном сохранении данного примера выводится ошибка.
2) или можно ли узнать более много команд как «Command.Execute()» .
или спрошу по другому, если надо будет перезаписать данный файл то как поступить.
В силу сиквельного синтаксиса заполнения эксельного листа возникают проблемы, если в названии номенклатуры были всевозможные кавычки.
Да, есть такая проблема.
Экранируйте подобные символы.
: Ошибка при установке значения атрибута контекста (ActiveConnection)
Catalog.ActiveConnection = СтрокаПодключения;
по причине:
Произошла исключительная ситуация (Microsoft JET Database Engine): Файл ‘D:\1С\Для аудиторов\’ не может быть открыт ядром базы данных Microsoft Jet. Файл уже открыт другим пользователем для монопольного доступа или требуется разрешение на просмотр данных.
вот такую штуку выдает, хоть при попытке создать новый, хоть при попытке записи в старый файл :-(
Возможно ли в данном случае добавить все необходимые записи одним запросом?
Т.е. в одном INSERT’е добавить одновременно несколько строк.
Спасибо.
Добрый день. Вопрос немного на другую тему. Загружаем из Excel в 1С. В экселевском файле в ячейке вместо значения указана ссылка на ячейку на другом листе книги. Как считать конечное значение?
Доброго времени суток. Кто знает, скажите пожалуйста, как через excel происходит подключение к 1С, то есть как посредством запросов, VBA и прочего, минуя выгрузку CSV таблиц из 1C сразу обращаться к дужным данным в 1C? В excel есть вкладка данные (рисунок 1), в ней есть графа "Из базы данных SQL Server" (рисунок 2), которая открывает окно ввода сервера и инструкций (рисунок 3). Как я понимаю нужно туда подставить какие-то значения, но какие? У меня есть пользовательский логин и пароль для 1C, на вкладке 1С "О программе" есть тоже кое-какая информация (рисунок 4), и я так понимаю её тоже нужно куда-то вставить. Кто знает, помогите пожалуйста подключиться к 1С, два дня перерывал весь интернет, ничего не нашёл (или не понял). Очень нужно.
Как подключиться через Ado к листам Excel?
Знаю строку для установления связи с файлом. Но как открыть recordset на лист (например, мне нужно.
Из Excel в DBGrid. Подключиться к рабочей книге MS Excel, как к базе данных.
Доброго времени суток! У меня возникли проблемы при выводе таблицы с Excel на DBGrid, а именно.
Excel через ODBC, Как выбрать через Select столбец из файла Excel по букве?
Собственно проблема такая. Надо с помощью команды Select получить данные из столбца файла Excel по.
Как подключиться к таблице SQL из Excel
Здравствуйте. Такая проблема, не могу подобрать скрипт для экспорта таблицы в SQL. Помогите.
Решение
Нужно посомтреть в агенте сервера 1с как называется сервер sql и база данных на sql и подставить эти значения в эксель видимо
Решение
Это типа связующее звео между 1с и sql сервером. Обычно ярлык запуска находится по пути C:\Program Files\1cv8\common\1CV8 Servers (x86-64).msc (зависит от разрядности ОС). там список баз которые подключены к sql серверу и реквизиты подключения
Решение
_ЕГОР_, Ну скорее всего да, потому что в компании все работаю на ноутбуках, сервер с 1С находится где-то в другом месте. К нему как-то удалённым доступом подключение происходит насколько я знаю. Честно-говоря не очень разбираюсь в этом
Решение
ГарриНаФеррари, не так. базы могут быть серверными, и файлавыми. а даже находят на сервере, она все ровно может быть файловая. Здесь подробней
Решение
О программе - Режим
Добавлено через 21 секунду
ой блин на первом скрине ведь видно что серверная
Добавлено через 32 секунды
значит агент сервера по любому есть
Решение
_ЕГОР_, Прошу прощения, вылетело из головы, 1С у нас установлена на виртуальной машине. Когда я зашёл в диск С через виртуальную машину, то там нашёл вот эти папки, но всё-равно не понятно, что есть что
Решение
_ЕГОР_, насколько я понял, там такого нет (но может я и не прав). Но на панели быстрого доступа есть "диспетчер серверов". Посмотри пожалуйста скрины, там есть то, что нужно?
Вот так это выглядит в экселе:
Provider=SQLOLEDB.1;Password=бла-бла-бла;Persist Security Info=True;User Catalog=ИмяБД;Data Source=123.456.789.65\ИмяИнстансаСервера,Порт;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation машины;Use Encryption for Data=False;Tag with column collation when possible=False
По большому счету, Вы должны зайти на виртуальную машину и посмотреть имя инстанса и имя машины. А вот что Вы будете делать дальше - непонятно. Вы знаете имена БД, таблиц Вашей 1С?
Добавлено через 3 минуты
Увидел Ваш ответ и понял, что для начала Вы поставьте у себя SQL Server Management Studio (это штатный менеджер сервера). Как только Вы сможете подключиться им к SQL СЕРВЕРУ на виртуалке - сразу увидите, что на нем есть и что Вам нужно. Запросы отладите, прежде чем в этого монстра под названием Эксель их пихать.
vpivo, скажи пожалуйста, а где посмотреть имя инстанса и имя машины (примерно где). Я открыл снова диспетчер серверов (он в панели быстрого доступа), там кажется ничего нужного (скрин скинул), открыл сведения о системе (это уже внутри виртуальной машины), там что-то написано, но насколько это полезно неизвестно (скрин скинул). И ещё, когда открываю виртуальную машину, она представляется в виде окна, в верху которого написано "terminal2.domain.local", может это и есть имя машины?
Добавлено через 1 минуту
По поводу SQL Server Management Studio, подскажи пожалуйста, где его взять можно?
Как в ADO подключиться к файлу EXCEL?
Нужно взять данные из EXEL и положить в базу. Не получается в ADO подключиться к файлу EXEL.
Как подключиться к листу Excel, получив с него длинные текстовые значения?
Добрый день всем. Есть лист Эксель, к которому подключаюсь напрямую запросом вида: .
Как подключиться через wi-fi к интернету
Всем привет! Я хотел бы спросить,вот куплю я вай-фай установлю его на комп,а как через фай-фай.
Как подключиться к БД через ConnectoPool?
Vsem privet. Narod kto znaet kak podkljuchit'sja k DB cherez ConnectoPool. Budu.
Как средствами vba подключиться к sap и выполнять транзакции с данными из файла excel
Как средствами vba подключиться к sap, если можно напишите пример. Нужно выполнять транзакции с.
Как подключиться к базе 1С через инет?
Имеется компьютер с 1С и находящейся на нем базе. Интернет раздает свитч, т.е. у компьютера серый.
Читайте также: