This site requires JavaScript to be enabled
IE BUMPER

Logo

Dear Tricentis qTest users,

If you would like to submit a Tricentis qTest ticket,

please use this support request form.

Tosca Version
IncidentLookup using list
Language
How can we help?

Solution Suggestions

Please enter your question to get suggestions.
  Help
IE BUMPER
Categories
Boolean Operators
OR or vertical bar symbol (|)
Finds a match if either the terms exist in a document (a union using sets).
AND
Finds a match if both terms exist in a document (an intersection of sets).
NOT, minus (-), or exclamation point (!)
Excludes documents that contain the term after NOT (a difference of sets).
Wildcards
Asterisk (*)
The asterisk symbol performs a multi character wildcard search.
Percent sign (%)
The percent sign performs a single character wildcard search.
Phrases
Quotation marks (")
Use quotation marks to search for an exact phrase with multiple terms.
For examples and more information look at our Knowledge Base: Advanced Search On The Support Portal

Exporting table/sql result to an Excel/csv

The Problem

If you want to save the result of a sql query or a table in an excel file via Tosca, there is limited approach to do it. You may have to loop through the records and save them in an excel which will take a long time.

 

A Solution

Using PowerShell and Tosca, you can do it in a seconds. You can save the result to your local machine as well as a shared drive.

 

An Example

 

Use the “TBox start program” from Tosca to start the PowerShell. (C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe)

 

Enter the query as an argument and run.

 

$Connection = 'Driver={SQL Server Native Client 11.0};Server=Your_Server;Database=Your_Database;Uid=YourUsername;Pwd=YourPassword;'

 

Send-SQLDataToExcel -Connection $Connection -SQL  "SELECT TOP 10 *  FROM Your_TableName" -Path 'C:\Temp.xlsx' -WorkSheetname Your_WorkSheetName

  

 

Note:

·         Make sure to install “ImportExcel” module before trying the solution. ( If you are an admin ignore the parameters “-Scope CurrentUser” )

 

Install-Module ImportExcel -Scope CurrentUser

 

·         You can also use Windows authentication instead of enter username and password. Refer here for the complete list.

 

·         Similar to this, you can also save your result as “.csv” using the module  ConvertTo-Csv.

Attachments
0 0

Abhilash Tomar

Created: 2019-04-01 12:27:25

Hi

could you provide how to install "ImportExcel"

it will be great you share the screen shot of these task implementation.

 

 

Pugazhvanan Ganapathy

2019-04-23 19:27:10

Hi ABHILASH,

It is in the note section. Install-Module ImportExcel is used to install the ImportExcel module. I will definitely provide the screenshots soon. Thank you!

Thanks,

Pugal

0 0

Odugu Reddy

Created: 2019-06-18 16:09:53

Hai Ganapathy,

 

Thanks for the article, could you please help me i was not able to proceed with the steps provided. after running the first command in the powershell. i couldn't see anything status. 

Regards,

LatheeshReddy

Pugazhvanan Ganapathy

2019-06-18 14:44:06

Hello Reddy,

You can try to see if your PowerShell script throwing an error. Go through the example 4 in this article https://support.tricentis.com/community/manuals_detail.do?lang=en&version=11.2.0&url=engines_3.0/automation_tools/process_operations/process_operations.htm

 

Thank you,

PG

0 0

Odugu Reddy

Created: 2019-06-21 18:39:51

Hi Ganapathy,

Thank you for the reply,

I am trying first through manually, as you mentioned using Tbox start program it's working like opening powershell window. but in order through pass the connection details and the import data into excel approach. is there any detailed steps if your provided that would be helpful. 

Beacuse we have 500+ scenarios need to take the whole data of the database into the excel. execution time is too high and it's not acceptable every where.

if you suggest or call that would be helpful.

 

Regards,

Odudu Reddy

+91-9008561987

0 0

Pugazhvanan Ganapathy

Created: 2019-06-28 20:16:56

Attaching a sample subset. Hope this helps. 

Attachments

Latheesh Reddy

2019-07-02 16:53:01

Thanks Ganapathy for sharing subset.

my case here i need to connect oracle database. is this the right format to provide the connection

$Connection = "Driver={Oracle in InstantClient32};Server=iecwxuvtodb505.primark.local;Database=MOMUATP1.primark.local;Uid=RMS_READ_ONLY;Pwd=***;"

After running the statement i was getting some problem saying datasource not found.

0 0

Pugazhvanan Ganapathy

Created: 2019-07-02 20:48:59

C:\>Send-SQLDataToExcel -path .\demo3.xlsx -WorkSheetname "LR" -Connection "DSN=LR" -sql "SELECT name AS CollectionName FROM AgLibraryCollection 
    Collection ORDER BY CollectionName"
    
    This example uses an Existing ODBC data source name "LR" which maps to an adobe lightroom database and gets a list of collection names into a worksheet. Apply the same concept and create an ODBC connection(32bit) for your Oracle database.

0 0

Shaik Gayasuddin

Created: 2019-08-01 09:33:42 , Updates: 2, Last Update: 2019-08-06 08:19:27

Hi Ganapathy,

Thank you for the details:

Requesting to clarify on the below:

- Powershell can be ran only with the admin rights and due to organization norms we cannot have access to powershell as admin rights is not available

- You have mentioned in one of the comments " Install-Module ImportExcel is used to install the ImportExcel module". Can you please provide any screenshot or any reference as we are unable to understand what you mean here

- Without power is there any way to extract the results table into excel or csv or any other format

0 0

Vinay Parashar

Created: 2019-09-06 14:52:31

Hello PUGAZHVANAN GANAPATHY,

 

I am not clear with the Note given in the description.can you please clarify and elaborate on that.

Regards,

Vinay

IE BUMPER

Tosca is the perfect solution

Optimize - Manage - Automate

Download Trial