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

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

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

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

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

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

• Используйте команду TRIM для удаления начальных, конечных и нескольких внедренных пространств.

• Удалите непечатаемые символы.

• Поиск и исправление орфографических ошибок и ошибок препинания.

• Удалите повторяющиеся строки или повторяющиеся поля.

• Убедитесь, что столбцы данных не содержат смешанные форматы, особенно числа, отформатированные в виде текста, или даты, отформатированные в виде чисел.

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

• Первые 10 способов очистки данных

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

• Преобразование чисел из текстового формата в числовой

• Преобразование дат из текстового формата в формат даты

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

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

После того как данные будут в 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.

Дополнительные сведения см. в статье Импорт данных из внешних источников данных (Power Query).

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

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

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

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

Дополнительные сведения

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.