Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2013
    Posts
    14

    Unanswered: Importing data from a file into table

    Hello,

    I am using DB2 UDB v9.7 as database. I have a tough situation at hand. Single row in data file is splitted into multiple records and thus when I am importing the file these records are getting rejected.
    For e.g. Table has 3 columns - Id, Name, Gender (NULL)
    and data in data file is like -
    001,Abc
    def,Male
    002,Abcdef,Male
    003,
    qwerty,Female
    004,qwerty,Female

    Now when i am importing this kind of file into table, rows with Id 001 and 003 are getting rejected.
    I have to manually correct these rows to be in a single line and there are millions of records in file.
    Is there any way to import this kind of file correctly without manually correcting the data.

    Any help would be highly appreciated.
    Thanks in anticipation.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I didn't look for tools or commands so seriously.
    So, there might be other better ways.


    Anyway,
    My idea was ..

    (1) IMPORT the each line of data of the file into a column of a temporary table.
    It looks like
    Code:
    ------------------------------ Commands Entered ------------------------------
    CREATE TABLE kapi.temp
    ( seq     INTEGER NOT NULL
              GENERATED ALWAYS AS IDENTITY
    , in_data VARCHAR(50)
    );
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    IMPORT FROM kapi_sample.txt OF DEL MODIFIED BY COLDEL; METHOD P(1) INSERT INTO kapi.temp(in_data);
    ------------------------------------------------------------------------------
    SQL3109N  The utility is beginning to load data from file "kapi_sample.txt".
    
    SQL3110N  The utility has completed processing.  "6" rows were read from the 
    input file.
    
    SQL3221W  ...Begin COMMIT WORK. Input Record Count = "6".
    
    SQL3222W  ...COMMIT of any database changes was successful.
    
    SQL3149N  "6" rows were processed from the input file.  "6" rows were 
    successfully inserted into the table.  "0" rows were rejected.
    
    
    Number of rows read         = 6
    Number of rows skipped      = 0
    Number of rows inserted     = 6
    Number of rows updated      = 0
    Number of rows rejected     = 0
    Number of rows committed    = 6
    Then
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT * FROM kapi.temp;
    ------------------------------------------------------------------------------
    
    SEQ         IN_DATA                                           
    ----------- --------------------------------------------------
              1 001,Abc                                           
              2 def,Male                                          
              3 002,Abcdef,Male                                   
              4 003,                                              
              5 qwerty,Female                                     
              6 004,qwerty,Female                                 
    
      6 record(s) selected.

    (2) Then edit the data by a query and use the query in an INSERT statement for your table.

    Note: The VARCHAR functions in the final SELECT list might be not neccesary,
    to use the query in the INSERT statement.
    Because, the VARCHAR functions were used to make the results readable.
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT VARCHAR(
              SUBSTR(concat_data ,      1 , p1      - 1)
            , 05) AS id
         , VARCHAR(
              SUBSTR(concat_data , p1 + 1 , p2 - p1 - 1)
            , 10) AS name
         , VARCHAR(
              SUBSTR(concat_data , p2 + 1)
            , 07) AS gender
     FROM  kapi.temp AS a
     LEFT  OUTER JOIN
           kapi.temp AS b
      ON   b.seq = a.seq + 1
      AND  TRANSLATE(
              LEFT(b.in_data , POSSTR(b.in_data , ',') - 1)
            , ''
            , '0123456789' , ' '
           ) <> ''
     CROSS JOIN
           LATERAL
           (VALUES a.in_data || COALESCE(b.in_data , '')
           ) AS f(concat_data)
     CROSS JOIN
           LATERAL
           (VALUES (  INSTR(concat_data , ',' , 1 , 1)
                    , INSTR(concat_data , ',' , 1 , 2)
                   )
           ) AS f2(p1 , p2)
     WHERE TRANSLATE(
              LEFT(a.in_data , p1 - 1)
            , ''
            , '0123456789' , ' '
           ) = ''
    ;
    The results were...
    Code:
    ID    NAME       GENDER 
    ----- ---------- -------
    001   Abcdef     Male   
    002   Abcdef     Male   
    003   qwerty     Female 
    004   qwerty     Female 
    
      4 record(s) selected.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    By the way,

    I am curious about why the format of the file was such strange(and various) formats?

    Because, you wrote "there are millions of records in file",
    so the file might be generated by some automatic ways(including by programs, so on ...), not by a hand.

    If so, it might be easier to produce the file with a pre-defined format,
    and not such ways that split a record by optional position(or occasionally not split a record).

Posting Permissions

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