Results 1 to 15 of 15
  1. #1
    Join Date
    Jun 2007
    Posts
    197

    Smile Unanswered: exporting into csv with condition

    hi ,
    I use to export table by following syntax

    EXPORT TO C:\ESHOPBACKUP\ISMCND.csv OF DEL MODIFIED BY chardel"" coldel, decpt. datesiso decplusblank MESSAGES C:\ESHOPBACKUP\ISMCND.txt SELECT * FROM ISM.ISMCND;

    for table ISMCND

    When export in csv such that where is NULL in column there will be blank cell in output csv file

    I want that in place of blank cell I want NULL charactor to be written there

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Please explain what you mean, maybe use some examples. Also explain why you want it that way. What DB2 version and OS?

    Andy

  3. #3
    Join Date
    Jun 2007
    Posts
    197

    Smile

    Quote Originally Posted by ARWinner
    Please explain what you mean, maybe use some examples. Also explain why you want it that way. What DB2 version and OS?

    Andy

    OS:-Windows XP
    database version:- DB2 7.1
    table from that export :-ISMCND
    export syntax:-EXPORT TO C:\ESHOPBACKUP\ISMCND.csv OF DEL MODIFIED BY chardel"" coldel, decpt. datesiso decplusblank MESSAGES C:\ESHOPBACKUP\ISMCND.txt SELECT * FROM ISM.ISMCND;

    Purpose :after exporting this csv file i want to upload this csv into Mysql data table

    Problem:-Now problem is that I got csv file from DB2 table such as where is NULL in DB2 there is BLANK CELL in csv file and when I load this file This blank cell inserts 0 instead of NULL

    If in place of Blank cell of csv there will be NULL charactor then to load this file in MySQL will give null value instead of 0

    This is the complete information:Please provide solution

  4. #4
    Join Date
    May 2007
    Location
    somewhere in dbforums
    Posts
    221
    well you can change your following statement
    SELECT * FROM ISM.ISMCND

    to something like this

    SELECT
    COLUMN1, ...... where i am assuming COLUMN1 is a primary key .....
    CASE
    WHEN COLUMN2 IS NULL ... where COLUMN2 can be nullable
    THEN
    'NULL'
    ELSE
    COLUMN2
    END AS COLUMN2,
    .... similarly for all other columns which can hold NULL values ....
    FROM
    ISM.ISMCND

    this will basically print NULL wherever the columns hold NULL values

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    This will only work for columns that have a string data type. For all other columns, you have to work-around this like here:
    Code:
    SELECT column1,
           COALESCE(string_col2, 'NULL'),
           COALESCE(CHAR(int_col3), 'NULL')
    FROM ...
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  6. #6
    Join Date
    May 2007
    Location
    somewhere in dbforums
    Posts
    221
    grrrr..... now I know why experience counts.... had faced this issue and forgotten all about it

  7. #7
    Join Date
    Jun 2007
    Posts
    197

    Smile

    Quote Originally Posted by stolze
    This will only work for columns that have a string data type. For all other columns, you have to work-around this like here:
    Code:
    SELECT column1,
           COALESCE(string_col2, 'NULL'),
           COALESCE(CHAR(int_col3), 'NULL')
    FROM ...
    Thanks for giving solution

    here is following table ISMCND description
    Column Type Type
    name schema name
    ------------------------------ --------- -----------------
    CNDRFNUM SYSIBM BIGINT
    CNDGROUP SYSIBM VARCHAR
    CNDCODE SYSIBM CHARACTER
    CNDDESC SYSIBM VARCHAR
    CNDCNDRFNUM SYSIBM BIGINT
    CREATEDATE SYSIBM TIMESTAMP
    MODIDATE SYSIBM TIMESTAMP
    DELETED SYSIBM CHARACTER
    CREATEDBY SYSIBM BIGINT


    for this export syntax

    EXPORT TO C:\ESHOPBACKUP\ISMCND.csv OF DEL MODIFIED BY chardel"" coldel, decpt. datesiso decplusblank MESSAGES C:\ESHOPBACKUP\ISMCND.txt select CNDRFNUM, CNDGROUP, CNDCODE, CNDDESC, CNDCNDRFNUM (BIGINT_COL5,'NULL'), CREATEDATE, MODIDATE, DELETED, CREATEDBY from ISMCND

    showing error

    SQL3022N An SQL error "-206" occurred while processing the SELECT string in the Action String parameter.

    what could be the export syntax according you?

  8. #8
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    What is this supposed to be:
    Code:
    CNDCNDRFNUM (BIGINT_COL5,'NULL')
    COALESCE is a standard SQL function, which returns the first non-null value of its input arguments. So I guess you want to do that:
    Code:
    COALESCE(CHAR(CNDCNDRFNUM), 'NULL')
    Btw: those are very cryptic table/column names and if you have the chance, I would suggest to rename them to something readable and comprehensible. The times where things had to be short due to some limitations are gone for more than 20 years already.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  9. #9
    Join Date
    Jun 2007
    Posts
    197

    Smile

    Quote Originally Posted by stolze
    COALESCE is a standard SQL function, which returns the first non-null value of its input arguments. So I guess you want to do that:
    Code:
    COALESCE(CHAR(CNDCNDRFNUM), 'NULL')
    Btw: those are very cryptic table/column names and if you have the chance, I would suggest to rename them to something readable and comprehensible. The times where things had to be short due to some limitations are gone for more than 20 years already.

    Thanks for giving suggestion and solution

    I am converting DB2 data to MySQL data
    There are 178 Tables and some of them have 15 to 25 fields
    so by using COALESCE It is too much hectic to put on every column

    so How to put NULL charactor for all tables

  10. #10
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Let's take a step back: why do you want to have such a conversion in the first place? Does MySQL need it?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  11. #11
    Join Date
    Jun 2007
    Posts
    197

    Smile

    Quote Originally Posted by stolze
    Let's take a step back: why do you want to have such a conversion in the first place? Does MySQL need it?
    I use following syntax to load csv file in mysql

    LOAD DATA LOCAL INFILE 'ISMCND.csv' INTO TABLE test.ISMCND FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\';

    It is required to put NULL charactor in place of blank cell because when I load that csv file , entry of Blank cell is 0 for the table in mysql but when we use NULL charactor there will be right entry mean null instead of 0

    so how to convert this blank cell to NULL charactor for many table while checking each table for column is much much hactic

  12. #12
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    I guess your best bet is to run a tool like SED or AWK over the DB2 output to convert the format to whatever makes MySQL happy.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  13. #13
    Join Date
    Jun 2007
    Posts
    197

    Smile

    Quote Originally Posted by stolze
    I guess your best bet is to run a tool like SED or AWK over the DB2 output to convert the format to whatever makes MySQL happy.
    Thanks Please provide me Link for SED or AWK tool for DB2

  14. #14
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    sed and awk are regular Unix tools.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  15. #15
    Join Date
    Jun 2007
    Posts
    197

    Smile

    Quote Originally Posted by stolze
    sed and awk are regular Unix tools.
    Thanks of your support for giving me such information

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •