Re: QTP Data table export causes the size of MS excel file to shoot up into MBs (791 Views)
Reply
Occasional Advisor
josmathew
Posts: 9
Registered: ‎04-02-2010
Message 1 of 9 (1,809 Views)

QTP Data table export causes the size of MS excel file to shoot up into MBs

An excel file is where my test data is stored. When prepared for the first time it was 44KB with 4 work sheets. Each of these work sheets are then imported to QTP during run time and some values are added to it. Once the data table is exported back to the sheet, the file size becomes 3-6 MB and can go up to 20-30 MB. the data added to the sheet is only into 27 cells. Its MS Excel 2007 and the file is stored with the extension ".xls"

 

Any idea why this happens?

If not at least any solution to avoid/ overcome this?

 

The problem faced is in case i need to open the same excel sheet using COM object then the time taken to open the work book is 10 times more than the time required to do validations/ other operations.

 

 

Occasional Advisor
Akash_1
Posts: 6
Registered: ‎10-05-2010
Message 2 of 9 (1,809 Views)

Re: QTP Data table export causes the size of MS excel file to shoot up into MBs

Dude use the .xlsx file extension for storing th excel files when using excel 2007 ... hopefullly it will solve the issue .... even we faced the same issue ... probably this trick resolved the issue .. lte me know if it works for you .

Occasional Advisor
josmathew
Posts: 9
Registered: ‎04-02-2010
Message 3 of 9 (1,809 Views)

Re: QTP Data table export causes the size of MS excel file to shoot up into MBs

I'm using QTP 10. Now is it to understand that datatable.importsheet method recognizes ".xlsx" files? I tried but it didn't work for me. Am I missing something here?

Occasional Advisor
Akash_1
Posts: 6
Registered: ‎10-05-2010
Message 4 of 9 (1,809 Views)

Re: QTP Data table export causes the size of MS excel file to shoot up into MBs

Ok try this when you import the sheet use .xls extension(as what i remmember datatables don't support .xlsx format)  and while exporting the result save it with extension .xlsx ... in this case u will have 2 files one for input in .xls and output file will be in .xlsx format ...u can name them differently .

 

Advisor
Lmac
Posts: 14
Registered: ‎06-28-2011
Message 5 of 9 (1,795 Views)

Re: QTP Data table export causes the size of MS excel file to shoot up into MBs

Try using ExportSheet instead of Export. It might solve your problem.

 

Otherwise export it as .CSV file.

Occasional Visitor
Carlin_Scott
Posts: 1
Registered: ‎02-09-2012
Message 6 of 9 (1,634 Views)

Re: QTP Data table export causes the size of MS excel file to shoot up into MBs

The problem isn't with exporting the DataTable. It's the import that's an issue. ImportSheet can on ocassion, introduce many empty cells into the DataTable. Import() doesn't do this. You will be limited to importing one workbook at a time however.

Advisor
pradeepsingh999
Posts: 35
Registered: ‎12-10-2012
Message 7 of 9 (887 Views)

Re: QTP Data table export causes the size of MS excel file to shoot up into MBs

Hi,

I am facing the same issue. If you have found any solution or workaround plz share with me.

 

Thanks & Regards

Pradeep Singh

Occasional Visitor
boboftherockies
Posts: 1
Registered: ‎09-05-2012
Message 8 of 9 (798 Views)

Re: QTP Data table export causes the size of MS excel file to shoot up into MBs

Hello Everyone,

 

I am facing the same issue when using exporting the datasheet using Datatable.Export method. The excel size is always large but not when I use the Datatable.Export method. Should I be raising a ticket with HP?

Advisor
pradeepsingh999
Posts: 35
Registered: ‎12-10-2012
Message 9 of 9 (791 Views)

Re: QTP Data table export causes the size of MS excel file to shoot up into MBs

[ Edited ]

Hi to All,

 

I have already faced this problem but later got a solution for this. Acutally what was happening is if excel sheet which is about to import into datatable  should not have any formatting (any type of excel cell formatting like color,size,font etc) because QTP datatable does not understand/handle these excel formatting and due to this reason when you export the datatable , size of exported excel file shoot up into MBs.  

 

So solution which helped me is before importing the excel sheet into datatable , please remove all formatting.

 

Correct me if I am wrong......

 

Thanks & Regards

Pradeep Singh 

The opinions expressed above are the personal opinions of the authors, not of HP. By using this site, you accept the Terms of Use and Rules of Participation.