Requirement to read data from the excel tables with multiple rows.

[muttu]

We are using below functions to read data from excel & store it in dictionary objects as key-value pair.

It works for the data in Table1(attached file Table) , we get the resulst as "David"

But it does not work for Table2(attached file Table2 ).For Table2 ,it displays error "Key is already associated with an element in this collection" Can someone help? 

Dim sAutomationPath

sAutomationPath

sAutomationPath = "d:\TC Sample Projects\Excel_MultipleRows\"

'Fetching values from excel tables

Function ConnectDB

ConnectDB

Set AConnection = ADO.CreateADOConnection

Dim sConnectionString

sConnectionString = _

"Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};" & _

"DriverId=790;" & _

"Dbq=" + sAutomationPath + "myFile.xlsx;" & _

"DefaultDir=" + sAutomationPath

AConnection.ConnectionString = sConnectionString

AConnection.LoginPrompt = False

AConnection.Open

Set ConnectDB = AConnection

End Function

'********************************************************************

Function FetchDB (sTableName, sSheetName)

FetchDB (sTableName, sSheetName)

Dim iIndex

'Get ADO Connection Object

Set AConnection = ConnectDB ()

Set Driver = DDT.ExcelDriver(sAutomationPath + "myFile.xlsx", sSheetName,true)

Set RecSet = AConnection.Execute_("SELECT * FROM "+ sTableName)

Set objDict = CreateObject("Scripting.Dictionary")

RecSet.MoveFirst

While Not RecSet.EOF

for iIndex = 0 to Driver.ColumnCount-1

objDict.Add (Driver.ColumnName(iIndex)), RecSet.Fields(Driver.ColumnName(iIndex)).Value

Next

RecSet.MoveNext

WEnd

Call DDT.CloseDriver(Driver.Name)

Set FetchDB = objDict

End Function

'********************************************************************

Sub Test1

Test1

 Set dictObj = FetchDB ("Table1","Sheet1")

Log.message (dictObj.item("EmpName"))

.message (dictObj.item("EmpName"))

End Sub



Result1:

David



 

Sub Test2

 Set dictObj = FetchDB ("Table2","Sheet1")

Log.message (dictObj.item("EmpName"))

.message (dictObj.item("EmpName"))

End Sub

Result2:

Error_Test2 file attached







Excel table: screenshot of the excek table data is attached.

[AlexKaras]

Hi Mruttunjay,



The problem is in the

  While Not RecSet.EOF

    for iIndex = 0 to Driver.ColumnCount-1

      objDict.Add (Driver.ColumnName(iIndex)), RecSet.Fields(Driver.ColumnName(iIndex)).Value

    Next

    RecSet.MoveNext

  WEnd

code snippet.



What it does is creation of the 'column name - column value' pairs.

I.e. for the Table1 the dictionary structure will be like this:

Key          Value

----           -----

TCID        TC1

EmpName David



which is fine.



Hovewer, for the Table2 the dictionary structure will be like this:

Key          Value

----           -----

TCID        TC1

EmpName David

TCID        TC2

EmpName Adam



which is incorrect because duplication of the key values is not allowed for the dictionary.



Maybe, instead of the mentioned code snippet you need just this one:

  While Not RecSet.EOF

    objDict.Add RecSet.Fields(Driver.ColumnName(0)).Value, RecSet.Fields(Driver.ColumnName(1)).Value

    RecSet.MoveNext

  WEnd



with the

  Log.message (dictObj.item("EmpName"))

line in Test2 replaced with

  Log.message (dictObj.item("TC2"))

?

[AlexanderM]

Hi Mruttunjay,





You get this error due to an attempt to add a duplicate key.





To prevent it, you can change the line with "Set RecSet..." so that it looks like this:





Set RecSet = AConnection.Execute("SELECT DISTINCT FROM "+ sTableName)