How to fetch data via ODBC DSN without using Qlik Products

[Andre_Sostizzo]

When having any issue fetching data from Data Source to Qlik Products(QlikView or Qlik Sense) via ODBC DSN, it is crucial to determine whether it is general or Qlik specific.

In order to investigate, the best way is to establish connection and fetch data from a 3rd party tool. Chances are:

• If a 3rd party tool cannot fetch data either, or gets the same result as Qlik Products, then the issue falls outside of Qlik Products.
• If a 3rd party tool can fetch data but Qlik Products cannot, or gets different result, then the issue falls inside Qlik Products.

Note this article is for testing ODBC connection. Also see related How to fetch data with ODBC DSN connections using Powershell

For testing OLE DB Connection String, please refer to article How to externally test an OLEDB Connection String using Windows PowerShell.

Environment:

• QlikView, all versions
• Qlik Sense, all versions

Prerequisite:

DSN must be already created and tested.

Then use one of the two options below.
 

Using the "DTM ODBC SQL runner" tool:

• Download the zip file from this page:http://www.sqledit.com/odbc/runner.html
• Unzip the file to a folder.
• Edit "example.ini" to include the following information:
  • DSN: The DSN to be tested
  • User: Username of Data Source
  • Password: Password of Data Source
• Edit source.sql to include the SQL statement that is intended to be tested.
• Run the following command:
  • 64-bit OS: sqlrun_x64.exe -Iexample.ini
  • 32-bit OS: sqlrun.exe -Iexample.ini

Verify if the connection was established successfully.

• Open "out.txt" and verify the result.

Using Microsoft Excel:

1. Create a TXT file includes 4 lines:
   • XLODBC
     1
     DSN=[DSN name];User Id=[Username];Password=[Password];
     SELECT * FROM [tablename]
   • Sample:

Rename this file from .TXT to .DQY file.

Double-click the .DQY file. Excel will show a Security Notice. Click "Enable".

Input the requested information(depends on the actual ODBC Driver)

Data will be fetched to Excel.