Как я могу запускать SQL-запросы в именованном диапазоне в листе excel? Excel в sql запрос

Как сделать SQL запрос в Excel

SQL – популярный язык программирования, который применяется при работе с базами данных (БД). Хотя для операций с базами данных в пакете Microsoft Office имеется отдельное приложение — Access, но программа Excel тоже может работать с БД, делая SQL запросы. Давайте узнаем, как различными способами можно сформировать подобный запрос.

Читайте также: Как создать базу данных в Экселе

Создание SQL запроса в Excel

Язык запросов SQL отличается от аналогов тем, что с ним работают практически все современные системы управления БД. Поэтому вовсе не удивительно, что такой продвинутый табличный процессор, как Эксель, обладающий многими дополнительными функциями, тоже умеет работать с этим языком. Пользователи, владеющие языком SQL, используя Excel, могут упорядочить множество различных разрозненных табличных данных.

Способ 1: использование надстройки

Но для начала давайте рассмотрим вариант, когда из Экселя можно создать SQL запрос не с помощью стандартного инструментария, а воспользовавшись сторонней надстройкой. Одной из лучших надстроек, выполняющих эту задачу, является комплекс инструментов XLTools, который кроме указанной возможности, предоставляет массу других функций. Правда, нужно заметить, что бесплатный период пользования инструментом составляет всего 14 дней, а потом придется покупать лицензию.

Скачать надстройку XLTools

После того, как вы скачали файл надстройки xltools.exe, следует приступить к его установке. Для запуска инсталлятора нужно произвести двойной щелчок левой кнопки мыши по установочному файлу. После этого запустится окно, в котором нужно будет подтвердить согласие с лицензионным соглашением на использование продукции компании Microsoft — NET Framework 4. Для этого всего лишь нужно кликнуть по кнопке «Принимаю» внизу окошка.

После этого установщик производит загрузку обязательных файлов и начинает процесс их установки.

Далее откроется окно, в котором вы должны подтвердить свое согласие на установку этой надстройки. Для этого нужно щелкнуть по кнопке «Установить».

Затем начинается процедура установки непосредственно самой надстройки.

После её завершения откроется окно, в котором будет сообщаться, что инсталляция успешно выполнена. В указанном окне достаточно нажать на кнопку «Закрыть».

Надстройка установлена и теперь можно запускать файл Excel, в котором нужно организовать SQL запрос. Вместе с листом Эксель открывается окно для ввода кода лицензии XLTools. Если у вас имеется код, то нужно ввести его в соответствующее поле и нажать на кнопку «OK». Если вы желаете использовать бесплатную версию на 14 дней, то следует просто нажать на кнопку «Пробная лицензия».

При выборе пробной лицензии открывается ещё одно небольшое окошко, где нужно указать своё имя и фамилию (можно псевдоним) и электронную почту. После этого жмите на кнопку «Начать пробный период».

Далее мы возвращаемся к окну лицензии. Как видим, введенные вами значения уже отображаются. Теперь нужно просто нажать на кнопку «OK».

После того, как вы проделаете вышеуказанные манипуляции, в вашем экземпляре Эксель появится новая вкладка – «XLTools». Но не спешим переходить в неё. Прежде, чем создавать запрос, нужно преобразовать табличный массив, с которым мы будем работать, в так называемую, «умную» таблицу и присвоить ей имя.Для этого выделяем указанный массив или любой его элемент. Находясь во вкладке «Главная» щелкаем по значку «Форматировать как таблицу». Он размещен на ленте в блоке инструментов «Стили». После этого открывается список выбора различных стилей. Выбираем тот стиль, который вы считаете нужным. На функциональность таблицы указанный выбор никак не повлияет, так что основывайте свой выбор исключительно на основе предпочтений визуального отображения.

Вслед за этим запускается небольшое окошко. В нем указываются координаты таблицы. Как правило, программа сама «подхватывает» полный адрес массива, даже если вы выделили только одну ячейку в нем. Но на всякий случай не мешает проверить ту информацию, которая находится в поле «Укажите расположение данных таблицы». Также нужно обратить внимание, чтобы около пункта «Таблица с заголовками», стояла галочка, если заголовки в вашем массиве действительно присутствуют. Затем жмите на кнопку «OK».

После этого весь указанный диапазон будет отформатирован, как таблица, что повлияет как на его свойства (например, растягивание), так и на визуальное отображение. Указанной таблице будет присвоено имя. Чтобы его узнать и по желанию изменить, клацаем по любому элементу массива. На ленте появляется дополнительная группа вкладок – «Работа с таблицами». Перемещаемся во вкладку «Конструктор», размещенную в ней. На ленте в блоке инструментов «Свойства» в поле «Имя таблицы» будет указано наименование массива, которое ему присвоила программа автоматически.

При желании это наименование пользователь может изменить на более информативное, просто вписав в поле с клавиатуры желаемый вариант и нажав на клавишу Enter.

После этого таблица готова и можно переходить непосредственно к организации запроса. Перемещаемся во вкладку «XLTools».

После перехода на ленте в блоке инструментов «SQL запросы» щелкаем по значку «Выполнить SQL».

Запускается окно выполнения SQL запроса. В левой его области следует указать лист документа и таблицу на древе данных, к которой будет формироваться запрос.
В правой области окна, которая занимает его большую часть, располагается сам редактор SQL запросов. В нем нужно писать программный код. Наименования столбцов выбранной таблицы там уже будут отображаться автоматически. Выбор столбцов для обработки производится с помощью команды SELECT. Нужно оставить в перечне только те колонки, которые вы желаете, чтобы указанная команда обрабатывала.

Далее пишется текст команды, которую вы хотите применить к выбранным объектам. Команды составляются при помощи специальных операторов. Вот основные операторы SQL:
- ORDER BY – сортировка значений;
- JOIN – объединение таблиц;
- GROUP BY – группировка значений;
- SUM – суммирование значений;
- DISTINCT – удаление дубликатов.
Кроме того, в построении запроса можно использовать операторы MAX, MIN, AVG, COUNT, LEFT и др.

В нижней части окна следует указать, куда именно будет выводиться результат обработки. Это может быть новый лист книги (по умолчанию) или определенный диапазон на текущем листе. В последнем случае нужно переставить переключатель в соответствующую позицию и указать координаты этого диапазона.

После того, как запрос составлен и соответствующие настройки произведены, жмем на кнопку «Выполнить» в нижней части окна. После этого введенная операция будет произведена.

Урок: «Умные» таблицы в Экселе

Способ 2: использование встроенных инструментов Excel

Существует также способ создать SQL запрос к выбранному источнику данных с помощью встроенных инструментов Эксель.

Запускаем программу Excel. После этого перемещаемся во вкладку «Данные».

В блоке инструментов «Получение внешних данных», который расположен на ленте, жмем на значок «Из других источников». Открывается список дальнейших вариантов действий. Выбираем в нем пункт «Из мастера подключения данных».

Запускается Мастер подключения данных. В перечне типов источников данных выбираем «ODBC DSN». После этого щелкаем по кнопке «Далее».

Открывается окно Мастера подключения данных, в котором нужно выбрать тип источника. Выбираем наименование «MS Access Database». Затем щелкаем по кнопке «Далее».

Открывается небольшое окошко навигации, в котором следует перейти в директорию расположения базы данных в формате mdb или accdb и выбрать нужный файл БД. Навигация между логическими дисками при этом производится в специальном поле «Диски». Между каталогами производится переход в центральной области окна под названием «Каталоги». В левой области окна отображаются файлы, расположенные в текущем каталоге, если они имеют расширение mdb или accdb. Именно в этой области нужно выбрать наименование файла, после чего кликнуть на кнопку «OK».

Вслед за этим запускается окно выбора таблицы в указанной базе данных. В центральной области следует выбрать наименование нужной таблицы (если их несколько), а потом нажать на кнопку «Далее».

После этого открывается окно сохранения файла подключения данных. Тут указаны основные сведения о подключении, которое мы настроили. В данном окне достаточно нажать на кнопку «Готово».

На листе Excel запускается окошко импорта данных. В нем можно указать, в каком именно виде вы хотите, чтобы данные были представлены:
- Таблица;
- Отчёт сводной таблицы;
- Сводная диаграмма.
Выбираем нужный вариант. Чуть ниже требуется указать, куда именно следует поместить данные: на новый лист или на текущем листе. В последнем случае предоставляется также возможность выбора координат размещения. По умолчанию данные размещаются на текущем листе. Левый верхний угол импортируемого объекта размещается в ячейке A1.

После того, как все настройки импорта указаны, жмем на кнопку «OK».

Как видим, таблица из базы данных перемещена на лист. Затем перемещаемся во вкладку «Данные» и щелкаем по кнопке «Подключения», которая размещена на ленте в блоке инструментов с одноименным названием.

После этого запускается окно подключения к книге. В нем мы видим наименование ранее подключенной нами базы данных. Если подключенных БД несколько, то выбираем нужную и выделяем её. После этого щелкаем по кнопке «Свойства…» в правой части окна.

Запускается окно свойств подключения. Перемещаемся в нем во вкладку «Определение». В поле «Текст команды», находящееся внизу текущего окна, записываем SQL команду в соответствии с синтаксисом данного языка, о котором мы вкратце говорили при рассмотрении Способа 1. Затем жмем на кнопку «OK».

После этого производится автоматический возврат к окну подключения к книге. Нам остается только кликнуть по кнопке «Обновить» в нем. Происходит обращение к базе данных с запросом, после чего БД возвращает результаты его обработки назад на лист Excel, в ранее перенесенную нами таблицу.

Способ 3: подключение к серверу SQL Server

Кроме того, посредством инструментов Excel существует возможность соединения с сервером SQL Server и посыла к нему запросов. Построение запроса не отличается от предыдущего варианта, но прежде всего, нужно установить само подключение. Посмотрим, как это сделать.

Запускаем программу Excel и переходим во вкладку «Данные». После этого щелкаем по кнопке «Из других источников», которая размещается на ленте в блоке инструментов «Получение внешних данных». На этот раз из раскрывшегося списка выбираем вариант «С сервера SQL Server».

Происходит открытие окна подключения к серверу баз данных. В поле «Имя сервера» указываем наименование того сервера, к которому выполняем подключение. В группе параметров «Учетные сведения» нужно определиться, как именно будет происходить подключение: с использованием проверки подлинности Windows или путем введения имени пользователя и пароля. Выставляем переключатель согласно принятому решению. Если вы выбрали второй вариант, то кроме того в соответствующие поля придется ввести имя пользователя и пароль. После того, как все настройки проведены, жмем на кнопку «Далее». После выполнения этого действия происходит подключение к указанному серверу. Дальнейшие действия по организации запроса к базе данных аналогичны тем, которые мы описывали в предыдущем способе.

Как видим, в Экселе SQL запрос можно организовать, как встроенными инструментами программы, так и при помощи сторонних надстроек. Каждый пользователь может выбрать тот вариант, который удобнее для него и является более подходящим для решения конкретно поставленной задачи. Хотя, возможности надстройки XLTools, в целом, все-таки несколько более продвинутые, чем у встроенных инструментов Excel. Главный же недостаток XLTools заключается в том, что срок бесплатного пользования надстройкой ограничен всего двумя календарными неделями.

Мы рады, что смогли помочь Вам в решении проблемы. Задайте свой вопрос в комментариях, подробно расписав суть проблемы. Наши специалисты постараются ответить максимально быстро.

Помогла ли вам эта статья?

Да Нет

lumpics.ru

SQL запросы к таблицам Excel

Порой таблицы Excel постепенно разрастаются настолько, что с ними становится неудобно работать. Поиск дубликатов, группировка, сложная сортировка, объединение нескольких таблиц в одну, т.д. — превращаются в действительно трудоёмкие задачи. Теоретически эти задачи можно легко решить с помощью языка запросов SQL… если бы только можно было составлять запросы напрямую к данным Excel.

Надстройка XLTools «SQL запросы» расширит Excel возможностями языка структурированных запросов:

Создание запросов SQL в интерфейсе Excel и напрямую к Excel таблицам
Автогенерация запросов SELECT и JOIN
Доступны JOIN, ORDER BY, DISTINCT, GROUP BY, SUM и другие операторы SQLite
Создание запросов в интуитивном редакторе с подстветкой синтаксиса
Обращение к любым таблицам Excel из дерева данных

Добавить «SQL запросы» в Excel 2016, 2013, 2010, 2007

Подходит для: Microsoft Excel 2016 – 2007, desktop Office 365 (32-бит и 64-бит).

Скачать надстройку XLTools

Как работать с надстройкой:

Как превратить данные Excel в реляционную базу данных и подготовить их к работе с SQL запросами

По умолчанию Excel воспринимает данные как простые диапазоны. Но SQL применим только к реляционным базам данных. Поэтому, прежде чем создать запрос, преобразуйте диапазоны Excel в таблицу (именованный диапазон с применением стиля таблицы):

Выделите диапазон данных > На вкладке «Главная» нажмите «Форматировать как таблицу» > Примените стиль таблицы.
Выберите эту таблицу > Откройте вкладку «Конструктор» > Напечатайте имя таблицы.Напр., «КодТовара».
Повторите эти шаги для каждого диапазона, который планируете использовать в запросах.«КодТовара», «ЦенаРозн», «ОбъемПродаж», т.д.
Готово, теперь эти таблицы будут служить реляционной базой данных и готовы к SQL запросам.

Как создать и выполнить запрос SQL SELECT к таблицам Excel

Надстройка «SQL запросы» позволяет выполнять запросы к Excel таблицам на разных листах и в разных книгах. Для этого убедитесь, что эти книги открыты, а нужные данные отформатированы как именованные таблицы.

Нажмите кнопку «Выполнить SQL» на вкладке XLTools > Откроется окно редактора.
В левой части окна находится дерево данных со всеми доступными таблицами Excel.Нажатием на узлы открываются/сворачиваются поля таблицы (столбцы).
Выберите целые талицы или конкретные поля.По мере выбора полей, в правой части редактора автоматически генерируется запрос SELECT.Обратите внимание: редактор запросов SQL автоматически подсвечивает систаксис.
Укажите, куда необходимо поместить результат запроса: на новый или существующий лист.
Нажмите кнопку «Выполнить» > Готово!

Операторы Left Join, Order By, Group By, Distinct и другие SQLite команды в Excel

XLTools использует стандарт SQLite. Пользователи, владеющие языком SQLite, могут создавать самые разнообразные запросы:

LEFT JOIN – объединить две и более таблиц по общему ключевому столбцу
ORDER BY – сортировка данных в выдаче запроса
DISTINCT – удаление дубликатов из результата запроса
GROUP BY – группировка данных в выдаче запроса
SUM, COUNT, MIN, MAX, AVG и другие операторы

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

Как объединить две и более Excel таблиц с помощью надстройки «SQL запросы»

Вы можете объединить несколько таблиц Excel в одну, если у них есть общее ключевое поле. Предположим, вам нужно объединить несколько таблиц по общему столбцу «КодТовара»:

Нажмите «Выполнить SQL» на вкладке XLTools > Выберите поля, которые нужно включить в объединённую таблицу.По мере выбора полей, автоматически генерируется запрос SELECT и LEFT JOIN.
Укажите, куда необходимо поместить результат запроса: на новый или существующий лист.
Нажмите «Выполнить» > Готово! Объединённая таблица появится в считанные секунды.

Появились вопросы или предложения? Оставьте комментарий ниже.

xltools.net

Как я могу запускать SQL-запросы в именованном диапазоне в листе excel? MS Excel онлайн

Все, что я пытаюсь сделать, это взять стандартный диапазон на листе excel (например, именованный диапазон или даже A1: F100) и запустить некоторые sql-запросы на нем и вернуть набор записей, который я могу либо выполнить в коде VBA, или даже просто вставить другой лист в ту же книгу.

Использование ADODB было одной мыслью, но как я мог настроить строку соединения, чтобы указать на какой-то диапазон в текущей книге?

Я знаю, прежде чем использовать мастер запросов Microsoft, который не был идеальным, но будет работать. Я не могу заставить это ссылаться на диапазон внутри листа, только другие файлы excel.

Вот функция, с которой я остаюсь. Когда я запускаю его несколько раз, мой excel падает с обычным сообщением об ошибке с ресурсами. Я удалил эту функцию из своей таблицы, и все работает многократно, поэтому это определенно вызвано кодом здесь.

Я очистил все объекты (правильно?). У кого-нибудь есть идеи, что может пойти не так? Может ли быть что-то в строке соединения, которая может быть изменена, или это может быть связано с вариантом, который возвращается из метода GetRows?

Я использую MS ADO 2.8, а также попробовал 2.5 с тем же поведением.

Function getTimeBuckets() As Collection Dim strFile As String Dim strCon As String Dim strSQL As String Dim dateRows As Variant Dim i As Integer Dim today As Date Dim cn As ADODB.Connection Dim rs As ADODB.Recordset Set cn = CreateObject("ADODB.Connection") Set rs = CreateObject("ADODB.Recordset") Set getTimeBuckets = New Collection strFile = ThisWorkbook.FullName strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _ & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";" cn.Open strCon strSQL = "SELECT DISTINCT(Expiration) FROM [PositionSummaryTable] where [Instrument Type] = 'LSTOPT'" rs.Open strSQL, cn dateRows = rs.GetRows rs.Close 'today = Date today = "6-may-2009" For i = 1 To UBound(dateRows, 2) If (dateRows(0, i) >= today) Then getTimeBuckets.Add (dateRows(0, i)) End If Next i Set dateRows = Nothing Set cn = Nothing Set rs = Nothing End Function

Solutions Collecting From Web of "Как я могу запускать SQL-запросы в именованном диапазоне в листе excel?"

Вы можете просто использовать имя.

Dim cn As ADODB.Connection Dim rs As ADODB.Recordset strFile = Workbooks(1).FullName strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _ & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";" Set cn = CreateObject("ADODB.Connection") Set rs = CreateObject("ADODB.Recordset") cn.Open strCon ''Pick one: strSQL = "SELECT * FROM DataTable" ''Named range strSQL = "SELECT * FROM [Sheet1$A1:E346]" ''Range rs.Open strSQL, cn Debug.Print rs.GetString

В ответ на вопрос часть 2

Я замечаю, что вам нужны только сегодняшние записи, поэтому вы можете модифицировать sql для:

strSQL = "SELECT DISTINCT(Expiration) FROM [PositionSummaryTable] " _ & "where [Instrument Type] = 'LSTOPT' AND [Expiration]=#" _ & Format(Date(),"yyyy/mm/dd") & "#"

Вы не закрыли соединение:

cn.Close

А потом

Set rs=Nothing Set cn=Nothing

Как насчет использования предложения LIKE?

Я попытался использовать:

select * from [PES$] where PID_TAG like '*5400001'

безуспешно….

это работает:

select * from [PES$] where PID_TAG = 'PIT5400001'

но этого я не хочу.

РЕДАКТИРОВАТЬ

hummm …. нам нужно изменить подстановочные знаки для работы … не используйте *, используйте%

Вы найдете все, что вам нужно:

Первая ссылка для VB.NET, ссылки 2 – 6 для VBA.

Один

Два

Три

Шесть

Найдено всего в google, около 2 – 4 минут.

Я не знаю о VBA, но есть код онлайн в Delphi и C #, который использует формат

SELECT * FROM [SheetName$A1:B2]

Ты это пробовал?

Если вы используете powershell, $ является внутренним символом. использовать `$

например:

"Select * from [VM`$A3:F30]"

excel.bilee.com

SQL-запросы к MS SQL Server из Excel. Подключение к внешним источникам данных

Для обработки данных в Excel требуется сначала получить сами данные. Часто такая информация находится во внешних источниках, а именно базах данных. В этой статье описывается подключение электронных таблиц к БД MS SQL Server.

На вкладке «Данные» имеется область «Получение внешних данных» на которой необходимо кликнуть по кнопке «Из других источников». Раскроется список источников, к которым можно осуществить подключение. В этом списке выберите пункт «С сервера SQL Server».

Теперь на экране появилось окно мастера подключения к данным. Изначально требуется указать имя сервера и учетные сведения для подключения (если Вы их не знаете, то обратитесь к администратору БД).

После заполнения формы нажмите кнопку «Далее», чтобы перейти к выбору базы данных.

Здесь же можно выбрать таблицы, из которых будут грузиться данные, или же снять галочку с пункта «Подключение к определенной таблице».

Нажмите кнопку «Далее», чтобы задать имя файла подключения и описание, чтобы в дальнейшем было проще найти данный файл.

Нажав кнопку «Готово», Вам будет предложено произвести импорт данных с сервера (если в мастере подключений Вами не была выбрана таблица, то сначала ее придется выбрать).

В текущем окне можно выбрать место и способ представления импортируемых данных: таблица, сводная таблица, сводная диаграмма или пропустить импорт, выбрав пункт «Только создать подключение».

SQL-запрос

По умолчанию, при импорте из SQL Server, Excel выгружает всю таблицу. Можно изменить выборку, указав SQL-запрос.

Для этого во время импорта данных (предыдущий рисунок) необходимо кликнуть по кнопке «Свойства», если подключение создается впервые, либо, если файл подключения уже был добавлен к книге, на вкладке «Данные» в области «Получение внешних данных» кликнуть по кнопке «Подключения», выбрать нужное из списка и открыть его свойства.

В свойствах подключения на вкладке «Определение» в поле «Тип команды» поменяйте значение на «SQL», а в поле «Текст команды» введите SQL-код и нажмите «ОК».

Теперь, чтобы получить данные с помощью нового подключения, достаточно вызвать его из существующих подключений на вкладке «Данные» в области «Получение внешних данных». Дальнейшая процедура импорта аналогична рассмотренному выше.

< Назад
Вперёд >

Похожие статьи:Новые статьи:

Если материалы office-menu.ru Вам помогли, то поддержите, пожалуйста, проект, чтобы мы могли развивать его дальше.

У Вас недостаточно прав для комментирования.

office-menu.ru

Что такое SQL-запрос, выполняемый в файле Excel? MS Excel онлайн

F1, F2, F3 etc Просто генерируют заголовки столбцов и НЕ являются ссылками на ячейки. Несмотря на то, что в строке соединения у вас есть HDR=YES , если первая строка пустая, поставщик будет автоматически генерировать имена столбцов от F1 до F(n) . Почему он выбирает FI, не знаю, но n – это просто порядковый номер столбца (начиная с 1), и имя имеет префикс, поэтому его нельзя путать с числом.

Если вы вернетесь к представлению своего набора данных как к базе данных, а не к электронной таблице, было бы бессмысленно ссылаться на определенную строку в запросе, например, если вы представляете себе небольшую таблицу

ID | A | B | C | ------------------- 1 | x | y | z | 2 | d | e | f | 3 | j | k | l |

Затем подумайте с точки зрения SQL, а не excel, следующий запрос не имеет смысла, чтобы получить только первую строку, потому что A1 = X:

SELECT A, B, C FROM Sheet1 WHERE A1 = 'x';

Вам нужно будет использовать столбцы так:

SELECT A, B, C FROM Sheet1 WHERE ID = 1 AND A = 'x';

F9, F10 и т. Д. – это ячейки, F (или любая другая буква / комбинация), являющаяся индикатором столбца, и 9 (или любое другое число) – индекс строки.

Этот сниппет делает здесь, создает соединение OLEDB с базой данных и выполняет запрос к базе данных. В начале он проверяет, является ли база данных еще одним листом excel. Если это то, то он использует этот тип подключения:

Provider = Microsoft.ACE.OLEDB.12.0; Источник данных = "+ dbFilePath + @"; Расширенные свойства = "" Excel 12.0 Xml; HDR = YES "

В противном случае он использует это:

"Provider = Microsoft.Jet.OLEDB.4.0; Источник данных =" "" + dbFilePath + @ "" "; Persist Security Info = False; Расширенные свойства ="

Вероятно, ожидается, что любой другой тип базы данных будет основан на доступе, как я предполагаю.

Jet и ACE являются поставщиками OLEDB, которые позволяют использовать синтаксис SQL в электронной таблице Excel и других данных в офисе MS. Итак, да, это ссылки на ячейки, которые псевдонимы. Поскольку у вас есть электронная таблица, вы можете посмотреть в ячейках и посмотреть, что такое данные. Например, я подозреваю, что ячейка F9 содержит номер сотрудника.

В Excel Spreasheet столбцы перечисляются от A до Z , затем AA через AZ т. Д., Строки перечисляются с 1 вверх. Таким образом, ссылка F9 ячейки скоординирована в столбце F и строке 9 .

Поскольку диапазон «FROM» Sheet1$A8:V является относительным, вам может потребоваться соответствующим образом выбрать соответствующий выбор. Что находится в 17-й строке? Является ли F17 номером сотрудника?

Возможно, измените запрос на

string strQuery = "SELECT * FROM [Sheet1$A8:V]"

и начните оттуда.

excel.bilee.com

sql запрос из excel « NC’s Blog

Microsoft Office Exel уже довольно давно стал ключевой программой при переносе разных баз, тк практически любая даже очень старая база работает с Exel и перевести в него данный не состовляет ни каких проблем. Это пошаговое руководство описывает различные способы импорта данных из листов Microsoft Excel в базы данных Microsoft SQL Server.

Требования перед началом

В приведенном ниже списке перечислены рекомендованные оборудование, программное обеспечение, сетевая инфраструктура, а также необходимые пакеты обновления:

Экземпляр Microsoft SQL Server 7.0, Microsoft SQL Server 2000, Microsoft SQL Server 2005 или Microsoft SQL Server 2008
Microsoft Visual Basic 6.0 для примеров объектов ADO, использующих Visual Basic

В ряде разделов данной статьи предполагается, что пользователь обладает достаточными знаниями в следующих областях:

Службы преобразования данных
Связанные серверы и распределенные запросы
Разработка объектов ADO на Visual Basic

Описание

В примерах, приведенных в данной статье, импорт данных Excel выполняется с помощью следующих функций:

Службы преобразования данных SQL Server (DTS)
Службы интеграции Microsoft SQL Server 2005 (SSIS)
Связанные серверы SQL Server
Распределенные запросы SQL Server
Поставщик объектов данных ActiveX(ADO) и Microsoft OLE DB для SQL Server
Поставщик ADO и Microsoft OLE DB для Jet 4.0

Примеры

Import или Append

В примерах команд SQL, используемых в статье, показаны запросы Create Table для импорта данных Excel в новую таблицу SQL Server с использованием конструкций SELECT…INTO…FROM. При сохранении ссылок на объекты-источники и получатели выражения, приведенные в примерах, могут быть преобразованы в запросы Append с использованием конструкций INSERT INTO…SELECT…FROM.

Использование DTS или SSIS

Для импорта данных Excel в таблицы SQL Server могут быть использованы мастер импорта служб преобразования данных (DTS) SQL Server или мастер импорта и экспорта SQL Server. При работе с мастером и выборе исходных таблиц Excel помните, что имена объектов Excel со знаком доллара ($) являются именами листов (например Лист1$), а имена объектов без знака доллара являются названиями именованных диапазонов Excel.

Использование связанного сервера

Для упрощения запросов книга Excel может быть настроена как связанный сервер в SQL Server. Для получения дополнительных сведений щелкните приведенный ниже номер статьи базы знаний Майкрософт: 306397 (http://support.microsoft.com/kb/306397/RU/ )

Следующая программа импортирует данные из рабочего листа «Customers» связанного сервера Excel «EXCELLINK» в новую таблицу SQL Server с именем XLImport1:

←** SELECT * INTO XLImport1 FROM EXCELLINK...[Customers$] **→

При использовании OPENQUERY источнику может быть передан сквозной запрос:

←** SELECT * INTO XLImport2 FROM OPENQUERY(EXCELLINK,

‘SELECT * FROM [Customers$]’)

**→

Использование распределенных запросов

Если устанавливать существующее подключение к книге Excel как связанный сервер нежелательно, данные могут быть импортированы с использованием функций OPENDATASOURCE или OPENROWSET. В следующих примерах кода также производится импорт данных из рабочего листа Excel «Customers» в новые таблицы SQL Server:

←** SELECT * INTO XLImport3 FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source=C:\test\xltest.xls;Extended Properties=Excel 8.0')...[Customers$] SELECT * INTO XLImport4 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\test\xltest.xls', [Customers$]) SELECT * INTO XLImport5 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\test\xltest.xls', 'SELECT * FROM [Customers$]' **→

Использование ADO и SQLOLEDB

Синтаксис «распределенных запросов», приведенный в разделе Использование распределенных запросов, может быть использован также в приложении ADO для импорта данных Excel в SQL Server, если для подключения к SQL Server используется Microsoft OLE DB для SQL Server (SQLOLEDB).

Для работы следующего примера программы на Visual Basic 6.0 требуется добавление ссылки на проект в объекты данных ActiveX (ADO). В этом примере показано использование функций OPENDATASOURCE и OPENROWSET для подключения SQLOLEDB.

on Visual Basic

←** Dim cn As ADODB.Connection Dim strSQL As String Dim lngRecsAff As Long Set cn = New ADODB.Connection cn.Open "Provider=SQLOLEDB;Data Source=<server>;" & _ "Initial Catalog=<database>;User ID=<user>;Password=<password>" 'Import by using OPENDATASOURCE. strSQL = "SELECT * INTO XLImport6 FROM " & _ "OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', " & _ "'Data Source=C:\test\xltest.xls;" & _ "Extended Properties=Excel 8.0')...[Customers$]" Debug.Print strSQL cn.Execute strSQL, lngRecsAff, adExecuteNoRecords Debug.Print "Records affected: " & lngRecsAff 'Import by using OPENROWSET and object name. strSQL = "SELECT * INTO XLImport7 FROM " & _ "OPENROWSET('Microsoft.Jet.OLEDB.4.0', " & _ "'Excel 8.0;Database=C:\test\xltest.xls', " & _ "[Customers$])" Debug.Print strSQL cn.Execute strSQL, lngRecsAff, adExecuteNoRecords Debug.Print "Records affected: " & lngRecsAff 'Import by using OPENROWSET and SELECT query. strSQL = "SELECT * INTO XLImport8 FROM " & _ "OPENROWSET('Microsoft.Jet.OLEDB.4.0', " & _ "'Excel 8.0;Database=C:\test\xltest.xls', " & _ "'SELECT * FROM [Customers$]')" Debug.Print strSQL cn.Execute strSQL, lngRecsAff, adExecuteNoRecords Debug.Print "Record

nikpro.wordpress.com

sql - excel vba - запрос в электронной таблице

Здесь приведен код VBA, который позволяет читать диапазон Excel с помощью текстового драйвера SQL. Это довольно сложный пример, но я предполагаю, что вы пришли сюда, потому что вы довольно продвинутый пользователь с более сложной проблемой, чем примеры, которые мы видим на других сайтах.

Прежде чем опубликовать код в полном объеме, здесь исходный комментарий "пример использования" в основной функции, FetchXLRecordSet:

'Пример использования: ' 'Установить rst = FetchXLRecordSet (SQL, "TableAccountLookup", "TableCashMap" ) ' "Если запрос использует два именованных диапазона," TableAccountLookup "и" TableCashMap ", ', как показано в этом операторе SQL: ' ' ВЫБРАТЬ 'B.Legal_Entity_Name, B.Status, 'SUM (A.USD_Settled) Как Settled_Cash ' ИЗ '[TableAccountLookup] AS A, '[TableCashMap] AS B ' ГДЕ 'A.Account IS NOT NULL 'AND B.Cash_Account IS NOT NULL 'AND A.Account = B.Cash_Account ' ГРУППА ПО 'B.Legal_Entity_Name, 'B.Status

Это неудобно, заставляя вас называть таблицы (или перечислять адреса диапазона полностью) при запуске запроса, но это упрощает код.

Вариант Явный Опциональный модуль "Функции поиска данных ADODB для поддержки Excel 'Онлайн-ссылка для строк подключения: 'http://www.connectionstrings.com/oracle#p15 'Онлайн-ссылка для объектов и свойств ADO: 'http://msdn.microsoft.com/en-us/library/ms678086(v=VS.85).aspx 'Внешние зависимости: 'Scripting - C:\Program files\scrrun.dll 'ADO - C:\Program files\Common\system\ado\msado27.tlb

Частный m_strTempFolder As String Частный m_strConXL As String Частный m_objConnXL Как ADODB.Connection

Открытое свойство Get XLConnection() Как ADODB.Connection Ошибка при ошибке GoTo ErrSub "Драйверы базы данных Excel имеют проблемы с памятью, поэтому мы используем текстовый драйвер ', чтобы читать файлы csv во временной папке. Мы заполняем эти файлы из 'диапазоны, определенные для использования в качестве таблиц функцией FetchXLRecordSet().

Dim objFSO As Scripting.FileSystemObject

Установить objFSO = Новый скрипт .FileSystemObject Установить m_objConnXL = Новый ADODB.Connection 'Укажите и очистите временную папку:

m_strTempFolder = objFSO.GetSpecialFolder(2).ShortPath

Если Right (m_strTempFolder, 1) <> "\" Then m_strTempFolder = m_strTempFolder и "\" Конец Если

m_strTempFolder = m_strTempFolder и "XLSQL"

Application.DisplayAlerts = False Если objFSO.FolderExists(m_strTempFolder) Тогда objFSO.DeleteFolder m_strTempFolder Конец Если Если не objFSO.FolderExists(m_strTempFolder) Тогда objFSO.CreateFolder m_strTempFolder Конец Если

Если Right (m_strTempFolder, 1) <> "\" Then m_strTempFolder = m_strTempFolder и "\" Конец Если

'Строка подключения текстового драйвера JET OLEDB: 'Provider = Microsoft.Jet.OLEDB.4.0; Источник данных = c:\txtFilesFolder \; Расширенные свойства = "текст; HDR = Да; FMT = Разграничено"; 'Строка подключения драйвера ODBC: 'Driver = {Microsoft Text Driver (*.txt; *.csv)}; Dbq = c:\txtFilesFolder \; Extensions = asc, csv, tab, txt;

m_strConXL = "Provider = Microsoft.Jet.OLEDB.4.0; Источник данных =" и m_strTempFolder и ";" m_strConXL = m_strConXL и "Расширенные свойства =" и Chr (34) и "text; HDR = Yes; IMEX = 1" и Chr (34) и ";" С m_objConnXL .CursorLocation = adUseClient .CommandTimeout = 90 .ConnectionString = m_strConXL .Mode = adModeRead Завершить с помощью Если m_objConnXL.State = adStateClosed Then Application.StatusBar = "Подключение к локальным таблицам Excel" m_objConnXL.Open Конец Если Установить XLConnection = m_objConnXL ExitSub: Application.StatusBar = False Exit Property

ErrSub: MsgPopup "Ошибка подключения к локальным данным Excel. Обратитесь в службу поддержки приложений.", VbCritical + vbApplicationModal, "Ошибка подключения к базе данных!", 10 Резюме ErrEnd 'Resume ExitSub ErrEnd: Конечная ошибка терминала. Halt. Конечное свойство

Открытый Sub CloseConnections() При ошибке Продолжить дальше Установить m_objConnXL = Nothing End Sub

Публичная функция FetchXLRecordSet (ByVal SQL As String, ParamArray TableNames()) Как ADODB.Recordset "Это позволяет вам извлекать данные из диапазонов Excel с помощью SQL. Вы 'необходимо передать дополнительные параметры, определяющие каждый диапазон, который вы используете в качестве таблицы ', чтобы эти диапазоны можно было сохранить как файлы csv в временной папке "XLSQL" "Обратите внимание, что ваш запрос должен использовать соглашения об именах" таблицы ", требуемые Excel ': http://www.connectionstrings.com/excel#20 При ошибке Продолжить дальше Dim я As Integer Dim iFrom As Integer Dim strRange As String Dim j As Integer Dim k As Integer Если IsEmpty (TableNames) Then TableNames = Array ("") Конец Если Если InStr (TypeName (TableNames) "," (") < 1 Then TableNames = Array (TableNames) Конец Если

Установить FetchXLRecordSet = Новый ADODB.Recordset С FetchXLRecordSet

.CacheSize = 8 Set.ActiveConnection = XLConnection

iFrom = InStr (8, SQL, "От", vbTextCompare) + 4

Для я = LBound (имена таблиц) для UBound (имена таблиц)

strRange = " strRange = TableNames (i)

Если strRange =" 0 "или strRange =" "Тогда j = InStr (SQL," FROM ") + 4 j = InStr (j, SQL," [ ") k = InStr (j, SQL," ] ") strRange = Mid (SQL, j + 1, k - j - 1) Конец Если

RangeToFile strRange SQL = Left (SQL, iFrom) и Replace (SQL, strRange, strRange и".csv ", iFrom + 1, 1) SQL = Заменить (SQL,".csv ",".csv ") SQL = Replace (SQL,".csv $",".csv ") SQL = Заменить (SQL,".csv.csv ",".csv ")

Следующий я

.Open SQL, adOpenStatic, adCmdText + adAsyncFetch i = 0 Do While.State> 1 i = (i + 1) Mod 3 Application.StatusBar =" Подключение к базе данных "и String (i,".") Сон 250 Loop

Завершить с помощью Application.StatusBar = False Конечная функция

Открытая функция ReadRangeSQL (SQL_Range As Excel.Range) As String 'Прочитайте диапазон в строку. "Каждая строка ограничена возвратом каретки и разрывом строки. Пустые ячейки объединяются в строку как "вкладки" из четырех пробелов. "NH Feb 2018: вы не можете вернуть более 32767 символов в диапазон. Dim я As Integer Dim j As Integer Dim arrCells как вариант Dim arrRows() As String Dim arrRowX() As String Dim strRow As String Dim boolIndent As Boolean Const SPACE As String * 1 = " "Const SPACE4 As String * 4 =" " Const MAX_LEN As Long = 32767 arrCells = SQL_Range.Value2 Если InStr (TypeName (arrCells), "(" ) Then ReDim arrRows (LBound (arrCells, 1) To UBound (arrCells, 1)) ReDim arrRowX (LBound (arrCells, 2) To UBound (arrCells, 2))

Для я = LBound (arrCells, 1) To UBound (arrCells, 1) - 1 boolIndent = True Для j = LBound (arrCells, 2) To UBound (arrCells, 2) Если isError (arrCells (i, j)) Тогда SQL_Range (i, j).Calculate Конец Если

Если Not isError (arrCells (i, j)) Тогда arrRowX (j) = arrCells (i, j) еще arrRowX (j) = vbNullString Конец Если

Если boolIndent And arrRowX (j) = "" Тогда arrRowX (j) = SPACE4 еще boolIndent = False Конец Если

Следующий j

arrRows (i) = Join (arrRowX, SPACE)

Если Len (Trim $(arrRows (i))) = 0 Тогда arrRows (i) = vbNullString еще arrRows (i) = RTrim $(Join (arrRowX, SPACE)) Конец Если

Следующий я

Стереть arrCells Стереть arrRowX

ReadRangeSQL = Join (arrRows, vbCrLf)

Стирание arrRows

ReadRangeSQL = Заменить (ReadRangeSQL, vbCrLf и vbCrLf, vbCrLf) еще ReadRangeSQL = CStr (arrCells) Конец Если

Если Len (ReadRangeSQL)> MAX_LEN Затем 'Отключение прерывания пробелов из каждой строки: Do While InStr (1, ReadRangeSQL, SPACE и vbCrLf, vbBinaryCompare)> 0 ReadRangeSQL = Заменить (ReadRangeSQL, SPACE и vbCrLf, vbCrLf) Loop

Конец Если Если Len (ReadRangeSQL)> MAX_LEN Затем "Уменьшите размер вкладки до 2 выборочно, после каждого отступа строки arrRows = Split (ReadRangeSQL, vbCrLf) Для я = LBound (arrRows) Для UBound (arrRows) Если Len (arrRows (i))> 16 Тогда Если InStr (12, arrRows (i), SPACE4)> 0 Тогда ArrRows (i) = Left $(arrRows (i), 12) и Replace (Right $(arrRows (i), Len (arrRows (i)) - 12), SPACE4, SPACE и SPACE) Конец Если Конец Если Следующий я ReadRangeSQL = Join (arrRows, vbCrLf) Стирание arrRows

Конец Если Если Len (ReadRangeSQL)> MAX_LEN Затем "Уменьшите размер вкладки до 2 без разбора.Это сделает ваш SQL неразборчивым: Do While InStr (1, ReadRangeSQL, SPACE4, vbBinaryCompare)> 0 ReadRangeSQL = Заменить (ReadRangeSQL, SPACE4, SPACE и SPACE) Петля Конец Если Конечная функция

Открытый Sub RangeToFile (ByRef strRange As String) 'Вывести диапазон в файл csv во временной папке, созданной функцией XLConnection 'strRange указывает диапазон в текущей книге, используя соглашения об именах' table ' 'для драйверов базы данных Excel OLEDB: http://www.connectionstrings.com/excel#20 "Первая строка диапазона считается набором имен столбцов. При ошибке Продолжить дальше Dim objFSO As Scripting.FileSystemObject Dim rng Как Excel.Range Dim strFile As String Dim arrData как вариант Dim iRow As Long Dim jCol As Long Dim strData As String Dim strLine As String strRange = Заменить (strRange, "[ "," ") strRange = Заменить (strRange, "] "," ") Если Right (strRange, 1) = "$" Тогда strRange = Заменить (strRange, "$ "," ") Установите rng = ThisWorkbook.Worksheets(strRange).UsedRange еще strRange = Заменить (strRange, "$ "," ") Set rng = Range (strRange)

Если rng ничего не значит Установите rng = ThisWorkbook.Worksheets(strRange).UsedRange Конец Если Конец Если Если rng ничего не значит Exit Sub Конец Если

Установить objFSO = Новый скрипт .FileSystemObject strFile = m_strTempFolder и strRange и ".csv" Если objFSO.FileExists(strFile) Тогда objFSO.DeleteFile strFile, True Конец Если Если objFSO.FileExists(strFile) Тогда Exit Sub Конец Если arrData = rng.Value2 С objFSO.OpenTextFile(strFile, ForWriting, True) 'Заголовок строки: strLine = " strData =" " iRow = LBound (arrData, 1) Для jCol = LBound (arrData, 2) To UBound (arrData, 2) strData = arrData (iRow, jCol) strData = Заменить (strData, Chr (34), Chr (39)) strData = Заменить (strData, Chr (10)," ") strData = Заменить (strData, Chr (13)," ") strData = strData и "," strLine = strLine и strData Следующий jCol

strLine = Влево (strLine, Len (strLine) - 1) 'Конечная запятая для запятой

Если Len (Заменить (Заменить (strLine, Chr (34), ""), ",", " "))> 0 Тогда .WriteLine strLine Конец Если

Остальная часть данных Для iRow = LBound (arrData, 1) + 1 To UBound (arrData, 1)

strLine =" " strData =" "

Для jCol = LBound (arrData, 2) To UBound (arrData, 2) Если IsError (arrData (iRow, jCol)) Тогда strData =" #ERROR " еще strData = arrData (iRow, jCol) strData = Заменить (strData, Chr (34), Chr (39)) strData = Заменить (strData, Chr (10)," ") strData = Заменить (strData, Chr (13)," ") strData = Заменить (strData, Chr (9)," ") strData = Trim (strData) Конец Если strData = Chr (34) и strData и Chr (34) и "," 'Цитаты, чтобы принудить все значения к тексту strLine = strLine и strData Следующий jCol

strLine = Влево (strLine, Len (strLine) - 1) 'Конечная запятая для запятой Если Len (Заменить (Заменить (strLine, Chr (34), ""), ",", ""))> 0 Тогда .WriteLine strLine Конец Если Следующий iRow

.Закрыть End With 'textstream object из objFSO.OpenTextFile Установить objFSO = Nothing Стереть arrData Установить rng = Nothing End Sub

И, наконец, запись набора записей в диапазон - код будет тривиальным, если бы не все ошибки, с которыми вам приходится обращаться:

Public Sub RecordsetToRange (rngTarget As Excel.Range, objRecordset As ADODB.Recordset, необязательный полевой список как вариант, необязательный ShowFieldNames как Boolean = False, необязательная ориентация в формате Excel.XlRowCol = xlRows) "Записать набор записей ADO в диапазон Excel в одном" ударе "на листе" Функция вызова отвечает за установку указателя записи (не должна быть EOF!) 'Целевой диапазон автоматически изменяется на размеры массива, а верхняя левая ячейка используется как начальная точка. При ошибке Продолжить дальше Dim OutputArray как вариант Dim я As Integer Dim iCol As Integer Dim iRow As Integer Dim varField как вариант Если objRecordset ничего не значит Exit Sub Конец Если Если objRecordset.State <> 1 Then Exit Sub Конец Если Если objRecordset.BOF И objRecordset.EOF Тогда Exit Sub Конец Если Если Orientation = xlColumns Then Если IsEmpty (FieldList) или IsMissing (FieldList) Затем OutputArray = objRecordset.GetRows еще OutputArray = objRecordset.GetRows(Поля: = FieldList) Конец Если еще Если IsEmpty (FieldList) или IsMissing (FieldList) Затем OutputArray = ArrayTranspose (objRecordset.GetRows) еще OutputArray = ArrayTranspose (objRecordset.GetRows(Поля: = FieldList)) Конец Если Конец Если ArrayToRange rngTarget, OutputArray Если ShowFieldNames Then Если Orientation = xlColumns Then ReDim OutputArray (LBound (OutputArray, 1) To UBound (OutputArray, 1), 1 to 1) iRow = LBound (OutputArray, 1) Если IsEmpty (FieldList) или IsMissing (FieldList) Затем Для я = 0 Для objRecordset.Fields.Count - 1 Если i> UBound (OutputArray, 1) Then Выход для Конец Если OutputArray (iRow + i, 1) = objRecordset.Fields(i).Name Следующий я еще Если InStr (TypeName (FieldList), "(" ) < 1 Then FieldList = Array (FieldList) Конец Если i = 0 Для каждого varField In FieldList OutputArray (iRow + i, 1) = CStr (varField) i = я = 1 следующий Конец Если ArrayToRange rngTarget.Cells(1, 0), OutputArray Else ReDim OutputArray (от 1 до 1, LBound (OutputArray, 2) до UBound (OutputArray, 2)) iCol = LBound (OutputArray, 2) Если IsEmpty (FieldList) или IsMissing (FieldList) Затем Для я = 0 Для objRecordset.Fields.Count - 1 Если i> UBound (OutputArray, 2) Then Выход для Конец Если OutputArray (1, iCol + i) = objRecordset.Fields(i).Name Следующий я еще Если InStr (TypeName (FieldList), "(" ) < 1 Then FieldList = Array (FieldList) Конец Если i = 0 Для каждого varField In FieldList OutputArray (1, iCol + i) = CStr (varField) i = я = 1 следующий Конец Если ArrayToRange rngTarget.Cells(0, 1), OutputArray Конец Если End If 'ShowFieldNames Стереть OutputArray End Sub

Публичная функция ArrayTranspose (InputArray как вариант) как вариант 'Переместить InputArray. 'Возвращает InputArray без изменений, если он не является 2-мерным вариантом (x, y) Dim iRow As Long Dim iCol As Long Dim iRowCount как долго Dim iColCount как долго Dim boolNoRows As Boolean Dim BoolNoCols As Boolean Dim OutputArray как вариант

Если IsEmpty (InputArray) Then ArrayTranspose = InputArray Функция выхода Конец Если Если InStr (1, TypeName (InputArray), "(" ) < 1 Then ArrayTranspose = InputArray Функция выхода Конец Если "Убедитесь, что мы можем прочитать размеры массива: При ошибке Продолжить дальше Err.Clear iRowCount = 0 iRowCount = UBound (InputArray, 1) Если Err.Number <> 0 Then boolNoRows = True Конец Если Err.Clear Err.Clear iColCount = 0 iColCount = UBound (InputArray, 2) Если Err.Number <> 0 Then BoolNoCols = True Конец Если Err.Clear

Если boolNoRows Then 'Все массивы имеют определенный Ubound (MyArray, 1)! "Эти варианты измерения не могут быть определены OutputArray = InputArray ElseIf BoolNoCols Затем "Это вектор. Строго говоря, вектор не может быть" транспонирован ", так как " вызов ординала "строка" или "столбец" произволен или бессмыслен. "Но... По соглашению, пользователи Excel рассматривают вектор как массив от 1 до n 'строк и 1 столбца.Таким образом, мы будем" переносить" его в вариант (от 1 до 1, от 1 до n) ReDim OutputArray (от 1 до 1, LBound (InputArray, 1) до UBound (InputArray, 1)) Для iRow = LBound (InputArray, 1) для UBound (InputArray, 1) OutputArray (1, iRow) = InputArray (iRow) Следующий iRow Else ReDim OutputArray (LBound (InputArray, 2) To UBound (InputArray, 2), LBound (InputArray, 1) для UBound (InputArray, 1)) Если IsEmpty (OutputArray) Then ArrayTranspose = InputArray Функция выхода Конец Если Если InStr (1, TypeName (OutputArray), "(" ) < 1 Then ArrayTranspose = InputArray Функция выхода Конец Если Для iRow = LBound (InputArray, 1) для UBound (InputArray, 1) Для iCol = LBound (InputArray, 2) для UBound (InputArray, 2) OutputArray (iCol, iRow) = InputArray (iRow, iCol) Следующий iCol Следующий iRow Конец Если

ExitFunction: ArrayTranspose = OutputArray Стереть OutputArray Конечная функция

Сообщите мне, как вы поживаете. Как всегда, следите за форматированием сбоев: у меня никогда не было кода <code> теги для работы на этом сайте и <PRE> не всегда соблюдается текстовыми полями, когда преформатированный текст содержит кавычки и объекты HTML.

Постскриптум: запуск SQL в "табличных" объектах Excel

Для полноты здесь приведен код для чтения в виде таблицы таблиц с таблицей SQL с функцией SQL, которая обрабатывает весь взлом текстового файла в фоновом режиме.

Я отправляю его сейчас, спустя некоторое время после моего первоначального ответа, потому что все используют богатый объект таблицы для табличных данных в Excel:

"Запустите запрос JOIN в ваших таблицах и напишите имена полей и данные в Sheet1: ... И полный список (дайте или возьмите пару функций в предыдущем дампе кода): Открытая функция RunSQL (SQL As String, TargetRange как Excel.Range, Необязательный DataSetName As String) "Запустите SQL с файлами таблицы в локальной папке ExcelSQL и напишите результаты в целевой диапазон

"SQL можно читать из диапазонов с помощью функции ReadRangeSQL Dim rst As ADODB.Recordset

еще RecordsetToRange rst, TargetRange, True Установить rst = Nothing Конец Если

0, тогда SetSchema 1 i = (i + 1) Mod 3 Application.StatusBar = "Ожидание данных "и String (i,".") Application.Wait Now + (0,25/24/3600) Loop Завершить с помощью

ERR_ADO:

strMsg = vbCrLf и vbCrLf и "Если это ошибка файла, кто-то открыл один из файлов исходных данных: повторите попытку через несколько минут". и vbCrLf и vbCrLf и "В противном случае, обратите внимание на это сообщение об ошибке и обратитесь к разработчику, или" и ПОДДЕРЖКА "и". ". Если Verbose Then MsgBox" Error & H "и Hex (Err.Number) и": "и Err.Description и strMsg, vbCritical + vbMsgBoxHelpButton," Ошибка поиска данных:", Err.HelpFile, Err.HelpContext Конец Если Резюме ExitSub Выход из функции

Dim strTempFolder Если m_objConnText ничего не значит

Установить m_objConnText = Новый ADODB.Connection strTempFolder = TempSQLFolder ', это проверит, разрешает ли папка операции SQL READ

Application.DisplayAlerts = False

'MS-Access ACE OLEDB Provider m_strConnText = "Provider = Microsoft.ACE.OLEDB.12.0; Источник данных =" и Chr (34) и strTempFolder и Chr (34) и "; Persist Security Info = True;" m_strConnText = m_strConnText и "Расширенные свойства =" и Chr (34) и "text; CharacterSet = UNICODE; HDR = Yes; HDR = Yes; IMEX = 1; MaxScanRows = 1" и Chr (34) и ";"

Конец Если Если Not m_objConnText ничего не значит

С m_objConnText

Если .State = adStateClosed Then

Application.StatusBar = "Подключение к локальным таблицам Excel" .CursorLocation = adUseClient .CommandTimeout = 90 .ConnectionString = m_strConnText .Mode = adModeRead .Open

Конец Если

Завершить с помощью Если m_objConnText.State = adStateClosed Then Установить m_objConnText = Nothing Конец Если

Конец Если Set connText = m_objConnText ExitSub: Application.StatusBar = False Exit Property

ErrSub: MsgBox "Ошибка подключения к локальным данным Excel. Пожалуйста, свяжитесь с" и ПОДДЕРЖКА и "." , vbCritical + vbApplicationModal, "Ошибка подключения к базе данных!", 10 Резюме ErrEnd 'Resume ExitSub ErrEnd: Конечная ошибка терминала. Halt. Конечное свойство

Открытый Sub CloseConnections() При ошибке Продолжить дальше Установить m_objConnText = Nothing End Sub

Публичная функция TempSQLFolder() As String Приложение. Устойчивое False 'Местоположение временных файлов таблицы, используемых функциями текстовых данных SQL 'Также запускает фоновый процесс для очистки файлов в течение 7 дней "Лучшее местоположение - это именованная вложенная папка в папке временного пользователя. 'локальная папка пользователя' temp 'может быть обнаружена во всех системах Windows, используя "GetObject (" Scripting.FileSystemObject "). GetSpecialFolder (2).ShortPath 'и обычно будет C:\Users [Имя пользователя]\AppData\Local\Temp 'Зависимости: 'Объектное свойство FSO (возвращает Scripting.FilesystemObject) "Dim strCMD As String Dim strMsg As String Dim strNamedFolder As String Static strTempFolder As String 'Cache it Dim iRetry As Integer Dim я As Long "Если мы уже нашли удобную папку temp, используйте статическое значение 'без запроса файловой системы и проверки прав на запись снова: Если strTempFolder <> "" Then TempSQLFolder = strTempFolder Функция выхода Конец Если При ошибке Продолжить дальше strTempFolder = GetObject ( "Scripting.FileSystemObject" ). GetSpecialFolder (2).ShortPath Если Right (strTempFolder, 1) <> "\" Then strTempFolder = strTempFolder и "\" Конец Если

strTempFolder = strTempFolder и "XLSQL"

Если не FSO.FolderExists(strTempFolder) Затем FSO.CreateFolder strTempFolder Конец Если i = 1 До FSO.FolderExists(strTempFolder) Или i> 6 Сон я * 250 Application.StatusBar = "Ожидание папки кэша SQL" и String (i Mod 4, "." ) Петля Если не FSO.FolderExists(strTempFolder) Затем GoTo Retry Конец Если

Если Right (strTempFolder, 1) <> "\" Then strTempFolder = strTempFolder и "\" Конец Если

TempSQLFolder = strTempFolder

Application.StatusBar = False

Конечная функция

Открытое свойство Get FSO() As Scripting.FileSystemObject ' 'Вернуть объект файловой системы При ошибке Продолжить дальше Если m_objFSO ничего не значит Установите m_objFSO = CreateObject ( "Scripting.FileSystemObject" ) 'Новый скриптинг .FileSystemObject Конец Если Если m_objFSO ничего не значит Shell "Regsvr32.exe/s scrrun.dll", vbHide Установите m_objFSO = CreateObject ( "Scripting.FileSystemObject" ) Конец Если Установить FSO = m_objFSO Конечное свойство

Public Sub SaveTable (необязательное имя таблицы как строка = "") 'Экспортировать объект таблицы в локальную папку SQL в виде файла csv 'Если имя не указано, все таблицы экспортируются асинхронно "Этот шаг необходим для запуска SQL на таблицах Dim wks Как Excel.Решетка Dim oList Как Excel.ListObject Dim sFile As String Dim bAsync As Boolean Если TableName = "" Тогда bAsync = True еще bAsync = False Конец Если Для каждого wks в ThisWorkbook.Worksheets Для каждого oList В wks.ListObjects Если oList.Name Like TableName Then sFile = oList.Name ArrayToCSV oList.Range.Value2, sFile,,,, bAsync 'Debug.Print' [ "и sFile и".csv] " Конец Если Следующий oList Следующий wks SetSchema End Sub

Открытый Sub RemoveTable (необязательный TableName As String = "*" ) При ошибке Продолжить дальше "Очистите временные файлы" Таблица "в локальной папке временного файла пользователя: Dim wks Как Excel.Решетка Dim oList Как Excel.ListObject Dim sFile As String Dim sFolder As String sFolder = TempSQLFolder Для каждого wks в ThisWorkbook.Worksheets Для каждого oList В wks.ListObjects

Если oList.Name Like TableName Then sFile = oList.Name и ".csv" Если Len (Dir (sFile))> 0 Тогда Оболочка "CMD/c DEL" и Chr (34) и sFolder и sFile и Chr (34), асинхронное удаление vbHide Конец Если Конец Если

Следующий oList Следующий wks End Sub

Поделитесь и наслаждайтесь: это ужасный взлом, но он дает вам стабильную платформу SQL.

И у нас до сих пор нет стабильной "родной" платформы для SQL в Excel: поставщик данных Microsoft.ACE.OLEDB.14.0 Excel по-прежнему имеет те же утечки памяти, что и Microsoft.Jet.OLEDB.4.0 и Excel ODBC который предшествовал этому, двадцать лет назад.

qaru.site