4 Replies Latest reply on Mar 29, 2010 9:36 PM by PoojaK

    Error while Loading data through .csv file

    PoojaK
      Hi,

      I am getting below date error when loading data through into Olap tables through .csv file.

      Data stored in .csv is 20071113121100.

      "
      TRANSF_1_1_1> CMN_1761 Timestamp Event: [Mon Mar 29 15:06:17 2010]
      TRANSF_1_1_1> TE_7007 Transformation Evaluation Error [<<Expression Error>> [TO_DATE]: invalid string for converting to Date
      ... t:TO_DATE(u:'2.00711E+13',u:'YYYYMMDDHH24MISS')]
      TRANSF_1_1_1> CMN_1761 Timestamp Event: [Mon Mar 29 15:06:17 2010]
      TRANSF_1_1_1> TT_11132 Transformation [Exp_FILE_CHNL_TYPE] had an error evaluating output column [CREATED_ON_DT_OUT]. Error message is [<<Expression Error>> [TO_DATE]: invalid string for converting to Date
      ... t:TO_DATE(u:'2.00711E+13',u:'YYYYMMDDHH24MISS')].

      TRANSF_1_1_1> CMN_1761 Timestamp Event: [Mon Mar 29 15:06:17 2010]
      TRANSF_1_1_1> TT_11019 There is an error in the port [CREATED_ON_DT_OUT]: The default value for the port is set to: ERROR(<<Expression Error>> [ERROR]: transformation error
      ... nl:ERROR(u:'transformation error')).
      TRANSF_1_1_1> CMN_1761 Timestamp Event: [Mon Mar 29 15:06:17 2010]
      TRANSF_1_1_1> TT_11021 An error occurred moving data from the transformation Exp_FILE_CHNL_TYPE: to the transformation W_CHNL_TYPE_DS.
      TRANSF_1_1_1> CMN_1761 Timestamp Event: [Mon Mar 29 15:06:17 2010]
      TRANSF_1_1_1> CMN_1086 Exp_FILE_CHNL_TYPE: Number of errors exceeded threshold [1].
      "

      Any help is greatly appreciated.

      Thanks,
      Poojak
        • 1. Re: Error while Loading data through .csv file
          PeakIndicators_Alastair
          Poojak,

          1) Wrong format, you wont get much support loading OLAP cubes in here I think.
          2) Has your CSV file been anywhere near excel by chance the conversion of 20071113121100 to 2.00711E+13 looks very much like what I see when Excel has an invalid number mask / precision specified for a cell.

          What generated the csv ? OBIEE ?

          Is that the only row failing or just the first you've encountered in your dataset?
          • 2. Re: Error while Loading data through .csv file
            PoojaK
            1) Wrong format, you wont get much support loading OLAP cubes in here I think.
            2) Has your CSV file been anywhere near excel by chance the conversion of 20071113121100 to 2.00711E+13 looks very much like what I see when Excel has an invalid number mask / precision specified for a cell.

            *** We are using EBS. File was already generated by consultants through SQL query. Getting error while loading though Informatica. Target table is setup as date datatype and source is String(19).

            Expression in Informatica is setup as below.

            IIF(ISNULL(CREATED_ON_DT), NULL, TO_DATE(CREATED_ON_DT, 'YYYYMMDDHH24MISS'))


            Thanks,
            Poojak
            • 3. Re: Error while Loading data through .csv file
              PeakIndicators_Alastair
              What tool to spool the file though ? Did it go anywhere near a GUI tool ? I'd bet it was precision on the datatype or incorrect datatype altogether

              If I paste 20071113121100 into a new excel workbook, the display comes back as 2.00711E+13 - when I set column datatype to number I see all digits.
              OK this is not excel but you get what im saying,
              Can you run the SQL query from SQL plus and spool directly to file ?
              • 4. Re: Error while Loading data through .csv file
                PoojaK
                I have opened file in TEXTPAD, entered the correct format and then save as CSV file. Able to load file through informatica.

                Thanks,
                Poojak