Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Импорт таблицы Excel через SQL запрос в Microsoft Query с преобразованием данных на лету
 
#1
16.01.2026 20:29:02
Всем привет! Помогите пожалуйста решить такую задачу. Поскольку речь идет про SQL запрос то опишу путь файлов. Для простоты на диске размещена папка C:\Test\  В папке два файла: Source.xlsx - Файл Источник и Import.xlsx - Файл импортирующий данные с встроенным SQL запросом.

Файл Source.xlsx на листе "Источник" имеет Таблицу "Data1", причем таблица с заголовком размещена не на первой строке листа, а на строку ниже, что несколько усложняет импорт данных с листа, т.к. стоит задача импортировать только таблицу "Data1", а не все данные с листа. Также выяснилось, что "Data1" в перовом столбце с заголовком [Дата] имеет изъян, а именно дата в ячейках содержится в текстовом формате, то есть воспринимается Экселем как текст, а не как дата.

В итоге стоит задача импортировать данные из книги Source.xlsx непосредственно из таблицы "Data1" , а не со всего листа в книгу Import.xlsx на Лист1.
При этом требуется преобразование данных в столбце [Дата] из текстовой строки в формат даты (типа 16.01.2026)  

Я немного понимаю VBA, но в SQL полнейший ноль.

С помощью "гугл окей" и Microsoft Query максимум, что мог родить такие параметры

Строка подключения
DSN=Excel Files;DBQ=C:\Test\Source.xlsx;DefaultDir=C:\Test;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;

Тип команды SQL

Текст команды SELECT * FROM [Источник$A2:F30] - только так получилось импортировать полноценную таблицу с заголовком, хотя признаю "топорность" метода.

Как прописать текст команды SQL для импорта данных только из  "Data1" (что то типа SELECT * FROM [Data1])не указывая явно диапазон как это сделано выше и произвести преобразование текста в столбце [Дата] в дату (что то типа CAST ("Data1"[Дата]).

Я знаю как это сделать в Power Query на раз-два, но пользуюсь Excel 2007, так что нужен SQL запрос. И еще пожалуйста, кто нибудь сориентируйте есть ли какой нибудь краткий справочник по синтаксису SQL именно для связки c Excel? Пока гуглил выяснилось что язык SQL имеет несколько разновидностей (SQL Lite, PostgreSQL, MySQL...) , какой именно SQL используется для построения запросов в среде Excel?

Файлы во вложении.


 
Изменено: Excelman - 16.01.2026 20:31:43
 
 
 
#2
17.01.2026 11:32:10
Excelman, Доброго времени суток. НЕЛЬЗЯ получить ListObject через ODBC-подключение к Excel-файлу, об этом можно в сети много иформации найти как и через ADODB.подключение. Только с указанием листа и диапазона
Код
"SELECT * FROM [Источник$A2:F]"
если ниже вашей таблицы нет других данных. Ну а ессли есть то тогда фиксированный диапазон
Код
"SELECT * FROM [Источник$A2:F29]"
Изменено: MikeVol - 17.01.2026 11:33:45
 
 
 
#3
17.01.2026 11:51:55
Спасибо за информацию! На забугорных форумах видел что средствами SQL как то получают адрес занимаего пространства, может там не ListObject и просто именованный диапазон был  и на основании этого делалась выборка Select. Ну да ладно, в принципе явное указание адреса диапазона пока не ставило для меня проблем. Но вопрос с конвертированием текста в число остался, (текст в дату).
 
 
 
#4
17.01.2026 11:53:05
MikeVol, это вам спасибо
Изменено: Excelman - 17.01.2026 11:54:35
 
 
 
#5
17.01.2026 12:20:49
Цитата
Excelman написал:
Но вопрос с конвертированием текста в число остался, (текст в дату).
Пробуйте так
Код
    Dim SQL         As String
    SQL = _
            "SELECT " & _
            "    *, " & _
            "    DateSerial( " & _
            "        Mid([Дата],7,4), " & _
            "        Mid([Дата],4,2), " & _
            "        Mid([Дата],1,2) " & _
            "    ) AS Дата " & _
            "FROM [Источник$A2:F]"
Возможно это поможет а может нет. Полный код будет таким
Код
Option Explicit

Sub Import_From_Data1_ADO()
    On Error GoTo Whoa
    Application.ScreenUpdating = False

    Dim dbPath      As String
    dbPath = ThisWorkbook.Path & "\Source.xlsx"

    Dim cnn         As ADODB.Connection
    Set cnn = New ADODB.Connection

    cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
            "Data Source=" & dbPath & ";" & _
            "Extended Properties='Excel 12.0 Xml;HDR=Yes;IMEX=1';"

    Dim SQL         As String
    SQL = _
            "SELECT " & _
            "    *, " & _
            "    DateSerial( " & _
            "        Mid([Дата],7,4), " & _
            "        Mid([Дата],4,2), " & _
            "        Mid([Дата],1,2) " & _
            "    ) AS Дата " & _
            "FROM [Источник$A2:F]"

    Dim rst         As ADODB.Recordset
    Set rst = New ADODB.Recordset

    rst.Open SQL, cnn, adOpenStatic, adLockReadOnly

    If rst.EOF Then
        MsgBox "В таблице Data1 нет записей.", vbCritical
        GoTo LetsContinue
    End If

    With ThisWorkbook.Worksheets("Лист1").ListObjects("Таблица_Запрос_из_Excel_Files")

        If Not .DataBodyRange Is Nothing Then
            .DataBodyRange.Delete
        End If

        .Range(2, 1).CopyFromRecordset rst

        Dim dateColIndex As Long
        dateColIndex = .ListColumns("Дата").Index
        
        .ListColumns(dateColIndex).DataBodyRange.NumberFormat = "dd.mm.yyyy"
        .Range.Columns.AutoFit
    End With

LetsContinue:
    On Error Resume Next
    rst.Close
    cnn.Close
    Set rst = Nothing
    Set cnn = Nothing
    Application.ScreenUpdating = True
    On Error GoTo 0
    Exit Sub

Whoa:
    MsgBox "Error Description: " & Err.Description & vbCrLf & _
            "Error Number: " & Err.Number, vbCritical
    Resume LetsContinue
End Sub
Открой редактор VBA → Tools → References, Поставь галочку:Microsoft ActiveX Data Objects 6.1 Library (или 2.8 / 6.0 — не критично) и нажми OK.
 
 
 
#6
17.01.2026 12:26:44
Всех приветствую. Может кому пригодится.
Цитата
написал:
Только с указанием листа и диапазона
Можно использовать именованный диапазон для выгрузки. Но нужно сначала сделать манипуляцию в файле Source, а именно: через vba создать имя и привязать к умной таблице, что то типа (для ручного режима не работает):
Код
Sub d()
    Dim tbl As ListObject
    Set tbl = ActiveSheet.ListObjects(1)
    Dim ws As Worksheet
    Set ws = ActiveSheet
    ws.Names.Add Name:="Источник!data_nm", RefersTo:=tbl.Range
End Sub
А потом уже можно использовать "SELECT * FROM [Источник$data_nm]"
Код
Sub tst()

    Dim WorkbookAddressList As String
    WorkbookAddressList = ThisWorkbook.Path & "\Source.xlsx"
    
    Dim conn As Object
    Set conn = CreateObject("ADODB.Connection")
    conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
            WorkbookAddressList & _
                    ";Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
    
    On Error GoTo CloseConnection
    
    Dim query As String
    query = "SELECT * FROM [Источник$data_nm]"
    
    Dim rs As Object
    Set rs = CreateObject("ADODB.Recordset")
    
    '    Set rs = conn.OpenSchema(adSchemaTables)
    
    rs.Open query, conn
    
    On Error GoTo CloseRecordset
    
    Worksheets("Лист1").Range("A1").CopyFromRecordset rs
    
    
    On Error GoTo 0
    
CloseRecordset:
    rs.Close
    
CloseConnection:
    conn.Close
    
    Set conn = Nothing
    Set rs = Nothing

    
End Sub
 
 
 
#7
19.01.2026 18:29:57
MikeVol, Alex, спасибо вам большое за помощь! Извините, что сразу не ответил.  Времени не хватает (впрочем, как и у всех в этой жизни). Alex, ваш вариант интересен, но, к сожалению, не подойдет, поскольку файл источника в реале доступен только с правами на просмотр, никаких редактирований не допускается. Повторюсь, сама концепция интересная.
MikeVol, пробую ваш код для перевода текста в дату(число).
Изменено: Excelman - 19.01.2026 18:31:21
 
 
 
Страницы: 1
Читают тему
Наверх