How to get data from an xml to excel which have more than 65000 rows

[rrdamuluri]

Hi,
I have a large xml file which will result more than 65000 rows most likely 300000. And I have noticed data table in QTP have a limit of 65000 rows. Can some one tell me how to overcome this constraint and write all the xml results to an excel file.
Following is the logic i'm trying:

Code:

Const XMLDataFile = "C:\Users\damulur\Desktop\on_mov_programs-db_20101011_1.xml"
Set xmlDoc = CreateObject("Microsoft.XMLDOM")
xmlDoc.Async = False
xmlDoc.Load(XMLDataFile)

datatable.AddSheet("output").AddParameter "TMSID",""

prg ="on/programs/program"
Set prg1=xmlDoc.selectNodes(prg )
For i=0 to (prg1.length-1)
TMS=prg1.item(i).getAttribute("TMSId")
DataTable.Value("TMSID","output") =TMS
DataTable.SetCurrentRow(i+1)
Next
DataTable.ExportSheet "C:\Users\damulur\Desktop\1.xls","output"

[Saket]

Basically this is a limit in excel and so in QTP.
what you can do is once you get the row > 65000 then add a new sheet and start writing into it.
By the way , I can see that you are finally exporting it to a excel file, why dont you directly wirte into a excel sheet rather in datatable.

[rrdamuluri]

Saket,
Thanks for quick reply. I'm new to QTP. Can you pls tell me how to write data directly to excel file like adding parameter and then data values.

[Saket]

try searching this forum, I am sure this has been discussed earlier. Let me know for any confusion.