HP Run Results viewer message ”Can’t create data table” in QTP.

  • Questions
  • HP Run Results viewer message ''Can't create data table'' in QTP.
Question ID: 104655
2
0

Just ran QTP test with no problem and the HP run results viewer displays "Can’t create data table". Does anybody know what is wrong

Marked as spam
Posted by (Questions: 45, Answers: 0)
Asked on October 19, 2012 5:37 am
31 views
Answers (2)
7
Private answer

The datatable is corrupted. You might be able to run the test but when attempting to display the results the data the view can not handle the corruption. Cleaning or re creating data table is best answer.

Corruption can happen when users update external datatables with tools like MS Excel or open office and they put extra data or formatting into the datatable. HP has release document following document on how to clean formats.

Here is HP document on this issue:
*KM324314 'How to clean up all Excel formatting in XLS file using Microsoft Excel'*

I have found that creating a new table has been the best answer. Here is a QTP Script I use to clear Datatables. One note make sure the test is set to run one iteration.


CleanFormat ''Z:CleanDataTableExample.xls'' 'This creates a ''new.xls'' on same folder

Sub CleanFormat(byval sourceFile)
	Set FSO = CreateObject(''Scripting.FileSystemObject'')
	If FSO.FileExists(sourceFile) Then    
		Set EXCEL = CreateObject(''Excel.Application'')
		EXCEL.Workbooks.Open(sourceFile)
		const xlText = -4158
		CreateBackup = false
		set Sheets = EXCEL.ActiveWorkbook.Sheets
		myCnt=Sheets.count
		reDim myArray(myCnt,2)
		
		i=0
		For Each sht In Sheets
			i=i+1  
			Sheets(sht.Name).Select
			myFileName=FSO.GetParentFolderName(sourceFile) & '''' & i & ''_'' & sht.Name & ''.txt''
			mySheetName=sht.Name
			myArray(i-1,0)=myFileName
			myArray(i-1,1)=mySheetName
			EXCEL.ActiveWorkbook.SaveAs myFileName, xlText,,,,CreateBackup
		Next
		EXCEL.DisplayAlerts = False
		EXCEL.Workbooks.Close
		EXCEL.Quit
		DataTable.DeleteSheet ''Action1''
		
		For x = 1 to i
			path2=Replace(FSO.GetParentFolderName(sourceFile), FSO.GetDriveName(sourceFile), '''') & ''''
			Set colFiles = GetObject(''winmgmts:{impersonationLevel=impersonate}!\.rootcimv2'').ExecQuery(''Select * from CIM_DataFile where Path ='''  & replace(path2,'''',''\'') &  ''' and drive = '''& FSO.GetDriveName(sourceFile) & ''' and extension='txt' and filename LIKE ''' & x & ''[_]_%''')
			sheetfile = ''''
			myFileName=myArray(x-1,0)
			mySheetName=myArray(x-1,1)
			If x>1  Then
				DataTable.AddSheet mySheetName
			End If
			 DataTable.ImportSheet myFileName, 1, mySheetName
			 FSO.DeleteFile myFileName,True
		Next
		DataTable.Export FSO.GetParentFolderName(sourceFile) & ''new.xls''
	End If
	Set FSO = nothing
End Sub

Marked as spam
Posted by (Questions: 2, Answers: 98)
Answered on October 19, 2012 1:08 pm
1
Private answer

What's your test doing with the datatable? Are you importing an Excel spreadsheet? If so, and it has any special formatting, that can cause problems. If that's happening, you can try using the following code *before* importing the sheet. It reads in your file (strFile), strips all formatting, then writes out a new file (strNewFile) with no formatting:

strFile = *Full path to file including file name*

strNewFile = *Full path to new, unformatted file that will be written*

Dim oExcel

Dim i

Set oExcel = CreateObject(''Excel.Application'')

oExcel.Workbooks.Open strFile

For i = 1 to oExcel.ActiveWorkbook.WorkSheets.Count

oExcel.ActiveWorkbook.WorkSheets.Item(i).Cells.ClearFormats

Next

oExcel.Workbooks.Item(1).SaveAs strNewFile

oExcel.WorkBooks.Item(1).Close

oExcel.Quit

I hope that helps.

Marked as spam
Posted by (Questions: 16, Answers: 749)
Answered on October 19, 2012 9:41 am
This procedure will work for some issues but not all. The only way I have found to clear all issue is to export each sheet to text and re import it.
( at October 19, 2012 1:09 pm)