Forum Discussion

mesg2anil's avatar
mesg2anil
Regular Contributor
14 years ago

Using Array to get Excel data

Hi,



I'm trying to get values (data) from excel, after using if and for loop to get rows and columns details, I'm trying to put the data to an array and then use the array to input the data from array to application. Below is the code which I'm using to get the data from excel and tying to put the data into array... this the place where I'm having trouble... please help!!



Function testarray()


'Open Excel Connection


 Set Excel = Sys.OleObject("Excel.Application")


 Call Excel.Workbooks.Open("E:\Anil-Backup\Test Complete\Projects_Old\In4Suite Project\ExcelFile\Rental Database\RentalTermSheet.xlsx")


 Delay 1000


 


 'Read number of rows and assign it to 'i'


 i = Excel.ActiveSheet.UsedRange.Rows.Count


 j = Excel.ActiveSheet.UsedRange.Columns.Count     


 


Log.Message i   


Log.Message j


 


 If i > 1 then   


for k = 2 to i     


For l = 1 to j 


s = VarToStr(Excel.Cells(k, l)) + "#"   


 


testarray = s 


 


next 


Next 


End if   


 


Call Excel.ActiveWorkbook.Save   


Call Excel.Workbooks.Close 


End Function


 


Sub arrcells


    


Dim str, arrStr 


Str = testarray() 


arrStr=Split(Str,"#") 


Log.Message arrStr (0)


'Log.Message arrStr(1) 


'Log.Message arrStr(2) 


 


End Sub

  • tristaanogre's avatar
    tristaanogre
    Esteemed Contributor
    14 years ago
    Is there any particular reason why using DDT.ExcelDriver won't work for you?  This object provides an interface to your Excel sheet that you can then input directly to your application.



    You're code could be reduced to the following:





    function TestExcelData()

    {

        var MyDDTObject = DDT.ExcelDriver("E:\\Anil-Backup\\Test Complete\\Projects_Old\\In4Suite Project\\ExcelFile\\Rental Database\\RentalTermSheet.xlsx", "Sheet1", true)

        while (!MyDDTObject.EOF())

        {

            Log.Message(MyDDTObject.Value(0))

            Log.Message(MyDDTObject.Value(1))

            Log.Message(MyDDTObject.Value(2))

            MyDDTObject.MoveNext;

        }

        DDT.CloseDriver(MyDDTObject.Name)

    }
  • mesg2anil's avatar
    mesg2anil
    Regular Contributor
    14 years ago
    Hi Robert,



    Thank you for replying!



    Other than getting the data from excel and inputing it into application, I was also trying to do some action based on rows I have in excel sheet, for example...



    Excel Sheet data...



    Name    DOB               Address1          City                    'This is header section

    Anil        10/10/2000    # 1                    Bengaluru            '1st row data

    Robert    1/1/2000        # 5                    US                      '2nd row data



    Now, if I have only 1st row in the excel sheet, I want to do some action in the script else if I have 2nd row in the excel sheet i want to do other steps in the script. In my code above I gave "If i > 1" that is excluding header, I was controlling the data input to application. In my code if I write

    If i = 2 then

        <do this action>

    elseif i > 2 then 'here it will be row 3 and above

        <    do this action>

    End If



    How to do that using DDT.Driver? Could you please help me in this regard? I'm writting the code in VB.
  • Abramova's avatar
    Abramova
    Icon for Staff rankStaff
    14 years ago

    Hi Anil,


    Pay attention to the value of the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel\FirstRowHasNames registry key. This key specifies whether the first row of the table is recognized as a header or as data. If you use the first row of the table as a header, you need to make sure that the key value is 01. If the first row of the table stores data, the key value should be 00. If a row is not empty, it is considered as the first row. Otherwise, it is ignored.

  • mesg2anil's avatar
    mesg2anil
    Regular Contributor
    14 years ago
    Hi Margaret / Robert,



    Thank you for replying!! :)



    The code which I pasted above at top, I want to use array in that code to collect the data from excel. Later Robert suggested me to use DDT.Driver, the code which Robert gave me, I could use that, but... again I had a question about how to control the rows in that code which Robert gave me.



    I need two suggestions here...

    1. If I use my code which I pasted at top, in that code if I want to use array to collect all the excel data, how to do that in my existing code? OR

    2. If I use the code which Robert sent, in that code "MyDDTObject" is acting like an array, also I want to do below...

    If rowcount = 1 then

        <perform action1>

    elseif rowcount = 2 then

        <perform action2>

    End if

    how to use above mentioned example in the code which Robert sent?
  • tristaanogre's avatar
    tristaanogre
    Esteemed Contributor
    14 years ago
    I would add a counter into the loop to keep track of how many times through the loop you are.  So, the code would look like this.  I'm sorry it's in JScript, I'm not really good at the syntax of VB so I default to what I know.



    function TestExcelData()

    {

        var RowCount = 1;

        var MyDDTObject = DDT.ExcelDriver("E:\\Anil-Backup\\Test Complete\\Projects_Old\\In4Suite Project\\ExcelFile\\Rental Database\\RentalTermSheet.xlsx", "Sheet1", true)

        while (!MyDDTObject.EOF())

        {

            if (RowCount == 1)

            {

                 DoRowStuff;

            }

            else

            {

                 DoOtherStuff;

             }

            Log.Message(MyDDTObject.Value(0))

            Log.Message(MyDDTObject.Value(1))

            Log.Message(MyDDTObject.Value(2))

            MyDDTObject.MoveNext;

            RowCount++;

        }

        DDT.CloseDriver(MyDDTObject.Name)

    }




    As was pointed out, there's no need to distinguish the header row when using the DDT driver, it should automatically recognize the first row as header values.
  • mesg2anil's avatar
    mesg2anil
    Regular Contributor
    14 years ago
    Hi Robert...



    I converted jscript code to VB and tried, it is working but what exactly happening is, it is displaying all the rows and columns data... below is the code...

    My requirement is, if I say RowCount = 2 then it should display only 2nd Row data from excel, but the code which you gave is displaying 1st and 2nd row and column data. Any modifications I still need to do in the below code?




    Sub ArrayTesting


     ArrayTesting

    RowCount = 0


    Set MyDDTObject = DDT.ExcelDriver("E:\Anil-Backup\Test Complete\Projects_Old\In4Suite Project\ExcelFile\Rental Database\RentalTermSheet.xlsx", "Sheet1", true)


    While Not MyDDTObject.EOF()


    If RowCount = 2 Then


    Log.Message RowCount


    Log.Message(MyDDTObject.Value(0))


    Log.Message(MyDDTObject.Value(1))


    Log.Message(MyDDTObject.Value(2))


    Else


    Log.Message(MyDDTObject.Value(0))


    Log.Message(MyDDTObject.Value(1))


    'Log.Warning "data not found"


    MyDDTObject.Next


    RowCount = RowCount + 1


    End If


    WEnd


    DDT.CloseDriver(MyDDTObject.Name)


     



    End Sub


      
  • Abramova's avatar
    Abramova
    Icon for Staff rankStaff
    14 years ago
    Hi Anil,



     In your case, you do not need to create the counter. You need to get the total number of rows in the Excel sheet and use this value for the verification. To obtain this value, you can use the Excel.Application COM object. For more information, please read Working With Excel Files via COM in the online documentation.



    For example, you can modify your script in the following way:



    Sub ArrayTesting

    ArrayTesting



    ' Get the total number of rows

    Dim Excel, RowCount



    Set Excel = Sys.OleObject("Excel.Application")

    Excel.Workbooks.Open("E:\Anil-Backup\Test Complete\Projects_Old\In4Suite Project\ExcelFile\Rental Database\RentalTermSheet.xlsx")

    RowCount = Excel.ActiveSheet.UsedRange.Rows.Count

    ' Iterate through the storage

    Set MyDDTObject = DDT.ExcelDriver("E:\Anil-Backup\Test Complete\Projects_Old\In4Suite Project\ExcelFile\Rental Database\RentalTermSheet.xlsx", "Sheet1", true)

    While Not MyDDTObject.EOF()

        If RowCount = 2 Then

            Log.Message RowCount

            Log.Message(MyDDTObject.Value(0))

            Log.Message(MyDDTObject.Value(1))

            Log.Message(MyDDTObject.Value(2))

        Else

            Log.Message(MyDDTObject.Value(0))

            Log.Message(MyDDTObject.Value(1))

            'Log.Warning "data not found"

            MyDDTObject.Next

            RowCount = RowCount + 1

        End If

    WEnd



    DDT.CloseDriver(MyDDTObject.Name)



    End Sub

  • mesg2anil's avatar
    mesg2anil
    Regular Contributor
    14 years ago
    Hi Margaret...



    The code which you sent is picking all the rows data and presenting... :(

    In the code you gave "If RowCount = 2 Then", it is not picking Row 2 data, it is giving all the rows data...



    In this mail at the top, the code which I gave is working as expected, but only suggestion I need there is, how to put all the data from excel into an array so that I can use, arraystring as data reference from excel to application.
  • Abramova's avatar
    Abramova
    Icon for Staff rankStaff
    14 years ago

    Hi Anil,


    I'm sorry, I did not understand your requirements before. The code that I posted actually checks whether the number of rows in the Excel sheet equals 2, and if it does, it retrieves all the data stored in the sheet.


    As for your initial question about an array, you do not need to create an array from script, you can create a DB Table variable that will provide access to data that is stored in your Excel file and use this variable in your test.

  • mesg2anil's avatar
    mesg2anil
    Regular Contributor
    14 years ago
    Hi Margaret...



    Please dont say sorry!!

    Thank you very much for the information... Useful info!! :)