5 Replies Latest reply: Nov 19, 2012 2:32 AM by BluShadow RSS

    Condition check for data

    873006
      Hi Team,

      I have 100 records in excel sheet i need find in table called emp i.e

      Example :
      12
      23
      34
      45
      56
      .
      .
      .
      100 in excel sheet.

      and i have table in oracle emp

      i need to check the condition is this number are there in this table or not .

      i am doing manual why like copy the records in excel and putting in query like
      select empno from emp where 
      empno in (
      2,
      3,
      4,
      5,
      6,
      .
      .
      );
      2 point is i need to remove the 1 value in the excel example if 12 is number i should table 2 only and need to find the emp table.

      Please help on this..

      Thanks
        • 1. Re: Condition check for data
          ranit B
          I'm not sure if i'm getting it right... But try this -

          Pull the data from XLS into an External table and then use it to compare with EMP table.

          Refer -- http://www.oracle-base.com/articles/9i/external-tables-9i.php

          HTH
          Ranit B.
          • 2. Re: Condition check for data
            873006
            Thanks Ranit for reply.

            But i am not able have much permission for creating the external table. i looking like if get data in excel i need copy and past in sql developer and using logic of query
            only creating table structure if possible.
            • 3. Re: Condition check for data
              jeneesh
              Use CONCATENATE and MID function in Excel
              =CONCATENATE(MID(A1,2,20),",")
              
              "A1 is th cell in excel which contains your value.
              "Paste it for 100 cells
              the above text can of 100 cells can be copied together and can be use in IN condition of your SQL
              • 4. Re: Condition check for data
                971895
                or else .. simply load the excel data into temporary table using sql loader then check with data to emp table.
                • 5. Re: Condition check for data
                  BluShadow
                  968892 wrote:
                  or else .. simply load the excel data into temporary table using sql loader then check with data to emp table.
                  Excel data is not in a format that SQL*Loader can read.
                  .xls files are a proprietary Microsoft file format which is binary in nature.

                  The only way to do it with SQL*Loader would be to export the data from Excel to a CSV file format (CSV files are NOT Excel files, though Excel is able to read and write them), and then write an appropriate control file to load that CSV format file.