Re: QTP - Importing DataTable from QC (3253 Views)
Reply
Advisor
Posts: 27
Registered: ‎03-10-2008
Message 1 of 23 (6,726 Views)

QTP - Importing DataTable from QC

I attached a MS Excel file in a folder of Quality Center and want to import this file in my QTP scripts running via Quality Center. When I run the scripts it gives a warning that "The DataTable.Import (file) operation failed. Invalid File". I get this error at line "DataTable.Import(dttable)".

Below is the code I am writing to import excel file as a DataTable.

Dim dttable
dttable = "\\[Quality Center] Subject\ePolicy Availability\EPolicyMasterFile.xls"
DataTable.Import(dttable)
DataTable.SetCurrentRow(1)
DI_FilePath=DataTable("DI_FilePath")

MsgBox DI_FilePath

Can anyone please suggest me in this? This is a normal excel file and file was not open while I was running the script. I have attached the screen shot of error.
Advisor
Posts: 13
Registered: ‎03-06-2008
Message 2 of 23 (6,726 Views)

Re: QTP - Importing DataTable from QC

When you import an entire table, the documentation states that "The imported table must match the test. The column names must match the parameters in the test, and the sheet names must match the action names."
This means that your imported spreadsheet must have a Global and an Action1 worksheet, even if you have no data in them. This also seems to imply that you must already have the column names set up in the test's data table. I myself, don't use this process very much. Instead I import individual worksheets as I need them. This example shows you how to do that.

Dim strSSPath, strWSName
strSSPath="L:\TSH\QA\QTPROTesting\Portal_Users.xls" 'This is the full path to the spreadsheet.
strWSName="PortalUsers" 'This is the actual worksheet.
strDSName=strWSName
DataTable.AddSheet strDTSName 'This command adds the datasheet to the datatable with no columns named
DataTable.ImportSheet strSSPathFile, strWSName, strDTSName 'This command loads the contents of the worksheet into the datatable.

Note you can also designate a path to load your resulting datasheet to after the test runs. In this case, with the code below it only loads the datasheet you designate.

Dim strResultsSSPath
strResultsSSPath="L:\TSH\QA\QTPROTesting\VerifyPortalAccessResults.xls"
DataTable.ExportSheet strResultsSSPath, strDSName
Advisor
Posts: 13
Registered: ‎03-06-2008
Message 3 of 23 (6,726 Views)

Re: QTP - Importing DataTable from QC

I also noticed that you were using a Quality Center path. I have tried that with no success. So you might want to try and load your spreadsheet or worksheet from a local drive or a shared network drive and ensure that works properly. That then proves that you syntax is correct.

If someone else has any information on importing a complete spreadsheet or a single worksheet from a spreadsheet stored in Quality Center, that information would also prove helpful to this thread.

Honored Contributor
Posts: 2,240
Registered: ‎12-12-2007
Message 4 of 23 (6,726 Views)

Re: QTP - Importing DataTable from QC

I posted a some code to the Mercury User Knowledge Base 2-3 years ago that downloaded a datatable from QC to the client and imported it to QTP, since it didn't work importing it directly from QC.

The User KB was never migrated to the HP site, but search for a discussion thread called "QTP accessing data files stored in TD" (dated 09/20/04). You will find the code there - but completely unformatted.
Jan Czajkowski

[Please do not contact me off line for receiving support. Use the forum!]
Advisor
Posts: 27
Registered: ‎03-10-2008
Message 5 of 23 (6,726 Views)

Re: QTP - Importing DataTable from QC

Jan, I tried searching your post - "QTP accessing data files stored in TD" but I did not get any. Can you please post this again in this threat, it will be useful for all.
Thanks,
Honored Contributor
Posts: 2,240
Registered: ‎12-12-2007
Message 6 of 23 (6,726 Views)

Re: QTP - Importing DataTable from QC

OK here are two of my key postings - I tried to at least add linefeeds:

"I had the same idea, and got it working. Here is an example of how it can be done:

'Get the client temp folder - create it if it does not exist
Set flib = CreateObject("Scripting.FileSystemObject")
If Not flib.FolderExists(flib.GetSpecialFolder(2)) Then
flib.CreateFolder(flib.GetSpecialFolder(2))
End If
tempfolder = flib.GetSpecialFolder(2)
'Get the folder where the data file is attached
Set tdc = TDUtil.TDConnection
set tm = tdc.TreeManager
set root = tm.TreeRoot("Subject")
set folder = root.FindChildNode("QTP")
Set fact = folder.Attachments
Set alist = fact.NewList("")
'Find the right attachment and download it to the temp folder
For Each att In alist
Set ext = att.AttachmentStorage
attname = att.DirectLink
If InStr(attname, "MyDataFile.xls") > 0 Then
ext.Load attname, True
'Copy the attachment to a file with the original filename
flib.CopyFile ext.ClientPath & "\" & attname, tempfolder & "\" & "MyDataFile.xls"
Exit For
End If
Next
DataTable.ImportSheet tempfolder & "\" & "MyDataFile.xls","global","MyAction [MyTest]"

Jan"
...

"Tanmay, The example I posted assumes that there is a folder named "QTP" directly under Subject to which the data files are stored as attachments. You should change it to what is relevant to your project.

GetSpecialFolder(2) will return the folder that the TMP environment variable refers to. If you can't create a temp folder, I guess the TMP environment variable is not defined on your system.

Daron, I see this as a temporary workaround until Mercury has fixed the bug of not being able to give a TD data path programmatically. Access is not an option in this case since users are storing data in Excel documents that they will update regularly themselves.

Also, the main reason why I want to store the data files in TD i that they should be easily accessible to everybody that can access TD, no matter what permissions they have on a particular server. I am curious as to whether this works when using an Access DB as well?

Jan"
Jan Czajkowski

[Please do not contact me off line for receiving support. Use the forum!]
Advisor
Posts: 13
Registered: ‎03-06-2008
Message 7 of 23 (6,726 Views)

Re: QTP - Importing DataTable from QC

Jan,

I noticed you were using the TDUtil object. Do you know if this will work as well with the QCUtil object? I know the TD objects are backwards compatible but I noticed that dkumar uses Quality Center and updating this to the new object might also be helpful.

If you're not sure or don't have the QA environment to work with, I will see if I can't modify it and if it works, let everyone know.

Dale
Honored Contributor
Posts: 2,240
Registered: ‎12-12-2007
Message 8 of 23 (6,726 Views)

Re: QTP - Importing DataTable from QC

You should be able to replace TDUtil by QCUtil without any further modifications, I believe.
Jan Czajkowski

[Please do not contact me off line for receiving support. Use the forum!]
Respected Contributor
Posts: 588
Registered: ‎01-16-2008
Message 9 of 23 (6,726 Views)

Re: QTP - Importing DataTable from QC

Hello dkumar,
try:
dttable = "[Quality Center] Subject\ePolicy Availability\EPolicyMasterFile.xls"

You don't need "\\" for the Path

Regards
Advisor
Posts: 27
Registered: ‎03-10-2008
Message 10 of 23 (6,726 Views)

Re: QTP - Importing DataTable from QC


I tried importing datatable from Quality Center without "\\" but this is also throwing error. I have done this many times in my previous projects but I don't know why this is not working now.

dttable = "[Quality Center] Subject\ePolicy Availability\EPolicyMasterFile.xls"

I am getting this error:

The DataTable.Import (file) operation failed. [Quality Center] Subject\ePolicy Availability\EPolicyMasterFile.xls path does not exist.

----------------------------

Also I tried to download the file from Quality Center to my C drive but this is also not working.

Dim fso, MyFile
Set fso = CreateObject("Scripting.FileSystemObject")
Set MyFile = fso.GetFile("[Quality Center] Subject\ePolicy Availability\flight32.mdb")

I am getting this error:

File not found
Honored Contributor
Posts: 2,240
Registered: ‎12-12-2007
Message 11 of 23 (6,723 Views)

Re: QTP - Importing DataTable from QC

Did you try it the way I suggested?
Jan Czajkowski

[Please do not contact me off line for receiving support. Use the forum!]
Advisor
Posts: 27
Registered: ‎03-10-2008
Message 12 of 23 (6,723 Views)

Re: QTP - Importing DataTable from QC

Jan, The method you suggested is working perfect. I was just trying if I can do this in one line.

Thanks,
Deepak
Advisor
Posts: 13
Registered: ‎03-06-2008
Message 13 of 23 (6,723 Views)

Re: QTP - Importing DataTable from QC

dkumar

The way you are trying to load the data table will not work. The Import and import sheet method will not import from Quality Center. This is documented in the help files I believe.

What you can do is use the Run Settings to designate where your data table is located within Quality Center. In your case, if you have the spreadsheet loaded as an attachment in your Quality Center project, then you can go to File > Settings > Resources. Within the Resources tab of Test Settings you will see the Data Table settings. The radio button for Default Location (under test directory) is probably checked. If you check the Other Location: radio button you will receive a warning that the sheet names and parameter names must match the values in your test or your test will fail. After you click on the OK button, you will be able to browse to a location within Quality Center where your excel spreadsheet resides.

You will then notice that the data table is now in your test within Quick Test Pro at Design Time not Run Time. If this is not what you want, then you're only option to do what you have described is to use Jan's solution because you CAN NOT use the ImportSheet or Import method and pull the datatable from Quality Center.

Hope this helps and good luck.
Regular Advisor
Posts: 117
Registered: ‎04-17-2008
Message 14 of 23 (6,723 Views)

Re: QTP - Importing DataTable from QC

Hi Jan,

code you have given above is working fine, but it doesn't work when I try to run my script,stored in QC, on the other system.

it doesn't identifies the path or it is not able to create file in temp folder.

even, if I delete the file from the Temp folder in the local system it throws some error.

"The retrieve DataTable.Value operation failed. The column does not exist."

Line (34): "user_name = DataTable.Value("User_Name",Global)".

then I checked whether it is searching the file in QC or not I found at that time code which located the file is being bypass,

" For Each att In alist
Set ext = att.AttachmentStorage
attname = att.DirectLink
If InStr(attname, "Login.xls") > 0 Then
ext.Load attname, True
"


Honored Contributor
Posts: 2,240
Registered: ‎12-12-2007
Message 15 of 23 (6,723 Views)

Re: QTP - Importing DataTable from QC

If the code iterating through the attachment is bypassed, that means there are no attachment to the current object. My guess is either your path is wrong or the object is not of the expected type.
Jan Czajkowski

[Please do not contact me off line for receiving support. Use the forum!]
Regular Advisor
Posts: 117
Registered: ‎04-17-2008
Message 16 of 23 (6,723 Views)

Re: QTP - Importing DataTable from QC

I am executing the same script...

if I delete the "Login.xls" file , it doesn't import it to local system again...

and on other system it is not at all working..

I have to execute the code explicitly...
Honored Contributor
Posts: 2,240
Registered: ‎12-12-2007
Message 17 of 23 (6,723 Views)

Re: QTP - Importing DataTable from QC

I don't understand what you are saying.

"if I delete the "Login.xls" file"

Does that mean you delete the copy from the temporary folder?

"it doesn't import it to local system again..."

Do you mean the temp copy is not created?

"and on other system it is not at all working.. "

Is the "other system" another PC with QTP installed? What is the difference in the behavior compared to what you call "local system"?

"I have to execute the code explicitly..."

What does that mean?
Jan Czajkowski

[Please do not contact me off line for receiving support. Use the forum!]
Regular Advisor
Posts: 117
Registered: ‎04-17-2008
Message 18 of 23 (6,723 Views)

Re: QTP - Importing DataTable from QC



"Does that mean you delete the copy from the temporary folder?"


yes, if I delete the "Login.xls" file in "Temp" folder from my system(Local)and then, if try to execute the script it doesn't create temp copy again.

"other system" is another system with QTP installed. It gives the error mentioned below

"The retrieve DataTable.Value operation failed. The column does not exist."

Line (34): "user_name = DataTable.Value("User_Name",Global)".


I have embedded this code to my Login script.


"I have to execute the code explicitly..."

means, I have to execute the code given by you individually

Honored Contributor
Posts: 2,240
Registered: ‎12-12-2007
Message 19 of 23 (6,723 Views)

Re: QTP - Importing DataTable from QC

If you say the script code works when executed separately, my guess is there is something else in your login script that prevents things.

What I would do is check that all objects are freed between executions, make sure that no process is locking the file copy, that all clients have write permissions to the temp folder.

On your other machine, check that you can access QC from QTP manually. Check that the DataTable object is really available. Comment out any On Error Resume Next statements to catch the problem at the root or use the debugger.

Jan Czajkowski

[Please do not contact me off line for receiving support. Use the forum!]
Occasional Visitor
Posts: 1
Registered: ‎11-26-2012
Message 20 of 23 (6,269 Views)

Re: QTP - Importing DataTable from QC

In any case somebody else encounters this error, you may want to check with the "limitations" of QTP's datatable (i.e. max characters allowed in a cell = 255).  It took 30 minutes of my life to resolve this.

Occasional Visitor
Posts: 1
Registered: ‎08-02-2013
Message 21 of 23 (4,807 Views)

Re: QTP - Importing DataTable from QC

Hi,

 

I am trying to import test data from excel to QTP 11 for automation of SAP test scripts. Below is the code. I have done the Record and Run settings in Automation tab to login to SAP ECC system.

 

'Add Sheet
Datatable.AddSheet "Sheet1"
'Import Sheet
Datatable.ImportSheet "C:\MRRPinput sheet.xls","Sheet1","Sheet1"
'Read and Write
Rowcount = Datatable.GetSheet("Sheet1").GetRowCount

 

when i run this code it is failing at Import sheet. I get an error "the datatable.importsheet operation failed. invalid file". I have already checked the path and the sheet name. Can please some one help me resolve this problem.

Occasional Visitor
Posts: 1
Registered: ‎05-12-2014
Message 22 of 23 (3,491 Views)

Re: QTP - Importing DataTable from QC

Please let me know if you found any solution for the problem mentioned.

 

Thanks,

Mamata

Collector
Posts: 2
Registered: ‎10-20-2011
Message 23 of 23 (3,253 Views)

Re: QTP - Importing DataTable from QC

Please try this solution. It works for me.

1. In the [Test setting]  dialog, Resource pane, Data table, try to select a test data file from QC/ALM. The path will be displayed like in the attached file.

2. Copy the path.

3.  Cancel that dialog.

4. In the very first action, please use this command

'paste the path from the step #2.

 DataTable.Import "[ALM\Resources] Resources\XXX\QTP\Test Data\Home\20140624_CreateCase".

 

Good luck

-------------------
Jason Truong
(Not only quality)
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.