Details

    • Solution:
      Hide

      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
      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

        Issue Links

        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

          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: