В excel из mysql: Экспорт данных из таблицы MySQL в Excel

Содержание

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

Иногда бывает проще работать с некоторыми данными в Excel, прежде чем импортировать их в базу данных MySQL.

Импорт Excel в MySQL phpMyAdmin

Иногда бывает проще работать с некоторыми данными в Excel, прежде чем импортировать их в базу данных MySQL.

Для этого сделайте несколько простых шагов.

Короче: экспортируйте файл Excel в CSV и импортируйте CSV в новую таблицу с помощью PHPMyAdmininterface.

Начиная с Excel, в примере ниже один лист, содержащий некоторые данные:

Лист Excel с данными

Выберите «Сохранить как», чтобы выбрать другой формат.

Опция Excel Сохранить как

Прокрутите вниз до формата CSV (с разделителями-запятыми) (* .csv).

Поиск формата разделенной запятой CSV

Убедитесь, что некоторые функции будут потеряны — CSV является текстовым форматом, никакие особенности Excel не будут экспортироваться (функции, программы и т. Д.).

Подтвердите потерю специальных функций Excel

Как импортировать Excel в PHPMyAdmin

В PHPMyAdmin перейдите в меню «Импорт».

Меню импорта в PHPMyAdmin

Найдите файл CSV, который был недавно сохранен, и выберите соответствующий формат:

Просмотрите локальный файл и выберите соответствующий формат CSV

Посмотрите на другие параметры, которые могут применяться, например, если столбцы имеют имена, проверьте соответствующий параметр «Первая строка файла содержит имена столбцов таблицы» и нажмите «Перейти»

Специфические параметры импорта CSV

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

Импортировать результаты со ссылками на базу данных и таблицу

Теперь можно просмотреть таблицу!

Отображение базы данных

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

Структура базы данных

PHPMyAdmin импортировать CSV в MySQL

В PHPMyAdmin импортировать CSV-файл в MySQL довольно просто.

После того, как данные подготовлены в CSV, следуйте структуре таблицы, откройте таблицу в PHPMyAdmin, перейдите на вкладку import, найдите файл CSV для импорта на ваш компьютер, убедитесь, что формат CSV был выбран в параметре формата файла. и импортируйте файл CSV в MySQL в PHPMyAdmin.

PHPMyAdmin импортировать CSV в MySQL option in import screen

Как импортировать Excel в MySQL PHP MyAdmin

Чтобы импортировать Excel в MySQL PHP MyAdmin, экспортируйте файл Excel в файл CSV. Затем импортируйте его в PHP MyAdmin, используя опцию файла данных CSV.

При импорте файла в MySQL из Excel прокрутите вниз до параметров «Формат» и выберите файл CSV. Это позволит преобразовать Excel в MySQL в PHP MyAdmin без необходимости что-либо программировать и просто с помощью графического интерфейса пользователя.

Узнайте, как импортировать данные Excel в базу данных MySQL

Как импортировать Excel в MySQL PHP MyAdmin

Создание базы данных в DirectAdmin

Если на вашем веб-сервере еще нет базы данных, независимо от используемого программного обеспечения для управления сервером, такого как cPanel или DirectAdmin, вам сначала необходимо создать базу данных, прежде чем вы сможете импортировать в нее какие-либо данные.

Просто перейдите на панель управления веб-пользователем и найдите экран управления MySQL. Там вы должны иметь возможность создать как базу данных, так и пользователя, который может получить доступ к этой базе данных — и то, и другое необходимо для того, чтобы иметь функциональную базу данных для вашего веб-сайта! Просто создайте его и используйте имя пользователя и пароль, выберите для подключения к базе данных и импорта экспорта, CSV или Excel в свою базу данных.

Создание новой базы данных на веб-сервере DirectAdmin

Часто Задаваемые Вопросы

Как импортировать файл Excel в PhpMyAdmin?: В PhpmyAdmin перейдите в меню «Импорт», выберите соответствующий формат: ищите другие варианты, которые могут применяться, и нажмите «Go». Затем появится подтверждение импорта, а также ссылки на полученную базу данных и таблицу.

Закончите Excel 2019 для начинающих в видео

Microsoft Excel учебный курс онлайн

Об авторе — YB Digital
Йоанн — международный бизнес-консультант, фасилитатор и координатор. Он проработал более 10 лет в глобальном бизнесе, цепочках поставок, финансах, проектах SAP ERP, веб-оптимизации и SEO для различных отраслей, таких как логистика, косметика, потребительские товары, косметика или мода, в более чем 30 странах по всему миру. .

содержание, данные, база данных, превосходить, Как импортировать файл Excel в базу данных MySQL в PHPMyAdmin, Импортировать, импортировать файл Excel в MySQL, импортировать Excel в MySQLimport Excel в PHPMyAdmin, MySQL, вариант

Подключение к источнику данных средствами MS Excel — Разработка на vc.ru

Бывают ситуации, когда на рабочей станции отсутствуют такие средства взаимодействия с БД как: MS SQL Server Management Studio, Aquafold Aqua Data Studio, DBeaver и т.п., а вероятность их установки в краткосрочной перспективе близка к нолю. В то же время, присутствует острая необходимость подключения к этой самой БД и работы с данными. Как оказалось, на помощь может прийти старый добрый MS Excel.

13 366
просмотров

В моем случае требовалось подключиться к MS SQL Server, однако, MS Excel умеет устанавливать соединение не только с ним, но и с большинством современных БД: MySQL, PostgreeSQL, IBM DB2 и даже Oracle и Teradata, а также с файлами данных CSV, XML, JSON, XLS(X), MDB и другими.

Теперь немного о действиях, совершенных мной с целью подключения к базе:

В новой книге на ленте выбираем «(1) Данные» -> «(2) Получение внешних данных» -> «(3) Из других источников» -> «(4) С сервера SQL Server».

Далее, в окне Мастера подключения к данным, заполняем «(1) Имя сервера» -> «(2) Учетные сведения»[ -> «(3) Имя пользователя» и «Пароль»]. Таким образом, мы сообщаем MS Excel, с каким сервером мы хотим установить соединение и какой метод аутентификации хотим использовать. Я использовал «проверку подлинности Windows», но возможно также указать учетные данные отличные от установленных в Windows.

Выбираем целевую «(1) Базу данных» -> «(2)(3) Определенную таблицу» или «Несколько таблиц» или же базу в целом (тогда оба «чекбокса» оставляем пустыми).

После всех проделанных манипуляций, Мастер подключения предложит сохранить файл подключения. Потребуется задать «(1) Имя файла». Желательно также указать «(2) Описание» и «(3) Понятное имя файла», чтобы спустя время было понятно какой файл подключения к какой базе или таблице обращается.

Теперь выбрать созданное подключение можно будет следующим образом: «(1) Данные» -> «(2) Получение внешних данных» -> «(3) Существующие подключения».

Открыв только что созданное подключение, в случае если вы соединялись с базой в целом, MS Excel опять предложит выбрать одну или несколько конкретных таблиц:

Определив таблицы, MS Excel предложит выбрать «(1) Способ представления данных» и «(2) Куда следует поместить данные». Для простоты я выбрал табличное представление и размещение на уже имеющемся листе, чтобы не плодить новые. Далее следует нажать на «(3) Свойства».

В свойствах подключения, нужно перейти на вкладку «(1) Определение». Здесь можно выбрать «(2) Тип команды». Даже если требуется выгружать лишь одну таблицу без каких-либо связей, настоятельно рекомендую выбрать SQL команду, чтобы иметь возможность ограничить размер выгружаемой таблицы (например, с помощью TOP(n)). Так, если вы попытаетесь выгрузить целиком таблицу базы, это может привести в лучшем случае к замедлению работы MS Excel, а в худшем к падению программы, к тому же – это необоснованная нагрузка на сам сервер базы данных и на сеть. После того как «(3) Текст команды» будет введен и нажата кнопка «ОК», MS Excel предложит сохранить изменения запроса – отвечаем положительно.

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

Теперь наше подключение будет находиться в книге и доступно в меню «(1) Данные» -> «(2) Подключения».

Как видно из кнопок формы, здесь мы можем:

«(1) Добавить» в книгу новое подключение из уже существующих;

«(2) Удалить» подключение из книги;

Отредактировать «(3) Свойства» подключения;

«(4) Обновить» данные, предоставляемые подключением.

Подытоживая весь вышеописанный опыт, хочется сказать, что конечно же по функционалу и кругу решаемых задач MS Excel не сравнится с такими системами как MS SQL Server Management Studio, Oracle SQL Developer и подобными. Но, в условиях ограниченного времени и доступных инструментов, при необходимости быстро получить доступ к данным из базы, MS Excel вполне справляется.

Экспорт результатов запроса Mysql в Excel?

Обычный способ добиться этого — экспортировать в CSV, а затем загрузить CSV в Excel.

Для серверного Excel-файла CSV из запроса SELECT выполните следующее:

 ВЫБЕРИТЕ... ИЗ какой-то таблицы, ГДЕ и т. д.
INTO OUTFILE 'someTableExport.csv' НАБОР СИМВОЛОВ utf8mb4
ПОЛЯ, ЗАКРЫВАЕМЫЕ ',' НЕОБЯЗАТЕЛЬНО ЗАКРЫТЫЕ '"' ИСКЛЮЧАЕМЫЕ ''
СТРОКИ, ЗАВЕРШАЕМЫЕ '\r\n';

Для серверного Excel-файла CSV используйте mysqldump примерно так:

 mysqldump -h serverHostName -u mysqlUserName -p --tab="someTableExport.csv" --fields-Optional-Enclosed-by=0x22 --fields-escaped-by='' --fields-terminated-by=0x2C - -lines-terminated-by=0x0D0A --databases имя_базы_данных --tables таблица1 таблица2 таблица3

Для клиентского файла CSV-файла, удобного для Excel, с использованием mysqlsh (оболочка MySQL), например:

 mysqlsh --user="mysqlUserName" --host="serverHostName" --port=3306 --schema="databaseName"
# После подключения выполните следующее:
util. exportTable("tableName", "file:///C:/Users/You/Desktop/test.csv", { диалект: "csv", fieldsEscapedBy: ""})

Форматы CSV по умолчанию в Excel и MySQL:

Помните, что Excel имеет свои собственные _{недостаточно документированные} идеи о том, как следует форматировать CSV-файлы, и они противоречат собственным представлениям MySQL о CSV-файлах; хотя Excel в значительной степени совместим с RFC 4180, вам все равно нужно подталкивать MySQL и связанные с ним инструменты для создания файлов CSV, которые Excel не будет неправильно интерпретировать:

	Эксель	MySQL (по умолчанию)	MySQL (если настроено)
SQL `NULL`	Значение нулевой длины	Литерал `\N`	Литерал `NULL`
Текстовые значения, не содержащие запятых, кавычек или разрывов строк	Не прилагается	Не прилагается	Включено в `"`
Текстовые значения, содержащие запятые, кавычки или разрывы строк	Включено в `"`	Не прилагается	Включено в `"`
Нетекстовые значения	Не прилагается	Не прилагается	Не прилагается
Разрывы строк и табуляции в текстовых значениях	Буквенный	Экранировано как `[\r]\n`	Буквенный
Двойные кавычки в текстовых значениях	Двойной `""`	Скрыто как `\"`	Двойной `""`
Разделитель полей	`,`	`\t` (Вкладка)	`,`
Разделитель записей	`\r\n`	`\n`	`\r\n`
Запятые внутри текстовых значений без кавычек	(Результаты в неверных данных таблицы)	Не экранировано	Всегда будет заключаться в кавычки, если значение содержит запятую
Поддержка UTF-8	Excel 2007-2013: — требуется ведущая спецификация UTF-8 Excel 2016+: — обрабатывает без спецификации UTF-8 _{с некоторой подсказкой 900 40}	Использовать `utf8mb4` . Не указывайте старые сломанные кодировки `utf8` или `utf8mb3` .	Использовать `utf8mb4`

Как видно из приведенной выше таблицы, MySQL может генерировать удобные для Excel CSV-файлы, за исключением того, что SQL NULL всегда будет интерпретироваться Excel как обычный текст, хотя использовать PowerQuery или даже просто Find- и-Заменить в Excel, чтобы заменить их пустыми ячейками.

Excel и

специальные Текстовые маркеры CSV

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

Кодировка Excel и UTF-8:

Удивительно, но только когда Excel исполнился 31 год (Excel 2016), когда Excel добавил встроенную поддержку кодировки UTF-8 в файлы без необходимости спецификации , но это по-прежнему по умолчанию импортирует и экспортирует файлы CSV с использованием системной кодировки по умолчанию, отличной от Unicode (например, Windows-1252 ).

Опция	`ВЫБЕРИТЕ ВНЕФАЙЛ`	`mysqldump --вкладка`	`mysqldump > файл.csv`	`mysqlsh`	Верстак MySQL
CSV на стороне сервера	Правда	Правда	Правда	Правда	Сломанный
Удаленный (на стороне клиента) CSV	Ложь	Ложь	Ложь	Правда	Сломанный
Поддержка версии сервера MySQL	Все версии	Все версии	Все версии	Только 5.7 и выше	Все версии

Вы можете выполнить экспорт на стороне сервера CSV, используя предложение INTO OUTFILE запроса SELECT .
- Поскольку это «обычный» SQL, который выполняется сервером MySQL, он работает независимо от того, какое клиентское средство MySQL вы используете, поэтому вам не нужно устанавливать MySQL Workbench.
- … но поскольку это экспорт на стороне сервера, вам необходимо иметь разрешение на запись в файловую систему сервера, которого у вас может не быть, и в этом случае рассмотрите возможность использования специальных инструментов экспорта, таких как mysqldump (см. ниже).
Предложение MySQL OUTFILE имеет ряд необязательных подпунктов , которые должны быть указаны для некоторого уровня совместимости с собственной программой чтения CSV Excel:
- ПОЛЯ...
  - ЗАВЕРШЕНО (по умолчанию: '\t' , для Excel используйте ',' )
  - [НЕОБЯЗАТЕЛЬНО] ЗАКРЫТ (по умолчанию: '' , должно быть '"' с ключевым словом ОПЦИОНАЛЬНО )
  - ESCAPED BY (по умолчанию: '\\' , для Excel используйте '' )
- ЛИНИИ. ..
  - TERMINATED BY (по умолчанию: '\n' , для Excel используйте '\r\n' )
  - НАЧИНАЯ С (по умолчанию: '' , для Excel вы можете опустить это или использовать по умолчанию MySQL).
- Не используйте ENCLOSED BY (без предыдущего ключевого слова НЕОБЯЗАТЕЛЬНО ), так как это будет заключать в кавычки все значения, независимо от типа (т. текст (строки) вместо чисел).
Обратите внимание, что нет возможности указать MySQL на вывод SQL NULL как пустые поля, и поэтому Excel будет интерпретировать их как текстовые строки без кавычек (т. е. как "NULL" ), поэтому после импорта файла вам нужно будет выполнить поиск и замену в Excel.
Если ваше имя файла INTO OUTFILE (например, 'someTableExport.csv' выше) не является абсолютным путем, то оно будет сохранено в каталоге datadir вашей базы данных. Выполнить ПОКАЗАТЬ ПЕРЕМЕННЫЕ, КАК 'datadir'; , чтобы получить путь. Обратите внимание, что у вас может не быть разрешения на чтение/запись для новых файлов в этом каталоге.

Таким образом, ваш запрос ( SELECT * FROM document WHERE documentid... ) будет выглядеть примерно так:

 SELECT
    *
ОТ
    документ
ГДЕ
    ID документа IN (ВЫБЕРИТЕ ID документа ИЗ TaskResult, ГДЕ taskResult = 2429)
В
    ВНЕФАЙЛ 'someTableExport.csv'
    ПОЛЯ, ЗАКРЫВАЕМЫЕ ',' НЕОБЯЗАТЕЛЬНО ЗАКРЫТЫЕ '"', ИСКЛЮЧАЕМЫЕ ''
    СТРОКИ, ЗАВЕРШАЕМЫЕ '\r\n';

Чтобы сохранить дамп в CSV-файл с помощью параметра командной строки --tab , как описано в разделе документация mysqldump .
К сожалению, mysqldump ‘s --tab= option не будет работать для удаленных серверов MySQL: это потому, что --tab="fileName.csv" может представлять только путь на сервере.
- Хотя вы можете использовать перенаправление stdout для создания локального файла (например, mysqldump --etc > output.csv ), вы не можете использовать --fields-terminated-by и другие параметры формата с stdout , что делает его бесполезным для вывода, совместимого с Excel. Поэтому, если вы находитесь удаленно и не можете использовать ssh -in, вам нужно будет вместо этого использовать MySQL Shell ( mysqlsh ) (см. ниже).
Обратите внимание, что mysqldump не поддерживает запросы SELECT для дампа данных: поддерживает простые фильтры в стиле WHERE с параметром --where= , но это не поддерживает такие вещи, как фильтрация используя INNER JOIN (хотя в качестве обходного пути вы можете SELECT в новую таблицу, а затем запустить mysqldump на этой новой таблице. Обратите внимание, что вы не можете использовать TEMPORARY TABLE с mysqldump , поскольку временные таблицы ограничены подключением) .

В случае OP из-за ограничений, присущих тому, как работает параметр командной строки --where= , им потребуется экспортировать обе таблицы ( документ и TaskResult ) и применить логику фильтрации в Excel. PowerQuery или аналогичный. Выполните экспорт следующим образом:

 mysqldump -h serverHostName -u mysqlUserName -p --tab="someTableExport.csv" --fields-Optional-Enclosed-by=0x22 --fields-escaped-by='' --fields-terminated-by=0x2C --lines-terminated-by=0x0D0A --databases имя_базы_данных --tables документ TaskResult

Приведенная выше командная строка должна работать без изменений в Windows cmd.exe , macOS zsh и bash в Linux — предоставлено mysqldump 9001 0 в вашем ПУТЬ .
Использование символов в шестнадцатеричном кодировании означает обход хлопот, связанных с выяснением того, как передавать двойные кавычки и разрывы строк в качестве литералов в вашей оболочке и терминале ( 0x22 is " , 0x2C is , 90 010 , и 0x0D0A равно \r\n ).
Избегайте , используя параметр --password= (он же -p ) на командной строки mysqldump , так как это будет означать, что ваш пароль будет сохранен в виде открытого текста в файл истории вашего терминала или консоли, что представляет собой очевидную серьезную угрозу безопасности.
- Таким образом, если вы находитесь в интерактивном сеансе командной строки и вам необходимо указать пароль, тогда mysqldump предложит вам ввести пароль сразу же при запуске программы, поэтому он не будет сохранен в файле истории.
- Если вы хотите запустить mysqldump в неинтерактивном контексте (например, из веб-приложения, демона или другого процесса), то (обычно) нет файла истории, о котором нужно беспокоиться, но вы все равно должны рассмотреть альтернативные подходы, прежде чем прибегая к обработке паролей небезопасным способом.
Если не указать абсолютный путь, а использовать короткое (неполное) имя файла, например INTO OUTFILE 'output.csv' или INTO OUTFILE './output.csv' b, выходной файл будет сохранен в каталог, указанный SHOW VARIABLES LIKE 'datadir'; .

К сожалению, вы не можете (если только в ваших данных нет двойных кавычек): по состоянию на конец 2022 года MySQL Workbench имеет открытую ошибку, связанную с его собственной функцией экспорта CSV: выходные файлы никогда не экранирует символы двойных кавычек в тексте, поэтому почти все программное обеспечение для обработки CSV сообщает об искаженном CSV-файле или импортирует данные в неправильные столбцы, что делает его совершенно непригодным для использования с Excel.

Вероятно, это самый простой вариант, но вам может потребоваться установить оболочку MySQL, поскольку в большинстве установок MySQL она не входит в комплект поставки.
MySQL Shell поддерживает подключение к MySQL Server 5.7 и выше (но не к более ранним версиям). Если вы все еще используете MySQL Server 5.6 или более раннюю версию (тогда вы на самом деле в любом случае следует обновить до 5.7 или новее) вам придется придерживаться mysqldump , запускаемого локально на самом сервере MySQL (но вы, конечно, можете использовать сеанс ssh ). Новый протокол «MySQL X» ( mysqlx://user@host/schema ) не поддерживается MySQL 5.7, но mysqlsh поддерживает не-X соединения с параметрами командной строки старого стиля.

Установите MySQL Shell, если она еще не установлена.
Если вы запускаете MySQL Shell без каких-либо аргументов командной строки (например, потому что вы используете ярлык меню «Пуск» в Windows)), тогда используйте команду \connect для подключения. Для MySQL 5.7 используйте \connect mysql://username@hostname Для MySQL 8. 0+ существует множество способов подключения, включая протокол «MySQL X», а также «классические» подключения. Обратитесь к документации для получения дополнительной информации. Если ваше имя пользователя содержит букву @ символов, то вам необходимо закодировать их в процентах (например, если вы используете Azure MySQL, ваше полное имя пользователя будет выглядеть как username%[email protected] ). Сразу после ввода команды \connect вам будет предложено ввести пароль в интерактивном режиме.
Если вы можете запустить mysqlsh с аргументами, то вы можете запустить mysqlsh --user="userName" --host="hostName" --port=3306 --schema="dbName" напрямую без использования команды \connect .
После подключения запустите команду util.exportTable(tableName, outputUri, options) со следующими аргументами:
tableName : имя вашей таблицы.
К сожалению, похоже, нет способа применить фильтр WHERE или экспортировать результаты запроса SELECT (хотя, как и в случае с mysqldump , вы всегда можете сохранить результаты запроса в новом 0009 TABLE , затем экспортируйте эту таблицу, затем DROP TABLE , когда вы закончите с ней. Помните, что TEMPORARY TABLE здесь не будет работать, так как таблицы, созданные в одном сеансе, не видны ни в каком другом сеансе, а mysqlsh будет иметь свой собственный сеанс.
outputUri : для локального сохранения файла используйте URI file:///.
В Windows вы можете использовать прямую косую черту в качестве разделителя имен каталогов вместо обратной косой черты. например файл:///C:/Users/Me/Desktop/export.csv .
варианты : Для обеспечения совместимости с Excel укажите { диалект: "csv", fieldsEscapedBy: ""} .
Диалект : параметр "csv" устанавливает совместимые с Excel значения по умолчанию для всех параметров OUTFILE , кроме одного, поэтому необходимо также указать fieldsEscapedBy: "" , иначе SQL NULL будет отображаться как \N (буквально), в то время как двойные кавычки и разрывы строк внутри текстовых значений будут экранированы обратной косой чертой, что не поддерживается Excel.

php — Как экспортировать таблицу в excel из базы данных mysql?

спросил
9 лет, 2 месяца назад

Изменено
9 лет, 2 месяца назад

Просмотрено
17 тысяч раз

У меня есть таблица в mysql, которая довольно велика для более чем 100 тыс. строк, и я хочу экспортировать ее в Excel. Тем не менее, я попробовал функцию экспорта в excel на phpmyadmin, но дамп файла excel занимает целую вечность. Это даже не демпинг. Ошибка всегда «соединение сброшено». Есть ли альтернативный способ, как это сделать??

php
mysql
excel
phpmyadmin
экспорт

Во-первых, 100 000 строк в Excel — это ужасная идея, и, конечно, это займет некоторое время. Это займет некоторое время, чтобы просто открыть. Если вы ДОЛЖНЫ сделать это, попробуйте:

 SELECT order_id, product_name, qty
ОТ заказов
ВНЕШНИЙ ФАЙЛ '/tmp/orders.csv'
ПОЛЯ, ЗАКРЫВАЕМЫЕ ','
ЗАКРЫТ '"'
СТРОКИ, ЗАКРЫВАЕМЫЕ '\n'

Это должно дать вам файл с именем: /tmp/orders.csv , который откроется в Excel.

Медленность вашего экспорта, вероятно, связана с сервером, на котором работает phpmyadmin. Я регулярно экспортирую миллионы строк с удивительной скоростью.

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