Qlik Community

Ask a Question

Knowledge

Search or browse our knowledge base to find answers to your questions ranging from account questions to troubleshooting error messages. The content is curated and updated by our global Support team

Announcements
Talk to Experts Tuesday: Live chat Aug. 24th 10 AM ET: Bring your Qlik Gold Client questions REGISTER TODAY

How to fetch data via ODBC DSN without using Qlik Products

Andre_Sostizzo
Digital Support
Digital Support

How to fetch data via ODBC DSN without using Qlik Products

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.

User-added image

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.
 
User-added image
 
  • 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:

User-added image

 

Rename this file from .TXT to .DQY file.
 
User-added image
 

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

 

User-added image

Input the requested information(depends on the actual ODBC Driver)
 
User-added image
 
Data will be fetched to Excel.
 
User-added image
Labels (2)
Attachments
Version history
Revision #:
3 of 3
Last update:
‎2020-08-24 04:23 AM
Updated by:
 
Contributors