Ask a Question

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

muttu
Occasional Contributor

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

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.







2 REPLIES 2
AlexKaras
Community Hero

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"))

?
Regards,
  /Alex [Community Hero]
____
[Community Heroes] are not employed by SmartBear Software but
are just volunteers who have some experience with the tools by SmartBear Software
and a desire to help others. Posts made by [Community Heroes]
may differ from the official policies of SmartBear Software and should be treated
as the own private opinion of their authors and under no circumstances as an
official answer from SmartBear Software.
The [Community Hero] signature is used with permission by SmartBear Software.
https://community.smartbear.com/t5/custom/page/page-id/hall-of-fame
================================
AlexanderM
Staff


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)
-----

Alexander
Customer Care Manager
cancel
Showing results for 
Search instead for 
Did you mean: