Экспорт из Excel в MySQL
Установка библиотеки PHPExcel
Для начала хотел бы обратить Ваше внимание на то, что данный урок – это своего рода продолжение урока Генерация прайс-листа в формате Excel при помощи PHP. Phpexcel, который публиковался ранее
При этом если Вы не знакомы с библиотекой PHPExcel и с основами работы с ней, то настоятельно рекомендую просмотреть вышеуказанный урок, так как сегодня мы не будем тратить время на изучение основ. Для данного урока мы будем использовать тестовый сайт, который написан с использованием объектно-ориентированного подхода и шаблона проектирования MVC. Сейчас я не буду приводить его код, так как он будет доступен Вам в дополнительных материалах к уроку. В функционале данного сайта описан механизм загрузки на сервер файлов формата Excel и соответственно, данный механизм нам необходимо доработать, а именно, после успешной загрузки файла на сервер, необходимо прочитать содержимое данного файла и перенести данные из необходимых столбцов таблицы Excel, в базу данных MySql.
Бесплатный курс по PHP программированию
Освойте курс и узнайте, как создать динамичный сайт на PHP и MySQL с полного нуля, используя модель MVC
В курсе 39 уроков | 15 часов видео | исходники для каждого урока
Теперь давайте установим библиотеку PHPExcel. Для этого мы воспользуемся инструментом Composer, (для тех кто не знаком с данным инструментов, рекомендую посмотреть урок Введение в Composer, поэтому открываем командную строку, переходим в каталог с тестовым сайтом и выполняем следующую команду:
Шаг 1. Автоматически собрать данные из файлов Excel
XLS файлы, полученные из исследовательских лабораторий, стандартизированы. Они всегда следуют одному шаблону. Нам нужно извлечь данные из этих исходных файлов и подготовить к дальнейшей обработке.
Именно это и выполняет команда XLTools.ExtractTable:
- Она извлекает данные из внешних Excel или CSV файлов. Вы можете собрать все необходимые данные в одном месте и продолжить работу с полным объемом данных. По большому счету, это инструмент копирования-вставки.
- Извлекая простой диапазон, он будет вставлен как именованная таблица. Только данные в формате таблицы могут служить источником данных для дальнейших операций, в т.ч. для выполнения SQL запросов в Excel.
Например, нам нужно извлечь эту таблицу с числовыми кодами, присвоенными лабораториям:
dbForge Studio for MySQL предоставляет инструменты для экспорта и импорта данных!
При работе с базами данных MySQL вы можете использовать функционал dbForge Studio for MySQL, который поможет перенести MySQL данные в новую базу данных, после обновления или создания “бэкапа”. С помощью мастера экспорта вы можете легко экспортировать табличные данные в различные файловые форматы. После экспорта табличных данных, вы можете импортировать их в любое время, используя мастер импорта данных.
- Экспорт данных из баз MySQL в 12 популярных форматов
- Импорт данных в таблицу базы MySQL из 8 популярных форматов
- Огромное количество опций для настройки процесса экспорта и импорта данных
- Доступные и понятные мастера для экспорта и импорта данных
- Хорошо проверенная функциональность, созданная разработчиками для разработчиков
Импорт данных
Если вам нужно импортировать данные в таблицу базы данных MySQL, вы можете положиться на dbForge Studio. Инструмент для импорта данных, предлагаемый ею, импортирует данные именно так, как вы того захотите. Он позволяет пользователям импортировать данные из восьми широко используемых форматов и точно настраивать процесс импорта.
В dbForge Studio инструмент для импорта данных в базы MySQL представлен в виде хорошо продуманного всеобъемлющего мастера, где каждый шаг процесса представлен на отдельной странице. Кроме того, у вас есть возможность сохранить шаблон импорта, который вы можете использовать позже для импорта данных с помощью этого же мастера или через командную строку.
Возможности мастера импорта данных:
- Импорт из 8 широко используемых форматов данных (Text, MS Excel, MS Excel 2007, MS Access, XML, CSV, DBF, ODBC)
- Шаблоны для сохранения настроек импорта для каждого формата данных
- Импорт в существующие или новые таблицы
- Предварительный просмотр импортированных данных
- Выбор форматов данных и настроек столбцов для импортированных данных
- Выбор режима импорта: Добавление, Обновление, Добавление/Обновление, Удаление, Перезаполнение
- Групповая вставка для ускорения процесса импорта
- Настройка обработки ошибок
- Создание лог-файла с подробным описанием процесса импорта
Узнайте больше о возможностях инструмента для импорта данных в нашем блоге:
Экспорт данных
Вместе с инструментом для импорта данных в базы MySQL, dbForge Studio также предлагает инструмент для экспорта данных. Он может экспортировать данные в 12 широко используемых форматов. Вы можете настроить процесс экспорта согласно вашим требованиям.
Экспорт данных из баз MySQL можно провести с помощью мастера. В мастере вы можете легко выбрать таблицы или представления, из которых необходимо экспортировать данные, выбрать столбцы или ряд столбцов для экспорта и скорректировать опции форматирования (для текстовых форматов). Также вы можете сохранить шаблон с требуемыми настройками для использования его в дальнейшем в мастере или командной строке.
Мастер экспорта данных баз MySQL предлагает:
- Экспорт в 12 широко используемых форматов (HTML, Text, MS Excel, MS Excel 2007, MS Access, RTF, PDF, XML, CSV, ODBC, DBF, SQL)
- Экспорт из таблиц, представлений либо запросов
- Шаблоны для сохранения настроек экспорта для каждого формата данных
- Выбор столбцов или ряда столбцов для экспорта
- Настройка шрифтов и цветов для графических форматов
- Выбор форматирования для текстовых форматов
- Настройка форматов данных для экспортированных данных
- Настройка страниц для форматов RTF, PDF, MS Excel
- Поддержка Unicode
Использование 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:
Теперь выполните следующую инструкцию:
В результате мы получим результаты, аналогичные подходу с использованием кода.
Импорт файла клиента для замещения на сервере базы данных MySQL
Можно импортировать данные от клиента (локального компьютера) на удаленный сервер базы данных MySQL, с помощью оператора LOAD DATA INFILE.
При использовании опции LOCAL в LOAD DATA INFILE клиентская программа считывает файл на стороне клиента и отправляет его на сервер MySQL. Файл будет загружен во временную папку базы данных сервера операционной системы, например, C: Windows Temp для ОС Windows или /TMP для Linux.
Эта папка не настраивается и не задается MySQL.
Давайте рассмотрим следующий пример:
LOAD DATA LOCAL INFILE 'c:/tmp/discounts.csv' INTO TABLE discounts FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY 'n' IGNORE 1 ROWS;
Разница заключается только в опции LOCAL оператора. Если вы загружаете большой CSV файл, вы увидите, что с опцией LOCAL загрузка осуществляется немного медленнее, потому что требуется определенное время, чтобы передать файл серверу базы данных.
Учетная запись пользователя, под которым мы подключается к серверу MySQL для импорта данных, может не иметь привилегию FILE, если используется опция LOCAL.
При импорте файла от клиента на удаленный сервер базы данных с помощью LOAD DATA LOCAL могут возникнуть некоторые проблемы с безопасностью, о которых вы должны знать, чтобы избежать потенциальных рисков.
Импорт CSV файла с помощью MySQL Workbench
MySQL Workbench предоставляет инструмент для импорта данных в таблицу БД. Он позволяет редактировать данные перед внесением изменений.
Ниже приведены этапы импорта данных в таблицу БД:
Откройте таблицу, в которую будут загружаться данные:
Нажмите кнопку Import , выберите CSV файл и нажмите кнопку Open :
Просмотрите данные, нажмите кнопку Apply :
В MySQL Workbench появится диалоговое окно « Apply SQL Script to Database », нажмите кнопку Apply , чтобы добавить данные в таблицу БД.
Мы рассказали вам, как импортировать CSV в таблицу MySQL с помощью LOAD DATA LOCAL , а также с использованием MySQL Workbench .
С помощью этих методов можно загружать данные и из других файлов текстовых форматов, таких, например, как текстовый файл с разделителями табуляции.
Данная публикация представляет собой перевод статьи « Import CSV File Into MySQL Table » , подготовленной дружной командой проекта Интернет-технологии.ру
Methods of Exporting MySQL Table to CSV
You will learn the following 5 methods to export your tables from MySQL to CSV:
1. Using Command Line
It is extremely easy to use the command line to export a MySQL table to CSV. You do not need to download any additional software. We have written an in-depth article also on MySQL export database command line.You will also learn how to export to CSV using the command line under the following conditions:
To export to CSV, do as follows:
Step 1
Navigate to the database which has the table you want to export using the following command:
Here, dbName must be replaced with the name of your database.
If your MySQL server has been started with –secure-file-priv option, you must use:
This command will show you the directory that has been configured. You can only store your output file in this directory.
Select all the data from the table and specify the location of the output file.
- Make sure to use the .csv extension for your output file.
- The ORDER clause can be used to arrange the data according to a particular attribute.
- The LIMIT clause is used to restrict the number of rows to be copied into the output file.
a. Exporting Selected Columns of a Table
- To do this you can use the SELECT statement to specify the columns you want to export.
- You may additionally use the WHERE clause to use specific conditions and filter the results.
b. Exporting Tables with a Timestamp
You may want to add a timestamp to the exported file, to do that you must use a MySQL prepared statement.
Use the following command to export to a CSV file, and add a timestamp for the time the file was created:
c. Export with Column Headers
It is often convenient to add column headers to the output file to better identify and analyze the data. To do this, you must use the UNION statement.Use the following command to add column headers:
d. Handling NULL Values
If your results contain NULL values, they will appear as ‘N’ in the exported file instead of NULL. This may lead to confusion and thus, you may want to replace this ‘N’ string with a string like NA (not applicable) that makes more sense.Use the following command to do it:
2. Using mysqldump
mysqldump is a utility tool provided by MySQL server that enables users to export tables, databases, and entire servers. Moreover, it is also used for backup and recovery.Here, we will discuss how mysqldump can be used to export a MySQL table to CSV.
Use the following command in a command prompt/terminal:
- The given command will create a copy of the table specified by tableName at the location you define using the -T option.
- The name of the file will be the same as that of the table and will have a .txt extension.
3. Using MySQL Workbench
MySQL Workbench provides an Import/Export Wizard which allows you to export our database/ tables to a specified format using a graphical user interface. The wizard supports JSON and CSV formats.
To download MySQL Workbench, click here.
You can follow the given steps to export your MySQL table using MySQL Workbench:
Step 1
Use the left bar “schemas” tab to locate the table you want to export.
In this example, we will be exporting the employee’s table in the classic model’s database.
- Right-click using your mouse on the table and select “Table Data Export Wizard” to get the following screen.
- Select the columns you want to export.
Step 3
- Click on Next.
- Browse to the directory where you want to save the output file.
- Choose the CSV format option.
Step 4
- Click on Next.
- Your data will start exporting.
- You can track the process through the logs.
4. Using phpMyAdmin
phpMyAdmin provides a graphical user interface to export your MySQL table in different formats. Apart from CSV, it supports other formats such as XML, JSON, YAML, and many others.
To download phpMyAdmin, click here.
To use phpMyAdmin to export data, follow these steps:
Step 1
- Log in to phpMyAdmin using a user that has required permissions.
- Navigate to the database which contains the source table as shown.
- Choose the table from the database.
- Click on Export in the top bar.
Step 3
Choose the CSV format from the format dropdown menu.
- Click on Go.
- Select the save file option when prompted.
5. Using CSV Engine
The CSV storage engine stores data in text files using comma-separated values format and is always compiled into the MySQL server.It is important to note that this method can only be used if the table does not have an index or an AUTO_INCREMENT constraint.
This command changes the format of the database to CSV. It can then directly be copied to another system easily.
Использование оператора BULK INSERT
Оператор BULK INSERT в SQL Server позволяет осуществлять импорт данных из файла в таблицу. В предложении WITH для данного оператора можно задавать множество опций, но нам нужны только две из них:
- FIELDTERMINATOR
-
Указывает разделить для столбцов. По умолчанию, разделителем является символ табуляции (\t). В CSV разделителем по умолчанию является запятая.
- ROWTERMINATOR
-
Указывает разделитель для строк.
В качестве разделителя в исходном CSV-файле используется знак табуляции (Tab). В CSV также можно использовать запятые, точки с запятой и другие символы в качестве разделителя столбцов. Главное указать символ в параметре FIELDTERMINATOR.
Выберите теперь все строки из таблицы CSV_Export с помощью оператора SELECT для проверки результата:
На рисунке ниже показан результат:
Как правило, данные из CSV-файла нужно сохранять во временную таблицу, а затем из нее вставлять в основную, т.к. зачастую необходимо парсить даты, числа с плавающей запятой и другие форматы. Их можно спарсить в строку во временной таблице и привести к нужному формату в основной. Например, если мы изменим тип столбца DateReport на DATETIME, SQL Server выдаст следующую ошибку:
В следующем примере показано, как можно обойти эту ошибку и получить дату:
Теперь в основной таблице столбец DateReport будет храниться в формате даты:
Conclusion
You now have 5 methods to export your MySQL table to CSV in your arsenal. If you are comfortable with writing queries using the command-line or mysqldump utility tool will prove to be the simplest way. However, if you are not confident with your querying skills, MySQL Workbench and phpMyAdmin will be your best bet. Extracting complex data from a diverse set of data sources such as MySQL can be a challenging task and this is where Hevo saves the day!
Hevo offers a faster way to move data from 100+ data sources such as SaaS applications or Databases such as MySQL into your Data Warehouse to be visualized in a BI tool. Hevo is fully automated and hence does not require you to code.
Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand. You can also have a look at the unbeatable pricing that will help you choose the right plan for your business needs.
Git + 1С. Часть 1. Как подключиться к команде разработки и начать использовать Git
Первая статья из цикла инструкций по работе с Git в 1С-разработке. Рассмотрим, как настроить рабочее место, как получить свою «копию» проекта для разработки и приступить к полезным действиям. Все примеры будут изложены в рамках трёх практических кейсов: 1. Моя команда дорабатывает типовую конфигурацию, использует приватный репозиторий на BitBucket, в котором версионируются внешние отчеты/обработки, расширения конфигураций и правила обмена; 2. Я участвую в стартап-команде, которая разрабатывает свою конфигурацию с использованием Git и GitLab; 3. Я принимаю участие в развитии OpenSource-продукта на GitHub как заинтересованный разработчик (контрибьютор).
PHP – выгрузка данных в Excel из базы MySQL
У многих при работе с PHP в связке с MySQL возникает такая потребность, как экспорт данных из базы в формат xls, для того чтобы люди, которым нужны эти данные, обрабатывали их в программе Excel или просто пользователям было удобно смотреть эти данные. Недавно у меня возникла такая потребность и сегодня я расскажу, как это дело можно реализовать.
Сразу скажу, что этот способ достаточно простой, но данные выгружаются нормально.
Для начала приведу пример конечного xls файла, в программе Excel выгрузка будет выглядеть примерно так:
Другими словами, никаких картинок, стилей выгружено не будет, только заголовки столбцов и сами данные.
До того как я пришел именно к такому варианту выгрузки я пробовал выгружать в формат csv, но получалось немного коряво, потом пробовал отрисовывать таблицу и сохранять ее с расширением xls, также получалось какая-то ерунда, способ, который я сейчас опишу, меня полностью устроил, и сейчас с Вами я им поделюсь.
Для начала приведу весь код, который я максимально прокомментировал, можете выделить его и сохранить с расширением php и пробовать, только не забудьте прописать настройки подключения к базе данных.
Реализация экспорта данных с SQL сервера в формат CSV в Access 2003
Так как CSV это обычный текстовый формат данных, где значения разделяются запятой или другим символом нам достаточно написать код VBA, который позволял бы сохранять текстовый файл с расширением CSV.
Для этого открываем свой ADP проект, он соответственно должен быть подключен к тестовой базе Test (или к той, которая нужна Вам).
Создаем форму или открываем на редактирование существующую форму и добавляем на ней одну кнопку, например, я ее назвал ExportInCSV.
Примечание! Сразу скажу, что в примере ниже я показываю сам принцип выгрузки, но данную возможность можно сделать более универсальной, например, на форму добавить текстовое поле и в него писать SQL запрос, для того чтобы передавать в процедуру или сделать возможность самостоятельно указывать любой разделитель, в примере ниже он прописан вручную в коде или, например, Вам нужно динамически формировать название экспортируемого файла все это под свои нужды Вы легко можете доработать самостоятельно.
В «Обработку события» нашей кнопки вставляем следующий код VBA (я его подробно прокомментировал):
Private Sub ExportInCSV_Click()
On Error GoTo Err 'Обработка ошибок
'Переменные
Dim Path As String, NameFile As String, PathNameFile As String
Dim PathSave As FileDialog, CountColumn As Integer, StrSql As String
Dim Response As Variant, rs As ADODB.Recordset
Dim StrLineInFile As String, Delim As String
Delim = ";" 'Разделитель
StrSql = "SELECT * FROM dbo.TestTable" 'Запрос к базе данных
'Получаем данные
Set rs = New ADODB.Recordset
rs.open StrSql, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
'Узнаем количество полей
CountColumn = rs.Fields.count
'Выбор каталога для выгрузки
Set PathSave = Application.FileDialog(msoFileDialogFolderPicker)
With PathSave
.ButtonName = "Сохранить"
.title = "Выбор папки для выгрузки"
.InitialFileName = "c:\"
.AllowMultiSelect = False
End With
'Если выбрали каталог, то начинаем выгружать
If PathSave.Show = -1 Then
NameFile = "TestFile.csv" 'Имя файла
Path = PathSave.SelectedItems(1) 'Путь
'Проверка на последний обратный слеш
If InStrRev(Path, "\") <> Len(Path) Then
Path = Path & "\"
End If
'Итоговый файл
PathNameFile = Path & NameFile
Response = MsgBox("Данные будут сохранены в " & PathNameFile & vbNewLine & _
"Для подтверждения нажмите ", vbYesNo, "Подтверждение")
If Response = vbYes Then
Open PathNameFile For Output As #1
'Записываем название полей
For i = 0 To CountColumn - 1
If StrLineInFile <> "" Then
StrLineInFile = StrLineInFile & Delim & rs.Fields(i).NAME
Else
StrLineInFile = rs.Fields(i).NAME
End If
Next
Print #1, StrLineInFile
'Объединяем все поля и записываем в файл, каждую строку отдельно
While Not (rs.EOF)
StrLineInFile = ""
For i = 0 To CountColumn - 1
If StrLineInFile <> "" Then
StrLineInFile = StrLineInFile & Delim & rs.Fields(i).value
Else
StrLineInFile = rs.Fields(i).value
End If
Next
Print #1, StrLineInFile
rs.MoveNext
Wend
Set rs = Nothing
Close #1
MsgBox "Данные выгружены", vbDefaultButton1, "Результат"
End If
End If
ex:
Exit Sub
Err:
MsgBox Err.Description
Resume ex
End Sub
Сохраняем и запускаем нашу форму, щелкаем по кнопке (в моем случае ExportInCSV), в итоге у нас откроется диалоговое окно выбора пути сохранения файла, мы выбираем каталог для экспорта и жмем «Сохранить». Затем подтверждаем свое действие и жмем «ОК».
После того как выгрузка будет завершена, появится соответствующее сообщение. Для проверки открываем каталог, в который выгрузился файл, находим файл TestFile.csv и открываем его любым блокнотом, например, мне нравится Notepad++.
Видим, что нужные данные у нас выгрузились и разделитель точка с запятой, все как мы и хотели.
На этом все, удачи!
Нравится3Не нравится1
Исходные данные
В качестве примера я буду использовать СУБД Microsoft SQL Server 2012 Express.
Допустим, у нас будет база данных Test, а в ней следующая таблица
CREATE TABLE dbo.TestTable(
Id int IDENTITY(1,1) NOT NULL,
ProductName VARCHAR(50) NOT NULL,
Price MONEY NULL,
CONSTRAINT PK_TestTable PRIMARY KEY CLUSTERED (Id ASC))
GO
Заполним таблицу тестовыми данными
INSERT INTO dbo.TestTable
(ProductName, Price)
VALUES('Принтер',100)
GO
INSERT INTO dbo.TestTable
(ProductName, Price)
VALUES('Сканер',150)
GO
INSERT INTO dbo.TestTable
(ProductName, Price)
VALUES('Монитор',200)
GO
INSERT INTO dbo.TestTable
(ProductName, Price)
VALUES('Системный блок',300)
GO
INSERT INTO dbo.TestTable
(ProductName, Price)
VALUES('Ноутбук',400)
GO
Посмотрим, что у нас получилось
SELECT * FROM dbo.TestTable
И эти данные нам нужно выгрузить в CSV файл и не просто выгрузить, а реализовать функционал, с помощью которого обычные пользователи могли бы сами экспортировать данные. В качестве разделителя допустим, нам необходимо использовать точку с запятой.
Интеграция 1С и C#. Обращение к 1С через COM. Создание номенклатуры и выполнение запроса к 1С из C# на примере интеграции с Союз-PLM (система информационной поддержки жизненного цикла изделий). Обработка «Загрузка заказа на сборку». (ERP) Промо
Союз-PLM представляет собой полнофункциональный программный комплекс для решения широкого спектра задач управления инженерной технической информацией наукоемких изделий и сложных инженерных объектов в области машиностроения, приборостроения, архитектуры, строительства.
На практике, это управление конструкторской документацией, интегрированное с САПР, бизнес-процессы, файловый архив. Все это работает в SQL и шевелится с помощью скриптов на C#. Скрипты свободно отлаживаются в MS Visual Studio. Подробнее смотрите на их сайте: http://www.programsoyuz.ru/products/system-soyuz-plm.html Редактирование, конструирование и использование бизнес-процессов на порядок лучше систем на базе 1С из тех, что я изучил.
1 стартмани
Из командной строки MySQL
Если у вас есть только доступ из командной строки к экземпляру MySQL, а не доступ к самому серверу (например, когда он не управляется вами, в случае Amazon RDS), проблема немного сложнее. Пока вы можете использовать на сервере для создания списка, разделенного запятыми, интерфейс командной строки MySQL по умолчанию разделяется на вкладки.
Просто введите запрос из командной строки и направьте его в файл:
mysql -u root -e "select * from database;" > output.tsv
Поскольку выходные данные MySQL разделены вкладками, это называется файлом TSV для «значений, разделенных табуляцией», и может работать вместо вашего файла CSV в некоторых программах, таких как импорт электронных таблиц. Но это не файл CSV, и конвертировать его в один сложно.
Вы можете просто заменить каждую вкладку запятой, которая будет работать, но приведет к сбою, если во входных данных есть запятые. Если вы на 100% уверены, что в вашем файле TSV нет запятых (обратитесь к ), вы можете заменить вкладки на :
sed "s/t/,/g" output.tsv > output.csv
Но если у вас есть запятые в ваших данных, вам придется использовать намного длиннее регулярное выражение:
sed "s/'/'/;s/t/","/g;s/^/"/;s/$/"/;s/n//g" output.tsv > output.csv
Это будет правильно экранировать поля с кавычками, что решит проблему с запятыми.
Примечание: символ табуляции является не стандарт, В macOS и BSD он недоступен, что приводит к путанице в каждой строчной букве «t», вызывающей вставить ошибочные запятые. Чтобы решить эту проблему, вам нужно использовать буквенный символ табуляции вместо :
sed "s/ /,/g" output.tsv > output.csv
Если ваши входные данные содержат вкладки, вам не повезло, и вам придется самостоятельно создавать файл CSV с использованием языка сценариев.
Экспорт данных из MySQL в Excel на PHP
А еще чтобы Вы понимали, какие данные я выгружаю, приведу пример простой таблицы в БД (у меня ее название test):
Тестовая таблица:
| id | firstname | name |
| 1 | Иванов | Иван |
| 2 | Петров | Петр |
| 2 | Петров2 | Петр2 |
Код:
<?php
mysql_connect ("localhost", "root","");//подключение к серверу
mysql_select_db("test") or die (mysql_error());//выбор базы данных
mysql_query('SET character_set_database = utf8');
mysql_query ("SET NAMES 'utf8'");
error_reporting(E_ALL);
ini_set("display_errors", 1);
if( !defined( "ExcelExport" ) ) {
define( "ExcelExport", 1 );
class ExportToExcel {
var $xlsData = "";
var $fileName = "";
var $countRow = 0;
var $countCol = 0;
var $totalCol = 3;//общее число колонок в Excel
//конструктор класса
function __construct (){
$this->xlsData = pack( "ssssss", 0x809, 0x08, 0x00,0x10, 0x0, 0x0 );
}
// Если число
function RecNumber( $row, $col, $value ){
$this->xlsData .= pack( "sssss", 0x0203, 14, $row, $col, 0x00 );
$this->xlsData .= pack( "d", $value );
return;
}
//Если текст
function RecText( $row, $col, $value ){
$len = strlen( $value );
$this->xlsData .= pack( "s*", 0x0204, 8 + $len, $row, $col, 0x00, $len);
$this->xlsData .= $value;
return;
}
// Вставляем число
function InsertNumber( $value ){
if ( $this->countCol == $this->totalCol ) {
$this->countCol = 0;
$this->countRow++;
}
$this->RecNumber( $this->countRow, $this->countCol, $value );
$this->countCol++;
return;
}
// Вставляем текст
function InsertText( $value ){
if ( $this->countCol == $this->totalCol ) {
$this->countCol = 0;
$this->countRow++;
}
$this->RecText( $this->countRow, $this->countCol, $value );
$this->countCol++;
return;
}
// Переход на новую строку
function GoNewLine(){
$this->countCol = 0;
$this->countRow++;
return;
}
//Конец данных
function EndData(){
$this->xlsData .= pack( "ss", 0x0A, 0x00 );
return;
}
// Сохраняем файл
function SaveFile( $fileName ){
$this->fileName = $fileName;
$this->SendFile();
}
// Отправляем файл
function SendFile(){
$this->EndData();
header ( "Last-Modified: " . gmdate("D,d M YH:i:s") . " GMT" );
header ( "Cache-Control: no-store, no-cache, must-revalidate" );
header ( "Pragma: no-cache" );
header ( "Content-type: application/x-msexcel" );
header ( "Content-Disposition: attachment; fileName=$this->fileName.xls" );
print $this->xlsData;
}
}
}
if(isset($_GET)) {
//фильтруем данные
$id = mysql_real_escape_string(stripslashes(trim(htmlspecialchars($_GET,ENT_QUOTES))));
$filename = 'Файл_с_id_'.$id; // задаем имя файла
$excel = new ExportToExcel(); // создаем экземпляр класса
$sql="SELECT * FROM test.test where id = $id";//запрос к базе
$rez=mysql_query($sql);
$excel->InsertText('Идентификатор');
$excel->InsertText('Фамилия');
$excel->InsertText('Имя');
$excel->GoNewLine();
While($row=mysql_fetch_assoc($rez)){
$excel->InsertNumber($row);
$excel->InsertText($row);
$excel->InsertText($row);
$excel->GoNewLine();
}
$excel->SaveFile($filename);
}
?>
Вся идея здесь заключается в функции pack(), которая упаковывает данные в бинарную строку, а мы в свою очередь последовательно заполняем эту строку данными, которые мы выгрузили из базы данных MySql с помощью обычной функции mysql_query().
Для того чтобы проверить работоспособность данного кода, с учетом того, что Вы настроили подключение к базе данных и создали аналогичную таблицу, можете послать такой запрос:
http://ваш_сайт/название _файла. php?id=2
И у Вас должно выгрузиться две строки с id равным 2.
И теперь каждый, которому Вы разрешите выгружать данные может легко экспортировать их на свой локальный компьютер через web интерфейс. Данный способ удобен как для корпоративных пользователей, если Вы разрабатываете приложение для своей организации, так и для пользователей Вашего web сайта в Интернете. Надеюсь, данный способ Вам помог. Удачи!
Нравится2Не нравится2
Копирование числовых ячеек из 1С в Excel Промо
Решение проблемы, когда значения скопированных ячеек из табличных документов 1С в Excel воспринимаются последним как текст, т.е. без дополнительного форматирования значений невозможно применить арифметические операции. Поводом для публикации послужило понимание того, что целое предприятие с более сотней активных пользователей уже на протяжении года мучилось с такой, казалось бы на первый взгляд, тривиальной проблемой. Варианты решения, предложенные специалистами helpdesk, обслуживающими данное предприятие, а так же многочисленные обсуждения на форумах, только подтвердили убеждение в необходимости описания способа, который позволил мне качественно и быстро справиться с ситуацией.
Introduction
Do you want to export tables from your MySQL database to a CSV format? You have landed at just the right post. We give you an easy, stepwise guide for 5 different methods to do just that.MySQL is the most popular open-source relational database. It stores data in the form of tables. It is offered under two different editions:
- The open-source MySQL community server
- The proprietary Enterprise server
However, the raw format of MySQL tables is supported by a limited number of applications. Therefore, it is often beneficial to convert MySQL data into CSV format.
In this article we will look into the following:
Pre-requisites
- Basic knowledge of MySQL
- Using MySQL shell
- Using a terminal/command line
- Write permission for the intended output file
- Read permission for the input MySQL table
- Pre-configured phpMyAdmin account (optional)
Обзор кейса
Карло, ИТ консультант, работал над сложной, но довольно типичной задачей. Ему нужно было разработать систему, которая бы создавала отчеты для руководства на основе Excel файлов, полученных от порядка 10-20 исследовательских лабораторий.
Сначала эти отчеты создавали в Excel – в основном вручную – и направляли руководству и лабораториям в печатном виде или PDF.
В таком подходе нет нужного уровня автоматизации и гибкости. Поэтому Карло решил найти способ выгружать данные из файлов лабораторий, организовывать их в заданном порядке, и экспортировать данные в базу данных MySQL. Потом, уже в веб интерфейсе, создавать необходимые отчеты, напр., графики динамики по годам, и направлять всем участникам ссылку на финальный отчет.
В подобных задачах важно, во-первых, минимизировать работу вручную и сократить время на подготовку данных. Во-вторых, аккуратно экспортировать подготовленные данные в MySQL
Один из подходов к автоматизации – создать специальный макрос VBA. Но при таком большом объеме необходимых операций написание макроса с нуля займет неоправданно много времени. Кроме того, если вы хоть раз пробовали настроить интеграцию макроса с внешней базой данных, вы представляете, насколько утомительной может быть синхронизация вручную. Одно небольшое изменение в процессе (к примеру, другое название исходной таблицы) ведет к бесконечным правкам в скрипте.
Надстройка XLTools, с другой стороны, дала Карло гибкие инструменты для автоматизации всего процесса без VBA: извлечь, подготовить и экспортировать данные в базу данных MySQL.
XLTools Автоматизация – это мощный и многоцелевой инструмент. Последовательность всех операций записана на одном листе Excel, и все операции вручную сводятся к нажатию одной единственной кнопки.
Рассмотрим некоторые операции, автоматизированные в этом процессе.
Выводы
Есть много других способов экспортировать результаты. Однако эти параметры вдохновят вас на использование других.
В заключение, вот несколько советов о том, когда их использовать:
1. Назначение SSMS для файла вариант — это самый простой вариант. Используется, если вам не нужно ничего автоматизировать и вы сразу получаете только один текстовый отчет.
2. SQLCMD — используйте его, когда у вас есть командный файл или если вы используете командную строку для автоматизации нескольких задания.
3. PowerShell — используйте его, когда вы автоматизируете задачи с помощью PowerShell или когда используете инструменты для вызова сценариев PowerShell.
4. Мастер импорта/экспорта в SSMS — используйте его, когда у вас есть миллионы строк для копирования файлы. Это очень быстрый вариант, специализирующийся на экспорте и импорте данных из нескольких источников.
5. Мастер SSIS (почти такой же, как номер 4, но мы используем SSDT вместо SSMS для создания пакета. ). Он похож на 4, но его можно настроить, и вы можете создавать действительно сложные пакеты, интегрированные с веб-службами, отправлять почту, PowerShell и многое другое. Используйте его, если вам нужно сложное решение, требующее интеграции между несколькими инструментами.
6. C # — используйте его, когда у вас есть код для других задач и вам необходимо интегрироваться с другими строками кода на C #.
7. SSRS — SSRS полезен для создания красивого настраиваемого отчета. Используйте его, когда презентация важна.
8. BCP — это очень быстрый вариант. Используйте его для больших объемов данных.
Ссылки
- Задача сценария SSIS
- Руководства по службам Reporting Services
- Решения PowerShell — несколько советов для повседневных задач
