Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft Access Новый топик    Ответить
Топик располагается на нескольких страницах: 1 2      [все]
 Импорт Excel таблиц в Access (VBA)  [new]
SiNtez_26
Member

Откуда:
Сообщений: 199
Добрый день, господа.
Вопрос следующий: в Access импортируются таблицы Excel с помощью обычного SELECT'a из листа -
INSERT INTO (Поля Access) SELECT Поля Excel FROM [Excel 12.0 xml;HDR=Yes;IMEX=1;DATABASE=" & currentFilePath & "]

Проблемы возникают, когда в Excel файле попадаются столбцы с нестандартными именами, содержащими в себе, например, точки.
Каким образом можно перед импортом открыть файл, c помощью REPLACE заменить точки на пробелы, и импортировать из уже отредактированного файла, не сохраняя его, чтобы не испортить исходник? Методы типа DoCmd.TransferSpreadsheet и "поячеечного" чтения не подходят, поскольку таблица содержит несколько десятков столбцов и около полумиллиона строк, что делает эти методы не совсем подходящими из-за времени выполнения.
22 июл 13, 14:09    [14599071]     Ответить | Цитировать Сообщить модератору
 Re: Импорт Excel таблиц в Access (VBA)  [new]
Rivkin Dmitry
Member

Откуда: Israel
Сообщений: 5500
Честно - я не спец по Excel, но приглядевшись к коду, я подумал, что надо, просто, игнорировать заголовки столбцов, т.е., вместо
HDR=Yes объявить HDR=No
22 июл 13, 15:02    [14599498]     Ответить | Цитировать Сообщить модератору
 Re: Импорт Excel таблиц в Access (VBA)  [new]
SiNtez_26
Member

Откуда:
Сообщений: 199
В таком случае придется использовать вместо имен столбцов Excel файла F1, F2... и т.д., что неприемлимо, поскольку столбцов слишком много и можно надолго засесть, учитывая что импортить нужно не одну таблицу. Неужели нельзя все сделать проще?
22 июл 13, 15:12    [14599572]     Ответить | Цитировать Сообщить модератору
 Re: Импорт Excel таблиц в Access (VBA)  [new]
SiNtez_26
Member

Откуда:
Сообщений: 199
Да, забыл добавить, что импортить нужно не все столбцы, что усложняет задачу с применением F1, F2...FN
22 июл 13, 15:17    [14599605]     Ответить | Цитировать Сообщить модератору
 Re: Импорт Excel таблиц в Access (VBA)  [new]
Лифчик
Member

Откуда:
Сообщений: 1699
SiNtez_26
перед импортом открыть файл, c помощью REPLACE заменить точки на пробелы, и импортировать из уже отредактированного файла, не сохраняя его, чтобы не испортить исходник?
А нельзя ли во временный файл сохранить с реплейсами и затем уже импортировать?
22 июл 13, 18:15    [14600606]     Ответить | Цитировать Сообщить модератору
 Re: Импорт Excel таблиц в Access (VBA)  [new]
BigSergSSP
Member

Откуда:
Сообщений: 38
SiNtez_26

Каким образом можно перед импортом открыть файл, c помощью REPLACE заменить точки на пробелы, и импортировать из уже отредактированного файла, не сохраняя его, чтобы не испортить исходник?

Методы типа ... "поячеечного" чтения не подходят ...


Тогда не понятно что вы хотите? Нужно ведь не весь файл считывать а только заголовки столбцов и сделать в них REPLACE.
Лично у меня DoCmd.TransferSpreadsheet импортирует файлы в районе 500к строк (порядка 10 полей) за пару десятков секунд, при этом точку заменяет на # автоматом (после импорта можно заменить на любой символ).
22 июл 13, 23:29    [14601566]     Ответить | Цитировать Сообщить модератору
 Re: Импорт Excel таблиц в Access (VBA)  [new]
грязный комплектовщик
Member

Откуда: екб
Сообщений: 130
BigSergSSP
...
Лично у меня DoCmd.TransferSpreadsheet импортирует файлы в районе 500к строк (порядка 10 полей) за пару десятков секунд, при этом точку заменяет на # автоматом (после импорта можно заменить на любой символ).

А как после импорта для всех названий полей в таблице символ # по-быстрому на что-то иное заменить?
23 июл 13, 08:57    [14602132]     Ответить | Цитировать Сообщить модератору
 Re: Импорт Excel таблиц в Access (VBA)  [new]
BigSergSSP
Member

Откуда:
Сообщений: 38
грязный комплектовщик,
Например, вот так:
CurrentDb.TableDefs(Table).Fields("Field#").Name=Replace(CurrentDb.TableDefs(Table).Fields("Field#").Name, "#", "")

Но существует и другие способы
23 июл 13, 17:32    [14605767]     Ответить | Цитировать Сообщить модератору
 Re: Импорт Excel таблиц в Access (VBA)  [new]
BigSergSSP
Member

Откуда:
Сообщений: 38
BigSergSSP
Например, вот так:
CurrentDb.TableDefs(Table).Fields("Field#").Name=Replace(CurrentDb.TableDefs(Table).Fields("Field#").Name, "#", "")

или вот так:
CurrentDb.TableDefs(Table).Fields(I).Name=Replace(CurrentDb.TableDefs(Table).Fields(I).Name, "#", "")
23 июл 13, 17:37    [14605801]     Ответить | Цитировать Сообщить модератору
 Re: Импорт Excel таблиц в Access (VBA)  [new]
грязный комплектовщик
Member

Откуда: екб
Сообщений: 130
BigSergSSP
Например, вот так:
CurrentDb.TableDefs(Table).Fields(I).Name=Replace(CurrentDb.TableDefs(Table).Fields(I).Name, "#", "")

Спс, отлично сработало.
Для вар-тов кучи полей в исходных эксельках самое то.
Sub ReplaceCharInTableName(strTableName As String)
Dim bytTableFieldCount As Byte, i As Byte
bytTableFieldCount = CurrentDb.TableDefs(strTableName).Fields.Count
For i = o To bytTableFieldCount - 1
    CurrentDb.TableDefs(strTableName).Fields(i).Name = Replace(CurrentDb.TableDefs(strTableName).Fields(i).Name, "#", " ")
Next i
'MsgBox CurrentDb.TableDefs(strTableName).Fields.Count
End Sub
24 июл 13, 12:40    [14609698]     Ответить | Цитировать Сообщить модератору
 Re: Импорт Excel таблиц в Access (VBA)  [new]
BigSergSSP
Member

Откуда:
Сообщений: 38
или вот так:
Sub ReplaceCharInTableName(strTableName As String)
Dim i As Byte
For i = 0 To CurrentDb.TableDefs(strTableName).Fields.Count - 1
    CurrentDb.TableDefs(strTableName).Fields(i).Name = Replace(CurrentDb.TableDefs(strTableName).Fields(i).Name, "#", " ")
Next
'MsgBox CurrentDb.TableDefs(strTableName).Fields.Count
End Sub

24 июл 13, 21:00    [14613349]     Ответить | Цитировать Сообщить модератору
 Re: Импорт Excel таблиц в Access (VBA)  [new]
nord-woolf
Member

Откуда: And God I know I'm one
Сообщений: 4894
BigSergSSP
или вот так:
...

Туда-же.
В топку.
:)
зы: Вытаскивайте CurrentDB из цикла, кешируйте ссылку на TableDef.
24 июл 13, 21:52    [14613477]     Ответить | Цитировать Сообщить модератору
 Re: Импорт Excel таблиц в Access (VBA)  [new]
BigSergSSP
Member

Откуда:
Сообщений: 38
nord-woolf
зы: Вытаскивайте CurrentDB из цикла, кешируйте ссылку на TableDef.


Покажи...
25 июл 13, 01:05    [14614099]     Ответить | Цитировать Сообщить модератору
 Re: Импорт Excel таблиц в Access (VBA)  [new]
nord-woolf
Member

Откуда: And God I know I'm one
Сообщений: 4894
Sub ReplaceCharInFieldsName(strTableName As String)
    Dim fld As DAO.Field
    With CurrentDb
        With .TableDefs(strTableName)
            For Each fld In .Fields
                fld.Name = Replace(fld.Name, "#", vbNullString)
            Next
        End With
    End With
End Sub
25 июл 13, 09:17    [14614491]     Ответить | Цитировать Сообщить модератору
 Re: Импорт Excel таблиц в Access (VBA)  [new]
BigSergSSP
Member

Откуда:
Сообщений: 38
nord-woolf,

И в чем принципиальная разница? (кроме оформления кода)
25 июл 13, 17:43    [14617896]     Ответить | Цитировать Сообщить модератору
 Re: Импорт Excel таблиц в Access (VBA)  [new]
СхБд
Guest
BigSergSSP,

сорри
14613477
25 июл 13, 17:59    [14618013]     Ответить | Цитировать Сообщить модератору
 Re: Импорт Excel таблиц в Access (VBA)  [new]
nord-woolf
Member

Откуда: And God I know I'm one
Сообщений: 4894
Если в цифрах, то:
Табличка (пустая) о 10 текстовых полях (в именах нет решеток)
Тестовая процедура:
+
Public Sub Test()
    Dim i As Integer, j As Integer
    Dim st As Long
    j = 100
    
    Debug.Print String(10, "-")
    st = apiTimeGetTime
    For i = 1 To j
        ReplaceCharInTableName "tbl1"
    Next i
    Debug.Print "ReplaceCharInTableName", apiTimeGetTime - st
    
    Debug.Print String(10, "-")
    st = apiTimeGetTime
    For i = 1 To j
        ReplaceCharInFieldsName "tbl1"
    Next i
    Debug.Print "ReplaceCharInFieldsName", apiTimeGetTime - st
End Sub

Результаты четырех замеров:
----------
ReplaceCharInTableName       2875 
----------
ReplaceCharInFieldsName      437 
----------
ReplaceCharInTableName       3062 
----------
ReplaceCharInFieldsName      454 
----------
ReplaceCharInTableName       2921 
----------
ReplaceCharInFieldsName      469 
----------
ReplaceCharInTableName       3047 
----------
ReplaceCharInFieldsName      610 

Так принципиальная разница заметнее? :)
25 июл 13, 18:18    [14618098]     Ответить | Цитировать Сообщить модератору
 Re: Импорт Excel таблиц в Access (VBA)  [new]
BigSergSSP
Member

Откуда:
Сообщений: 38
СхБд
BigSergSSP,

сорри
14613477


По существу, пожалуйста...
25 июл 13, 18:18    [14618099]     Ответить | Цитировать Сообщить модератору
 Re: Импорт Excel таблиц в Access (VBA)  [new]
СхБд
Guest
BigSergSSP
СхБд
BigSergSSP,

сорри
14613477


По существу, пожалуйста...

а чо там не посуществу

чем плохо
(с) ...Вытаскивайте CurrentDB из цикла....
25 июл 13, 18:27    [14618134]     Ответить | Цитировать Сообщить модератору
 Re: Импорт Excel таблиц в Access (VBA)  [new]
BigSergSSP
Member

Откуда:
Сообщений: 38
nord-woolf,

Убедительно! :)) Спасибо
25 июл 13, 18:41    [14618186]     Ответить | Цитировать Сообщить модератору
 Re: Импорт Excel таблиц в Access (VBA)  [new]
ИВП
Member

Откуда:
Сообщений: 389
Импортирую данные из Ёкселевского файла "Файл.xls" с листа "Лист"
в таблицу "Табл" базы MSA таким запросом:


s = "Insert into Табл select * from [Лист$A1:D500] IN 'Файл.xls' [Excel 5.0;HDR=YES;IMEX=1] where Поле1 > 0
CurrentDb.Execute s


Всё работает прекрасно.
Не могу придумать, как в этом же запросе в дополнительное поле "ДатаВремя" таблицы "Табл"
записать дату/время выполнения этого запроса, т.е. дату/время импорта данных.
Что нужно добавить в текст запроса, чтобы во все записи таблицы "Табл"
в поле "ДатаВремя" заносилось значение функции Now()?
26 июл 13, 16:35    [14623374]     Ответить | Цитировать Сообщить модератору
 Re: Импорт Excel таблиц в Access (VBA)  [new]
Анатолий ( Киев )
Guest
s = "Insert into Табл select *, Now() As ДатаВремя from [Лист$A1:D500] IN 'Файл.xls' [Excel 5.0;HDR=YES;IMEX=1] where Поле1 > 0
CurrentDb.Execute s
29 июл 13, 11:40    [14631022]     Ответить | Цитировать Сообщить модератору
 Re: Импорт Excel таблиц в Access (VBA)  [new]
ИВП
Member

Откуда:
Сообщений: 389
То
Анатолий ( Киев )!

Два дня ждал отклика от ДОБРЫХ людей!
Спасибо, всё получилось!!!!!!!!!!!!!
30 июл 13, 00:50    [14635155]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: Импорт Excel таблиц в Access (VBA)  [new]
sv1mmen
Member

Откуда:
Сообщений: 8
ИВП,

Импортирую данные из Ёкселевского файла "Файл.xls" с листа "Лист"
в таблицу "Табл" базы MSA таким запросом:


s = "Insert into Табл select * from [Лист$A1:D500] IN 'Файл.xls' [Excel 5.0;HDR=YES;IMEX=1] where Поле1 > 0
CurrentDb.Execute s

Мне нужно создать запрос в VBA на добавление данных их таблицы Excell в Acces. Используя ваш вышеперечисленный способ выходит ошибка "Object required" Что не так делаю?
26 июн 19, 14:09    [21915575]     Ответить | Цитировать Сообщить модератору
 Re: Импорт Excel таблиц в Access (VBA)  [new]
Кривцов Анатолий
Member

Откуда:
Сообщений: 580
Вы лучше покажите реальную строку SQL вашего запроса.
Вы ее в конструктор запросов вставляли? Список полей видели? Открыть как таблицу пробовали?
Может пример файла пришлете для опытов?
26 июн 19, 14:22    [21915590]     Ответить | Цитировать Сообщить модератору
 Re: Импорт Excel таблиц в Access (VBA)  [new]
Кривцов Анатолий
Member

Откуда:
Сообщений: 580
sv1mmen, у вас реально IN 'Файл.xls' или там полный путь к файлу?
26 июн 19, 14:27    [21915596]     Ответить | Цитировать Сообщить модератору
 Re: Импорт Excel таблиц в Access (VBA)  [new]
sv1mmen
Member

Откуда:
Сообщений: 8
пример моего нового запроса, взял с этой ветки "Добавление в Access данных Excel модулем" у Im_Max

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "TISBD", "C:\Users\artem.makarchuk\Desktop\Цель 2019\L3 MBH-BS порегионально_new(1).xlsx", True

End Sub

Но запрос выдаёт только ошибки копирования. Либо вообще не копирует.
28 июн 19, 12:30    [21916939]     Ответить | Цитировать Сообщить модератору
 Re: Импорт Excel таблиц в Access (VBA)  [new]
Кривцов Анатолий
Member

Откуда:
Сообщений: 580
sv1mmen
пример моего нового запроса
DoCmd.TransferSpreadsheet acExport, ...
Нифига не въехал! Вы спрашиваете про импорт, а выполняете экспорт. И где здесь запрос?
1 июл 19, 09:49    [21917928]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: 1 2      [все]
Все форумы / Microsoft Access Ответить