Oc-windows.ru

IT Новости из мира ПК
1 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Перенос данных из excel в access

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

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

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

При переносе данных из Excel в Access выполняются три основных действия.

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

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

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

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

Перед импортом данных в Access в Excel рекомендуется выполнить следующие действия.

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

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

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

Поиск и устранение ошибок правописания и знаков препинания.

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

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

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

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

Выбор наиболее подходящих типов данных при импорте

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

Формат номера Excel

Тип данных Access

Текст, поле MEMO

Тип данных «текст» в Access хранит буквенно-цифровые данные длиной до 255 символов. Тип данных МЕМО Access сохраняет алфавитно-цифровые данные до 65 535 символов.

Нажмите кнопку » записка «, чтобы не усекать данные.

Число, процент, дробь, экспоненциальный

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

Чтобы избежать ошибок при преобразовании данных, выберите значение 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 для всех значений, в то время как Excel использует 1 для всех значений истина и 0 для всех значений «ложь».

Выберите «да/нет», который автоматически преобразует базовые значения.

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

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

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

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

Простой способ автоматического добавления данных

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

Читать еще:  Назовите функции ms excel

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

Действие 2: нормализация данных с помощью мастера анализа таблиц

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

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

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

С помощью этого мастера вы можете сделать следующее:

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

Добавьте первичный ключ в существующее поле, содержащее уникальные значения, или создайте новое поле с типом данных «Счетчик».

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

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

Создавайте резервные копии исходной таблицы и переименование, добавив «_OLD» к имени. Затем вы создаете запрос, который восстанавливает исходную таблицу с исходным именем таблицы, так что существующие формы и отчеты, основанные на исходной таблице, будут работать с новой структурой таблицы.

Шаг 3: подключение к данным Access из Excel

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

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

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

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

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

Сводная из базы данных Access

Предположим, что у вас есть большая база данных. Назовем её «products». И под большая я подразумеваю порядка 3млн записей(строк) или больше. Непосредственно на один лист Excel такое количество данных точно не поместится. Можно, конечно, хранить и на разных листах. Тогда можно воспользоваться статьей Сводная таблица из нескольких листов. Но во-первых, данный метод работает не очень стабильно и может требовать изменений в зависимости от версии Excel и так же требует разрешения выполнения макросов, а во-вторых, для такого количества записей это не лучшее решение, т.к. хранить такое количество данных в книгах Excel не совсем правильно. Поэтому даже если у вас есть несколько книг/листов, забитых нужными данными по полной и надо эти данные объединить для дальнейшего анализа сводной таблицей — то самое лучшее на мой взгляд решение, это объединить их через MS Access в одну таблицу и потом уже построить сводную на основании таблицы не составит труда.

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

  • все таблицы должны содержать одинаковое количество столбцов с полностью идентичными заголовками
  • заголовки не должны содержать переносов строк, тире, дефисов, точек, запятых. Лучше вообще отказаться от любых знаков препинания и сомнительных символов — оставьте только пробелы между словами(и то даже их лучше заменить нижним подчеркиванием)
  • если в таблицах присутствуют числовые данные, которые впоследствии необходимо будет суммировать — убедитесь, что все данные именно числовые и нет текстовых. Это поможет избежать ошибок импорта
  • необходимо убедиться, что таблицы не содержат пустых строк и столбцов, а так же объединенных ячеек
Читать еще:  Как считать в excel

Теперь, когда все таблицы готовы можно приступить к импорту данных в Access. Открываете Access и выбираете Создать (New)Новая база данных (Blank database) . Указываете имя базы и месторасположение(папку):

После создания базы проходим несколько шагов:

  1. переходим на вкладку Внешние данные (External Data) -группа Импорт и связи (Import & Link)Excel.
  2. Выбираем файл, данные из которого необходимо перенести в Access
  3. Указываем Импортировать данные источника в новую таблицу в текущей базе данных (Import the source data into a new table in the current database) :
  4. на следующем шаге будет предложено выбрать лист или именованный диапазон для импорта (Show worksheets, Show named ranges) :

    я выбрал лист «products», т.к. именно так у меня называется лист с данными. Жмем Далее (Next)
  5. на этом шаге просто убеждаемся, что галка Первая строка содержит заголовки столбцов (First Row Contains Column Heading) установлена. Если нет — устанавливаем
  6. жмем Готово (Finish)

Первая часть базы наполнена. Теперь необходимо дополнить созданную в Access таблицу данными других листов или книг. Для этого повторяем все описанные выше шаги, но на 2-м шаге выбираем Добавить копию записей в конец таблицы (Append a copy of the record to the table) . Тогда данные будут дополнены в уже созданную нами таблицу из первого листа, а не будут записаны в новую(чего нам не надо).
Все, теперь можно приступать к созданию сводной таблицы.

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

  • Excel 2010Файл (File)Параметры (Options)Панель быстрого доступа (Quick Access Toolbar)
  • Excel 2007Кнопка офисПараметры Excel (Excel options)Панель быстрого доступа (Quick Access Toolbar)

или непосредственно с панели быстрого доступа:

Выбрать команды из: Все команды (All Commands) . Ищем там Мастер сводных таблиц и диаграмм (PivotTable and PivotChart Wizard) и переносим на панель быстрого доступа:

Теперь жмем на эту кнопку и на первом шаге появившегося окна Мастера выбираем во внешнем источнике данных (external data source) :

на втором шаге жмем кнопку Получить данные (Get Data) :

В появившемся окне необходимо выбрать MS Access Database или База данных MS Access.

Тут есть важный момент. Галочка Использовать мастер запросов (Use the Query Wizard to create/edit queries) должна быть включена. Жмем ОК.

Далее выбираем в правом окне папку, в которой расположена наша база данных. В левом окне выбираем сам файл Базы данных:

Подтверждаем выбор нажатием кнопки ОК.
Далее необходимо создать запрос выборки. По сути можно просто нажать на имя таблицы Базы данных и после этого на значек «>»

Но если вам необходимо будет работать только с некоторыми столбцами из всей таблицы — можно последовательно перенести их в правое поле(предварительно в левом развернув плюсик рядом с именем таблицы). Убрать лишние столбцы из правого поля можно кнопочкой » (Return Data to Microsoft Excel) и жмем Готово. При этом в окне второго шага мастера сводных таблиц и диаграмм правее кнопки Получить данные должна появиться надпись Данные получены (Data fields have been retrieved) :

Если у вас данная надпись появилась, то смело жмем Далее (Next) и на последнем шаге мастера выбираем ячейку и лист, в которые необходимо поместить сводную таблицу:

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

Статья помогла? Поделись ссылкой с друзьями!

Excel и базы данных

Преобразование списков Excel в базу данных Access

Только что мы рассмотрели различные способы экспорта-импорта таблиц базы данных 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
Импорт списков 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.

Ссылка на основную публикацию
Adblock
detector