Как импортировать из эксель в аксесс

Как импортировать данные из Excel в Access

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

Количество источников, использованных в этой статье: 10. Вы найдете их список внизу страницы.

Количество просмотров этой статьи: 44 767.

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

Как импортировать из эксель в аксесс

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, копированием/выделением/селектом диапазона ячеек. Помогите пожалуйста, горит.

Решать такую задачу со стороны Excel сложнее чем из Access, в котором достаточно выполнить два запроса, один на обновление второй на добавление.
В Вашем варианте можно сделать следующее:
создать рекордсет ADO в Excel,
заполнить его данными из листа "output",
открыть рекордсет с набором записей таблицы "test"
фильтром и перебором всех записей произвести сравнение, для редактирования или добавления записей.
Все эти процедуры и библиотека ADO должны присутствовать в проекте VBA файла xls.
Евгений.

Получается последовательность такая:
1создать рекордсет ADO в Excel,
2заполнить его данными из листа "output",
22 Подконнектится
3открыть рекордсет с набором записей таблицы "test"
4фильтром и перебором всех записей произвести сравнение, для редактирования или добавления записей.

Как раз проблема с пп. 1 и 2 — я не знаю что прописать.

Dim xlsRecordSet as ADODB.Recordset

Set xlsRecordSet = New ADOD.Recordset

Const ConnectionString As String = _

"Provider = Microsoft.Jet.OLEDB.4.0;" + _

"Data Source = C:\Data\myData.mdb;Persist Security Info=False"

Dim Connection As ADODB.Connection

Set Connection = New ADODB.Connection

If Connection.State = ObjectStateEnum.adStateOpen Then objConn.Close

If Connection.State = ObjectStateEnum.adStateOpen Then objConn.Close

Как рекордсет создается и заполняется Вы можете посмотреть в коде.

Также его можно сортировать и передавать в массив ( это для примера ).

Dim rs As Object , i% , V ()

Set rs = CreateObject ( "ADODB.Recordset" ) ‘создание рекордсета

rs.Fields.Append "Fld" , 200 , 255 ‘ добавление поля в рекордсет

rs.Open ‘открываем рекордсет

rs.AddNew ‘добавление записи в реккордсет

rs ( 0 ) = "Text" ‘ввод данных

rs.Sort = "Fld" ‘имя поля сортировки "Fld desk" — по убыванию

V = rs.GetRows ‘при необходимости передаем все содержимое рекордсета в массив

rs.Close ‘ закрываем рекордсет

Set rs = Nothing ‘ очищаем переменную для освобождения памяти

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

‘=======================================================================В цикле перебора набора записей, создается строчная переменная содержащая значение поля рекордсета

sSql = "INSERT INTO Table1 ( Fld1 ) SELECT ‘" & rs ( 1 ) & "’" ‘текст момещается в апострофы

Импорт базы данных Excel в Access

Электронные таблицы очень полезный инструмент. Но в целом ряде случаев для работы со структурированной информацией лучше использовать базы данных. Попробуем разобраться с этим вопросом.

• 1. Мы перенесем (импортируем) Список Excel Книги в Access и поработаем с формами, запросами и отчетами. Формы Access создаются Мастерами буквально несколькими кликами мыши. Запросы позволяют манипулировать информацией: отбирать, преобразовывать, вычислять. Это больше и легче, чем фильтрация Excel. Отчеты Access позволяют создавать на основе информации базы данных, часто, используя запросы, выходные документы с колонтитулами, названиями, заголовками и другими атрибутами документа.
• 2. Мы научимся использовать шаблон Microsoft Access электронного каталога Biblio и модифицируем его в соответствии с нашими потребностями. Серьезные специалисты работают с текстами, делают выписки. Но нужен инструмент, который бы позволил среди этих многочисленных выписок находить полезные именно для текущей работы. Делать это можно с использованием ключевых слов. Мы сделаем такую базу данных. Легко. Действительно легко.
• 3. Модифицировать — это часто, как носить костюм, сшитый для другого человека. Можно походить и в таком, но все же приятнее, когда вещь сделана именно для тебя. То есть, если это не очень сложно для вас, было бы полезно научиться делать несложные базы данных самостоятельно. Мы обозначим некоторый минимум знаний, который позволит создавать базы данных в 3—5 таблиц с отношениями многие-ко-многим, подстановками и т.д. И научимся с ними работать.

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

База данных (БД) есть совокупность взаимосвязанных таблиц, собранных с определенной целью о сущностях одной предметной области.

Мы об этом говорили, но подчеркнем еще раз: очень важно определить перечень вопросов, на которые должны быть получены ответы с помощью базы данных и задач, которые необходимо решать. Также полезно определить некоторые рамки. Например, насколько большой будет эта база данных, на каком временном интервале эта база данных будет использоваться. Мне думается, что 3—5 тыс. книг это более чем приличная библиотека. Если человек активно использует несколько сотен книг — это уже очень много. Но в каталог библиотеки может попасть в несколько раз больше книг, поскольку далеко не все заинтересовавшие вас книги станут вашим рабочим инструментом.

Наши задачи следующие.

• 1. Определить наличие той или иной книги в домашней библиотеке.
• 2. Подготовить требование для книги из библиотеки Университета (вы работали с этой книгой и в вашем каталоге есть реквизиты книги).
• 3. Составить список литературы по учебной дисциплине.
• 4. Составить список литературы по проблеме, которой вы занимаетесь.
• 5. Составить список литературы по определенной проблеме за определенный период времени.
• 6. И, может быть главное, обеспечить возможность создания выписок из изученных книг и их систематизацию по ключевым словам.

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

При исключении повторяющихся записей можно использовать один из двух подходов. Во-первых, Расширенный фильтр. Если выполнить команду Данные / Дополнительно, то появится окно Расширенный фильтр (рис. 4.1). В этом окне нужно выставить флаг Только уникальные записи.

Рис. 4.1. Фильтрация только в уникальных записях

Во-вторых, на вкладке Данные в группе Работа с данными есть команда Удалить дубликаты. После выполнения команды появится окно оповещения (рис. 4.2). [1] [2]

Рис. 4.2. Окно оповещения об удалении дубликатов записей

Рис. 43. Шаблоны баз данных Access

A/c. 4.4. Начальная страница работы с пустой базой данных

Чтобы импортировать в Access таблицу Excel из Excel, необходимо выполнить команду Внешние данные / Excel. В результате появится окно Внешние данные (рис. 4.5).

Рис. 4.5. Импорт таблицы в Access таблицы Excel

Через кнопку Обзор нужно выбрать файл для импорта данных. Это также файл Книги80. Заголовки столбцов таблицы Excel будут полями таблицы Access. Лучше, если будут удалены пробелы между словами в этих заголовках. Система, конечно, укажет на ошибки, но чтобы потом с ними не возиться, следует перед импортом внимательно просмотреть таблицу Excel. Вот что получится, если этого не сделать (рис. 4.6).

Рис. 4.6. Пример ошибки при импорте

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

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

Рис. 4.7. Работа с мастером Импорта

При подготовке таблицы Excel к импорту необходимо поработать с форматом данных. Если не позаботиться об этом заблаговременно, то гарантированы большие проблемы, в том числе очень возможно, что придется отказаться от результатов импорта. Обратите внимание на денежный формат Цены (рис. 4.8).

Чтобы работать в режиме конструктора, необходимо вызвать Контекстное меню (рис. 4.9). Работать с ним предпочтительнее, нежели обращаться к командам ленты.

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

Доступ к VBA — импорт / экспорт Excel — запрос, отчет, таблица и формы

В этом руководстве будут рассмотрены способы импорта данных из Excel в таблицу Access и способы экспорта объектов Access (запросов, отчетов, таблиц или форм) в Excel.

Импортировать файл Excel в Access

Чтобы импортировать файл Excel в Access, используйте acImport вариант DoCmd.TransferSpreadsheet :

Или вы можете использовать DoCmd.TransferText чтобы импортировать файл CSV:

Импортировать Excel в функцию доступа

Эту функцию можно использовать для импорта файла Excel или CSV в таблицу доступа:

Вы можете вызвать функцию так:

Доступ к экспорту VBA в новый файл Excel

Чтобы экспортировать объект Access в новый файл Excel, используйте DoCmd.OutputTo метод или DoCmd.TransferSpreadsheet метод:

Экспорт запроса в Excel

Эта строка кода VBA экспортирует запрос в Excel с помощью DoCmd.OutputTo:

Или вы можете использовать вместо этого метод DoCmd.TransferSpreadsheet:

Примечание: Этот код экспортирует в формат XLSX. Вместо этого вы можете обновить аргументы для экспорта в формат файла CSV или XLS (например, acFormatXLSX к acFormatXLS).

Экспорт отчета в Excel

Эта строка кода экспортирует отчет в Excel с помощью DoCmd.OutputTo:

Или вы можете использовать вместо этого метод DoCmd.TransferSpreadsheet:

Экспорт таблицы в Excel

Эта строка кода экспортирует таблицу в Excel с помощью DoCmd.OutputTo:

Или вы можете использовать вместо этого метод DoCmd.TransferSpreadsheet:

Экспорт формы в Excel

Эта строка кода экспортирует форму в Excel с помощью DoCmd.OutputTo:

Или вы можете использовать вместо этого метод DoCmd.TransferSpreadsheet:

Экспорт в функции Excel

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

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

Экспорт в существующий файл Excel

Приведенные выше примеры кода отлично подходят для экспорта объектов Access в новый файл Excel. Однако их нельзя будет экспортировать в существующую книгу.

Чтобы экспортировать объекты Access в существующую книгу Excel, мы создали следующую функцию:

Вы можете использовать эту функцию так:

Обратите внимание, что вас просят определить:

• Что выводить? Таблица, отчет, запрос или форма
• Имя объекта
• Имя выходного листа
• Путь и имя выходного файла.

Экспорт SQL-запроса в Excel

Вместо этого вы можете экспортировать SQL-запрос в Excel, используя аналогичную функцию:

Где вас просят ввести:

• SQL-запрос
• Имя выходного листа
• Путь и имя выходного файла.

Функция экспорта в новый файл Excel

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