Details

    • Solution:
      Hide

      Note: This solution is no longer maintained. For the latest information, please refer to our documentation:

      https://community.exasol.com/t5/connect-with-exasol/proper-csv-export-from-mysql/ta-p/1483

      Background

      As of the time of this article's last update, MySQL had moved from 5.7 to 8.0. We leave in the MySQL 5.7 documentation for archive purposes, but consider it deprecated. We will first present how to extract the CITY table data into a CSV using the DB client DB_Visualizer, followed by using the MySQL 8.0 Workbench to export CSV files, followed by the original article content.

      Prerequisites

      Check the version of MySQL you are using. You can simply log in from the command line and see the pertinent version information.

      For purposes of using DB_Visualizer, the assumption is you already have set up the MySQL connection in DB_Visualizer. If not, there here is a brief guide using DB_Visualizer to set up a MySQL Connection. If you are not going to use DB_Visualizer, you can skip to the section, Using MySQL Server 8.0 Workbench to export a CSV.

      Step 1. Choose Create Database from the Database Menu Selection.

      Step 2. Name the new connection

      Step 3. Choose MySQL from the Select Database Driver pulldown menu

      Step 4. Specify Connection credentials

      Step 5. Connect to the new MySQL instance in DB_Visualizer.

      How to export CSVs from MySQL

      Using DB_Visualizer to export MySQL data to a CSV file

      Step 1. Choose the appropriate Database, schema, and table from the Database Explorer window

      Step 2. Right click and choose Export Table

      Step 3. Set Output Format to CSV

      Step 4. Export CSV 

      Using MySQL Server 8.0 Workbench to export a CSV

      Step 1. From the Workbench, choose Data Export from far left under Navigator

       

      Step 2. Choose Schema and Table

      Step 3. Set your Export

      Step 4. Export

      Using MySQL 8.0 to export a CSV

      Use SQL Query - SELECT … INTO…:
      (Documentation https://dev.mysql.com/doc/refman/8.0/en/select-into.html

      For additional information about this query see below the paragraph, "option 2" under the section, How to export a CSV using MySQL Server 5.7

      SELECT CITY_ID, IFNULL(CITY, ''), IFNULL(Country_ID,0), IFNULL(IF(last_update = '0000-00-00', '', last_update), '')
      INTO OUTFILE 'C:/users/joas/downloads/city4.csv'
      FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
      ESCAPED BY '\"'
      LINES TERMINATED BY '\n'
      FROM city WHERE 1; 

        

      How to export a CSV using MySQL Server 5.7

      MySQL Server 5.7

      Option 1)

      Use the MySQL Workbench:

      • Connect to your Database
      • Right click on the table you want to export and open the Table Data Export Wizard
      • Check the needed columns. If you have “Date”- type columns, proceed with step B by clicking Advanced. Otherwise, continue with A by clicking Next.

      B) This step is optional and only needed, if you export “Date”-type columns:

      • Here you can see the query that will be used as a base for export.
        To prevent invalid entries in “Date”-columns (‘0000-00-00’), you have to add an if statement to the query. It will check if the content is valid or not and will replace it with "" (empty string) if necessary. You can modify the replacement string of course (e.g. with the function CURDATE() to fill in the current date).

        After you are done adjusting the query you can continue with step A by clicking Next.

      A)

      • Here you can set the file path for the csv file and some additional options
      • Finish the configuration by clicking Next
      • You are now ready to export your data into a new .csv file
      • Click Next to start the process
      • The CSV file is stored in the selected location

      Notes:

      • The Table Data Export tool enables you to export only one table at a time. You will have to repeat the procedure for every needed table.
      • NULL will automatically be converted to "" (empty string).
      • Double quotes will automatically be masked with double-quotes.
      • MySQL Workbench limits the size of the result set to 1000 by default.
        To change that, you have to set the option to your needed size as follows:

      Option 2)

      Use SQL Query - SELECT … INTO…:
      (Documentation https://dev.mysql.com/doc/refman/8.0/en/select-into.html)

      Example:

      SELECT ID, IFNULL(Name, ''), IFNULL(CountryCode, '') IFNULL(IF(FoundingDate = '0000-00-00', '', FoundingDate),'')
      INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/city.csv'
      FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
      ESCAPED BY '\"'
      LINES TERMINATED BY '\n'
      FROM city WHERE 1;
      

      This query will export all selected columns from the table city to the file city.csv.

      • As you can see it is very important to make proper use of the MySQL function IFNULL(). This way it is guaranteed, that potential NULL values are replaced by "" (empty string).
        Note: Only needed if option “NotNull” is disabled for affected columns.
      • To prevent invalid entries in “Date”-columns (‘0000-00-00’), you have to add an if statement to the query. It will check whether the content is valid or not and will replace it with "" (empty string) if necessary. You can modify the replacement string of course (e.g. with the function CURDATE() to fill in the current date).
        Depending on your settings (e.g. option “NotNull” is disabled), you might have to combine IFNULL() with an additional if statement as shown in the example.
      • The option ESCAPED BY is required to make sure, that double quotes are converted correctly ("Example" -> ""Example"")
        To export multiple tables at once just combine the customized SQL statements.

      Example:

      SELECT ID, IFNULL(Name, ''), IFNULL(CountryCode, '') IFNULL(IF(FoundingDate = '0000-00-00', '', FoundingDate),'')
      INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/city.csv'
      FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
      ESCAPED BY '\"'
      LINES TERMINATED BY '\n'
      FROM city WHERE 1;
      
      SELECT Code, IFNULL(Name, ''), IFNULL(Continent, ''), IFNULL(Region, '')
      INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/country.csv'
      FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
      ESCAPED BY '\"'
      LINES TERMINATED BY '\n'
      FROM country WHERE 1;
      
      SELECT CountryCode, IFNULL(IsOfficial, ''), Language, IFNULL(Percentage, '')
      INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/countrylanguage.csv'
      FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
      ESCAPED BY '\"'
      LINES TERMINATED BY '\n'
      FROM countrylanguage WHERE 1;
      
      Show
      Note: This solution is no longer maintained. For the latest information, please refer to our documentation: https://community.exasol.com/t5/connect-with-exasol/proper-csv-export-from-mysql/ta-p/1483 Background As of the time of this article's last update, MySQL had moved from 5.7 to 8.0. We leave in the MySQL 5.7 documentation for archive purposes, but consider it deprecated. We will first present how to extract the CITY table data into a CSV using the DB client DB_Visualizer, followed by using the MySQL 8.0 Workbench to export CSV files, followed by the original article content. Prerequisites Check the version of MySQL you are using. You can simply log in from the command line and see the pertinent version information. For purposes of using DB_Visualizer, the assumption is you already have set up the MySQL connection in DB_Visualizer. If not, there here is a brief guide using DB_Visualizer to set up a MySQL Connection. If you are not going to use DB_Visualizer, you can skip to the section,  Using MySQL Server 8.0 Workbench to export a CSV . Step 1. Choose Create Database from the Database Menu Selection. Step 2. Name the new connection Step 3. Choose MySQL from the Select Database Driver pulldown menu Step 4. Specify Connection credentials Step 5. Connect to the new MySQL instance in DB_Visualizer. How to export CSVs from MySQL Using DB_Visualizer to export MySQL data to a CSV file Step 1. Choose the appropriate Database, schema, and table from the Database Explorer window Step 2. Right click and choose Export Table Step 3. Set Output Format to CSV Step 4. Export CSV  Using MySQL Server 8.0 Workbench to export a CSV Step 1. From the Workbench, choose Data Export from far left under Navigator   Step 2. Choose Schema and Table Step 3. Set your Export Step 4. Export Using MySQL 8.0 to export a CSV Use SQL Query - SELECT … INTO…: (Documentation https://dev.mysql.com/doc/refman/8.0/en/select-into.html )  For additional information about this query see below the paragraph, "option 2" under the section, How to export a CSV using MySQL Server 5.7 SELECT CITY_ID, IFNULL(CITY, ''), IFNULL(Country_ID,0), IFNULL(IF(last_update = '0000-00-00', '', last_update), '') INTO OUTFILE 'C:/users/joas/downloads/city4.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\"' LINES TERMINATED BY '\n' FROM city WHERE 1;    How to export a CSV using MySQL Server 5.7 MySQL Server 5.7 Option 1) Use the MySQL Workbench : Connect to your Database Right click on the table you want to export and open the Table Data Export Wizard Check the needed columns. If you have “ Date ”- type columns, proceed with step B by clicking Advanced. Otherwise, continue with A by clicking Next. B) This step is optional and only needed, if you export “ Date ”-type columns: Here you can see the query that will be used as a base for export. To prevent invalid entries in “Date”-columns (‘0000-00-00’), you have to add an if statement to the query. It will check if the content is valid or not and will replace it with "" (empty string) if necessary. You can modify the replacement string of course (e.g. with the function CURDATE() to fill in the current date). After you are done adjusting the query you can continue with step A by clicking Next. A) Here you can set the file path for the csv file and some additional options Finish the configuration by clicking Next You are now ready to export your data into a new .csv file Click Next to start the process The CSV file is stored in the selected location Notes: The Table Data Export tool enables you to export only one table at a time. You will have to repeat the procedure for every needed table. NULL will automatically be converted to "" (empty string). Double quotes will automatically be masked with double-quotes. MySQL Workbench limits the size of the result set to 1000 by default. To change that, you have to set the option to your needed size as follows: Option 2) Use SQL Query - SELECT … INTO…: (Documentation https://dev.mysql.com/doc/refman/8.0/en/select-into.html ) Example: SELECT ID, IFNULL( Name , ''), IFNULL(CountryCode, ' ') IFNULL( IF (FoundingDate = ' 0000-00-00 ', ' ', FoundingDate),' ') INTO OUTFILE ' C :/ProgramData/MySQL/MySQL Server 5.7/Uploads/city. csv ' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\"' LINES TERMINATED BY '\n' FROM city WHERE 1; This query will export all selected columns from the table city to the file city.csv . As you can see it is very important to make proper use of the MySQL function IFNULL() . This way it is guaranteed, that potential NULL values are replaced by "" (empty string). Note: Only needed if option “NotNull” is disabled for affected columns. To prevent invalid entries in “ Date ”-columns (‘0000-00-00’), you have to add an if statement to the query. It will check whether the content is valid or not and will replace it with "" (empty string) if necessary. You can modify the replacement string of course (e.g. with the function CURDATE() to fill in the current date). Depending on your settings (e.g. option “NotNull” is disabled), you might have to combine IFNULL() with an additional if statement as shown in the example. The option ESCAPED BY is required to make sure, that double quotes are converted correctly ("Example" -> ""Example"") To export multiple tables at once just combine the customized SQL statements. Example: SELECT ID, IFNULL( Name , ''), IFNULL(CountryCode, ' ') IFNULL( IF (FoundingDate = ' 0000-00-00 ', ' ', FoundingDate),' ') INTO OUTFILE ' C :/ProgramData/MySQL/MySQL Server 5.7/Uploads/city. csv ' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\"' LINES TERMINATED BY '\n' FROM city WHERE 1; SELECT Code, IFNULL( Name , ''), IFNULL(Continent, ' '), IFNULL(Region, ' ') INTO OUTFILE ' C :/ProgramData/MySQL/MySQL Server 5.7/Uploads/country. csv ' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\"' LINES TERMINATED BY '\n' FROM country WHERE 1; SELECT CountryCode, IFNULL(IsOfficial, ''), Language , IFNULL(Percentage, ' ') INTO OUTFILE ' C :/ProgramData/MySQL/MySQL Server 5.7/Uploads/countrylanguage. csv ' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\"' LINES TERMINATED BY '\n' FROM countrylanguage WHERE 1;
    • Category 1:
      ETL / Data Integration
    • Category 2:
      3rd Party Tools - ETL

      Attachments

      1. image-2020-04-22-15-02-38-099.png
        image-2020-04-22-15-02-38-099.png
        7 kB
      2. image-2020-04-22-15-09-19-659.png
        image-2020-04-22-15-09-19-659.png
        52 kB
      3. image-2020-04-22-15-12-27-433.png
        image-2020-04-22-15-12-27-433.png
        34 kB
      4. image-2020-04-22-15-16-49-306.png
        image-2020-04-22-15-16-49-306.png
        31 kB
      5. image-2020-04-22-15-23-03-883.png
        image-2020-04-22-15-23-03-883.png
        13 kB
      6. image-2020-04-22-15-28-05-031.png
        image-2020-04-22-15-28-05-031.png
        15 kB
      7. image-2020-04-22-15-30-27-365.png
        image-2020-04-22-15-30-27-365.png
        37 kB
      8. image-2020-04-22-16-51-54-837.png
        image-2020-04-22-16-51-54-837.png
        27 kB
      9. image-2020-04-22-16-54-00-386.png
        image-2020-04-22-16-54-00-386.png
        40 kB
      10. image-2020-04-22-16-55-51-960.png
        image-2020-04-22-16-55-51-960.png
        34 kB
      11. image-2020-04-22-17-12-32-826.png
        image-2020-04-22-17-12-32-826.png
        47 kB
      12. image-2020-04-22-17-16-07-930.png
        image-2020-04-22-17-16-07-930.png
        23 kB
      13. image-2020-04-22-17-18-42-880.png
        image-2020-04-22-17-18-42-880.png
        37 kB
      14. image-2020-04-22-17-20-13-081.png
        image-2020-04-22-17-20-13-081.png
        45 kB
      15. image-2020-04-22-17-20-29-558.png
        image-2020-04-22-17-20-29-558.png
        50 kB
      16. MySQL1.png
        MySQL1.png
        20 kB
      17. MySQL2.png
        MySQL2.png
        18 kB
      18. MySQL3.png
        MySQL3.png
        19 kB
      19. MySQL4.png
        MySQL4.png
        24 kB
      20. MySQL5.png
        MySQL5.png
        13 kB
      21. MySQL6.png
        MySQL6.png
        58 kB

        Issue Links

          Activity

            People

            • Assignee:
              CaptainEXA Captain EXASOL
              Reporter:
              CaptainEXA Captain EXASOL
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: