DSN-less ODBC data sources

What is a DSN?

DSN acronym stands for Data Source Name. You can create these entries using Windows's ODBC data sources management applet. The DSNs can be useful because they abstract certain connection settings from the application, like server name, encryption, and many more.

Sometimes though the use of the DSNs can be cumbersome and unnecessary. For a desktop application like SelectCompare the connection string can directly contain all required parameters without the necessity of creating a separate configuration item that requires maintenance.

Creating an ODBC connection in SelectCompare

SelectCompare allows the user to configure a data source using the built-in operating system dialog windows. These windows look dated and sometimes can be a bit confusing. While it is possible to create a connection without a DSN, it can be quite cumbersome.

To facilitate the creation of the data connections, you can paste the connection strings directly to the "Connection string" edit box and test the connection as it is shown in the image below:

SQL Server

Driver={ODBC Driver 17 for SQL Server};Server=serverName\instanceName;Database=myDataBase;Trusted_Connection=yes;

MySQL

Driver={MySQL ODBC 5.2 ANSI Driver};Server=myServerAddress;Database=myDataBase;User=myUsername;Password=myPassword;Option=3;

For information about other variants of the connection strings, please check https://www.connectionstrings.com/mysql-connector-odbc-5-2/

PostgreSQL

Driver={PostgreSQL ANSI};Server=IP address;Port=5432;Database=myDataBase;Uid=myUsername;Pwd=myPassword;

Other variants of the ODBC connection strings for PostgreSQL can be found on this page: https://www.connectionstrings.com/postgresql-odbc-driver-psqlodbc/

Microsoft Excel

Microsoft Excel is one of the most important tools for any person who works with data. Data comparisons should be a natural part of work with Excel spreadsheets, without the need of writing complex formulas and lookups. Comparison of Excel spreadsheets can be as simple as connecting to the workbooks and selecting from the spreadsheets. SelectCompare allows for such data comparison.

Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};Dbq=myExcelWorkbook;FIL=Excel 12.0;

An alternative connection string can be used for Excel, using the OLEDB provider: 

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=myExcelWorkbook;Extended Properties="Excel 12.0"

CSV files

CSV files are a very important format of data. SelectCompare can compare data in them as well - you need to install an ODBC driver for CSV files and configure the connections to query them.

Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=myCSVFilesFolder;Extensions=asc,csv,tab,txt;