Как импортировать таблицу из excel в access

Копирование данных из 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

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

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

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

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

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

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

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

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

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

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

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

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

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

Перемещение данных из 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 рекомендуется:

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

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

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

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

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

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

Excel числовом формате

Тип данных Access

Комментарии

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

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

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

Number, Percentage, Fraction, Scientific

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

Чтобы избежать ошибок преобразования данных, выберите значение Double.

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

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

Дополнительные сведения см. в статье «Изменение системы даты, формата или двузначной интерпретации года» и «Импорт или ссылка на данные в Excel книге».

Выберите дату.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Импорт данных

В программе есть возможность автоматического импорта информации в справочники:

• из таблиц Microsoft Excel (формат *.xls);
• из файлов Microsoft Word (формат *.doc);
• из программы «Директор» фирмы Аверс;
• из текстового файла (формат *.tхt);
• из файлов с разделителями (формат *.csv);
• из файлов Microsoft Access (формат *.mdb).

Импорт из файлов Microsoft Excel, Word

При импорте из таблицы формата MS Excel или Word программа ищет названия столбцов справочника, которые должны обязательно содержаться в таблице Excel (Word).

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

Другими словами, если распечатать все справочники, удалить в них всю информацию, то затем ее можно в полном объеме импортировать из распечатанных ранее документов, не поправляя ничего в таблицах MS Excel (Word).

Чтобы выполнить импорт:

• откройте нужный справочник;
• нажмите кнопку , выберите нужный файл:
• откроется окно «Импорт данных». Проверьте, насколько полно информация была взята из файла. Нажмите кнопку , при этом будут исключены строки, в обязательных столбцах которых информации не представлено, либо она не уникальна. Нажмите , чтобы импортировать информацию.

Импорт из программы «Директор»

Если Вы используете программу «Директор», то данные, внесенные ранее, можно не набирать заново с клавиатуры, а автоматически загрузить в справочники.

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

Чтобы выполнить импорт:

• откройте нужный справочник;
• нажмите кнопку ;
• выберите тип файлов «База данных «Директор» (*.dbc)»;
• выберите файл базы данных программы «Директор», нажмите кнопку «Открыть»;
• откроется окно «Импорт данных». Проверьте, насколько полно информация была взята из файла. Нажмите кнопку , при этом будут исключены строки, в обязательных столбцах которых информации не представлено, либо она не уникальна. Нажмите , чтобы импортировать информацию.

Импорт из текстового файла формата *.tхt

При импорте из текстового файла (формат *.tхt) можно заполнить только один столбец справочника.

Первой строкой в текстовом файле должна быть строка с названием этого столбца. Например, в справочнике «Преподаватели» таковым является столбец «Фамилия И. О.».

Чтобы выполнить импорт:

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

Импорт из файлов формата *.csv

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

Чтобы выполнить импорт:

• откройте нужный справочник;
• нажмите кнопку ;
• выберите тип файлов «CSV-файлы (*.csv)»;
• выберите файл формата *.csv и нажмите кнопку «Открыть»;
• откроется окно «Импорт данных». Проверьте, насколько полно информация была взята из файла. Нажмите кнопку , при этом будут исключены строки, в обязательных столбцах которых информации не представлено, либо она не уникальна. Нажмите , чтобы импортировать информацию.

Импорт из файлов Microsoft Access (формата *.mdb)

Импорт из документа формата *.mdb может производиться при переходе на новый учебный год. Данные о преподавателях, студентах и т.д. будет легче ввести из старой базы данных, чем заново заполнять вручную.

Чтобы выполнить импорт:

• откройте нужный справочник;
• нажмите кнопку ;
• выберите тип файлов «Базы данных Microsoft Access (*.mdb)»;
• выберите файл Microsoft Access (формата *.mdb) и нажмите кнопку «Открыть»;
• откроется окно «Импорт данных». Проверьте, насколько полно информация была взята из файла. Нажмите кнопку , при этом будут исключены строки, в обязательных столбцах которых информации не представлено, либо она не уникальна. Нажмите , чтобы импортировать информацию.