Гость
Войти | Профиль | Очистить
Действия ...
Форумы / Microsoft Access [игнор отключен] [закрыт для гостей] / Импорт Excel таблиц в Access (VBA) / 25 сообщений из 28, страница 1 из 2
  1  все
22.07.2013, 14:09
    #38339026
SiNtez_26
SiNtez_26
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Импорт Excel таблиц в Access (VBA)
Добрый день, господа.
Вопрос следующий: в Access импортируются таблицы Excel с помощью обычного SELECT'a из листа -
Код: sql
1.
INSERT INTO (Поля Access) SELECT Поля Excel FROM [Excel 12.0 xml;HDR=Yes;IMEX=1;DATABASE=" & currentFilePath & "]


Проблемы возникают, когда в Excel файле попадаются столбцы с нестандартными именами, содержащими в себе, например, точки.
Каким образом можно перед импортом открыть файл, c помощью REPLACE заменить точки на пробелы, и импортировать из уже отредактированного файла, не сохраняя его, чтобы не испортить исходник? Методы типа DoCmd.TransferSpreadsheet и "поячеечного" чтения не подходят, поскольку таблица содержит несколько десятков столбцов и около полумиллиона строк, что делает эти методы не совсем подходящими из-за времени выполнения.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
22.07.2013, 15:02
    #38339166
Rivkin Dmitry
Rivkin Dmitry
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Импорт Excel таблиц в Access (VBA)
Честно - я не спец по Excel, но приглядевшись к коду, я подумал, что надо, просто, игнорировать заголовки столбцов, т.е., вместо
HDR=Yes объявить HDR=No
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
22.07.2013, 15:12
    #38339188
SiNtez_26
SiNtez_26
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Импорт Excel таблиц в Access (VBA)
В таком случае придется использовать вместо имен столбцов Excel файла F1, F2... и т.д., что неприемлимо, поскольку столбцов слишком много и можно надолго засесть, учитывая что импортить нужно не одну таблицу. Неужели нельзя все сделать проще?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
22.07.2013, 15:17
    #38339200
SiNtez_26
SiNtez_26
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Импорт Excel таблиц в Access (VBA)
Да, забыл добавить, что импортить нужно не все столбцы, что усложняет задачу с применением F1, F2...F N
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
22.07.2013, 18:15
    #38339541
Лифчик
Лифчик
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Импорт Excel таблиц в Access (VBA)
SiNtez_26 перед импортом открыть файл, c помощью REPLACE заменить точки на пробелы, и импортировать из уже отредактированного файла, не сохраняя его, чтобы не испортить исходник? А нельзя ли во временный файл сохранить с реплейсами и затем уже импортировать?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
22.07.2013, 23:29
    #38339798
BigSergSSP
BigSergSSP
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Импорт Excel таблиц в Access (VBA)
SiNtez_26
Каким образом можно перед импортом открыть файл, c помощью REPLACE заменить точки на пробелы, и импортировать из уже отредактированного файла, не сохраняя его, чтобы не испортить исходник?

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

Тогда не понятно что вы хотите? Нужно ведь не весь файл считывать а только заголовки столбцов и сделать в них REPLACE.
Лично у меня DoCmd.TransferSpreadsheet импортирует файлы в районе 500к строк (порядка 10 полей) за пару десятков секунд, при этом точку заменяет на # автоматом (после импорта можно заменить на любой символ).
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.07.2013, 08:57
    #38339918
грязный комплектовщик
грязный комплектовщик
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Импорт Excel таблиц в Access (VBA)
BigSergSSP...
Лично у меня DoCmd.TransferSpreadsheet импортирует файлы в районе 500к строк (порядка 10 полей) за пару десятков секунд, при этом точку заменяет на # автоматом (после импорта можно заменить на любой символ).
А как после импорта для всех названий полей в таблице символ # по-быстрому на что-то иное заменить?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.07.2013, 17:32
    #38341024
BigSergSSP
BigSergSSP
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Импорт Excel таблиц в Access (VBA)
грязный комплектовщик,
Например, вот так:
Код: vbnet
1.
CurrentDb.TableDefs(Table).Fields("Field#").Name=Replace(CurrentDb.TableDefs(Table).Fields("Field#").Name, "#", "")


Но существует и другие способы
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.07.2013, 17:37
    #38341034
BigSergSSP
BigSergSSP
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Импорт Excel таблиц в Access (VBA)
BigSergSSPНапример, вот так:
Код: vbnet
1.
CurrentDb.TableDefs(Table).Fields("Field#").Name=Replace(CurrentDb.TableDefs(Table).Fields("Field#").Name, "#", "")


или вот так:
Код: vbnet
1.
CurrentDb.TableDefs(Table).Fields(I).Name=Replace(CurrentDb.TableDefs(Table).Fields(I).Name, "#", "")
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
24.07.2013, 12:40
    #38341928
грязный комплектовщик
грязный комплектовщик
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Импорт Excel таблиц в Access (VBA)
BigSergSSPНапример, вот так:
Код: vbnet
1.
CurrentDb.TableDefs(Table).Fields(I).Name=Replace(CurrentDb.TableDefs(Table).Fields(I).Name, "#", "")


Спс, отлично сработало.
Для вар-тов кучи полей в исходных эксельках самое то.
Код: vbnet
1.
2.
3.
4.
5.
6.
7.
8.
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
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
24.07.2013, 21:00
    #38342876
BigSergSSP
BigSergSSP
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Импорт Excel таблиц в Access (VBA)
или вот так:
Код: vbnet
1.
2.
3.
4.
5.
6.
7.
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
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
24.07.2013, 21:52
    #38342903
nord-woolf
nord-woolf
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Импорт Excel таблиц в Access (VBA)
BigSergSSPили вот так:
...
Туда-же.
В топку.
:)
зы: Вытаскивайте CurrentDB из цикла, кешируйте ссылку на TableDef.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.07.2013, 01:05
    #38343026
BigSergSSP
BigSergSSP
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Импорт Excel таблиц в Access (VBA)
nord-woolfзы: Вытаскивайте CurrentDB из цикла, кешируйте ссылку на TableDef.

Покажи...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.07.2013, 09:17
    #38343168
nord-woolf
nord-woolf
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Импорт Excel таблиц в Access (VBA)
Код: vbnet
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
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
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.07.2013, 17:43
    #38344101
BigSergSSP
BigSergSSP
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Импорт Excel таблиц в Access (VBA)
nord-woolf,

И в чем принципиальная разница? (кроме оформления кода)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.07.2013, 17:59
    #38344141
СхБд
СхБд
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Импорт Excel таблиц в Access (VBA)
BigSergSSP,

сорри
14613477
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.07.2013, 18:18
    #38344174
nord-woolf
nord-woolf
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Импорт Excel таблиц в Access (VBA)
Если в цифрах, то:
Табличка (пустая) о 10 текстовых полях (в именах нет решеток)
Тестовая процедура:
 
Код: vbnet
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
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


Результаты четырех замеров:
Код: vbnet
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
----------
ReplaceCharInTableName       2875 
----------
ReplaceCharInFieldsName      437 
----------
ReplaceCharInTableName       3062 
----------
ReplaceCharInFieldsName      454 
----------
ReplaceCharInTableName       2921 
----------
ReplaceCharInFieldsName      469 
----------
ReplaceCharInTableName       3047 
----------
ReplaceCharInFieldsName      610


Так принципиальная разница заметнее? :)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.07.2013, 18:18
    #38344175
BigSergSSP
BigSergSSP
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Импорт Excel таблиц в Access (VBA)
СхБдBigSergSSP,

сорри
14613477

По существу, пожалуйста...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.07.2013, 18:27
    #38344186
СхБд
СхБд
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Импорт Excel таблиц в Access (VBA)
BigSergSSPСхБдBigSergSSP,

сорри
14613477

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

чем плохо
(с) ...Вытаскивайте CurrentDB из цикла....
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.07.2013, 18:41
    #38344202
BigSergSSP
BigSergSSP
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Импорт Excel таблиц в Access (VBA)
nord-woolf,

Убедительно! :)) Спасибо
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.07.2013, 16:35
    #38345611
ИВП
ИВП
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Импорт Excel таблиц в Access (VBA)
Импортирую данные из Ёкселевского файла "Файл.xls" с листа "Лист"
в таблицу "Табл" базы MSA таким запросом:
Код: plaintext
1.
2.
3.
s = "Insert into Табл select * from [Лист$A1:D500]  IN 'Файл.xls' [Excel 5.0;HDR=YES;IMEX=1] where Поле1 > 0 
CurrentDb.Execute s

Всё работает прекрасно.
Не могу придумать, как в этом же запросе в дополнительное поле "ДатаВремя" таблицы "Табл"
записать дату/время выполнения этого запроса, т.е. дату/время импорта данных.
Что нужно добавить в текст запроса, чтобы во все записи таблицы "Табл"
в поле "ДатаВремя" заносилось значение функции Now()?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
29.07.2013, 11:40
    #38347141
Анатолий ( Киев )
Анатолий ( Киев )
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Импорт Excel таблиц в Access (VBA)
s = "Insert into Табл select *, Now() As ДатаВремя from [Лист$A1:D500] IN 'Файл.xls' [Excel 5.0;HDR=YES;IMEX=1] where Поле1 > 0
CurrentDb.Execute s
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
30.07.2013, 00:50
    #38348032
ИВП
ИВП
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Импорт Excel таблиц в Access (VBA)
То
Анатолий ( Киев )!

Два дня ждал отклика от ДОБРЫХ людей!
Спасибо, всё получилось!!!!!!!!!!!!!
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Период между сообщениями больше года.
26.06.2019, 14:09
    #39830732
sv1mmen
sv1mmen
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Импорт Excel таблиц в Access (VBA)
ИВП,

Импортирую данные из Ёкселевского файла "Файл.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" Что не так делаю?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.06.2019, 14:22
    #39830741
Кривцов Анатолий
Кривцов Анатолий
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Импорт Excel таблиц в Access (VBA)
Вы лучше покажите реальную строку SQL вашего запроса.
Вы ее в конструктор запросов вставляли? Список полей видели? Открыть как таблицу пробовали?
Может пример файла пришлете для опытов?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
  1  все
Форумы / Microsoft Access [игнор отключен] [закрыт для гостей] / Импорт Excel таблиц в Access (VBA) / 25 сообщений из 28, страница 1 из 2
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 442ms
Закрыть
start [/forum/topic.php?fid=45&mobile=1&tid=1610624]:
 0ms
get settings:
 10ms
get forum list:
 13ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 27ms
get topic data:
 9ms
get forum data:
 2ms
get page messages:
 60ms
get tp. blocked users:
 1ms
others: 312ms
total:  442ms
x
x
Закрыть


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