Forum Discussion

tmahender12's avatar
tmahender12
Frequent Contributor
10 years ago

To get unique values from xls Colomn

HI,   I have a requirement, where there are 100 rows in a xls, with duplicate values in the cell, i need to get unique values from that colomn. any ideas
tristaanogre's avatar
tristaanogre
Esteemed Contributor
10 years ago

Consider using the DDT.ExcelDriver object. This allows you to connect to your Excel sheet and then interate through the records to find what you're looking for.  You can also pass in an SQL query to this object to reduce the recordset down to only what you want by calling the ADOCommandObject.CommandText. Something like this

 

var MyExcelRecords;

MyExcelRecords = DDT.ExcelDriver("C:\MyDir\MyExcel.xls");
MyExcelRecords.ADOCommandObject.CommandText = "SELECT DISTINCT Col_1 from MyExcel.xls";

 

 

  • tmahender12's avatar
    tmahender12
    Frequent Contributor
    10 years ago

    its not working

    getting error at

    MyExcelRecords = DDT.ExcelDriver("C:\MyDir\MyExcel.xls");
    • altemann's avatar
      altemann
      Contributor
      10 years ago
      What error are you getting?
      • tristaanogre's avatar
        tristaanogre
        Esteemed Contributor
        10 years ago

        My sample code I gave was intended to be pseudo-code and not necessarily something to be implemented as is.

        Looking over what I wrote, if you're going to use the JScript language (which is what I was using) it should look like this:

        function GetCol1Unique();

{
var MyExcelRecords;

MyExcelRecords = DDT.ExcelDriver("C:\\MyDir\\MyExcel.xls");
MyExcelRecords.ADOCommandObject.CommandText = "SELECT DISTINCT Col_1 from MyExcel.xls";
return MyExcelRecords;
}

        Note that the pathing needs to use double backslashes as the backslash is a special character for use in strings in JScript.

        But, as mentioned, it would be helpful to know what the error is that you're getting.