6 Replies Latest reply: Jan 2, 2013 12:15 AM by kikolus RSS

    excel sheet and forms 10g rel2

      db and dev 10g rel2 ,
      hi all ,
      i've a machine in which the employee put his card to record the attending and leaving date of this employee .
      but the problem is the machine records the employees data in an excel sheet , and i've to import every new record from
      the excel sheet to forms 10g rel2 ,
      and sure i can not import the whole excel file every time an employee put his card in the machine .

      how could i solve this problem ?
      thanks in advance
        • 1. Re: excel sheet and forms 10g rel2
          Andreas Weiden
          I would guess that your time-catching machine does not write a real excel-sheet, but more a csv-file. If so, you cam read that using TEXT_IO or CLIENT_TEXT_IO from forms (or using UTL_FILE from the database).
          • 2. Re: excel sheet and forms 10g rel2
            can these solutions transfer each new record to my form ? or the whole file ?
            • 3. Re: excel sheet and forms 10g rel2
              Andreas Weiden
              These are not solutions, but techniques you can use to import files.

              The logic to extract only new records has to be implemented by you yourself. E.g. you could store the last imported date in a table and skip all records before this date. Also, i would guess that this time-taking machine can be configured to write one file per day or something similar.
              • 4. Re: excel sheet and forms 10g rel2
                If this is csv-like file (not binary Excel file) then consider to create external table based on it. Then you'll be able to do whatever you want with data (consider to make such external table as a source and copy data to another table (using merge or whatever you want). Then you can mark already processed/displayed records on your new table or simply lists external table minus your table to see newly created records.
                Hope this helps you .
                • 5. Re: excel sheet and forms 10g rel2
                  If this is csv-like file
                  it is .csv file .
                  and i do not know about external tables , but i think what you are saying could work well ,
                  if you could provide me with a link to know about these tables , or demonstrate how i can create it to me ?
                  thank a lot
                  • 6. Re: excel sheet and forms 10g rel2
                    Here you have link to the documentation:
                    Cause I don't know your file structure I can only prepare a generic example for you (see my comments for more details):
                    create table ext_table_csv ( 
                    --here you should define table columns e.g. 
                    EMPNO    NUMBER(4), 
                    ENAME    VARCHAR2(10), 
                    JOB      VARCHAR2(9), 
                    MGR      NUMBER(4), 
                    HIREDATE DATE, 
                    SAL      NUMBER(7,2), 
                    COMM     NUMBER(7,2), 
                    DEPTNO   NUMBER(2) 
                    organization external ( 
                      type              oracle_loader 
                      default directory ext_dir -- ORACLE directory name 
                      access parameters ( 
                        records delimited  by newline 
                        fields  terminated by ';' -- depends on your delimiter 
                        missing field values are null -- optional clause 
                      location ('file.csv') -- file name 
                    reject limit unlimited; -- self explanatory 
                    Hope this helps.