Exporting DB result data to Excel sheet in QTP

[Vishnu V.Potti]

Hi All,

I want to know the way to export DB data to excel sheet in Qtp. There
were multiple rows in DB results.

Plz check the below piece of code I wrote and correct.

I am having 93 columns in a row. Thatswhy I used loop to write column
values to sheet instead of assigning to rs field name and then
writing.

Set rs = conn1.Execute(query,,adExecuteNoRecords)
colcount= rs.fields.count
Set ex=Createobject("Excel.Application")
Set a=ex.workbooks.open("C:\DB_data.xls")
Set b=a.worksheets("sheet1")
j=1
Do While Not rs.EOF
For i=0 to colcount-1
rval=rs.fields(i).name <!-- Until this step everything is perfect -->
b.cells (j,i).value=rs.fields(i).name.("")
rval=""
next
rs.moveNext
j=j+1
loop
a.save
a.close

[Shirish K]

Hi,

Once you connected to DB and executed your query then redirect the results in to .csv file

using file system object create file as abc.csv 

write result to file

save file.

No need to create the excel file.

.csv you can then directly import in QTP using datatable.import....

-Shirish

--
You received this message because you are subscribed to the Google
"QTP - HP Quick Test Professional - Automated Software Testing"
group.
To post to this group, send email to Mercu...@googlegroups.com
To unsubscribe from this group, send email to
MercuryQTP+...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/MercuryQTP?hl=en

[Vishnu V.Potti]

Hi Shirish,Thanks but actually the problem I am facing now is writing
all the column data one by one for all rows to the sheet. Be it be
datatable / CSV. The code for writing all the columns is failing some
where in the loop.

On Mar 13, 11:56 am, Shirish K <shirish1...@gmail.com> wrote:
> Hi,
>
> Once you connected to DB and executed your query then redirect the results
> in to .csv file
> using file system object create file as abc.csv
> write result to file
> save file.
> No need to create the excel file.
>
> .csv you can then directly import in QTP using datatable.import....
>
> -Shirish
>

> > MercuryQTP+...@googlegroups.com<MercuryQTP%2Bunsu...@googlegroups.com>

[abhishek awale]

Hi Vishnu,

Please look at the code below , i have changed your code just a bit and it's working fine now.

Hope this helps!!!

Set rs = conn1.Execute(query,,adExecuteNoRecords)

        rowcount = rs.RecordCount

colcount= rs.fields.count

Set ex=Createobject("Excel.Application")

Set a=ex.workbooks.Add

a.Activate

Set b=a.worksheets("sheet1")

For k = 1 to colcount ' printing the column names and asigning the row a different color

b.Cells(1,k).Interior.ColorIndex = 3

b.cells (1,k).value = rs.fields(k).name

Next

j=2 ' the column names are printed in the first row thats why started the data population from second row.

Do While Not rs.EOF

For i=1 to colcount

rval=rs.fields(i).name 

b.cells (j,i).value=rs.fields(""& rval &"")

rval=""

next

rs.moveNext

j=j+1

loop

a.SaveAs("C:\DB_data.xls")

a.Deactivate

a.close

Please let me know if you have any querries.  :)

-- 
Regards,

Abhishek Awale
Quality Assurance Engineer,
Impetus Infotech India Pvt Ltd,
[ M ] +91 982 768 5004 [ E ] abhishe...@gmail.com, abhi_...@yahoo.co.in [ W ] www.impetus.com

[Vishnu V Potti]

Hi Abhishek,
                   Thanks, the code worked properly. Although there was some error being thrown on last iteration of every loop ( Error looked like "Item description not found for collection"), I was able to export all the rows to Excel sheet.. Thank you once again. :)

--
REGARDS
VISHNU V POTTI

[KANDUKURI NARESH]

Hi All,

 

 

I think, we can directly save the recordset to an excel sheet.

Please check for the corresponding method in the www.w3schools.com

Thanks and Regards,
Naresh Kandukuri...
Catch me @ 9989299857

[ARIE...@aol.com]

I suspect there was a problem in how the loops were configured:

For k = 1 to colcount   vs.  For i=0 to colcount-1

> > I am having 93 columns in a row. That's why I used loop to write column

-
REGARDS
VISHNU V POTTI

Naresh Kandukuri...
Catch me @ 9989299857

 

--