Uploaded image for project: 'Solution Center'
  1. Solution Center
  2. SOL-428

Proper csv export from MySQL

    XMLWordPrintable

    Details

    • Type: How To
    • Status: Published
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: None
    • Labels:
      None
    • Solution:
      Hide

      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 http://dev.mysql.com/doc/refman/5.7/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
      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 http://dev.mysql.com/doc/refman/5.7/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. MySQL6.png
          MySQL6.png
          58 kB
        2. MySQL5.png
          MySQL5.png
          13 kB
        3. MySQL4.png
          MySQL4.png
          24 kB
        4. MySQL3.png
          MySQL3.png
          19 kB
        5. MySQL2.png
          MySQL2.png
          18 kB
        6. MySQL1.png
          MySQL1.png
          20 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: