Импорт и экспорт таблиц Excel в базах данных Access

Импорт и экспорт таблиц Excel в базах данных Access

Интеграция Access 2010 с другими приложениями осуществляется с помощью вкладки Внешние данные ленты. Здесь присутствуют области Импорт и Экспорт. Обмен информацией осуществляется с БД Access, электронными таблицами Excel, текстовыми и XML-файлами, возможен экспорт в PDF и XPS, отправка по электронной почте. При щелчке на кнопке Дополнительно появляется выпадающее меню, позволяющее работать с форматами HTML, dBASE, распределенными базами данных, а также с источниками данных ODBC.

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

Рис. 1. Экспорт в Excel. Выбор места

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

Рассмотрим пример импорта файлов формата XLS. Щелкнув на соответствующей пиктограмме области Импортировать и связать (рис. 2), мы увидим окно, аналогичное изображенному на рис. 1.

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

Рис. 2. Область Импортировать и связать

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

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

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

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

Импорт excel в базу данных

На этом шаге мы рассмотрим использование средства Мастер текстов для процесса импортировния.

Для импортирования текстового файла в Excel выполните команду Данные | Внешние данные | Импорт текстового файла . В открывшемся диалоговом окне Импорт текстового файла найдите файл, предназначенный для импортирования. По умолчанию в этом окне ображаются только текстовые файлы с расширением TXT . Если импортируемый файл имеет другое расширение, то в списке Тип файла диалогового окна Импорт текстового файла выберите опцию Все файлы .

Excel отобразит диалоговое окно Мастер текстов (импорт) . Этот мастер представляет собой последовательность диалоговых окон, в которых определяется информация, необходимая для разбиения строк текстового файла на столбцы таблицы.

Мастер текстов (импорт) — шаг 1 из 3

На рисунке 1 показано первое из трех окон мастера текстов.

Рис. 1. Первый этап импортирования

В разделе Формат исходных данных проверьте тип импортируемого текстового файла: с разделителями или с полями фиксированной длины. Можно также указать строку, с которой нужно начать импортирование.

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

Мастер текстов (импорт) — шаг 2 из 3

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

Рис. 2. Второй этап импортирования (для файлов с символами — разделителями)

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

Если на первом шаге выбрана опция Фиксированной ширины , то появится диалоговое окно, изображенное на рисунке 3. На этом шаге Excel определяет разбиение на столбцы. В разделе Образец разбора данных вертикальными линиями показано, как будут разбиты строки текстового файла на столбцы. Если линии разделения указаны неправильно, то их можно передвигать, вставлять новые и удалять. Инструкции, как это делать, приведены в диалоговом окне.

Рис. 3. Второй этап импортирования (для файлов с фиксированной шириной полей)

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

Мастер текстов (импорт) — шаг 3 из 3

На рисунке 4 показано последнее из трех диалоговых окон средства Мастер текстов (импорт) .

Рис. 4. Третий этап импортирования

В этом окне можно определить, какой формат применять к каждому столбцу (общий, текстовый или формат «дата»). Некоторые столбцы можно пропустить — они не будут импортированы. Если щелкнуть на кнопке Подробнее , откроется дополнительное диалоговое окно (рисунок 5), где можно настроить числовой формат импортируемых данных.

Рис. 5. Диалоговое окно Дополнительная настройка импорта текста

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

Рис. 6. Диалоговое окно Импорт данных

Excel импортирует данные и отобразит панель инструментов Внешние данные (рис. 7), которая помогает работать с импортируемыми данными.

Рис. 7. Панель инструментов Внешние данные

Например, если щелкнуть на кнопке Свойства диапазона данных (рис. 8), то откроется диалоговое окно Свойства внешнего диапазона , в котором можно увидеть и изменить параметры, установленные для данного диапазона импортированных данных.

Как импортировать файл excel в базу данных MySQL

Кто-нибудь может объяснить, как импортировать файл Microsoft Excel в базу данных MySQL?

Например, моя таблица Excel выглядит следующим образом:

Экспортируйте его в некоторый текстовый формат. Самым простым будет, вероятно, версия с разделителями табуляции, но CSV также может работать.

Посмотрите на полпути вниз по странице, так как это даст хороший пример для данных, разделенных вкладкой:

ПОЛЯ, ПРОВЕРЯЕМЫЕ ‘ t’, ВКЛЮЧЕНЫ » ЭФФЕКТИВНЫМ »

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

Существует простой онлайн-инструмент, который может это сделать, называется sqlizer.io.

Вы загружаете в него файл XLSX, вводите имя листа и диапазон ячеек, и он генерирует инструкцию CREATE TABLE и несколько операторов INSERT для импорта всех ваших данных в базу данных MySQL.

(Отказ от ответственности: я помогаю запустить SQLizer)

Ниже приведен еще один способ импорта данных таблиц в базу данных MySQL, которая не полагается на какое-либо дополнительное программное обеспечение. Предположим, вы хотите импортировать таблицу Excel в таблицу sales базы данных MySQL с именем mydatabase .

Выберите соответствующие ячейки:

Вставить в Mr. Data Converter и выберите вывод в формате MySQL:

Измените определения имен таблиц и столбцов в соответствии с вашими требованиями в сгенерированном выпуске:

Если вы используете MySQL Workbench или уже вошли в mysql из командной строки, вы можете выполнить сгенерированный SQL непосредственно из шага 3. В противном случае вставьте код в текстовый файл (например, import.sql ) и выполните эту команду из оболочки Unix:

mysql mydatabase < import.sql

Другие способы импорта из файла SQL можно найти в этом ответе на переполнение стека.

На самом деле существует несколько способов импортировать файл excel в базу данных MySQL с различной степенью сложности и успеха.

Excel2MySQL или Navicat. Полное раскрытие, я являюсь автором Excel2MySQL. Эти 2 утилиты не являются бесплатными, но они являются самым простым вариантом и имеют наименьшие ограничения. Они также включают дополнительные функции, которые помогут импортировать данные Excel в MySQL. Например, Excel2MySQL автоматически создает вашу таблицу и автоматически оптимизирует типы данных полей, такие как даты, время, поплавки и т.д. Если вы спешите или не можете использовать другие параметры для работы с вашими данными, эти утилиты могут удовлетворить ваши потребности.

LOAD DATA INFILE. Этот популярный вариант, пожалуй, самый технический и требует некоторого понимания выполнения команды MySQL. Перед загрузкой и использованием типов полей VARCHAR соответствующего размера необходимо вручную создать таблицу. Поэтому ваши полевые типы данных не оптимизированы. LOAD DATA INFILE имеет проблемы с импортом больших файлов, которые превышают размер «max_allowed_packet». Особое внимание необходимо учесть, чтобы избежать проблем с импортом специальных символов и иностранных символов в Юникоде. Вот недавний пример, который я использовал для импорта csv файла с именем test.csv.

phpMyAdmin. Сначала выберите свою базу данных, затем выберите вкладку «Импорт». phpMyAdmin автоматически создаст вашу таблицу и пометит ваши поля VARCHAR, но не будет оптимизировать типы полей. У phpMyAdmin есть проблемы с импортом больших файлов, которые превышают размер «max_allowed_packet».

MySQL для Excel. Это бесплатная надстройка Excel от Oracle. Этот параметр немного утомительный, поскольку он использует мастер, а импорт медленный и багги с большими файлами, но это может быть хорошим вариантом для небольших файлов с данными VARCHAR. Поля не оптимизированы.

Импорт CSV в SQL Server

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

Использование оператора BULK INSERT

Оператор BULK INSERT в SQL Server позволяет осуществлять импорт данных из файла в таблицу. В предложении WITH для данного оператора можно задавать множество опций, но нам нужны только две из них:

FIELDTERMINATOR

Указывает разделить для столбцов. По умолчанию, разделителем является символ табуляции (t). В CSV разделителем по умолчанию является запятая.

ROWTERMINATOR

Указывает разделитель для строк.

Давайте попробуем выгрузить данные для простого CSV-Файла. Этот файл содержит 10 строк для простого отчета и вы можете скачать его по следующей ссылке. Используйте следующий код:

В качестве разделителя в исходном CSV-файле используется знак табуляции (Tab). В CSV также можно использовать запятые, точки с запятой и другие символы в качестве разделителя столбцов. Главное указать символ в параметре FIELDTERMINATOR.

Выберите теперь все строки из таблицы CSV_Export с помощью оператора SELECT для проверки результата:

На рисунке ниже показан результат:

Как правило, данные из CSV-файла нужно сохранять во временную таблицу, а затем из нее вставлять в основную, т.к. зачастую необходимо парсить даты, числа с плавающей запятой и другие форматы. Их можно спарсить в строку во временной таблице и привести к нужному формату в основной. Например, если мы изменим тип столбца DateReport на DATETIME, SQL Server выдаст следующую ошибку:

В следующем примере показано, как можно обойти эту ошибку и получить дату:

Теперь в основной таблице столбец DateReport будет храниться в формате даты:

Использование SQL Server Management Studio

Импортировать данные из CSV можно стандартными средствами программы SQL Server Management Studio. Для этого выполните следующие шаги:

В обозревателе объектов Object Explorer выберите целевую базу данных, щелкните по ней правой кнопкой мыши и выберите из контекстного меню команду Tasks —> Import Data.

На экране появится диалоговое окно, в котором нужно выбрать источник данных DataSource из выпадающего списка. Выберите вариант Flat File Source и нажмите кнопку Next. На следующем экране укажите путь до файла CSV на жестком диске. Для нашего тестового файла нужно снять галочку «Column names in the first data row», т.к. в файле не содержатся заголовки столбцов в первой строке.

После этого SQL Server Management Studio загрузит файл и отобразит данные в таблице:

Обратите внимание, что программа автоматически определит разделитель между столбцами и строками в CSV-файле. Вы также можете настроить параметры столбцов (по умолчанию SSMS задаст столбцы в формате NVARCHAR(50) с названиями Column 0, Column 1 и т.д.) Перейдите на вкладку Advanced в левом боковом меню:

Как видите я изменил названия столбцов. Если у вас есть строки длиной больше 50, попросите SQL Server проверить все столбцы в файле. Это можно сделать с помощью кнопки Suggest Types (Предложить типы). SQL Server проанализирует первые 100 строк в файле и укажет предлагаемые типы для каждого столбца. В процессе проверки указывается ошибка. В зависимости от размера файла вы можете выбрать вариант, чтобы просмотреть весь файл или просто выбранные поля.

На следующем экране вы можете выбрать таблицу, в которую нужно импортировать данные. Здесь можно выбрать одну из существующих таблиц в базе данных, либо таблицу dbo.report, которую SSMS сгенерирует автоматически. Я рекомендую использовать второй вариант, а потом вручную преобразовать типы данных в основную таблицу.

После всех выполненных шагов SQL Server Management Studio отобразит процесс выполнения, сгенерирует таблицу report и заполнит ее данными:

На следующем рисунке отображена сгенерированная таблица report в окне Object Explorer:

Теперь выполните следующую инструкцию:

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

Импорт таблицы Excel или текстового файла в Numbers на Mac

В Numbers можно импортировать электронные таблицы Excel, а также текстовые файлы с разделителями или с полями фиксированной ширины. Если Вы импортировали текстовый файл с разделителями или с полями фиксированной ширины, но таблица выглядит неправильно, Вы также можете изменить настройки импорта.

Примечание. После редактирования импортированной таблицы изменить настройки импорта невозможно.

Импорт файла Excel

Перетяните файл Excel на значок Numbers в Dock или в папке «Программы».

Также можно выбрать «Файл» > «Открыть» в строке меню Numbers, а затем выберите нужный Вам файл Excel.

Импорт текстового файла

Перетяните файл на значок Numbers в Dock или в папке «Программы».

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

Изменение настроек текстовых файлов с разделителями

Перетяните файл на значок Numbers в Dock или в папке «Программы».

Нажмите на любую область таблицы, чтобы выбрать ее.

В боковой панели «Формат» нажмите вкладку «Таблица», затем нажмите «Изменение настроек импорта».

Нажмите вкладку «С разделителями» в окне «Настройки импорта».

Выберите один или несколько вариантов разделителей рядом с элементом «Разделитель значений» или введите собственный разделитель.

Разделитель — это символ, который размечает разрывы столбцов.

Выполните одно из следующих действий.

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

Кодировка текста. Нажмите всплывающее меню «Кодировка текста» и выберите требуемый вариант.

Игнорирование дублированных разделителей. Выберите параметр «Считать последовательные разделители одним».

Транспонирование строк и столбцов Чтобы транспонировать, то есть поменять местами, строки и столбцы в таблице во время импорта, выберите параметр «Транспонировать строки и столбцы».

Нажмите «Обновить таблицу».

Изменение настроек текстового файла с полями фиксированной ширины

Перетяните файл на значок Numbers в Dock или в папке «Программы».

Нажмите на любую область таблицы, чтобы выбрать ее.

В боковой панели «Формат» нажмите вкладку «Таблица», затем нажмите «Изменение настроек импорта».

Нажмите вкладку «Фиксированная ширина» в окне «Настройки импорта».

Выполните одно из следующих действий.

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

Объединение столбцов. Перетяните синюю стрелку вверх и за пределы адресной строки.

Добавление нового столбца. Нажмите в любой части адресной строки столбца.

Кодировка текста. Нажмите всплывающее меню «Кодировка текста» и выберите требуемый вариант.

Транспонирование строк и столбцов Чтобы транспонировать, то есть поменять местами, строки и столбцы в таблице во время импорта, выберите параметр «Транспонировать строки и столбцы».