Всем здравствуйте! Собственно, вопрос в названии темы. Каким образом можно (можно ли?) результат запроса в PowerQuery не выгружать на лист, а получить в виде VBA-массива, чтобы потом обработать его макросами.
Всем здравствуйте! Собственно, вопрос в названии темы. Каким образом можно (можно ли?) результат запроса в PowerQuery не выгружать на лист, а получить в виде VBA-массива, чтобы потом обработать его макросами.nilem
Добрый день. Я думаю, что можно, надо экспериментировать. Запрос хранится в книге, данные можно получить через OLEDB. Посмотрите в свойствах запроса в "Подключениях" к книге. Встречный вопрос, а почему не обработать данные в PQ до необходимого результата?
Добрый день. Я думаю, что можно, надо экспериментировать. Запрос хранится в книге, данные можно получить через OLEDB. Посмотрите в свойствах запроса в "Подключениях" к книге. Встречный вопрос, а почему не обработать данные в PQ до необходимого результата?sboy
Спасибо за ответ. Хотелось, чтобы сам PQ своими средствами создавал массив. "почему не обработать данные в PQ" - результат будет порядка 5 млн строк, их надо будет разбить и, например, сохранить в csv.
Спасибо за ответ. Хотелось, чтобы сам PQ своими средствами создавал массив. "почему не обработать данные в PQ" - результат будет порядка 5 млн строк, их надо будет разбить и, например, сохранить в csv.nilem
Нет, с этим вроде все норм. Запрос происходит к базе Оракл.
Я несколько о другом О доступе к результату запроса Power Query из VBA через ADODB. 1. Выполняется первый раз более минуты. 2. Иногда вылетает с ошибкой. 3. Если и сработает, то вывод начинается со второй строки данных. Пример прикладываю. Excel 2016 64bit, версия Power Query 2.61.5192
Нет, с этим вроде все норм. Запрос происходит к базе Оракл.
Я несколько о другом О доступе к результату запроса Power Query из VBA через ADODB. 1. Выполняется первый раз более минуты. 2. Иногда вылетает с ошибкой. 3. Если и сработает, то вывод начинается со второй строки данных. Пример прикладываю. Excel 2016 64bit, версия Power Query 2.61.5192anvg
В общем, как и советовал boa, с ADO получилось выгрузить 5 млн строк и записать в csv. Долго конечно, но работает. Если использовать запрос Power Query из ADO по примеру anvg, да, первая строка данных пропадает. Видимо, и запрос PQ не берет заголовки, и ADO тоже - так и пропадают 2 верхние строки (заголовок и 1-я строка данных). Но это только предположение. Такие вот пироги
В общем, как и советовал boa, с ADO получилось выгрузить 5 млн строк и записать в csv. Долго конечно, но работает. Если использовать запрос Power Query из ADO по примеру anvg, да, первая строка данных пропадает. Видимо, и запрос PQ не берет заголовки, и ADO тоже - так и пропадают 2 верхние строки (заголовок и 1-я строка данных). Но это только предположение. Такие вот пироги nilem
может SQL-запрос не правильно составлен? накидал "Козу" для выгрузки данных, может поможет...
[vba]
Код
Sub getData()
Dim sSQL$, FileNameCSV$ Dim CON As Object: Set CON = CreateObject("ADODB.Connection") Dim RST As Object: Set RST = CreateObject("ADODB.Recordset")
CON.CommandTimeout = 0 'Устанавливает число секунд ожидания выполнения команды. Значение по умолчанию - 30. 0-безлимит CON.Open ConnectionString:="строка подключения" 'найдете на сайте https://www.connectionstrings.com/oracle/ RST.CursorLocation = 3 'adUseClient ' что бы потом можно было посчитать количество записей в рекордсете
sSQL = "Select * From table" 'Здесь ваш SQL-запрос
If Not RST.EOF Then FileNameCSV = "полный путь к файлу csv" Open FileNameCSV For Output As #1 Print #1, "Заголовки через разделитель" Print #1, RST.GetString(, , ";", vbCrLf) 'https://docs.microsoft.com/ru-ru/sql/ado/reference/ado-api/getstring-method-ado?view=sql-server-2017 Close #1 End If RST.Close: CON.Close: Set CON = Nothing
может SQL-запрос не правильно составлен? накидал "Козу" для выгрузки данных, может поможет...
[vba]
Код
Sub getData()
Dim sSQL$, FileNameCSV$ Dim CON As Object: Set CON = CreateObject("ADODB.Connection") Dim RST As Object: Set RST = CreateObject("ADODB.Recordset")
CON.CommandTimeout = 0 'Устанавливает число секунд ожидания выполнения команды. Значение по умолчанию - 30. 0-безлимит CON.Open ConnectionString:="строка подключения" 'найдете на сайте https://www.connectionstrings.com/oracle/ RST.CursorLocation = 3 'adUseClient ' что бы потом можно было посчитать количество записей в рекордсете
sSQL = "Select * From table" 'Здесь ваш SQL-запрос
If Not RST.EOF Then FileNameCSV = "полный путь к файлу csv" Open FileNameCSV For Output As #1 Print #1, "Заголовки через разделитель" Print #1, RST.GetString(, , ";", vbCrLf) 'https://docs.microsoft.com/ru-ru/sql/ado/reference/ado-api/getstring-method-ado?view=sql-server-2017 Close #1 End If RST.Close: CON.Close: Set CON = Nothing
... If Not rs.EOF Then ' rs.MoveFirst s = vbNullString ff = FreeFile: Open fName For Output As #ff 'сначала заголовки For Each fld In rs.Fields 'Dim fld As ADODB.Field s = s & ";" & fld.Name Next fld Print #ff, Mid(s, 2) 'теперь все остальное Do While Not rs.EOF s = vbNullString For Each fld In rs.Fields s = s & ";" & fld.Value Next fld Print #ff, Mid(s, 2) rs.MoveNext Loop Close #ff Else MsgBox Mssg & "нет подходящих данных", 48, "Обновление данных" rs.Close: Set rs = Nothing: conn.Close: Set conn = Nothing bStart = False: Exit Sub End If ...
[/vba]
Странно, но GetString работает заметно медленнее, чем Do...Loop & MoveNext (проверял на 200k записей)
boa, спасибо за "Козу" ) У меня вот так:
[vba]
Код
... If Not rs.EOF Then ' rs.MoveFirst s = vbNullString ff = FreeFile: Open fName For Output As #ff 'сначала заголовки For Each fld In rs.Fields 'Dim fld As ADODB.Field s = s & ";" & fld.Name Next fld Print #ff, Mid(s, 2) 'теперь все остальное Do While Not rs.EOF s = vbNullString For Each fld In rs.Fields s = s & ";" & fld.Value Next fld Print #ff, Mid(s, 2) rs.MoveNext Loop Close #ff Else MsgBox Mssg & "нет подходящих данных", 48, "Обновление данных" rs.Close: Set rs = Nothing: conn.Close: Set conn = Nothing bStart = False: Exit Sub End If ...
[/vba]
Странно, но GetString работает заметно медленнее, чем Do...Loop & MoveNext (проверял на 200k записей)nilem
nilem, вот тут и подвох вы выгружаете каждую строку по отдельности, а в моем примере все выгружается скопом. после вывода заголовков выгружайте весь РСТ в файл
[vba]
Код
Sub getDataToCSV()
Dim sSQL$, FileNameCSV$, s$ Dim fld As Object 'As ADODB.Field Dim CON As Object: Set CON = CreateObject("ADODB.Connection") Dim RST As Object: Set RST = CreateObject("ADODB.Recordset")
CON.CommandTimeout = 0 'Устанавливает число секунд ожидания выполнения команды. Значение по умолчанию - 30. 0-безлимит CON.Open ConnectionString:="строка подключения" 'найдете на сайте https://www.connectionstrings.com/oracle/ RST.CursorLocation = 3 'adUseClient ' что бы потом можно было посчитать количество записей в рекордсете
sSQL = "Select * From table" 'Здесь ваш SQL-запрос
If Not RST.EOF Then FileNameCSV = "полный путь к файлу csv" Open FileNameCSV For Output As #1
'сначала заголовки через разделитель For Each fld In RST.Fields s = s & ";" & fld.Name Next fld Print #1, Mid(s, 2) 'теперь все остальное Print #1, RST.GetString(, , ";", vbCrLf) 'https://docs.microsoft.com/ru-ru/sql/ado/reference/ado-api/getstring-method-ado?view=sql-server-2017 Close #1 End If RST.Close: CON.Close: Set CON = Nothing
End Sub
[/vba]
nilem, вот тут и подвох вы выгружаете каждую строку по отдельности, а в моем примере все выгружается скопом. после вывода заголовков выгружайте весь РСТ в файл
[vba]
Код
Sub getDataToCSV()
Dim sSQL$, FileNameCSV$, s$ Dim fld As Object 'As ADODB.Field Dim CON As Object: Set CON = CreateObject("ADODB.Connection") Dim RST As Object: Set RST = CreateObject("ADODB.Recordset")
CON.CommandTimeout = 0 'Устанавливает число секунд ожидания выполнения команды. Значение по умолчанию - 30. 0-безлимит CON.Open ConnectionString:="строка подключения" 'найдете на сайте https://www.connectionstrings.com/oracle/ RST.CursorLocation = 3 'adUseClient ' что бы потом можно было посчитать количество записей в рекордсете
sSQL = "Select * From table" 'Здесь ваш SQL-запрос
If Not RST.EOF Then FileNameCSV = "полный путь к файлу csv" Open FileNameCSV For Output As #1
'сначала заголовки через разделитель For Each fld In RST.Fields s = s & ";" & fld.Name Next fld Print #1, Mid(s, 2) 'теперь все остальное Print #1, RST.GetString(, , ";", vbCrLf) 'https://docs.microsoft.com/ru-ru/sql/ado/reference/ado-api/getstring-method-ado?view=sql-server-2017 Close #1 End If RST.Close: CON.Close: Set CON = Nothing
Да, именно таким кодом и проверял. Казалось бы, что GetString д.б. быстрее/проще/эффективнее, но на самом деле получилось, что построчная запись в файл примерно в 5 раз быстрее, чем GetString. Не знаю, как это объяснить. Может, от базы зависит?
Да, именно таким кодом и проверял. Казалось бы, что GetString д.б. быстрее/проще/эффективнее, но на самом деле получилось, что построчная запись в файл примерно в 5 раз быстрее, чем GetString. Не знаю, как это объяснить. Может, от базы зависит?nilem
Если использовать запрос Power Query из ADO по примеру anvg, да, первая строка данных пропадает. Видимо, и запрос PQ не берет заголовки, и ADO тоже - так и пропадают 2 верхние строки (заголовок и 1-я строка данных). Но это только предположение.
ADO действительно первую строку понимает как заголовки столбцов и отделяет ее от данных, при этом она сохраняется в том же объекте recordset в качестве названия полей Fields и их оттуда можно вытащить.
Если использовать запрос Power Query из ADO по примеру anvg, да, первая строка данных пропадает. Видимо, и запрос PQ не берет заголовки, и ADO тоже - так и пропадают 2 верхние строки (заголовок и 1-я строка данных). Но это только предположение.
ADO действительно первую строку понимает как заголовки столбцов и отделяет ее от данных, при этом она сохраняется в том же объекте recordset в качестве названия полей Fields и их оттуда можно вытащить.Bamboo
ADO действительно первую строку понимает как заголовки столбцов и отделяет ее от данных,
А можно привести пример и, хотя бы минимально объяснить, как было получено такое поведение? Сделал останов на If pRset.EOF Then pRset.MoveNext В Immediate получаю следующее
ADO действительно первую строку понимает как заголовки столбцов и отделяет ее от данных,
А можно привести пример и, хотя бы минимально объяснить, как было получено такое поведение? Сделал останов на If pRset.EOF Then pRset.MoveNext В Immediate получаю следующее
Вход
Регистрация
Напомнить пароль
Автосумма
Часто используемые
ВПР
ЕСЛИ
СУММЕСЛИ
Финансовые
Логические
Текстовые
Даты и времени 
Ссылки и массивы 
Математические
Другие функции
Проверка свойств и значений
Баз данных
Математические
Обработки массивов
Обработки строк
Преобразования типов данных 
Загрузки данных
Работы с файлами
Обработки системных параметров
Обработки цвета
Работы с датами и временем
Преобразования в системы счисления
Финансовые
О доступе к результату запроса Power Query из VBA через ADODB. 
