6 Replies Latest reply: Nov 6, 2013 4:57 AM by Rarebazza RSS

    Import csv into Apex with time only data.

    Rarebazza

      Hi,

       

      I have been trying to figure out this issue for a while now and have been on serveral forums with no luck.

       

      Im learning SQL and PL/SQL so please be kind

       

      I am tasked to a csv where the data is time only in the column "KO_TIME" for example '13:30' or '18:00' 24 hour clock of just hours and minutes.

      I am aware of the TO_CHAR function to convert a date to a string within an insert statement but I want this to be be part of the creation of the table syntax so I am able to use the APEX CSV import tool and it will understand the data 00:00 time format.

       

      The table creation is this so far but with syntax errors;

       

      CREATE TABLE  "FOOT_BALL_VENUES" 
         (     "ID" NUMBER,
           "DESCRIPTION" VARCHAR2(30),
           "DAY" VARCHAR2(30),
           "DATE_FIX" DATE,
           "KO_TIME" DATE AS (TO_CHAR('HH24:MM')
                      "FIXTURE" VARCHAR2(255),
           CONSTRAINT "FOOT_BALL_VENUES_PK" PRIMARY KEY ("ID") ENABLE
         )
      /

      CREATE OR REPLACE TRIGGER  "bi_FOOT_BALL_VENUES"

        before insert on "

       

      FOOT_BALL_VENUES"       

        for each row

      begin 

        if :new."ID" is null then

          select "FOOT_BALL_VENUES_SEQ".nextval into :new."ID" from dual;

        end if;

      end;

       

      /

      ALTER TRIGGER  "bi_FOOT_BALL_VENUES" ENABLE

      /

      Thanks for your time guys.

       

      Rarebazza

        • 1. Re: Import csv into Apex with time only data.
          jariola

          Hi,

           

          If your csv KO_TIME column have only time, then use VARCHAR2 data type to your table KO_TIME column.

           

          If csv KO_TIME column have date and time, then use DATE format and create e.g. virtual column that shows only time.

          CREATE TABLE  "FOOT_BALL_VENUES"
             (     "ID" NUMBER,
               "DESCRIPTION" VARCHAR2(30),
               "DAY" VARCHAR2(30),
               "DATE_FIX" DATE,
               "KO_TIME" DATE,
               "KO_ONLY_TIME" VARCHAR2(255) GENERATED ALWAYS AS (TRIM(TO_CHAR(KO_TIME,'HH24:MI'))) VIRTUAL,
               "FIXTURE" VARCHAR2(255),
               CONSTRAINT "FOOT_BALL_VENUES_PK" PRIMARY KEY ("ID") ENABLE
             )
          /
          

           

          Regards,

          Jari

          • 2. Re: Import csv into Apex with time only data.
            Rarebazza

            Hi Jari,

             

            thank you so much for this, I can now start to understand the logic.

             

            I have tried to run the script and it states that on line 7 there is an error message.

             

            Error report:

            SQL Error: ORA-00907: missing right parenthesis

            00907. 00000 -  "missing right parenthesis"

            *Cause:   

            *Action:

            • 3. Re: Import csv into Apex with time only data.
              jariola

              Hi Rarebazza ,

               

              Sorry, but witch script you did run and get that error?

               

              Regards,
              Jari

              • 4. Re: Import csv into Apex with time only data.
                Rarebazza

                Hi Jari,

                 

                The script I ran was;

                 

                CREATE TABLE "FOOT_BALL_VENUES"

                  (

                    "ID"           NUMBER,

                    "DESCRIPTION"  VARCHAR2(30),

                    "DAY"          VARCHAR2(30),

                    "DATE_FIX"     DATE,

                    "KO_TIME"      DATE,

                    "KO_ONLY_TIME" VARCHAR2(255) GENERATED ALWAYS AS (TRIM(TO_CHAR(KO_TIME,'HH24:MI'))) VIRTUAL,

                    "FIXTURE"      VARCHAR2(255),

                    CONSTRAINT "FOOT_BALL_VENUES_PK" PRIMARY KEY ("ID") ENABLE

                  )

                 

                thanks


                • 5. Re: Import csv into Apex with time only data.
                  jariola

                  Hi,

                   

                  That table creation clause works ok in apex.oracle.com.

                   

                  Virtual columns are introduced on 11G database.

                  What is your database version?

                   

                  If your database do not support virtual columns, then you have option create view top of your table.

                   

                  Regards,

                  Jari

                  • 6. Re: Import csv into Apex with time only data.
                    Rarebazza

                    Hi Jari,

                     

                    I believe you are right with this as i am working on 10g. will be on 11g within the year.

                    I think I will have to create the table as varchar2 string to start with and then export the csv's as '00:00' string.

                    Then somehow  later convert these back to date (time only) in a plsql function to be called upon when runnigng the application.

                     

                    Thank you so much for your time.