How to retrieve data from excel sheet by checking column name and its value & next column and its value(without using keyword view) scripting only

This is the question asked by visitor Nags

Hi,

Import the excel sheet to your datatable. Then use a for loop like
row_cnt=datatable.getrowcount
for i=1 to row_cnt
datatable.getsheet(i)
a=datatable.value("header",1)
b=datatable.value("header1",1)
.
.
datatable.setnextrow
Next

You can simply access the excell sheet (not import) by using the script. See bellow example:

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.WorkBooks.Open("path of yourfile.xls")
Set objDriverSheet = objWorkbook.Worksheets("name of the sheet")

then just acces the cell itself :
row = 1
objDriverSheet.Cells(row,"A").Value

I used just default column names by A,B,C,......

Hope that helps,

I am using the For Loop to get data from the data table. The summary code is:
C=datatable.getrowcount
For i=1 to c
msgbox Datatable.Value("Column1")
Next
'The message box always returns data of row 1. Irrespective of the iteration. How is it possible.

the reason may be cos you r not updating the current row value :
May be this will give you some idea:

i=1
RowCount = DataTable.GetSheet("Global").GetRowCount
CurrentRow = DataTable.GetSheet("Global").GetCurrentRow
For j =1 to RowCount
...
Do whatever you want to do with that value here
...
DataTable.SetCurrentRow (i) 'set the current row to the next one
Next


Thanks,
Kunal.

Please check the below script for reading/Accessing data from excell sheet.

n = DataTable.getSheet("Action1").GetRowCount

For i = 1 to n dataTable.set currentrow(i)

x = dataTable.value("data", 1)
if x = "1" Then
msgbox x
else
msgbox " not a valid data"
end if

Next.

Please check the below script for reading/Accessing data from excell sheet.

n = DataTable.getSheet("Action1").GetRowCount

For i = 1 to n dataTable.set currentrow(i)

x = dataTable.value("data", 1)
if x = "1" Then
msgbox x
else
msgbox " not a valid data"
end if

Next.

Thansk for usefull info

here we can use two ways of coding.
1st method:
datatable.importsheet "path of the excel file.xls",source sheetID,desination sheetID
n = datatable.getsheet("desination sheetname").getrowcount
for i = 1 to n
columnname = datatable.getsheet("destination sheetname").getparameter(i).name
if colunmname = knowncolumnname then
value = datatable.getsheet(destinationsheetname).getparameter(i)
end if
next

2nd method:
set objexcel = createobject("excel.application")
Set objWorkbook = objExcel.WorkBooks.Open("path of the file.xls")
Set objDriverSheet = objWorkbook.Worksheets("name of the sheet")
columncount = objDriverSheet.usedrange.columns.count
rowcount = objDriverSheet.usedrange.rows.count
for i = 1 to colunmcount
columnname = objDriversheet.cells(i,1)
if columnname = knowncolumnname then
for j = 1 to rowcount
fieldvalue = objdriversheet.cells(j,i)
next
end if
next

Thanks
Bhaskar kumar

Hello Bhaskar,

I used your 2nd method to read data from excel sheet. Can you please tell me how can I create an array to continously loop through excel sheet and read ONLY required values.

Shouldn't I use an array inside the for loop or outside? Please help.

Thanks much.

Hi Bhaskar,
I used your 2nd method, but I'm not able to get the column count and row count of the used range of the excel sheet. Could you please help. I'll really appreciate it. Thanks.

Below is my code:
Set objExcelObj = CreateObject("Excel.Application")
objExcelObj.Visible = True
Set objWorkbook = objExcelObj.Workbooks.Open(strParticipantDataSetupFile)

Set objParticipantSheet = objWorkbook.Worksheets(strSheetName)
columncount = objParticipantSheet.usedrange.columns.count
rowcount = objParticipantSheet.usedrange.rows.count

For i = 1 to columncount
columnname = objParticipantSheet.cells(i,1)
If columnname = "PlatformName_vc" Then
For j = 1 to rowcount
fieldvalue = objParticipantSheet.cells(j,i)
Next
End If
Next

Hi ,
I have imported the file & tried with your code.
Suppose there are 3 records in my file & I want to retrieve the values from column 'Name'.
But I am getting the required output 3 times since there are 3 records. I want the required O/P only once. Could u pls guide me on this? Below is my code.

DataTable.Import "C:\1.xls"
row_cnt=datatable.getrowcount
for i=1 to row_cnt
datatable.getsheet(1)
a=datatable.value("DbTable_Row_1_Col_1_out",1)
'b=datatable.value("header1",1)

msgbox a
datatable.setnextrow
Next

Hi All,

Just try below code , hope this will help you

''opens excel file
Set xlApp = GetObject("","Excel.Application")
xlApp.visible = true
Set xlWrkbk = xlApp.Workbooks.Open("abc.xls")
Set xlWrksht = xlWrkbk.Worksheets("Data") ' Data is the name of the sheet
intStartRow = 2 ' Row from whcih you need to start

For intRow = intStartRow to xlWrksht.UsedRange.Rows.Count

strAccountNumber = Trim(xlWrksht.Range("A" & intRow))
strAccuntName = Trim(xlWrksht.Range("B" & intRow))
dtDate = Trim(xlWrksht.Range("C" & intRow))

Next