Contents Show
IntroductionIn this article we will learn how to use ZappyShell Command Line Tools for MySQL Excel Export from Table or Query result The problemThere is no native way of Exporting MySQL Data to Excel. You can use some coding techniques or libraries but when things become complex you will run out of options quickly. SolutionZappyShell for DB can export your MySQL data to Excel, PDF, Excel, CSV in one line command. This is just single exe less than 5 MB.. Excel export allows you to export data in existing or new excel file. You can specify sheetname and Cell Location where you want to start writing data. Append mode can automatically add new data in the existing workbook.Download here. Now Lets check how to do this. Download and Install MySQL ODBC DriverTo export data from MySQL we will need ODBC Driver. In this tutorial we have use MySQL ODBC 5.2 Driver but if you are using different driver then change connection string accordingly. Download and Install MySQL ODBC Driver from below URL MySQL Excel Export – Download MySQL 5.2 ODBC Driver MySQL Excel Export – Install MySQL ODBC Driver Download and Install ZappyShell Command Line Tools
MySQL Excel Export – Multiple tablesIn the following example we are exporting 3 tables (seperated by vertical bar) MySQL Excel Export – Multiple tables (using pattern)In below example all tables where name staring with cust will be exported from sales schema. MySQL Excel Export – SQL QueryIn the following example we are exporting output of SQL Query to Excel file. If you have complex query with multi line then you can use Map file with one dataset node. MySQL Excel Export – Split Files and TabsSometimes you may have need to split large amount of data into multiple files. With ZappyShell command line you can split data three different ways. Lets check each example. MySQL Excel Export – Split By Row CountIn below example we are exporting MySQL customer table to Excel files and splitting files with maximum 1000 records per file option. c:\tools>db.exe export customers --out c:\data\cust.xlsx --split --splitrows 1000 --connstr "Driver={MySQL ODBC 5.2 ANSI Driver};Server=your_server_name_or_ip;Port=3306;Uid=user1;pwd=SomeP@ssword;Database=your_db_name" MySQL Excel Export – Split By File SizeIn below example we are exporting MySQL customer table to Excel files and splitting files with maximum file size = 10MB option. You also use other size specifier like KB, GB, BYTES etc. c:\tools>db.exe export customers --out c:\data\cust.xlsx --split --splitsize 10MB --connstr "Driver={MySQL ODBC 5.2 ANSI Driver};Server=your_server_name_or_ip;Port=3306;Uid=user1;pwd=SomeP@ssword;Database=your_db_name" MySQL Excel Export – Split By Group By ColumnIn this example we have exported customers in to separate file by country. c:\tools>db.exe export customers --out c:\data\cust.xlsx --groupby Country --connstr "Driver={MySQL ODBC 5.2 ANSI Driver};Server=your_server_name_or_ip;Port=3306;Uid=user1;pwd=SomeP@ssword;Database=your_db_name" MySQL Excel Export – Table to Excel, Split files by Country Column MySQL Excel Export – Split sheets By Group By ColumnIn this example we have exported customers into separate sheet by country. c:\tools>db.exe export customers --out c:\data\cust.xlsx --groupby Country --excel-split-ontab --connstr "Driver={MySQL ODBC 5.2 ANSI Driver};Server=your_server_name_or_ip;Port=3306;Uid=user1;pwd=SomeP@ssword;Database=your_db_name" ZappyShell – Export MySQL to Excel Split on tabs Exporting Compressed Excel filesIf you want to output compressed Excel (GZIP format) then use below command. c:\tools>db.exe export customers --out c:\data\cust.xlsx --gzip --connstr "Driver={MySQL ODBC 5.2 ANSI Driver};Server=your_server_name_or_ip;Port=3306;Uid=user1;pwd=SomeP@ssword;Database=your_db_name" Using date time place holders in Excel file nameIf you want to use date time place holders in file name so it automatically uses specified date time parts use below command simply putting specifiers in file name anywhere. c:\tools>db.exe export customers --out c:\data\cust_{{YYYY}}_{{MM}}_{{DD}}_{{HH24}}{{MI}}{{SS}}.xlsx --connstr "Driver={MySQL ODBC 5.2 ANSI Driver};Server=your_server_name_or_ip;Port=3306;Uid=user1;pwd=SomeP@ssword;Database=your_db_name" ConclusionIn this article we have gone through few ways to use ZappyShell Command Line Tools to export data from MySQL to Excel. You can also use same command line to export in CSV, JSON, PDF format. Reference Links
How do I export MySQL query output to Excel?How to export/import MySQL data to Excel. The SELECT INTO … OUTFILE statement.. The From Database feature in Excel.. The MySQL for Excel add-in.. Export to Excel using a third-party software.. How do I save SQL query output to a file?Click Query, and then click Results to File. Enter and then execute the SQL statement. In the Save Results dialog box, specify the following settings: Save In: Select a directory in which to save the file.
How do I export SQL query results to Excel command line?Steps to export query output to Excel in SQL Developer. Step 1: Run your query. To start, you'll need to run your query in SQL Developer. ... . Step 2: Open the Export Wizard. ... . Step 3: Select the Excel format and the location to export your file. ... . Step 4: Export the query output to Excel.. How do I create a MySQL query in Excel?Start Excel, click the Data tab. In the appeared ribbon, click From Other Sources, and then click From Microsoft Query. In the next dialog, choose the data source you want to connect to (e.g., using data source name - Devart ODBC MySQL). Uncheck Use the Query Wizard to Create/Edit Queries and click OK.
|