Sql запрос к листам excell

@LEonardo_ · Регистрация: 05.06.2015

Author24 — интернет-сервис помощи студентам

Здравствуйте!
Пытаюсь решить задачу сравнения данных на двух листах эксель(на каждом порядка 150 000 записей).
Вцелом задача: найти только записи листа1 которых нет на листе2. Нет на листе2 значит, что нет совпадений 11 из 20 полей записи.
Использую sql запрос к листам книги, вот такой примерно(очень его упростил уже пытаясь разобраться, но самостоятельно не вышло)

Visual Basic

sSql = "select * " & _
                "from [" & s1.Name & "$] t1, [" & s2.Name & "$] t2," & _
                "t1 left join t2 on (t1.Фамилия=t2.Фамилия and t1.Имя=t2.Имя and cdbl(t1.Номер)=cdbl(t2.Номер)) " & _
                "where t2.Фамилия is null"

а выполняется он вот так:

Кликните здесь для просмотра всего текста

Visual Basic

    ...
    Set rs = CreateObject("ADODB.Recordset")
    Set cn = CreateObject("ADODB.Connection")
    FieldName="Yes"
    Select Case CLng(Split(Application.Version, ".")(0))
        Case Is < 12
            sCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FPath _
            & ";Extended Properties=""Excel 8.0;HDR=" & FieldName & ";IMEX=1"";"
        Case Else
            sCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & FPath _
            & ";Extended Properties=""Excel 12.0;HDR=" & FieldName & ";IMEX=1"";"
    End Select
    
    cn.Open sCon
    If Not cn.State = 1 Then Exit Sub
    
    Set rs = cn.Execute(sSql)
    OutPutFieldsName=true
    If OutPutFieldsName Then
        For i = 0 To rs.fields.Count - 1
            OutPutRange.Offset(0, i) = rs.fields(i).Name
        Next
        Set OutPutRange = OutPutRange.Offset(1, 0)
    End If
    OutPutRange.CopyFromRecordset rs
    rs.Close: cn.Close
    Set cn = Nothing: Set rs = Nothing
    ...

ядро используется ms.jet
и все хорошо выполняется, все как мне нужно
Однако в результате получаем таблицу с огромной кучей колонок, которые вообщем-то не нужны
Однако если я пишу запрос определяя поля для вывода

Visual Basic

sSql = "select t1.Серия,t1.Фамилия,t2.Фамилия,t1.номер,t2.номер " & _
                "from [" & s1.Name & "$] t1, [" & s2.Name & "$] t2," & _
                "t1 left join t2 on (t1.Фамилия=t2.Фамилия and t1.Имя=t2.Имя and cdbl(t1.Номер)=cdbl(t2.Номер)) " & _
                "where t2.Фамилия is null"

то получаю пустой результат.
По работе left join он должен заполнять поля, которые не нашел значениями null, как он это делает если запрашивать все (select * ...), но тут убрав условие вообще я вижу, что в итоговую таблицу просто не вошли те строки, совпадения по которым не найдено, то есть выводится меньше строк чем в начальной таблице...

честно, я не понимаю, что за хрень, помогите пожалуйста разобраться....

еще странные вещи вот в чем: select * выдает мои несколько строчек разницы, а select count(*) считает, что результатов запроса 0

Возможно это надо в ветку про sql однако чутье мне подсказывает, что есть какая-то особенность именно в экселе, с sql запросами к которому я относительно недавно познакомился

Так же, если есть идеи по альтернативному решению задачи, буду рад!
Спасибо!

mobile · 28.03.2017, 20:03

Попробуйте с подзапросами

Visual Basic

sSql = "select t1.Серия,t1.Фамилия,t2.Фамилия,t1.номер,t2.номер " & _
                "from " & _
                "(select * from [" & s1.Name & "$] ) t1 " & _
                "left join " & _
                "(select * from [" & s2.Name & "$] ) t2 " & _
                "on (t1.Фамилия=t2.Фамилия and t1.Имя=t2.Имя and t1.Номер=t2.Номер) " & _
                "where t2.Фамилия is null"

@snipe · 29.03.2017, 03:26

если условие задачи стоит конкретно
найти только записи листа1 которых нет на листе2
то можно воспользоваться Not In() в условии запроса, предварительно объединив столбцы поиска в одно слово
выглядеть будет примерно так

Visual Basic

sSql = "select t1.Серия,t1.Фамилия, t1.номер " & _
                " from [" & s1.Name & "$] ) as t1 " & _
                where (t1.Фамилия & t1.номер) not in "_
                "(select t2.Фамилия & t2.номер as index from [" & s2.Name & "$] ) as t2) "

по поводу

Сообщение от LEonardo_

Однако если я пишу запрос определяя поля для вывода
.......
то получаю пустой результат.

тут вот какая штука

Visual Basic

Case Is < 12
            sCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FPath _
            & ";Extended Properties=""Excel 8.0;HDR=" & FieldName & ";IMEX=1"";"
        Case Else
            sCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & FPath _
            & ";Extended Properties=""Excel 12.0;HDR=" & FieldName & ";IMEX=1"";"

вот это значение HDR=" & FieldName &
оно должно быть либо Yes либо No
(т.е. выглядеть так HDR=Yes или HDR=No)
при Yes первая строка содержит названия столбцов при No - нет
и если HDR вдруг стало No а вы в запросе указываете имена конкретных столбцов то вполне реально получить тот результат что оказался у вас перед глазами

@LEonardo_ · 29.03.2017, 10:28 **[ТС]**

Сообщение от snipe

и если HDR вдруг стало No а вы в запросе указываете имена конкретных столбцов

так ведь прямо указываю, без вариантов:

Сообщение от LEonardo_

Visual Basic

1	FieldName="Yes"

Сообщение от snipe

то можно воспользоваться Not In()

да, я рассматривал вариант этот, только оно не правильно работает если появляются значения null. ну и реальный запрос он намного сложнее... вроде бы Join с ms jet работает быстрей...

опытным путем установлено, что проблема была в работе cdbl(). это одно из важнейших полей было, но в одной таблице это строка типа 0000123456789 а в другой целое число 123456789. заменил на странную конструкцию типа cstr(t1.номер*1) и заработало(может есть еще варианты?)...

на самом деле забавно... но ответ искать не хочется, тк я не знаю способа отладить sql запрос из vba...
в целом меня устраивает, обрабатывает мои листы с 150 000 строк, 20 полями по 11 из которых сравнение меньше минуты

Дополнительно, если можно,
Можете посоветовать где почитать про sql запросы из vba(интересует запуск pl/sql конструкций для бд oracle 11 из vba а так же впринципе возможности работы с книгой эксель как с таблицами бд(ну вот примерно как в вопросе топика))

за ответы Спасибо

	29.03.2017, 10:28

Опции темы