Как сделать импорт из excel в access

Добавил пользователь Алексей Ф.
Обновлено: 05.10.2024

Пока известен только метод "TransferSpreadsheet", но он не обеспечивает выполнения задачи. Кто может помочь советом, полезной ссылкой или кодом прошу отозваться. Полный код отработки нажатия кнопки на импорт данных приведен ниже.

Private Sub Btn_Imprt_Click()
Dim result As Integer

With Application.FileDialog(1)
.ButtonName = "Добавить"
.AllowMultiSelect = False
.Filters.Clear

result = .Show
If result <> 0 Then
FileName = Trim(.SelectedItems.Item(1))
DoCmd.TransferSpreadsheet acImport, , TblName, FileName, True
End If
End With

Например, если у вас в источнике и приемнике есть поля уникального кода записи, вы можете сделать критерием соответствия записей равенство значений в этих полях. Тогда для обновления вам потребуется для каждой исходной записи НАЙТИ в приемнике запись с таким же кодом и обновить поля найденной записи значениями полей источника.

Искать эти записи и обновлять их можно разными способами. Например, СВЯЗАТЬ (INNER JOIN) в запросе на обновление (UPDATE) эти две таблицы по этому полю кода (исходную таблицу можно присоединить), и выполнить SET значений для нужных полей.

Очень часто у программистов и пользователей, у которых реализовано приложение в связке клиент (Access – adp проект) и сервер (Microsoft SQL Server – база данных) возникает необходимость импорта данных, с помощью adp проекта, в базу данных, причем чтобы это могли делать простые пользователи, т.е. максимально просто, без привлечения программистов. И сегодня мы поговорим о реализации данной возможности.

Так как импортируемые данные могут быть разные, сегодня мы рассмотрим 2 примера импорта.

Импорт данных из Excel
Импорт изображения в базу данных и ее дальнейшее отображение в отчетах и формах

Импорт данных из Excel в Microsoft SQL Server на VBA Access

И начнем мы с импорта данных из таких форматов как xls и dbf, так как это очень часто требуется в работе, а если у кого и не часто, то все равно рано или поздно такая необходимость возникнет.

Когда у меня стояла задача реализовать такой импорт, я нашел много способов и средствами Microsoft SQL сервера, и встроенными средствами Access, но в каждом из них были небольшие минусы, иными словами, не для каждодневного использования пользователями. Но мне встретился такой способ, который меня полностью устроил и именно о нем я сейчас расскажу.

Данный способ заключается в подключении напрямую к файлу источнику через поставщика Microsoft.Jet.OLEDB.4.0.

Для начала давайте определимся, с какими данными мы будем работать.

Примечание! Называйте лист в файле, на котором располагаются данные, также как и сам файл.

Импортировать мы будем во временную таблицу, например, testimport, из которой Вы легко сможете переносить или обновлять данные в других таблицах (например, через процедуру).

Таблицу создадим вот так:

Теперь можно переходить к самому процессу импорта, для визуального оформления нам понадобится одна форма и всего лишь одна кнопка на ней (как Вы реализуете форму это Ваше дело, или Вы просто добавите данную кнопку на существующую форму у себя в приложении, т.е. форма здесь не главное).

Общий смысл заключается в том, что Вы выбираете файл, а если конкретней, то считываете путь к файлу, затем подключаетесь к этому файлу, считываете данные и записываете в свою базу данных.

Примечание! Если Вы хотите накапливать данные в таблице (в нашем случае testimport), то в файле источнике, добавляете какой-нибудь признак, чтобы потом иметь возможность выбрать те данные, которые Вы только что загрузили (например, столбец с датой, и в базе соответственно тоже добавьте), если не хотите, то перед импортом данных в таблицу, очищайте ее, иначе все данные будут накапливаться.

Импорт изображения в Microsoft SQL Server на VBA Access

Теперь перейдем к импорту изображения и его дальнейшее отображение. Мне это понадобилось тогда, когда возникла необходимость выводить некую картинку в отчетах, но при условии того, что проект adp был один общий, а баз было несколько, т.е. для разных групп пользователей (несколько филиалов), и картинки во всех филиалах разные, поэтому статически прописать в отчетах было нельзя, поэтому мне пришлось искать другие решения.

Примечание! Примеры ниже тестовые, поэтому Вы можете создать свою таблицу со своими полями, и, соответственно, свою форму.

Здесь, конечно же, следовало бы предусмотреть возможность отсутствия данных, но для примера сойдет. Также если у Вас несколько картинок, то укажите условие в запросе.

Вот в принципе и все, если что непонятно пишите в комментариях, может, чем помогу. Удачи!

Но для столь любимой всеми программы Excel Access делает исключение. Можно скопировать набор ячеек в Excel и затем вставить их в программу Access для создания новой таблицы. Эта процедура действует, потому что Excel различает данные разных типов (хотя она далеко не так придирчива, как Access). Например, Excel по-разному трактует числа, даты, текст и логические значения.

Вот как действует этот метод.

1. В программе Excel выделите ячейки, которые хотите скопировать.

Если у электронной таблицы есть заголовки столбцов, включите их в область выделения. Программа Access сможет использовать их как имена полей.

Неважно, какая у вас версия Excel — этот метод действует во всех версиях программы.

2. Нажмите комбинацию клавиш + , чтобы скопировать выделенную область.

3. Перейдите в программу Access.

4. Щелкните кнопкой мыши где-нибудь в области переходов и нажмите комбинацию клавиш + .

Программа Access заметит, что вы пытаетесь вставить группу ячеек Excel, и попытается преобразовать их в таблицу. Сначала она поинтересуется, содержатся ли в первой строке области выделения заголовки столбцов.

5. Если в пункте 1 вы выделили заголовки, щелкните кнопку Да, в противном случае Нет.

Если выбран вариант Да, программе Access не нужно создавать случайные имена полей — она может использовать ваши заголовки.

Access создает новую таблицу для работы с новыми данными. Эта таблица названа так же, как таблица Excel. Если у таблицы имя листа Лист1 (Sheetl) (как у большинства таблиц Excel), теперь у вас есть таблица Лист1.

6. Щелкните мышью кнопку ОК.

Теперь можно проверить таблицу, чтобы убедиться в том, что типы данных и имена полей такие, как вы хотели.

Только что мы рассмотрели различные способы экспорта-импорта таблиц базы данных Access в списки Excel. Пожалуй, чаще приходится выполнять обратную операцию - переноса существующей базы данных Excel в базу данных Access. Необходимость в этом может возникать, например, в тех случаях, когда база данных Excel разрослась настолько, что дальнейшая работа требует более мощного инструментария Access. Иногда списки Excel используются просто для пополнения таблиц существующей базы данных Access.

Понятно, что обратное преобразование данных Excel в Access выполняется сложнее по той простой причине, что база данных Access устроена значительно сложнее, чем списки Excel. Поэтому нет многих возможностей, которые есть при переносе данных из Access в Excel. Нельзя, например, сохранить таблицу Excel в виде mdb-файла в формате базы данных Access. Тем не менее, существуют, по крайней мере, два способа переноса данных. Первый из них позволяет начать работу по переносу данных в Excel, используя его команду "Перенести в MS Access", являющейся аналогом рассмотренной выше команды Access - "Связи с Office - Анализ в MS Excel". Второй способ использует возможности Access по импорту внешних данных. Я рассмотрю сейчас оба эти способа и выполню обратный перенос созданной базы данных офиса РР в Excel в базу данных Access, создав под другим именем новую копию существующей базы данных.

Импорт списков Excel в приложении Access

Приложение Access позволяет импортировать внешние данные, и я воспользуюсь этой возможностью для переноса списков Excel. Я создал в Access новую, пока что пустую базу данных, дал ей имя "dbPPnew" и занялся выполнением операции импорта, выбрав из меню "Файл" команду "Внешние данные | Импорт…". В открывшемся окне Импорта я, как обычно, в окошке "тип файла" выбрал из большого раскрывающегося списка нужный мне тип - Microsoft Excel - затем в поле "Папка" выбрал нужную папку, выбрал файл с книгой Excel, содержащей базу данных и нажал кнопку "Импорт". В результате, Мастер Импорта начинает свою работу. Вот первое окно, которое открывает этот Мастер, предлагая импортировать рабочие листы или именованные диапазоны книги Excel:

Обратите внимание, я предпочел работать с именованными диапазонами, а не с листами книги. Дело в том, что Мастер импорта не слишком интеллектуален и не может разобраться, где на рабочем листе начинается список Excel. Он предполагает, что заголовки полей списка начинаются в первой строке. Я же рабочий лист начинал с некоторого общего заголовка, и только потом уже размещал список. По этой причине, прежде чем заниматься импортом списков, я ввел именованные диапазоны для списков, назвав каждый диапазон по имени списка. Это позволит Мастеру Импорта разобраться с именами полей списка и сделать их именами полей таблицы Access, при условии, что на втором шаге работы Мастера будет включен флажок "Первая строка содержит заголовки столбцов". Я включил этот флажок, а на третьем шаге работы включил переключатель "В новой таблице", поскольку речь идет не о добавлении данных в существующую таблицу, а о создании новой таблицы. Вот как выглядит следующее окно Мастера Импорта, позволяющего уточнить характеристики полей таблицы:

На следующем шаге можно указать Мастеру, какое поле является ключевым или добавить в таблицу поле счетчика, которое и будет играть роль ключа. Единственная проблема возникает в случае составного ключа, - Мастеру можно задать лишь одно ключевое поле, остальную работу по уточнению состава ключа придется выполнить уже в Access. Наконец, на последнем шаге работы можно указать не только имя таблицы, но и включить два флажка, один из которых вызывает Мастера Анализа таблиц, который позволяет провести проверку эффективности (с точки зрения этого Мастера) качества проектирования таблицы и определить целесообразность ее возможного разбиения на несколько таблиц.

Я не стал вызывать Мастера Анализа таблиц, но надеюсь, что еще придет его время, и я расскажу подробнее о шагах его работы. Таблица "Книги" была успешно перенесена из Excel в Access. Аналогичным образом можно было бы импортировать и другие списки Excel, преобразуя их в таблицы базы данных Access. Но следующий список "Заказчики" я перенесу из Excel в Access, используя команду "Перенести в MS Access", которая появляется в меню Excel при включенной надстройке "AccessLinks".

Перенос списков из Excel в Access

Специальная надстройка AccessLinks добавляет в меню Excel команды, позволяющие преобразовать списки Excel в объекты базы данных Access - таблицы, формы, отчеты. Если надстройка AccessLinks еще не подключена, то это следует сделать, выбрав команду "Надстройки" из меню "Сервис" и включив флажок соответствующей надстройки. При включенной надстройке в меню "Данные" появляются три команды: MS Access Form, MS Access Report, Convert to MS Access. Первые две из них позволяют по данным списка Excel построить форму и отчет базы данных Access, я не буду на них останавливаться, поскольку по существу все построение осуществляют известные в Access Мастера построения форм и отчетов. Давайте чуть более подробно рассмотрим лишь третью команду, преобразующую список в таблицу базы данных. Первое окно, которое появляется после вызова этой команды, позволяет указать базу данных Access:

А далее все возвращается на круги своя и работу продолжает уже знакомый нам Мастер Импорта, который и создает таблицу в базе данных Access. Замечу только, что поскольку работа начинается в Excel, то Excel способен распознать, где начинается список и передать точный список Мастеру Импорта без всяких пустых строк. Это, пожалуй, достаточно важная причина, по которой я рекомендовал бы перенос списков выполнять, используя именно этот способ работы. На этом я и закончу рассмотрение вопросов экспорта - импорта баз данных между Excel и Access.

Файлы, созданные приложениями электронных таблиц, управления проектами и т. д. также представляют собой таблицы специального вида. Было бы естественным включить в Access 2002 возможность импорта таких файлов в базу данных Access. И такая возможность в Access 2002 есть. При этом Access 2002 поддерживает следующие форматы:

файлы рабочих листов (XLS) Excel 2.x, 3.0, 4.0, 5.0, 7.0 (Excel 95), 8.0 (Excel 97), 9.0 (Excel 2000), 10.0 (Excel 2002);
электронные таблицы Lotus 1-2-3 в форматах WKS, WK1, WK2, WRK3, WRK4.

Этих форматов вполне достаточно, потому что практически все приложения, предназначенные для работы с электронными таблицами, позволяют экспортировать данные в один из этих форматов.

Access 2002 позволяет создавать таблицы в базе данных, импортируя их из рабочего листа Excel. При этом можно импортировать как целый рабочий лист, так и именованный диапазон ячеек этого листа. Кроме того, в процессе импорта может быть создана новая таблицы Access, которая будет содержать все импортируемые данные, или же эти данные будут добавлены к уже существующей таблице Access. При добавлении данных в существующую таблицу заголовки столбцов рабочего листа Excel (во всяком случае, для тех столбцов, которые должны импортироваться) должны совпадать с названиями полей этой таблицы.

Импортирование отдельного рабочего листа из рабочей книги возможно, начиная с Microsoft Excel 5.0 и выше. При необходимости импортировать только один рабочий лист из других файлов электронных таблиц, содержащих несколько листов, нужно сначала каждый лист сохранить в отдельном файле.

Для того чтобы импорт прошел без ошибок, данные на импортируемом листе должны быть соответствующим образом организованы. На рис. 3.7 показан формат представления данных на рабочем листе Excel, который наиболее подходит для импорта в базу данных. Обратите внимание на то, что имена всех полей находятся в первой строке таблицы, а оставшиеся строки содержат данные. Тип данных в каждой ячейке одного столбца должен быть одинаковым, и в каждой строке должны использоваться одни и те же поля. В таком виде таблица на рабочем листе максимально соответствует таблице Access, что позволит упростить процесс импорта.

Рис. 3.7. Рабочий лист Excel

Для того чтобы подготовить данные на рабочем листе для импорта, может потребоваться выполнить несколько предварительных действий.

Если ячейки рабочего листа содержали формулы, по которым вычислялись значения, то в таблицу Access будут импортированы только вычисленные значения.

Теперь данные рабочего листа можно импортировать в таблицу Access 2002. Для этого:

Запустите Access 2002 и откройте базу данных, в которую необходимо импортировать данные. Активизируйте окно База данных (Database), щелкнув по его заголовку левой кнопкой мыши.
Выберите команду Файл, Внешние данные, Импорт (File, Get External Data, Import). Появится диалоговое окно Импорт (Import).
Перейдите в папку, содержащую файл рабочего листа Excel. В раскрывающемся списке Тип файла (File of Type) выделите элемент Microsoft Excel (*.xls). Выделите имя файла и нажмите кнопку Импорт (Import) или просто дважды щелкните по имени файла левой кнопкой мыши. Запустится Мастер импорта электронной таблицы (Import Spreadsheet Wizard), первое диалоговое окно которого показано на рис. 3.8.
Если необходимо импортировать весь рабочий лист, выберите переключатель листы (Show Worksheets). Если же предполагается импортировать именованный диапазон рабочего листа, то выберите переключатель именованные диапазоны (Show Named Ranges). В списке первого диалогового окна Мастера импорта электронных таблиц будут выведены имена рабочих листов или диапазонов, соответственно. На рис. 3.8 список объектов состоит только из одного наименования "Клиенты", т. к. импортируемая таблица имеет один рабочий лист.

Рис. 3.8. Первое диалоговое окно Мастера импорта электронной таблицы

Рис. 3.9. Второе диалоговое окно Мастера импорта электронной таблицы

Рис. 3.10. Третье диалоговое окно Мастера импорта электронной таблицы

Рис. 3.11. Четвертое диалоговое окно Мастера импорта электронной таблицы

Рис. 3.12. Пятое диалоговое окно Мастера импорта электронной таблицы

Прежде чем определить ключевое поле на основе одного из столбцов таблицы, убедитесь в том, что ячейки этого столбца содержат уникальные значения.

Если имя таблицы, которое вы указали на последнем шаге Мастера импорта электронной таблицы, совпадет с именем уже существующей в базе данных таблицы, то Access 2002 выведет диалоговое окно, в котором спросит у вас, заменить ли уже существующую таблицу новой или переименовать создаваемую таблицу.

Импортированная таблица появится в окне базы данных. Чтобы проверить, что желаемый результат достигнут, откройте импортированную таблицу в режиме Таблицы, дважды щелкнув по ней левой кнопкой мыши.

Рис. 3.14. Таблица ошибок импорта

Чтобы узнать, какие типы данных полей были выбраны при импорте рабочего листа, в окне базы данных откройте созданную таблицу в режиме Конструктора (рис. 3.15). В отличие от присоединенных таблиц, все свойства полей импортированных таблиц можно изменить.

Рис. 3.15. Импортированная таблица в режиме Конструктора

Access 2002 позволяет также связать рабочий лист Excel с базой данных Access. Благодаря этой возможности с присоединенным рабочим листом Excel можно работать как в исходном формате средствами Excel, так и в формате таблицы базы данных Access, причем это можно делать одновременно. Что касается изменения структуры рабочего листа, то на него распространяются правила работы с присоединенными таблицами, т. е. в режиме Конструктора можно изменить только те свойства полей, которые связаны с отображением этих полей в таблицах Access. При изменении структуры таблицы или перемещении ее в другую папку нужно обновить связь с ней.

Присоединение рабочего листа Excel выполняется практически так же, как и импорт, только используется команда Связь с таблицами (Link Tables). Мастер связывания электронной таблицы (Link Spreadsheet Wizard) аналогичен мастеру импорта, однако он не позволяет выбирать столбцы рабочего листа и изменять их параметры.

Читайте также: