Forum Discussion

nexus's avatar
nexus
Occasional Contributor
10 years ago

Trouble getting desired excel data

Hi,

 

I need to retrieve a certian row from an excel file but am running into problems. First I tried the COM object but since I do not have excel install on my machine it gave the error of unknown classID. So I moved onto the DDT driver and it seems to have issue with how the columns are formated. I want to get the second row of the attached sheet. But if you run the following code, 

 

var path = "C:\\Users\\" + Sys.UserName + "\\Downloads\\test.xls";
var Driver = DDT.ExcelDriver(path, "Sheet1");

while (! Driver.EOF() )
{
for(i = 0; i < DDT.CurrentDriver.ColumnCount; i++)
Log.Message(DDT.CurrentDriver.ColumnName(i) + ": " + aqConvert.VarToStr(DDT.CurrentDriver.Value(i)));
Driver.Next(); // Goes to the next record
}

 

The second row is not correctly printed. I've tried several wasy to print that second row but I'm stumped. 

  • nexus's avatar
    nexus
    10 years ago

    Thank you 

     

     

  • dmiscannon's avatar
    dmiscannon
    Frequent Contributor
    10 years ago

    What are you getting as the column names? My guess is the column names are either blank or 'Product 1'  or 'Product 2.' That's because Excel column headers should be in the first row and not the second. Try removing the first row from the spreadsheet and see what you get.

    To verify my above assumption, I took your spreadsheet and created a Table Data variable and created a DDT loop with it. Then I added the Append Log Folder command in a Keyword test. The names of the Excel Columns were F1, NoName' F3, F4, Product1, etc. (Basically the first row of your spreadsheet.)

    • nexus's avatar
      nexus
      Occasional Contributor
      10 years ago

      For column names I am getting F1, NoName, F3, F4, etc which is fine. Since I am downloading this file from a server, I don't want to modify the file, I want to verify the names of the second row which includes Accounts, ID, MarketVolume etc but I am getting mostly blank results when it loops through. The strange thing happens where the third and following rows get printed like expected, but the second row does not. 

  • nexus's avatar
    nexus
    Occasional Contributor
    10 years ago

    I also tried:

     

    var path = "C:\\Users\\" + Sys.UserName + "\\Downloads\\test.xls";
    var Qry = ADO.CreateADOQuery();
    Qry.ConnectionString = "Provider = Microsoft.ACE.OLEDB.12.0; Data Source = " + path + "; Extended Properties ='Excel 12.0 Xml;HDR=YES' ";
    Qry.SQL = "Select * FROM [sheet1$]";
    Qry.Open();
    Qry.First();

    for(var i = 0; i < Qry.FieldCount; i++)
    {
       Log.Message(Qry.Field(i).Value);
    }
    Qry.Close();

     

     

    And like the DDT method it printed: Accounts, ID, blank, Email, blank, blank, (21 more blanks).

     

    Is there anyway to print out the second column in its entirety?