Домашняя страница Undo Do Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Из Excel в Access - быстро! - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
Страница 1 из 11
Модератор форума: _Boroda_, Pelena, Manyasha, SLAVICK 
Мир MS Excel » Вопросы и решения » Excel и другие приложения » Access » Из Excel в Access - быстро! (Скоростной экспорт/импорт)
Из Excel в Access - быстро!
Gustav Дата: Пятница, 08.04.2016, 19:38 | Сообщение № 1
Группа: Друзья
Ранг: Старожил
Сообщений: 1409
Репутация: 541 ±
Замечаний: 0% ±

начинал с Excel 4.0...
На днях участник Djubocco сетовал в соседней теме http://www.excelworld.ru/forum/17-4539-182810-16-1460047613 , что его процедура перемещает 50 тыс. строк из Excel в Access за дикое количество времени, кажется, 167 минут?

Я не успел откликнуться, а тема уже оказалось закрытой... А товарищ Djubocco чего-то замолчал... Но мой альтруистический порыв оказался сильнее - надо ж выручить человека! - поэтому сам возобновлю разговор.

Использование библиотеки ADO в данном случае не очень удачный выбор. В Access существуют собственные, гораздо более эффективные средства экспорта/импорта.

Вот код, исполняемый в Access, которым я сегодня за 2 минуты "всосал" xlsx-файл размером 18 мегайт, содержащий 400 тыс.строк х 11 столбцов:
[vba]
Код
Sub fastImport()
    Access.Application.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "Лист1", "C:\...\...\MyFile.xlsx", True
End Sub
[/vba]"Access.Application" указываю, чтобы было понятно, к чему привязываться, если код будет запускаться извне (из Excel или еще откуда). Внутри Access достаточно начать этот оператор с "DoCmd".


Мой tip box - яд 41001663842605
 
Ответить
СообщениеНа днях участник Djubocco сетовал в соседней теме http://www.excelworld.ru/forum/17-4539-182810-16-1460047613 , что его процедура перемещает 50 тыс. строк из Excel в Access за дикое количество времени, кажется, 167 минут?

Я не успел откликнуться, а тема уже оказалось закрытой... А товарищ Djubocco чего-то замолчал... Но мой альтруистический порыв оказался сильнее - надо ж выручить человека! - поэтому сам возобновлю разговор.

Использование библиотеки ADO в данном случае не очень удачный выбор. В Access существуют собственные, гораздо более эффективные средства экспорта/импорта.

Вот код, исполняемый в Access, которым я сегодня за 2 минуты "всосал" xlsx-файл размером 18 мегайт, содержащий 400 тыс.строк х 11 столбцов:
[vba]
Код
Sub fastImport()
    Access.Application.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "Лист1", "C:\...\...\MyFile.xlsx", True
End Sub
[/vba]"Access.Application" указываю, чтобы было понятно, к чему привязываться, если код будет запускаться извне (из Excel или еще откуда). Внутри Access достаточно начать этот оператор с "DoCmd".

Автор - Gustav
Дата добавления - 08.04.2016 в 19:38
anvg Дата: Суббота, 09.04.2016, 18:43 | Сообщение № 2
Группа: Друзья
Ранг: Обитатель
Сообщений: 346
Репутация: 196 ±
Замечаний: 0% ±

2010, 2016
Доброе время суток
гораздо более эффективные средства экспорта/импорта.
У меня аналогичный пример 12 столбцов, 400000 строк, вес файла Excel 33 мегабайта плюс вставка данных в существующую таблицу занял порядка 97 секунд. Office 2016, 64bit.
Но. И с ADODB не всё так плохо, кодом из Excel вставилось тоже самое в существующую таблицу в Access за 301 секунду
[vba]
Код
Public Sub InsertToTable()
    Const lastRow = 400000, lastCol = 12
    Dim pCon As New ADODB.Connection, pRSet As New ADODB.Recordset, vData As Variant
    Dim k As Long, t As Single, i As Long
    t = Timer: k = 0
    pCon.CursorLocation = adUseClient
    pCon.Open "DBQ=c:\Projects\Database2 min.accdb;Driver={Microsoft Access Driver (*.mdb, *.accdb)};DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;ReadOnly=0;ExtendedAnsiSQL=1;"
    pRSet.CursorLocation = adUseClient: pRSet.CursorType = adOpenStatic
    pRSet.Open "Select * From forImport Where FLong1 Is Null", pCon, adOpenStatic, adLockOptimistic
    vData = Range("A2").Resize(lastRow, lastCol).Value
    pCon.BeginTrans
    For i = 1 To lastRow
        k = k + 1
        If (k Mod 10000) = 0 Then Debug.Print k: DoEvents
        pRSet.AddNew
        pRSet(0).Value = vData(i, 1)
        pRSet(1).Value = vData(i, 2)
        pRSet(2).Value = vData(i, 3)
        pRSet(3).Value = vData(i, 4)
        pRSet(4).Value = vData(i, 5)
        pRSet(5).Value = vData(i, 6)
        '
        pRSet(6).Value = vData(i, 7)
        pRSet(7).Value = vData(i, 8)
        pRSet(8).Value = vData(i, 9)
        pRSet(9).Value = vData(i, 10)
        pRSet(10).Value = vData(i, 11)
        pRSet(11).Value = vData(i, 12)
    Next
    pRSet.UpdateBatch: pCon.CommitTrans
    pRSet.Close: pCon.Close
    MsgBox Timer - t
End Sub
[/vba]
Так что у автора закрытого топика скорее всего были индексы в таблице Access, что и приводило к таким "тормозам".
 
Ответить
СообщениеДоброе время суток
гораздо более эффективные средства экспорта/импорта.
У меня аналогичный пример 12 столбцов, 400000 строк, вес файла Excel 33 мегабайта плюс вставка данных в существующую таблицу занял порядка 97 секунд. Office 2016, 64bit.
Но. И с ADODB не всё так плохо, кодом из Excel вставилось тоже самое в существующую таблицу в Access за 301 секунду
[vba]
Код
Public Sub InsertToTable()
    Const lastRow = 400000, lastCol = 12
    Dim pCon As New ADODB.Connection, pRSet As New ADODB.Recordset, vData As Variant
    Dim k As Long, t As Single, i As Long
    t = Timer: k = 0
    pCon.CursorLocation = adUseClient
    pCon.Open "DBQ=c:\Projects\Database2 min.accdb;Driver={Microsoft Access Driver (*.mdb, *.accdb)};DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;ReadOnly=0;ExtendedAnsiSQL=1;"
    pRSet.CursorLocation = adUseClient: pRSet.CursorType = adOpenStatic
    pRSet.Open "Select * From forImport Where FLong1 Is Null", pCon, adOpenStatic, adLockOptimistic
    vData = Range("A2").Resize(lastRow, lastCol).Value
    pCon.BeginTrans
    For i = 1 To lastRow
        k = k + 1
        If (k Mod 10000) = 0 Then Debug.Print k: DoEvents
        pRSet.AddNew
        pRSet(0).Value = vData(i, 1)
        pRSet(1).Value = vData(i, 2)
        pRSet(2).Value = vData(i, 3)
        pRSet(3).Value = vData(i, 4)
        pRSet(4).Value = vData(i, 5)
        pRSet(5).Value = vData(i, 6)
        '
        pRSet(6).Value = vData(i, 7)
        pRSet(7).Value = vData(i, 8)
        pRSet(8).Value = vData(i, 9)
        pRSet(9).Value = vData(i, 10)
        pRSet(10).Value = vData(i, 11)
        pRSet(11).Value = vData(i, 12)
    Next
    pRSet.UpdateBatch: pCon.CommitTrans
    pRSet.Close: pCon.Close
    MsgBox Timer - t
End Sub
[/vba]
Так что у автора закрытого топика скорее всего были индексы в таблице Access, что и приводило к таким "тормозам".

Автор - anvg
Дата добавления - 09.04.2016 в 18:43
Udik Дата: Суббота, 09.04.2016, 18:50 | Сообщение № 3
Группа: Друзья
Ранг: Старожил
Сообщений: 1586
Репутация: 191 ±
Замечаний: 0% ±

Excel 2016 х 64
если код будет запускаться извне (из Excel или еще откуда)

Чет я торможу, если из экселя запустим, то как эксель узнает куда экспортировать, ну там имя базы, таблицы?


вот вам барабан
яд 41001231307558 wm R419131876897
udik1968@gmail.com
 
Ответить
Сообщение
если код будет запускаться извне (из Excel или еще откуда)

Чет я торможу, если из экселя запустим, то как эксель узнает куда экспортировать, ну там имя базы, таблицы?

Автор - Udik
Дата добавления - 09.04.2016 в 18:50
Gustav Дата: Суббота, 09.04.2016, 19:16 | Сообщение № 4
Группа: Друзья
Ранг: Старожил
Сообщений: 1409
Репутация: 541 ±
Замечаний: 0% ±

начинал с Excel 4.0...
если из экселя запустим, то как эксель узнает куда экспортировать, ну там имя базы, таблицы?

Ну, естественно, нужна предварительная подготовка. Типа CreateObject("Access.Application"), OpenDatabase и т.д. Просто открыть файл MDB или новый и писать в него (как в случае с ADO) - недостаточно, т.к. объект DoCmd доступен только в экземпляре Access.

P.S. Примерно такая минимальная болванка:
[vba]
Код
Sub runAccess()
    Set acApp = CreateObject("Access.Application")
    acApp.OpenCurrentDatabase strFileName
    
    Set acDoCmd = acApp.DoCmd
    acDoCmd.TransferSpreadsheet 0, 9, "Лист1", "C:\...\...\MyFile.xlsx", True

    
    acApp.Quit
End Sub
[/vba]


Мой tip box - яд 41001663842605

Сообщение отредактировал Gustav - Суббота, 09.04.2016, 19:46
 
Ответить
Сообщение
если из экселя запустим, то как эксель узнает куда экспортировать, ну там имя базы, таблицы?

Ну, естественно, нужна предварительная подготовка. Типа CreateObject("Access.Application"), OpenDatabase и т.д. Просто открыть файл MDB или новый и писать в него (как в случае с ADO) - недостаточно, т.к. объект DoCmd доступен только в экземпляре Access.

P.S. Примерно такая минимальная болванка:
[vba]
Код
Sub runAccess()
    Set acApp = CreateObject("Access.Application")
    acApp.OpenCurrentDatabase strFileName
    
    Set acDoCmd = acApp.DoCmd
    acDoCmd.TransferSpreadsheet 0, 9, "Лист1", "C:\...\...\MyFile.xlsx", True

    
    acApp.Quit
End Sub
[/vba]

Автор - Gustav
Дата добавления - 09.04.2016 в 19:16
Udik Дата: Суббота, 09.04.2016, 19:20 | Сообщение № 5
Группа: Друзья
Ранг: Старожил
Сообщений: 1586
Репутация: 191 ±
Замечаний: 0% ±

Excel 2016 х 64
нужна предварительная подготовка

Ага, понял :) .


вот вам барабан
яд 41001231307558 wm R419131876897
udik1968@gmail.com
 
Ответить
Сообщение
нужна предварительная подготовка

Ага, понял :) .

Автор - Udik
Дата добавления - 09.04.2016 в 19:20
Мир MS Excel » Вопросы и решения » Excel и другие приложения » Access » Из Excel в Access - быстро! (Скоростной экспорт/импорт)
Страница 1 из 11
Поиск:

Яндекс цитирования
© 2010-2017 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!