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

Копирование данных из Excel

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

Чтобы копирование данных прошло без проблем, они должны быть согласованы и правильно структурированы.

Очистка данных

• Все данные в столбце должны быть одного типа и иметь одинаковый формат. Например, все номера телефонов должны включать код города, но не префикс страны, все адреса должны включать название города и области или края, но не название страны, а все цены должны включать копейки, даже если цена равна 0,00.
• Удалите все подзаголовки, строки итогов и примечаний, а также пустые строки.

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

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

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

Добавление данных Excel в новую таблицу

1. Выделите и скопируйте в Excel данные, которые нужно добавить в таблицу.
2. В Access на вкладке Главная нажмите кнопку Вставить.
3. Чтобы указать, содержит ли первая строка заголовки столбцов, выберите Да или Нет.
4. При необходимости переименуйте таблицу и поля.

Добавление данных Excel в существующую таблицу

1. Выделите и скопируйте в Excel данные, которые нужно добавить в таблицу.
2. В приложении Access откройте таблицу, в которую нужно вставить данные.
3. В конце таблицы выберите пустую строку.
4. На вкладке Главная нажмите кнопку Вставить и выберите пункт Добавить следующую.

При копировании данных Excel в базу данных Access исходные данные в Excel не изменяются.

Перемещение данных из Excel в Access

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

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

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

При перемещении данных из Excel в Access необходимо выполнить три основных шага.

Примечание: Сведения о моделировании данных и связях в Access см. в статье Основы проектирования баз данных.

Шаг 1. Импорт данных из Excel в Access

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

Очистка данных перед импортом

Перед импортом данных в Access в Excel рекомендуется:

• Преобразуйте ячейки, содержащие неатомные данные (т. е. несколько значений в одной ячейке), в несколько столбцов. Например, ячейку в столбце «Навыки», которая содержит несколько значений навыков, таких как «Программирование на C#», «Программирование VBA» и «Веб-дизайн», следует разбить на отдельные столбцы, каждый из которых содержит только одно значение навыка.
• Используйте команду TRIM для удаления начальных, конечных и нескольких внедренных пространств.
• Удалите непечатаемые символы.
• Поиск и исправление орфографических ошибок и ошибок препинания.
• Удалите повторяющиеся строки или повторяющиеся поля.
• Убедитесь, что столбцы данных не содержат смешанные форматы, особенно числа, отформатированные в виде текста, или даты, отформатированные в виде чисел.

Дополнительные сведения см. в следующих разделах справки Excel:

• Первые 10 способов очистки данных
• Фильтр уникальных значений или удаление повторяющихся значений
• Преобразование чисел из текстового формата в числовой
• Преобразование дат из текстового формата в формат даты

Примечание: Если ваши потребности в очистке данных сложны или у вас нет времени или ресурсов для самостоятельной автоматизации процесса, вы можете использовать стороннего поставщика. Для получения дополнительных сведений найдите «программное обеспечение для очистки данных» или «качество данных» в любимой поисковой системе в веб-браузере.

Выбор оптимального типа данных при импорте

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

Числовой формат Excel

Тип данных Access

Рекомендации

Тип данных Access Text хранит буквенно-цифровые данные длиной до 255 символов. Тип данных Access Memo хранит буквенно-цифровые данные длиной до 65 535 символов.

Выберите Memo , чтобы избежать усечения данных.

Число, Процент, Дробь, Научный

Access имеет один тип данных Number, который зависит от свойства Field Size (Byte, Integer, Long Integer, Single, Double, Decimal).

Выберите Double (Двойной ), чтобы избежать ошибок преобразования данных.

Access и Excel используют один и тот же серийный номер даты для хранения дат. В Access диапазон дат больше: от -657 434 (1 января 100 г. н.э.) до 2 958 465 (31 декабря 9999 г.).

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

Выберите Дата.

Access и Excel хранят значения времени, используя один и тот же тип данных.

Выберите Время, которое обычно используется по умолчанию.

В Access тип данных Currency хранит данные в виде 8-байтовых чисел с точностью до четырех десятичных разрядов и используется для хранения финансовых данных и предотвращения округления значений.

Выберите Валюта, которая обычно используется по умолчанию.

Access использует значение -1 для всех значений Да и 0 для всех значений No, тогда как Excel использует 1 для всех значений TRUE и 0 для всех значений FALSE.

Выберите Да/Нет, чтобы автоматически преобразовать базовые значения.

Гиперссылка в Excel и Access содержит URL-адрес или веб-адрес, который можно щелкнуть и подписаться.

Выберите Гиперссылка, в противном случае access может использовать тип данных Text по умолчанию.

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

Дополнительные сведения см. в разделе Справки Access Импорт или ссылка на данные в книге Excel.

Автоматическое добавление данных простым способом

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

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

Шаг 2. Нормализация данных с помощью мастера анализатора таблиц

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

1. Перетаскивание выбранных столбцов в новую таблицу и автоматическое создание связей

2. Используйте команды кнопки, чтобы переименовать таблицу, добавить первичный ключ, сделать существующий столбец первичным ключом и отменить последнее действие.

Этот мастер можно использовать для выполнения следующих действий:

• Преобразуйте таблицу в набор таблиц меньшего размера и автоматически создайте связь первичного и внешнего ключей между таблицами.
• Добавьте первичный ключ в существующее поле, содержащее уникальные значения, или создайте новое поле идентификатора, использующее тип данных AutoNumber.
• Автоматическое создание связей для обеспечения целостности ссылок с помощью каскадных обновлений. Каскадные удаления не добавляются автоматически, чтобы предотвратить случайное удаление данных, но вы можете легко добавить каскадные удаления позже.
• Выполните поиск в новых таблицах избыточных или повторяющихся данных (например, одного клиента с двумя разными номерами телефонов) и обновите их по мере необходимости.
• Создайте резервную копию исходной таблицы и переименуйте ее, добавив «_OLD» к ее имени. Затем создается запрос, который восстанавливает исходную таблицу с именем исходной таблицы, чтобы все существующие формы или отчеты, основанные на исходной таблице, работали с новой структурой таблицы.

Шаг 3. Подключение к доступу к данным из Excel

После нормализации данных в Access и создания запроса или таблицы, которые восстанавливают исходные данные, просто подключиться к данным Access из Excel. Теперь ваши данные находятся в Access как внешний источник данных, поэтому их можно подключить к книге через подключение к данным, которое представляет собой контейнер данных, используемый для поиска внешнего источника данных, входа в нее и доступа к нему. Сведения о подключении хранятся в книге, а также могут храниться в файле подключения, например в файле подключения к данным Office (ODC) (расширение ODC-файла) или файле имени источника данных (расширение DSN). После подключения к внешним данным вы также можете автоматически обновлять (или обновлять) книгу Excel из Access при каждом обновлении данных в Access.

Получение данных в Access

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

Пример данных в не нормализованной форме

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

Идентификатор заказа

Импорт из Excel в Access

Перенос таблиц Excel в СУБД Access осуществляется при помощи интерфейса последней в несколько простых шагов:

1. Откройте либо создайте новую базу данных.

2. На вкладке «Внешние данные» в группе «Импорт и связи» нажмите на иконку «Excel»

3. Перед Вами появиться окно, где необходимо выбрать сам файл источника и способ его импортирования:

• Перенос данных из источника в новую таблицу базы данных;
• Если в базе уже имеются таблицы, то можно добавить импортируемые данные к уже существующим;
• Импорт с сохранением связи с источником. Этот способ позволяем моментально обновлять таблицы в БД при их изменении в файле источника, но не наоборот.

Для примера используем первый вариант.

4. Далее следует указать диапазон, в котором находится требуемая информация. Это может быть лист либо именованный диапазон. Начиная с данного этапа, если Вас все устраивает, процесс можно закончить, подтвердив нажатием на кнопку «Готово».

5. Следующие 2 шага определяют заголовки будущей таблицы:

• Указывается содержание в исходном файле заголовков.
  
• Обозначаются имена заголовка, тип данных и индекс для каждого поля. Также любой столбец можно пропустить, установив галочку на элементе формы «не импортировать (пропустить) поле».
  

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

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

• Критерий Манна-Уитни
• Подключение MySQL в Excel
• Подключение Excel к SQL Server

Если материалы office-menu.ru Вам помогли, то поддержите, пожалуйста, проект, чтобы я мог развивать его дальше.

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

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

Проведет вебинар сертифицированный тренер Microsoft, автор популярных учебных курсов «Visual Basic for Application (VBA). Автоматизация работы в Microsoft Excel» и «Microsoft Access. Разработка баз данных» Завьялов Андрей Николаевич. Андрей Николаевич – ведущий преподаватель центра «Специалист» по курсам Microsoft Access и программированию в среде Microsoft Office на языке VBA. В центре преподает с 1999 года, успешно обучил несколько тысяч специалистов. Он умеет доступно изложить материал, интересно отвечает на вопросы. Его выпускники отмечают доброжелательность преподавателя к слушателям, использование практических примеров из жизни на занятиях и стремление передать весь свой огромный опыт и глубокие знания.

Поделиться

с 18.05.2024
по 01.06.2024

суббота утро-день
10:00 — 17:10