3 Replies Latest reply on Oct 23, 2013 10:12 PM by Mike Kutz

    Transforming a column value from CSV before loading

    950123

      Hi,

      I have a Timestamp value in the CSV like 10/23/2013  2:26:00 AM

      I am loading this CSV into a table which has the column datatype DATE.

       

      I'm new to APEX and am not quite sure how to setup the Transformation rules.

       

      Can anyone help??

      Thanks.

        • 1. Re: Transforming a column value from CSV before loading
          Mike Kutz

          Data Loader?

          String to Date conversion gets done via FORMAT.

           

          date format should be:

          MM/DD/YYYY HH12:MI:SS AM

           

          MK

          • 2. Re: Transforming a column value from CSV before loading
            950123

            The format which I have received the value is 9/4/2013  1:54:00 AM

             

            I tried using a PL/SQL function body:

            DECLARE

              new_creation_date DATE;

              var1 VARCHAR2(20);

            BEGIN

              new_creation_date := TO_DATE(TO_CHAR('9/4/2013  1:54:00 AM', 'MM/DD/YYYY  HH12:MI:SS AM'), 'MM/DD/YYYY');

              RETURN new_creation_date;

            END;

             

            This gave me a ORA-06502: PL/SQL: numeric or value error: character to number conversion error

             

            Can you tell me more about FORMAT?

            Thanks.

            • 3. Re: Transforming a column value from CSV before loading
              Mike Kutz

              You are silently casting a string to a number (not a date).

              Try this:

              declare
                l_input_from_csv varchar2(20) := '9/4/2013 1:54:23 AM';
                l_date date;
              begin
                l_date := to_date( l_input_from_csv, 'MM/DD/YYYY HH12:MI:SS AM');
                return l_date;
              end;
              

               

              HOWEVER -- I think APEX will convert the date data type back into a string data type (which is not what you want to happen).

               

              Assuming you are using the Data Loader in your application:

              The first page/step of the process asks for the file, separator, etc.

              The second page/step asks you to assign TABLE column to CSV column (***!!!!!)

              -- this also includes any special 'formatting' that needs to take place.

              -- it is on THIS page that you will add the appropriate FORMAT string to the appropriate text box. (the empty box under the dropdown list)

              -- from my experience, APEX will run the to_date() command using that 'format string' for you.

              The third page/step is a verification step

              The 4th page/step shows you what has been done.

               

              MK