Export Data

Tables -> Export Data

With SQLyog you can export data of a table you can select the table in the Object Browser and by right clicking select Backup/Export. Here we have 2 options,

  • Backup Database As SQL Dump…
  • Export Table Data as CSV, SQL, Excel etc...

Select 2nd option Backup/Export -> Export Table Data as CSV, SQL, Excel etc... (press Ctrl + Alt + C). This option is also available in menu Table -> Backup/Export -> Export Table Data as CSV, SQL, Excel etc...

Now you can export data of a table in various formats. The available options are:

  • CSV
  • Excel
  • HTML
  • XML
  • SQL (Note that this 'export data as SQL' is not intended for backup purposes. Read the full explanation at the bottom of this page!)

Also, you can export the resultset of a query in the same formats. You can also select what columns to include in the export. To access this feature you can

  • right-click the GRIDs of DATA or RESULT tabs,
  • right-click a table in the Object Browser.
  • select the option from 'Table' or 'Tools' menu. From 'Tools' menu it will export what tab is active (the DATA tab or a RESULT tab)

In every case this dialogue will appear:  

The relevant section of this dialogue becomes active as you select your option. We will now have a detailed look on the different export options:

As CSV:

If you want to change the program defaults (or whatever you entered last time you use this function) Click the Change and in the appearing dialogue check options as you need and specify a file name for the export.

To fully understand this dialog you will need to understand the MySQL "LOAD DATA INFILE" statement. However, there is an extremely useful feature if you wish to copy your data from the Clipboard directly to an Excel workbook or to a file which will be then opened in MS Excel. Use the button Change -> Specify the escape characters pops and now click on Fill Excel friendly values where SQLyog will automatically select the delimiters used in Excel. Note that to use with Windows localizations distributed in most continental European countries you should consider change the " , " (comma) to " ; " (semicolon), as quite a lot of localized programs expect that (true for Microsoft Excel and the Microsoft text-ODBC driver for instance).

But you can of course specify any other delimiters if you need and technical details for rows (Escape Characters and Line Terminator Characters) and fields (Fixed Length, if used, or Terminators and Enclosers, if field length is Variable). Here you can also specify if Column Names must be copied with the data.

After filling in all the details, press Export in the Export Data as CSV dialog window.

As Excel:

The Excel data format we support is the XML-based format introduced with Microsoft Office 2003. It also works on Office 2007 (only small changes that does not affect this functionality). With use with previous versions there is a 'Compability pack' for Excel 2000 and Excel 2002/XP available as a download from the Microsoft webpage.

Next image shows SQLyog connected to MySQL with a database containing a broad variety of DATE and TIME type variables you will see some 'weird' values but they are all valid with the MySQL version in question and the server sql_mode:

After export using the Excel option it displays like this in Excel 2003

Sample output in CSV

As HTML: 

Sample output in HTML

As XML: 

As SQL Statements:

With 'Export as SQL' a single-table/result set can be exported. The purpose of this export option is to provide a simple and fast way to export data embedded in SQL INSERT statements. With this tool you can also export data embedded in INSERT statements from a resultset from a query using a JOIN or UNION. However you should observe that if two or more JOINED tables have identically named columns the data will not import unless you use an ALIAS for at least one of them.

Also a very simple 'CREATE STATEMENT for the TABLE' is created (if you choose), but this create statement is not a complete CREATE statements with all table and column options (such as Character Set and Storage Engine settings) and it should not be used for backup purposes. Click here to get more information on how to dump data as SQL Statements (Batch Scripts) or here to get more information about the (scheduled) Backup 'powertool' of SQLyog Enterprise. Those are the two options we provide that are 'fit' for backup purposes and other situations where you want detailed information about table structure(s).

If you want to use a text file with SQL statements for Backup purposes you should not use this option, but use the 'Backup database as SQL Dump' option instead.

'Export as SQL' will be enabled only if there is a single table associated with the Result tab. For a multi-table result sets even selecting a table from drop-down, 'Export as SQL' will be disabled.