Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Microsoft Office [игнор отключен] [закрыт для гостей] / Подключение к excel-листам / 25 сообщений из 48, страница 1 из 2
  1  все
19.05.2016, 14:41
    #39239146
ferzmikk
ferzmikk
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подключение к excel-листам
Здравствуйте!

В excel-ом файле на каждом листе "Лист1" и 'Лист2" есть по одной таблице с подключением с MDX-запросом. На "Лист3" нужно вставить таблицу с подключением с SQL-запросом к этим двум листам. Нужно сделать внешнее соединение из этих двух таблиц.

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

Делаю так. Данные - Из других источников- Из Microsoft Query. Но фишка в том, что синтаксис Microsoft Query не совсем такой как SQL. Более капризный. Запутался там как делать внешнее соединение Для данной задачи нужно подключаться через Microsoft Query или можно подключаться по другому (через другой провайдер)? ADO не вариант, там ограниченный SQL.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.05.2016, 09:00
    #39239682
ferzmikk
ferzmikk
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подключение к excel-листам
В общем, сделал так

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

Вариант 1
В строке подключения ввел так Provider=Microsoft.ACE.OLEDB.12.0;Excel 12.0;Database=C:\Users\User\Desktop\СМ2.xlsm. Тип команды - SQL. Текст запроса
Код: sql
1.
Select * From [ИД1$]

Выгрузились данные нормально.

Захожу в свойства подключения и в строке подключения теперь другая запись
Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source="";Mode=Share Deny None;Extended Properties="Excel 12.0;Database=C:\Users\User\Desktop\СМ2.xlsm";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Engine Type=35;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False

Теперь вставляю такой текст простого запроса с объединением
Код: sql
1.
2.
3.
4.
5.
6.
Select
[ИД1$].[Поле1],
[ИД2$].[Поле3]
From [ИД1$]  JOIN [ИД2$]
ON
[ИД1$].[Поле1]=[ИД2$].[Поле2]

Пишет, что ошибка в From. Вроде все по синтаксису написал.


Вариант 2

Если в строке подключения написать Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\User\Desktop\СМ2.xlsm;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"" .Тип команды - SQL. И текст запроса
Код: sql
1.
Select [ИД1$].[Поле1],[ИД1$].[Поле2] From [ИД1$][ИД1$]

Данные выгружаются нормально.

Захожу в свойства подключения и в строке подключения теперь также другая запись как в первом варианте.

Если вставить такой текст запроса
Код: sql
1.
2.
3.
4.
5.
6.
Select
[ИД1$].[Поле1],
[ИД1$].[Поле3]
From [ИД1$][ИД1$] JOIN  [ИД2$][ИД2$]
ON
[ИД1$].[Поле1]=[ИД2$].[Поле2]

Пишет также, что ошибка в From.

Что не так делаю? Может надо что в строке подключения изменить?

Какой вариант подключения лучше использовать?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.05.2016, 21:12
    #39240445
anvg
anvg
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подключение к excel-листам
Доброе время суток
Странно всё это. Попробуйте явно указать тип соединения INNER JOIN
ferzmikkКакой вариант подключения лучше использовать?, а вот подключение лучше использовать ODBC . С OLEDB периодически возникают проблемы с блокировкой файла. При подключении запроса к самому себе, всё нормально, а вот с другими бывают проблемы.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.05.2016, 07:20
    #39241091
ferzmikk
ferzmikk
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подключение к excel-листам
anvg,

вместо JOIN написал INNER JOIN - заработал.

Потом FULL JOIN не работал. Использовал UNION - заработало. Похоже SQL к excel листам ограниченный.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.05.2016, 07:42
    #39241096
ferzmikk
ferzmikk
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подключение к excel-листам
anvgferzmikkКакой вариант подключения лучше использовать?, а вот подключение лучше использовать ODBC .
Тут я не совсем понял.
Код: xml
1.
2.
Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};
DBQ=C:\MyExcel.xlsx;

Это писать в строке подключения?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.05.2016, 09:13
    #39241124
anvg
anvg
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подключение к excel-листам
Потом FULL JOIN не работаДля обращения к листам Excel используется движок Access, а он года с 97, в плане SQL, не развивается. Там есть только Left Join, Right Join, Inner Join
Тут я не совсем понял.

Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};
DBQ=C:\MyExcel.xlsx;

Это писать в строке подключения?
Смотря как вы создавали подключение. В уже существующем, если не ошибаюсь, перейти с OleDb на ODBC не получится (не эксперементировал). А так да, по минимальному тексту подключения всё правильно
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.05.2016, 13:06
    #39241358
ferzmikk
ferzmikk
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подключение к excel-листам
anvgСмотря как вы создавали подключение. В уже существующем, если не ошибаюсь, перейти с OleDb на ODBC не получится (не эксперементировал). А так да, по минимальному тексту подключения всё правильно
Получилось. Делал так: в меню "Из других источников" - "С сервера SQL сервер" - выбираю любую базу данных, потом меняю свойства подключении и текст запроса.
Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=C:\Users\User\Desktop\СМ2.xlsm;
Только потом excel изменил запись в подключении
Provider=MSDASQL.1;Persist Security Info=True;Extended Properties="DBQ=C:\Users\User\Desktop\СМ2.xlsm;Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DriverId=1046;MaxBufferSize=2048;PageTimeout=5;"

Тут фишка в том, что если этот файл будет открывать другой пользователь, то ему надо будет изменить строку подключения, а именно, изменить путь к файлу. Такая проблема только макросом решается, который в строке подключения меняет путь к файлу, или есть другие варианты решения?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.05.2016, 13:27
    #39241373
ferzmikk
ferzmikk
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подключение к excel-листам
Обратил внимание. Если в свойствах подключении ставить тип команды "По умолчанию" или "SQL", то таблица формируется. А если ставить "Таблица", то выходит такое сообщение
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.05.2016, 17:35
    #39241595
anvg
anvg
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подключение к excel-листам
Provider=MSDASQL.1;Увы, это слой с ODBC в OLEDB. Чтобы чистый ODBC получить делайте через MS Query, а потом точно также редактируете строку подключения.
Такая проблема только макросом решается, который в строке подключения меняет путь к файлуУвы, да, макросом или "ручками". Можете, попробовать вариант с Power Query, там, используя, таблицу параметров, можно через функцию ЯЧЕЙКА("filename"; A1) в ней получить путь к файлу в котором выполняется запрос, а по нему сконструировать относительный путь к вашему файлу-источнику данных.
"По умолчанию" или "SQL", то таблица формируется. А если ставить "Таблица", то выходит такое сообщение Естественно, вы же запрос помещаете. Оставьте только, например, [ИД1$], тогда будет и "Таблица" работать.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
24.05.2016, 07:39
    #39241829
ferzmikk
ferzmikk
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подключение к excel-листам
anvgProvider=MSDASQL.1;Увы, это слой с ODBC в OLEDB. Чтобы чистый ODBC получить делайте через MS Query, а потом точно также редактируете строку подключения.
DSN=Excel Files;DBQ=C:\Users\User\Desktop\СМ3.xlsm;DefaultDir=C:\Users\User\Desktop;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;

Тип команды - SQL, но тут неактивно поле, то есть нельзя изменить.

Если в свойствах подключении нажать на "Изменить запрос", то вылезает такое сообщение. Как можно изменить запрос в конструкторе?

Почему кнопка "параметры" не активна? Даже бывает не активна, когда подключаешься к кубу. Как еe сделать активной?

Какую роль играет файл подключения?

В интернете мало информации на тему Создание подключений к excel листам, где прописаны такие тонкие логические нюансы
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
24.05.2016, 11:20
    #39242007
ferzmikk
ferzmikk
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подключение к excel-листам
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
24.05.2016, 22:27
    #39242746
anvg
anvg
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подключение к excel-листам
ип команды - SQL, но тут неактивно поле, то есть нельзя изменить.При Odbc подключении другого и нет. А вам зачем?
Я обычно для создания подключения пользуюсь такой заготовкой, а потом допиливаю подключение.
Код: vbnet
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
Public Sub CreateOdbcConnection()
    Const connStr = "ODBC;DBQ=$1;Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};MaxBufferSize=2048;PageTimeout=5;ReadOnly=1;ExtendedAnsiSQL=1;"
    Dim pLO As ListObject, pSheet As Worksheet
    Set pSheet = ThisWorkbook.Worksheets.Add
    Set pLO = pSheet.ListObjects.Add(xlSrcExternal, Replace$(connStr, "$1", ThisWorkbook.FullName), True, xlYes, pSheet.Range("A1"))
    With pLO.QueryTable
        .CommandType = xlCmdSql
        .CommandText = "Select * From [Лист1$]"
        .Refresh
        .WorkbookConnection.Description = "Connection to Лист1"
        .WorkbookConnection.Name = "Query" & Format$(Now, "yyyymmddhhnnss")
    End With
End Sub


Успехов.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.05.2016, 13:46
    #39243204
ferzmikk
ferzmikk
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подключение к excel-листам
anvg,

Обнаружил такую фишку. В запросе присутствует 2 параметра и записываем их как знаками "?".
Код: vbnet
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
Public Sub CreateOdbcConnection()
    Const connStr = "ODBC;DBQ=$1;Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};MaxBufferSize=2048;PageTimeout=5;ReadOnly=1;ExtendedAnsiSQL=1;"
    Dim pLO As ListObject, pSheet As Worksheet
    Set pSheet = ThisWorkbook.Worksheets.Add
    Set pLO = pSheet.ListObjects.Add(xlSrcExternal, Replace$(connStr, "$1", ThisWorkbook.FullName), True, xlYes, pSheet.Range("A1"))
    
    Set Параметр1 = pLO.QueryTable.Parameters.Add("@Параметр1", xlParamTypeVarChar)
    Параметр1.SetParam xlConstant, "Город1"
    
    Set Параметр2 = pLO.QueryTable.Parameters.Add("@Параметр2", xlParamTypeVarChar)
    Параметр2.SetParam xlConstant, "SKU1"    
       
    With pLO.QueryTable
        .CommandType = xlCmdSql
        .CommandText = "SELECT * FROM `ИД1$`  WHERE (`Поле1` = ? AND `Поле2` = ?) "
        .Refresh
        .WorkbookConnection.Description = "Connection to ÈÄ1"
        .WorkbookConnection.Name = "Query" & Format$(Now, "yyyymmddhhnnss")
    End With
End Sub


Какой именно знак "?" соответствует для заданного параметра, определяется последовательность заданных параметров Set Параметр1 и Set Параметр2.

Записывал в тексте параметры не как "?", а как "@Параметр1" и "@Параметр2". Но так почему то не работает.

Как надо правильно записывать в тексте запроса разные параметры (без знаков вопросов)?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.05.2016, 16:09
    #39243341
anvg
anvg
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подключение к excel-листам
Какой именно знак "?" соответствует для заданного параметра, определяется последовательность заданных параметров Set Параметр1 и Set Параметр2.По опыту использования по порядку следования. 1-ый параметр 1-ому ?, второй - второму. С другой стороны с глубокой вложенностью параметров не экспериментировал - есть ли приоритет параметра по положению в основном запросе ли или в подзапросе или главное порядковый номер ? в тексте SQL. Если установите то или другое, сообщите, пожалуйста.
Именованных параметров в Access нет, увы.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.05.2016, 19:01
    #39243452
ferzmikk
ferzmikk
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подключение к excel-листам
anvgесть ли приоритет параметра по положению в основном запросе ли или в подзапросе или главное порядковый номер ?
Это пример простого запроса, на самом деле будет сложным. Разные таблицы на разных листах, в запросе присутствуют подзапросы, несколько параметров. Со временем будут изменяться столбцы, добавляться еще таблицы. Хотелось бы сделать запрос мобильным, чтобы потом легко дорабатывать. Конечно здесь не такого удобного конструктора как в 1С - СКД.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.05.2016, 11:58
    #39243875
ferzmikk
ferzmikk
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подключение к excel-листам
Реально ли с помощью программы SQL Server Management Studio подключиться к excel-листу?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.05.2016, 18:02
    #39244284
anvg
anvg
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подключение к excel-листам
Подключиться не подключиться, а вот брать данные из закрытой книги вполне возможно.
Using OPENROWSET to Read Excel Worksheets from SQL Server: Part 1 – How to Set Up the System , Импорт данных из Excel в SQL Server
Успехов.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
28.05.2016, 18:17
    #39245466
ferzmikk
ferzmikk
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подключение к excel-листам
Вот на двух первых листах присутствуют таблицы с подключением с MDX-запроса. На третьем листе делаю SQL-запрос к этим двум листам. Первые два листа обновляю, потом обновляю третий. После после обновления двух листов данные свежие. Потом обновляю третий лист, но данные берутся из двух листов, но которые были до обновления. Почему так происходит? Как исправить?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
28.05.2016, 18:23
    #39245467
ferzmikk
ferzmikk
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подключение к excel-листам
Даже если менять SQL-запрос, то все равно обращается к данным до обновления
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
30.05.2016, 12:15
    #39246097
ferzmikk
ferzmikk
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подключение к excel-листам
Как сделать корректное обновление?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
30.05.2016, 15:34
    #39246294
ferzmikk
ferzmikk
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подключение к excel-листам
Может надо параметры подключения изменить?

DBQ=C:\Users\User\Desktop\СМ3.xlsm;Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DriverId=1046;ExtendedAnsiSQL=1;MaxBufferSize=2048;PageTimeout=5;ReadOnly=1;
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
31.05.2016, 16:25
    #39247368
ferzmikk
ferzmikk
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подключение к excel-листам
Обратил внимание на следующее.

Если обновить таблицы на первых двух листах с подключением OLAP с помощью MDX-запросов, то данные обновляются корректно. Если обновить лист с SQL-запросом к этим двум листам, то отображаются данные с листов до обновления.

Если обновить таблицы на первых двух листах и программно создать лист с SQL-запросом к этим двум листам, то такая же проблема.

Если добавить новый лист с таблицей с подключением OLAP и также программно создать лист с SQL-запросом, то также все.

Если создать новый файл с такими же таблицами, то данные свежие. Но если в этом новом файле опять обновить данные на первых двух листах, то опять такая же проблема.

Почему так происходит? Как сделать так, чтобы данные можно корректно обновлять без создании новых файлов?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.06.2016, 07:30
    #39249702
ferzmikk
ferzmikk
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подключение к excel-листам
anvgДля обращения к листам Excel используется движок Access, а он года с 97, в плане SQL, не развивается. Там есть только Left Join, Right Join, Inner Join
Может эту тему перенести в "Microsoft Access"?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.06.2016, 09:55
    #39249802
anvg
anvg
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подключение к excel-листам
Доброе время суток
Извиняюсь, давно не заглядывал.
Могу предположить следующее. Не эксперементировал. При обновлении запрос обращается не к текущей открытой книге, а к её версии на диске. Естественно, что там изменений нет.
Поэтому, скорее всего стоит написать макрос, который сначала обновляет mdx запросы, сохраняет книгу, а затем обновляет запрос к листам с результатами mdx запросов. Скорее всего в настройках подключения mdx запросов стоит включить синхронное обновление, иначе сохранение может прервать их обновление, либо каким- то образом отслеживать факт завершения обновления. По RefreshAll есть толпа обсуждений, но, правда, законченного решения проблемы нет. Всё же там можно будет, найти что-то полезное по поводу асинхронного обновления.
Успехов
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.06.2016, 11:43
    #39249948
ferzmikk
ferzmikk
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подключение к excel-листам
anvgСкорее всего в настройках подключения mdx запросов стоит включить синхронное обновление, иначе сохранение может прервать их обновление
Такая строка подключения
Provider=MSOLAP.4;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=profit;Data Source=...;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error
Что тут нужно дописать?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
  1  все
Форумы / Microsoft Office [игнор отключен] [закрыт для гостей] / Подключение к excel-листам / 25 сообщений из 48, страница 1 из 2
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 443ms
Закрыть
start [/forum/topic.php?fid=61&mobile=1&tid=2172918]:
 0ms
get settings:
 9ms
get forum list:
 12ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 50ms
get topic data:
 7ms
get forum data:
 2ms
get page messages:
 52ms
get tp. blocked users:
 1ms
others: 302ms
total:  443ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]