Заполнение access из excel vba
Excel: умный импорт из Excel листа в таблицу Access
Всем привет, помогите разобраться с нижеизложенным.
Что Дано:
Есть книга Excel "C:\Data\test.xls". В этой книге есть лист "output", с таблицей данных (первая строка - название полей).
Есть база Access "C:\Data\myData.mdb.xls". В этой базе есть таблица "test" с данными.
Что надо сделать:
Необходимо создать макрос в Excel, который бы вставлял данные из листа output, в таблицу "test", причем если в таблице "test" есть уже такие данные (определяется по трем ключевым полям), то их необходимо заменить. Т.е. не должно быть дублированых записей в таблице "test". Этим макросом я буду пополнять ежедневно данные в таблице "test", либо заменять уже существующие (на верные или скорректированные).
В чем проблема:
Я не знаю как создать таблицу (объект?) с дынными из Excel, что бы:
a. проверить есть ли такие же записи в таблице "test" (если есть, то удалить их из "test" и залить заново)
b. Вставить их в access.
Все это я буду делать (уже делаю) через ADO. Сейчас копаюсь в книгах и инете - не нашел примеров создания таблицы (recordset'а) в Excel, копированием/выделением/селектом диапазона ячеек. Помогите пожалуйста, горит.
Здравствуйте Kokain.
Решать такую задачу со стороны Excel сложнее чем из Access, в котором достаточно выполнить два запроса, один на обновление второй на добавление.
В Вашем варианте можно сделать следующее:
создать рекордсет ADO в Excel,
заполнить его данными из листа "output",
открыть рекордсет с набором записей таблицы "test"
фильтром и перебором всех записей произвести сравнение, для редактирования или добавления записей.
Все эти процедуры и библиотека ADO должны присутствовать в проекте VBA файла xls.
Евгений.
Добрый день,
Получается последовательность такая:
1создать рекордсет ADO в Excel,
2заполнить его данными из листа "output",
22 Подконнектится
3открыть рекордсет с набором записей таблицы "test"
4фильтром и перебором всех записей произвести сравнение, для редактирования или добавления записей.
Как раз проблема с пп. 1 и 2 - я не знаю что прописать.
Здравствуйте Kokain.
Как рекордсет создается и заполняется Вы можете посмотреть в коде.
Также его можно сортировать и передавать в массив (это для примера).
Организовать циклы добавления нужного количества полей, сбора данных листа и заполнения полей рекордсета, надеюсь Вы сможете самостоятельно.
Евгений.
Я подключаюсь к базе на sql server, и мне необходимо исполнить запрос, который хранится в переменной myQuery (string). Проблема в том, что в запросе должно быть условие: where a.actdate=CurrentDate. А так как myQuery это строка, то CurrentDate не подхватывается (переменной CurrentDate (тип Date) я присваиваю значение с листа, например 11.01.2008). Как сделать вытащить дынные за нужную мне дату? Помогите пожалуйста.
P.S: Более менее разобрался с подключением из vba excel к access базам и ms sql server. Спасибо господину Teslenko_EA
Если написать так: where a.actdate=@CurrentDate, то выдет ошибку 'Must declare the variable @CurrentDate'
Kokain писал(а): Я подключаюсь к базе на sql server, и мне необходимо исполнить запрос, который хранится в переменной myQuery (string). Проблема в том, что в запросе должно быть условие: where a.actdate=CurrentDate. А так как myQuery это строка, то CurrentDate не подхватывается (переменной CurrentDate (тип Date) я присваиваю значение с листа, например 11.01.2008). Как сделать вытащить дынные за нужную мне дату? Помогите пожалуйста.
Всем доброго времени суток, появился еще один вопрос:
Что необходимо:
1. Я создаю xlsRecordSet c данными из Excel листа >>
2. затем подключаюсь к базе access; и c помощью sql запроса проверяю существуют ли в базе записи такие же как в xlsRecordSet'e >>
3. Если в базе нет такихже данных, то вставляю их в access.
Вопросы:
- Возможно ли обращаться к xlsRecordSet'у как таблице, то есть написать к ней sqlQuery (как это будет выглядеть)? Если невозможно, то Что надо написать, чтобы вставить в access данные из xlsRecordSet'а?
P.S. Проблему c Date победил, спасибо за оперативные комментарии. ))) на sql сервере был тип datetime (YYYY-MM-DD HH.MM.SS).
Здравствуйте Kokain.
"Возможно ли обращаться . . написать к ней sqlQuery. " - нет, но данные рекордсета можно использовать при построении запроса.
В цикле перебора набора записей, создается строчная переменная содержащая значение поля рекордсета
Макрокоманду ИмпортЭкспортЭлектроннойТаблицы можно использовать для импорта или экспорта данных между текущей базой данных Access (MDB или ACCDB) и файлом электронной таблицы. Вы также можете связать данные в электронной таблице Excel с текущей базой данных Access. Это позволит просматривать данные через Access, при этом не теряя возможность работы с ними в Excel. Кроме того, вы можете связать данные в файле электронной таблицы Lotus 1-2-3, но они также будут доступны в Access только для чтения.
Примечание: В Access 2010 макрокоманда ПреобразоватьЭлектроннуюТаблицу была переименована в ИмпортЭкспортЭлектроннойТаблицы.
Примечание: Эта макрокоманда доступна только для доверенных баз данных.
Настройка
Макрокоманда ИмпортЭкспортЭлектроннойТаблицы имеет следующие аргументы:
Аргумент макрокоманды
Тип преобразования
Нужный тип преобразования. В поле Тип преобразования в разделе Аргументы макрокоманды в окне конструктора макросов можно выбрать значение Импорт, Экспорт или Связь. Значение по умолчанию — Импорт.
Примечание: Тип преобразования Связь не поддерживается для проектов Access (ADP).
Тип электронной таблицы
Тип электронной таблицы для импорта, экспорта или связи. В этом поле можно выбрать один из типов электронной таблицы. Значение по умолчанию — Книга Excel.
Примечание: Можно импортировать данные из WK4-файлов Lotus или связать их (с доступом только для чтения), но нельзя экспортировать данные Access в этот формат. Кроме того, Access больше не поддерживает импорт, экспорт и связывание данных с помощью этой макрокоманды для WKS-файлов Lotus и электронных таблиц Excel версии 2.0. Если вы хотите импортировать или связать данные электронной таблицы в формате Excel версии 2.0 или Lotus WKS, необходимо преобразовать данные электронной таблицы в формат более поздней версии Excel или Lotus 1-2-3, прежде чем импортировать или связывать их.
Имя таблицы
Имя таблицы Access для импорта, экспорта или связывания данных электронной таблицы. Вы также можете ввести имя запроса на выборку Access, из которого следует экспортировать данные. Это обязательный аргумент.
Если для аргумента Тип преобразования выбрано значение Импорт, Access добавит данные электронной таблицы в указанную таблицу, если она уже существует. В противном случае Access создает новую таблицу для данных.
При использовании макрокоманды ИмпортЭкспортЭлектроннойТаблицы невозможно задать данные для экспорта с помощью инструкции SQL. Вместо этого необходимо сначала создать запрос, а затем указать имя запроса в аргументе Имя таблицы.
Имя файла электронной таблицы для импорта, экспорта или связи. Следует указывать полный путь. Это обязательный аргумент.
При экспорте данных из Access создается новая электронная таблица. Если имя файла совпадает с именем существующей электронной таблицы, Access заменяет ее, кроме случая экспорта в книгу Excel версии 5.0 или более поздней. В этом случае Access копирует экспортируемые данные в следующий доступный новый лист в книге.
При импорте или связывании электронной таблицы Excel версии 5.0 или более поздней можно указать отдельный лист с помощью аргумента Диапазон.
С именами полей
Указывает, должна ли первая строка электронной таблицы содержать имена полей. Если выбрано значение Да, Access использует имена в этой строке как имена полей в таблице Access при импорте или связывании электронной таблицы. Если выбрано значение Нет, Access обрабатывает первую строку как обычную строку данных. По умолчанию используется значение Нет.
При экспорте таблицы или запроса на выборку Access в электронную таблицу имена полей записываются в первую строку электронной таблицы независимо от значения этого аргумента.
Диапазон ячеек для импорта или связи. Чтобы импортировать или связать электронную таблицу целиком, оставьте этот аргумент пустым. Вы можете ввести имя диапазона в электронной таблице или указать диапазон ячеек для импорта или связи, например А1:Е25 (обратите внимание, что синтаксис А1..Е25 не работает в Access 97 и более поздних версиях). При импорте или связывании электронной таблицы Excel версии 5.0 или более поздней можно добавить имя листа и восклицательный знак перед диапазоном; например: Бюджет!А1:В7.
Примечание: При экспорте в электронную таблицу необходимо оставить этот аргумент пустым. Если задать диапазон, экспорт завершится сбоем.
Примечания
Вы можете экспортировать данные запросов на выборку Access в электронные таблицы. Access экспортирует результирующий набор запроса, обрабатывая его так же, как таблицу.
Данные электронной таблицы, которые добавляются в существующую таблицу Access, должны соответствовать ее структуре.
Каждое поле в электронной таблице должно иметь тот же тип данных, что и соответствующее поле в таблице Access.
Поля должны следовать в том же порядке (за исключением случая, когда для аргумента С именами полей задано значение Да: в этом случае названия полей в электронной таблице должны совпадать с именами полей в таблице Access).
Вызов данной макрокоманды аналогичен выбору Excel в группе Импорт или Экспорт на вкладке Внешние данные. Вы можете использовать эти команды, чтобы выбрать источник данных (базу данных Access или другого типа, электронную таблицу или текстовый файл). Если выбрана электронная таблица, на экране последовательно появляются диалоговые окна или запускается мастер Access, в котором можно указать имя электронной таблицы и другие параметры. Аргументы макрокоманды ИмпортЭкспортЭлектроннойТаблицы отражают параметры, заданные в этих диалоговых окнах или мастерах.
Примечание: В запросах и фильтрах для связанной электронной таблицы учитывается регистр.
При связывании с электронной таблицей Excel, открытой в режиме редактирования, Access будет ждать окончания работы электронной таблицы Excel в этом режиме, чтобы завершить связывание; при этом нет ограничений по времени.
Чтобы выполнить макрокоманду ИмпортЭкспортЭлектроннойТаблицы в модуле Visual Basic для приложений (VBA), используйте метод TransferSpreadsheet объекта DoCmd.
Метод TransferSpreadsheet выполняет действие TransferSpreadsheet в Visual Basic.
Синтаксис
выражение.TransferSpreadsheet (TransferType, SpreadsheetType, TableName, FileName, HasFieldNames, Range, UseOA)
выражение: переменная, представляющая объект DoCmd.
Параметры
Имя | Обязательный или необязательный | Тип данных | Описание |
---|---|---|---|
TransferType | Необязательный | AcDataTransferType | Нужный тип переноса. Значение по умолчанию — acImport. |
SpreadsheetType | Необязательный | AcSpreadSheetType | Тип электронной таблицы для импорта, экспорта или связи. |
TableName | Необязательный | Variant | Строковое выражение, являющееся именем таблицы Office Access, предназначенной для импорта данных электронной таблицы, экспорта данных электронной таблицы или связывания данных электронной таблицы, или запрос на выборку Access, результаты которого нужно экспортировать в электронную таблицу. |
FileName | Необязательный | Variant | Строковое выражение, являющееся именем и путем электронной таблицы для импорта, экспорта или связывания. |
HasFieldNames | Необязательный | Variant | Используйте значение True (1), чтобы использовать первую строку электронной таблицы в качестве имен полей при импорте или связывании. Используйте значение False (0), чтобы считать первую строку электронной таблицы обычными данными. Если оставить этот аргумент пустым, предполагается, что используется значение по умолчанию (False). При экспорте таблицы или данных запроса на выборку Access в электронную таблицу имена полей записываются в первую строку электронной таблицы независимо от введенного значения этого аргумента. |
Range | Необязательный | Variant | Строковое выражение, являющееся допустимым диапазоном ячеек или именем диапазона в электронной таблице. Этот аргумент применяется только для импорта. Чтобы импортировать электронную таблицу целиком, оставьте этот аргумент пустым. При экспорте в электронную таблицу необходимо оставить этот аргумент пустым. Если ввести диапазон, экспорт завершится сбоем. |
UseOA | Необязательный | Variant | Этот аргумент не поддерживается. |
Примечания
Используйте метод TransferSpreadsheet для импорта или экспорта данных между текущей базой данных Access или проектом Access (ADP) и файлом электронной таблицы. Вы также можете связать данные в электронной таблице Excel с текущей базой данных Access. Это позволит просматривать и изменять данные электронной таблицы с помощью Access, при этом не теряя возможность полного доступа к ним в Excel. Кроме того, вы можете связать данные в файле электронной таблицы Lotus 1-2-3, но они будут доступны в Access только для чтения.
Также можно использовать объекты данных ActiveX (ADO) для создания связи с помощью свойства ActiveConnection для объекта Recordset.
Пример
В следующем примере импортируются данные из указанного диапазона электронной таблицы Lotus Newemps.wk3 в таблицу Employees (Сотрудники) Access. В качестве имен полей используется первая строка электронной таблицы.
Поддержка и обратная связь
Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.
Существуют различные способы переноса данных из книги Excel в базы данных Access. Можно скопировать данные с открытого листа и вставить их в таблицу Access, импортировать лист в новую или существующую таблицу либо связать лист с базой данных Access.
В этой статье приведено подробное описание процедуры импорта или связывания данных Excel с классическими базами данных Access.
В этой статье
Общее представление об импорте данных из Excel
Если требуется сохранить данные одного или нескольких листов Excel в Access, следует импортировать содержимое листа в новую или существующую базу данных Access. При импорте данных в Access создается их копия в новой или существующей таблице, а исходный лист Excel не изменяется.
Стандартные сценарии импорта данных Excel в Access
Опытному пользователю Excel требуется использовать Access для работы с данными. Для этого необходимо переместить данные из листов Excel в одну или несколько новых таблиц Access.
В отделе или рабочей группе используется Access, но иногда данные поступают в формате Excel, и их необходимо объединять с базами данных Access. Требуется выполнить импорт полученных листов Excel в базу данных.
Пользователь применяет Access для управления данными, однако получает еженедельные отчеты от остальных участников команды в виде книг Excel. Требуется организовать процесс импорта таким образом, чтобы данные импортировались в базу данных каждую неделю в заданное время.
Первый импорт данных из Excel
Сохранить книгу Excel в виде базы данных Access невозможно. В Excel не предусмотрена функция создания базы данных Access с данными Excel.
При открытии книги Excel в Access (для этого следует открыть диалоговое окно Открытие файла, выбрать в поле со списком Тип файлов значение Файлы Microsoft Office Excel и выбрать файл) создается ссылка на эту книгу, но данные из нее не импортируются. Связывание с книгой Excel кардинально отличается от импорта листа в базу данных. Дополнительные сведения о связывании см. ниже в разделе Связывание с данными Excel.
Импорт данных из Excel
В этом разделе описано, как подготовиться к операции импорта, выполнить ее и как сохранить параметры импорта в виде спецификации для повторного использования. Помните, что данные можно одновременно импортировать только из одного листа. Импортировать все данные из книги за один раз невозможно.
Подготовка листа
Найдите исходный файл и выделите лист с данными, которые требуется импортировать в Access. Если необходимо импортировать лишь часть данных листа, можно задать именованный диапазон, содержащий только те ячейки, которые требуется импортировать.
Определение именованного диапазона (необязательно)
Перейдите в Excel и откройте лист, данные из которого нужно импортировать.
Выделите диапазон ячеек, содержащих данные, которые необходимо импортировать.
Щелкните выделенный диапазон правой кнопкой мыши и выберите пункт Имя диапазона или Определить имя.
В диалоговом окне Создание имени укажите имя диапазона в поле Имя и нажмите кнопку ОК.
Имейте в виду, что в ходе одной операции импорта можно импортировать лишь один лист. Чтобы импортировать данные нескольких листов, операцию импорта следует повторить для каждого листа.
Просмотрите исходные данные и выполните необходимые действия в соответствии с приведенной ниже таблицей.
Число исходных столбцов, которые необходимо импортировать, не должно превышать 255, т. к. Access поддерживает не более 255 полей в таблице.
Пропуск столбцов и строк
В исходный лист или именованный диапазон рекомендуется включать только те строки и столбцы, которые требуется импортировать.
Смещ_по_строкам В ходе операции импорта невозможно фильтровать или пропускать строки.
Столбцы. В ходе операции экспорта невозможно пропускать столбцы, если данные добавляются в существующую таблицу.
Убедитесь, что ячейки имеют табличный формат. Если лист или именованный диапазон включает объединенные ячейки, их содержимое помещается в поле, соответствующее крайнему левому столбцу, а другие поля остаются пустыми.
Пустые столбцы, строки и ячейки
Удалите все лишние пустые столбцы и строки из листа или диапазона. При наличии пустых ячеек добавьте в них отсутствующие данные. Если планируется добавлять записи к существующей таблице, убедитесь, что соответствующие поля таблицы допускают использование пустых (отсутствующих или неизвестных) значений. Поле допускает использование пустых значений, если свойство Обязательное поле (Required) имеет значение Нет, а свойство Условие на значение (ValidationRule) не запрещает пустые значения.
Чтобы избежать ошибок при импорте, убедитесь, что каждый исходный столбец содержит данные одного типа в каждой строке. Access сканирует первые восемь исходных строк, чтобы определить тип данных полей таблицы. Настоятельно рекомендуем убедиться в том, что первые восемь исходных строк не смешивают значения с разными типами данных ни в каких столбцах. В противном случае Access может не назначить столбецу правильный тип данных.
Рекомендуется также отформатировать все исходные столбцы в Excel и назначить им определенный формат данных перед началом операции импорта. Форматирование является необходимым, если столбец содержит значения с различными типами данных. Например, столбец "Номер рейса" может содержать числовые и текстовые значения, такие как 871, AA90 и 171. Чтобы исключить отсутствующие или неверные значения, выполните указанные ниже действия.
Щелкните заголовок столбца правой кнопкой мыши и выберите пункт Формат ячеек.
На вкладке Числовой в группе Категория выберите формат. Для столбца "Номер рейса" лучше выбрать значение Текстовый.
Если исходные столбцы отформатированы, но все же содержат смешанные значения в строках, следующих за восьмой строкой, в ходе операции импорта значения могут быть пропущены или неправильно преобразованы. Сведения о разрешении этих вопросов см. в разделе Разрешение вопросов, связанных с отсутствующими и неверными значениями.
Если первая строка листа или именованного диапазона содержит имена столбцов, в Access можно указать, что данные первой строки должны рассматриваться в ходе операции импорта как имена полей. Если исходный лист или диапазон не содержит имен, рекомендуется добавить их в исходные данные до операции импорта.
Примечание: Если планируется добавить данные в существующую таблицу, убедитесь, что имя каждого столбца в точности соответствует имени поля. Если имя столбца отличается от имени соответствующего поля в таблице, операция импорта завершится неудачей. Чтобы просмотреть имена полей, откройте таблицу в Access в режиме конструктора.
Закройте исходную книгу, если она открыта. Если исходный файл остается открытым в ходе операции импорта, могут возникнуть ошибки преобразования данных.
Подготовка конечной базы данных
Откройте базу данных Access, в которой будут храниться импортируемые данные. Убедитесь, что база данных доступна не только для чтения и что есть права на ее изменение.
Если ни одна из существующих баз данных не подходит для хранения импортируемых данных, создайте пустую базу данных. Для этого выполните указанные ниже действия.
Откройте вкладку Файл, нажмите кнопку Создать и выберите пункт Пустая база данных.
Перед началом операции импорта следует определить, в какой таблице будут храниться данные: в новой или существующей.
Создание новой таблицы. Если необходимо сохранить данные в новой таблице, в Access создается таблица, в которую добавляются импортируемые данные. Если таблица с указанным именем уже существует, содержимое существующей таблицы перезаписывается импортируемыми данными.
Добавление в существующую таблицу. При добавлении данных в существующую таблицу строки из листа Excel добавляются в указанную таблицу.
Следует помнить, что ошибки в ходе операции добавления зачастую объясняются тем, что исходные данные не соответствуют структуре и параметрам полей в конечной таблице. Чтобы избежать таких ошибок, откройте таблицу в режиме конструктора и проверьте указанные ниже параметры.
Первая строка. Если первая строка исходного листа или диапазона не содержит заголовки столбцов, убедитесь, что расположение и тип данных каждого столбца соответствуют нужному полю таблицы. Если же первая строка содержит заголовки столбцов, совпадение порядка следования столбцов и полей необязательно, но имя и тип данных каждого столбца должны в точности совпадать с именем и типом данных соответствующего поля.
Отсутствующие или лишние поля. Если одно или несколько полей исходного листа отсутствуют в конечной таблице, их следует добавить до начала операции импорта. Если же таблица содержит поля, которые отсутствуют в исходном файле, их не требуется удалять из таблицы при условии, что они допускают использование пустых значений.
Совет: Поле допускает использование пустых значений, если его свойство Обязательное поле (Required) имеет значение Нет, а свойство Условие на значение (ValidationRule) не запрещает пустые значения.
Индексированные поля. Если свойство Индексировано поля таблицы имеет значение Да (без повторов), соответствующий столбец исходного листа или диапазона должен содержать уникальные значения.
Для выполнения операции импорта перейдите к указанным ниже действиям.
Запуск операции импорта
Расположение мастера импорта или связывания зависит от используемой версии Access. Выполните действия, которые соответствуют вашей версии Access.
Если вы используете последнюю версию Access или Access 2019, доступную по подписке на Microsoft 365, на вкладке "Внешние данные" в группе "Импорт & Связь" нажмите кнопку "Новый источник данных > из файла > Excel".
Если вы используете Access 2016, Access 2013 или Access 2010, на вкладке Внешние данные в группе Импорт и связи нажмите кнопку Excel.
Примечание: Вкладка Внешние данные доступна только в том случае, если открыта база данных.
В диалоговом окне Внешние данные - Электронная таблица Excel в поле Имя файла укажите имя файла Excel, содержащего данные, которые необходимо импортировать.
Чтобы указать импортируемый файл, нажмите кнопку Обзор и воспользуйтесь диалоговым окном Открытие файла.
Укажите способ сохранения импортируемых данных.
Чтобы сохранить данные в новой таблице, выберите вариант Импортировать данные источника в новую таблицу в текущей базе данных. Позднее будет предложено указать имя этой таблицы.
Чтобы добавить данные в существующую таблицу, выберите параметр Добавить копию записей в конец таблицы и выберите таблицу в раскрывающемся списке. Этот параметр недоступен, если база данных не содержит таблиц.
Сведения о связывании с источником данных путем создания связанной таблицы см. ниже в разделе Связывание с данными Excel.
Будет запущен мастер импорта электронных таблиц, который поможет выполнить импорт. Перейдите к дальнейшим действиям.
Использование мастера импорта электронных таблиц
На первой странице мастера выберите лист, содержащий данные, которые необходимо импортировать, и нажмите кнопку Далее.
На второй странице мастера щелкните элемент листы или именованные диапазоны, выберите лист или именованный диапазон, который необходимо импортировать, и нажмите кнопку Далее.
Если первая строка исходного листа или диапазона содержит имена полей, выберите вариант Первая строка содержит заголовки столбцов и нажмите кнопку Далее.
Если данные импортируются в новую таблицу, заголовки столбцов используются в Access в качестве имен полей в таблице. Эти имена можно изменить в ходе операции импорта или после ее завершения. Если данные добавляются к существующей таблице, убедитесь, что заголовки столбцов исходного листа в точности соответствуют именам полей конечной таблицы.
Если данные добавляются к существующей таблице, перейдите к действию 6. Если данные добавляются в новую таблицу, выполните оставшиеся действия.
Мастер предложит просмотреть свойства полей. Щелкните столбец в нижней части страницы, чтобы отобразить свойства нужного поля. При необходимости выполните указанные ниже действия.
Просмотрите и измените имя и тип данных конечного поля.
Чтобы создать индекс для поля, присвойте свойству Индексировано (Indexed) значение Да.
Чтобы пропустить весь исходный столбец, установите флажок Не импортировать (пропустить) поле.
Настроив параметры, нажмите кнопку Далее.
На следующем экране задайте первичный ключ для таблицы. При выборе варианта автоматически создать ключ Access добавляет поле счетчика в качестве первого поля конечной таблицы и автоматически заполняет его уникальными значениями кодов, начиная с 1. После этого нажмите кнопку Далее.
Сведения о том, как запустить сохраненную спецификацию импорта или экспорта, см. в статье Запуск сохраненной спецификации импорта или экспорта.
Сведения о том, как запланировать выполнение задач импорта и связывания в определенное время, см. в статье Планирование спецификации импорта или экспорта.
Разрешение вопросов, связанных с отсутствующими и неверными значениями
Откройте целевую таблицу в режиме таблицы, чтобы убедиться, что в таблицу были добавлены все данные.
Откройте таблицу в режиме конструктора, чтобы проверить типы данных и другие свойства полей.
В приведенной ниже таблице описаны действия по разрешению проблем, связанных с отсутствующими или неверными значениями.
Графические элементы, такие как логотипы, диаграммы и рисунки, не импортируются. Их следует добавить в базу данных вручную после завершения операции импорта.
Импортируются результаты вычисляемого столбца или ячейки, но не базовая формула. В ходе операции импорта можно указать тип данных, совместимый с результатами формулы, например числовой.
Значения TRUE или FALSE и -1 или 0
Если исходный лист или диапазон включает столбец, который содержит только значения TRUE или FALSE, в Access для этого столбца создается логическое поле, в которое вставляется значение -1 или 0. Если же исходный лист или диапазон включает столбец, который содержит только значения -1 и 0, в Access для этого столбца по умолчанию создается числовое поле. Чтобы избежать этой проблемы, можно изменить в ходе импорта тип данных поля на логический.
При импорте данных в новую или существующую таблицу приложение Access не поддерживает многозначные поля, даже если исходный столбец содержит список значений, разделенных точками с запятой (;). Список значений обрабатывается как одно значение и помещается в текстовое поле.
В случае усечения данных в столбце таблицы Access попытайтесь увеличить ширину столбца в режиме таблицы. Если не удается решить проблему с помощью этого способа, это означает, что объем данных в числовом столбце Excel слишком велик для конечного поля в Access. Например, в базе данных Access свойство FieldSize конечного поля может иметь значение Байт, а исходные данные могут содержать значение больше 255. Исправьте значения в исходном файле и повторите операцию импорта.
Чтобы обеспечить правильное отображение значений в режиме таблицы, может потребоваться изменить свойство Формат некоторых полей в режиме конструктора. Ниже приведены примеры.
После завершения импорта в логическом поле в режиме таблицы отображаются значения -1 и 0. Чтобы устранить эту проблему, необходимо после завершения импорта изменить значение свойства Формат этого поля на Да/Нет для отображения флажков.
Даты в длинном и среднем форматах отображаются в Access как краткие даты. Чтобы устранить эту проблему, откройте конечную таблицу в Access в режиме конструктора и измените свойство Формат поля даты на Длинный формат даты или Средний формат даты.
Примечание: Если исходный лист содержит элементы форматирования RTF, например полужирный шрифт, подчеркивание или курсив, текст импортируется без форматирования.
Повторяющиеся значения (нарушение уникальности ключа)
Импортируемые записи могут содержать повторяющиеся значения, которые невозможно сохранить в поле первичного ключа в конечной таблице или в поле, для которого свойству Индексировано присвоено значение Да (без повторов). Удалите повторяющиеся значения в исходном файле и повторите операцию импорта.
Значения дат, сдвинутые на 4 года
Значения полей дат, импортированных с листа Excel, оказываются сдвинуты на четыре года. В Excel для Windows используется система дат 1900, в которой даты представляются целыми числами от 1 до 65 380, соответствующими датам от 1 января 1900 г. до 31 декабря 2078 г. В Excel для Macintosh используется система дат 1904, в которой даты представляются целыми числами от 0 до 63 918, соответствующими датам от 1 января 1904 г. до 31 декабря 2078 г.
Прежде чем импортировать данные, измените систему дат для книги Excel или выполните после добавления данных запрос на обновление, используя выражение [имя поля даты] + 1462 для корректировки дат.
Отформатируйте исходные столбцы.
Переместите строки таким образом, чтобы первые восемь строк каждого столбца не содержали значения с разными типами данных.
В ходе операции импорта выберите подходящий тип данных для каждого поля. Если тип данных указан неправильно, после завершения операции весь столбец может содержать пустые или неверные значения.
Описанные выше действия позволяют свести к минимуму количество пустых значений. В приведенной ниже таблице представлены ситуации, в которых пустые значения все же будут появляться.
In Microsoft Access, it is possible to accomplish most things in at least a couple of different ways, and importing Excel data into Access is no different. So, in this article, we show you how to import Excel into Access using the VBA language. For instance, we will be using the Excel Object Model. B y learning this approach, you will learn insights regarding Microsoft Access, Microsoft Excel object model integration and, at the same time, we will present some ideas regarding DML (data manipulation language) operations against Access tables using SQL.
Advantages of importing Excel into Access Using Excel Object Model
- Full access to each row / column value that allows complex validations, lookup processes and data cleanup
- Data type conversion
- Access to all Excel object model functions if required
Let’s Start Importing Excel Into Access using VBA
To create the final outcome the reader will need
- A test excel file
- A Microsoft Access database with one table and a single form with a button
The first step will be to obtain some Excel sample data to test the load process. Once generating several lines of dummy data is a tedious process a simpler approach is to download some sample test data, I found this sample file
In the same page the reader will find other smaller and bigger files. The selected file layout is as follows
How To Import Excel Into Access
There are ten columns present without column headings.
In this example we will import columns A, B and G into the destination database table.
After creating an empty Microsoft Access database, the user should create an empty table with the layout presented below
How To Import Excel Into Access
The table field ItemId will be mapped to column A, the field Description will be mapped to column B and the field Price to column G. The reader should pay attention to the field price underlying data type. Once source data can have decimal numbers the destination field should be able to accommodate them (remember when working with SQL the decimal separator is the dot). The first field is simply an integer sequence and the second one a description text string (more notes on this later on).
Once the table is properly created the next step will be the creation of a simple form with a button to trigger the process. Assuming the reader already has some Microsoft Access experience the final form should be similar to
How To Import Excel Into Access
Change the button property caption to Import Excel and the button name to cmdImportExcel (as mentioned in previous tutorial, naming conventions are always a good practice). Click the save button and name the form as frmImportExcel.
To provide some user friendly features the created button will call a file picker control dialog, this way the user will be able to select the source file from the hard-drive picking it from any location.
Right click on the insert button, choose build event and then code builder. The Visual Basic editor should open
How To Import Excel Into Access
As a side not let’s force explicit variable declaration in the code so each required variable is declared using its proper data type and no machine resources are allocated in vain. In the top of the code (bellow Option Compare Database) simply add
From now on all required variables will require a proper declaration.
Before implementing the code let’s explain the underlying sequence logic:
- The user will see a file picker dialog opening
- The user will be able to pick the excel file
- If an Excel file was picked its path will be assigned to a variable
- The path stored in that variable will be used to open the Excel in background and load all rows into the Access table
Implementing the file dialog control
To use the file dialog control, we must provide a reference to the Microsoft Office XX Object Library (will depend on the office version installed). To do that access the Tools -> References menu in the VBA editor.
How To Import Excel Into Access
Navigate down using the scroll bar and choose the installed Office object library installed.
How To Import Excel Into Access
From now on all objects and methods provided by it are available to use.
Let’s add the file dialog picker underlying code…
In the VBA editor, inside cmdImportExcel_Click() event put the following code. The code is highly commented so the reader understands what each line is doing.
Private Sub cmdImportExcel_Click()
On Error GoTo cmdImportExcel_Click_err:
Dim fdObj As Office.FileDialog ‘declares variable fdObj
Dim varfile As Variant ‘variant type variable that will store the selected file path
Set fdObj = Application.FileDialog(msoFileDialogFilePicker) ‘instantiates the variable creating a filepicker object using late binding
With fdObj ‘using the with statement we will be working with fdObj by default
‘does not allow selecting more than one file
‘clears the file dialog file type existing filters
‘this file dialog will only allow the selection of xlsx files
.Filters.Add “Excel 2007+”, “*.xlsx”
‘optional, set the file dialog title
.Title = “Please select the excel file to import …”
If .SelectedItems.Count = 1 Then ‘a file was selected
Call MsgBox(“The selected file was: ” & .SelectedItems(1)) ‘for now we will test the file picking by sending the file path to a message box on screen
Else ‘no file was selected
Call MsgBox(“No file was selected.”)
Select Case Err.Number
Call MsgBox(Err.Number & ” – ” & Err.Description, vbCritical + vbOKOnly, “System Error …”)
Now let’s test the code and check how it works. First let’s visualize the form view by clicking as presented in the next picture
Then the form will be presented
The reader should now press the Import Excel button and a file picker dialog will be presented
How To Import Excel Into Access
Next please select the downloaded file, hit the open button and the outcome should be similar to the one bellow only showing a proper path
If this is the outcome, everything is working as expected and the file picking component is working already. Next we will see how to include the Excel object model into the Microsoft Access solution. The reader will have to include the proper reference to the Excel object model. Once again, access the Tools -> References menu in the VBA editor.
How To Import Excel Into Access
And mark the check box reference to the Excel application
How To Import Excel Into Access
From now on all Excel object methods and properties will be available once an Excel variable is created inside the Microsoft Access VBA code. This technique is called early binding, data types are known in advance but checking these references is not mandatory. If those references are not set a late binding technique is being used but it will make development more difficult as the code editor intellisense will not show the properties and methods, deep documentation reading will be required. A common approach is to use the reference while developing and removing it when finished, the unknown constants must then be replaced by their corresponding integer values. We will not get into much more detail but it is important to be aware these references are not mandatory, they just make life easier.
Once the reference to Excel is created we can declare variables types defined there. We will need to declare three variables
- xlApp – will be a reference to a hidden Excel application
- xlWb – will be a reference to the workbook opened as a result of opening the file to import
- xlWs – will be a reference to the worksheet having data to import
The steps involved in the load process can be described as follows
- Delete existing data in destination table
- Loop through all excel lines running an insert statement per line until the last line is reached
- The loop process will stop when it does not find any more data in the column A
The required code for the entire process is the following. We will add extensive comments on all lines, please check them bellow in green
Private Sub cmdImportExcel_Click()
On Error GoTo cmdImportExcel_Click_err:
Dim fdObj As Office.FileDialog ‘declares variable fdObj
Dim varfile As Variant ‘variant type variable that will store the selected file path
Set fdObj = Application.FileDialog(msoFileDialogFilePicker) ‘instantiates the variable creating a filepicker object using early binding
With fdObj ‘using the with statement we will be working with fdObj by default
‘does not allow selecting more than one file
‘clears the file dialog file type existing filters
‘this file dialog will only allow the selection of excel files, this is achieved handling the Filters collection
.Filters.Add “Excel 2003”, “*.xls”
.Filters.Add “Excel 2007+”, “*.xlsx”
‘optional set the file dialog title
.Title = “Please select the excel file to import …”
If .SelectedItems.Count = 1 Then ‘a file was selected so data can be imported from Excel from this point the loop import process will run
Dim xlApp As Excel.Application ‘the excel application
Dim xlWb As Excel.Workbook ‘the excel workbook reference that will point to the opened workbook
Dim xlWs As Excel.Worksheet ‘the excel worksheet with data
Dim intLine As Long ‘the line counter
Dim strSqlDml As String ‘string variable that stores the executed SQL statements
Dim strColumnBcleaned As String ‘string variable that stores values from column B after replacing single quotes by four single quotes
‘remember the quote is the string delimiter in SQL so it needs to be escaped
Dim strColumnGcleaned As String ‘string variable that stores values from column G cleaned, the clean step replaces commas by dots as
‘the decimal separator in SQL is the dot
varfile = .SelectedItems(1) ‘picking the selected file full path
clean the existing table
CurrentDb.Execute “DELETE * FROM tblExcelImport”, dbFailOnError
‘instantiate the Excel application, creating the Excel application in memory, the excel Accplication will be visible so the user is able to see the loop iterating through Excel rows but usually it is hidden and only visible if indeed required
Set xlApp = New Excel.Application
‘opening the picked file by calling the Excel workbooks collection open method, it receives the file location as parameter and returns a reference for the opened file
Set xlWb = xlApp.Workbooks.Open(varfile)
‘seting the worksheet to the first one within the available, as it is the one having data to be imported
Set xlWs = xlWb.Worksheets(1)
‘default counter initial value/line, this means we start iterating in line one
‘the next two lines replace single quotes in column B value and commas by dots as decimal separator in column G
strColumnBcleaned = Replace(xlWs.Cells(intLine, 2).Value2, “‘”, “”””)
strColumnGcleaned = Replace(xlWs.Cells(intLine, 7).Value2, “‘”, “”””)
‘the next line creates a SQL insert statement using the previous obtained cleaned variables and the value for column A
The insert statement must have the sequence present in the destination table and is obtained by concatenating values per each line presented in the Excel file while iterating
strSqlDml = “INSERT INTO tblExcelImport VALUES(” & xlWs.Cells(intLine, 1).Value2 & “, ‘” & strColumnBcleaned & “‘, ” & strColumnGcleaned & “)”
‘executes the insert statement against the database, the dbFailOnError is an optional value that will make the Execute process return an error if the SQL was not properly executed
CurrentDb.Execute strSqlDml, dbFailOnError
‘the next line only puts the selected cell in Excel in the actual line position, this is not required and will even make the process slower, it is just present here so the reader can see things running
intLine = intLine + 1
Loop Until IsEmpty(xlWs.Cells(intLine, 1)) ‘stopping criteria, when values in column A stop the loop will stop, please note in cells collection the first index is the row and the second one the column so we are making row changing. Once the loop stops the steps after close the open workbook, quit excel and clean the memory references to the created objects
Set xlApp = Nothing
Set xlWb = Nothing
Set xlWs = Nothing
‘the next step opens the loaded table so the user can see imported data
DoCmd.OpenTable “tblExcelImport”, acViewNormal, acEdit
‘this branch only happens if no file was selected
Else ‘no file was selected
Call MsgBox(“No file was selected.”)
Select Case Err.Number
Call MsgBox(Err.Number & ” – ” & Err.Description, vbCritical + vbOKOnly, “System Error …”)
As mentioned before, this is not the only approach to import Excel data, it is possible to create linked tables and, to import data into Microsoft Access, the reader should also consider the DoCmd.TransferDatabase method as well. Linked tables can make use of connectivity drivers so they may not point only to databases, it is even possible to create a linked table to a text file.
Some relevant examples related to the IN clause can be found over here
To run a query in Access pulling data from an Excel file a similar solution to the following one can be used
SELECT CustomerID, CompanyName
IN “c:\documents\xldata.xls” “EXCEL 5.0;”
WHERE CustomerID Like “A*”
ORDER BY CustomerID;
Also, when dealing with SQL statements built dynamically it is very important to handle possible null values, replacing them by default values or even forcing the null value insert.
If a value can be null the variable needs to be of type Variant to store it and then use it in the built insert statement.
Conclusion
Thanks for reading about how to import excel into Access. Leave a comment if you have any questions on how to import excel into access.
Читайте также: