How to fetch data with ODBC DSN connections using Powershell

[NadiaB]

This article describes basic steps on how to test a connection with ODBC DSN in Powershell.

Also see How to fetch data via ODBC DSN without using Qlik Products

Environment:

• QlikView
• Qlik Sense Enterprise on Windows
• Microsoft Windows Server 2012 and above

Steps:

1. Configure a DSN connection using the ODBC Data Source Administrator
2. Open PowerShell  ISE
3. Use the following code and change values accordingly
   

   Get-Date
   $dnsName= "DSNNAME"
   $user="USERID"
   $password="PASSWORD"
   $csvPath= "C:\<path>\<filename>.csv"
   $sqlQuery= "select * from DB.dbo.Table"
   
   $conn = New-Object Data.Odbc.OdbcConnection
   $conn.ConnectionString= "dsn=$dnsName;uid=$user;pwd=$password;"#"dns=$dnsName;uid=sa;pwd=Password123!;"
   $conn.open()
   $command =$conn.CreateCommand();
   $command.CommandText=$sqlQuery
   
   $dataAdapter = New-Object System.Data.Odbc.OdbcDataAdapter $command
   
   $dataTable = new-object "System.Data.DataTable"
   $dataAdapter.Fill($dataTable)
   $conn.close()
   
   $dataTable | Export-csv -Path $csvPath -NoTypeInformation
   Get-Date
   Where:
   DSNNAME is the name was configured in the ODBC Data Source Administrator tool
   USERID is the user id will be use to connect to the database
   PASSWORD is the password for the provided user id

   
   
4.  Click the "Run Script" button or press F5, verify the execution was completed successfully and open the path where the file was generated.
   
   
   
   Use as reference both dates to verify how long it took to complete the query from the server/computer from where are you testing.