The external data source import utility allows you to import data from any OLE DB compliant system (Access, MS SQL, Oracle, Excel...).   The first step is to define the connections to the external database and the date format to use when querying the external database.

BUTTONS:

	Used to add a new conncetions. Opens the OLE DB Connection String Settings window allowing you to set the Name, Date Format, and Connection String for the Connection.
	Deletes the currently selected connection (i.e. the current row in the grid).  Asks for confirmation before removing.
	Searches the list.
	Prints the list of Data Sources.
	Exports the list to an Excel file.
	Closes the form.
	Displays a form that allows you to edit the current record.

FIELDS:

Name: The name used to identify the connection.  This field is used on the Variable Setup, DB Interface tab to tell the import which connection to use for the variable.

Date Format: The date format to use when querying based on date fields in the external database.  You can choose from the 3 available options or type in a date format string.

Connection String: The ADO database connection string that is used to connect to the database.  Some common connection strings are shown below. 

Access 97, 2000, 2002, 2003:

No Security:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mydatabase.mdb;User Id=admin;Password=;

Workgroup Security:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mydatabase.mdb;Jet OLEDB:System Database=system.mdw;

Database Password:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mydatabase.mdb;Jet OLEDB:Database Password=MyDbPassword;

Access 2007,2010, 2013:

No Security:
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccessFile.accdb;
Persist Security Info=False;

Database Password:
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccessFile.accdb;
Jet OLEDB:Database Password=MyDbPassword;

MS SQL Server 2000:

Provider=sqloledb;Data Source=servername\instanceName;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;

MS SQL Server 2005:

Provider=SQLNCLI;Server=Servername\instanceName;Database=myDataBase;Uid=myUsername;Pwd=myPassword;

MS SQL Server 2008:

Provider=SQLNCLI10;Server=myServerName\theInstanceName;Database=myDataBase;Uid=myUsername;Pwd=myPassword;

MS SQL Server 2012:

Provider=SQLNCLI11;Server=myServerName\theInstanceName;Database=myDataBase;Uid=myUsername;Pwd=myPassword;

Oracle:

Provider=OraOLEDB.Oracle;Data Source=MyOracleDB;User Id=myUsername;Password=myPassword;

(uses the Oracle provider - OraOLEDB)

WIMS Oracle:

Provider=OraOLEDB.Oracle.1;Data Source=MyOracleDB;User Id=OPSWWTUTOR;Password=###;

Paradox:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\myDb;Extended Properties=Paradox 5.x;

dBase/FoxPro:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\folder;Extended Properties=dBASE IV;User ID=Admin;Password=;

NOTE: the driver used for this connection string olny accepts 8.3 Filename.  8.3 filenames are limited to at most eight characters (after any directory specifier), followed optionally by a filename extension consisting of a period (.) and at most three further characters.

csv files:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=z:\;Extended Properties="text;HDR=Yes;FMT=Delimited";

Dates are specfied by using # character.  To specify field names with spaces surrond the field name with the backquotes `.

Example Query:  select count(Assigned) from EmailDailyCount.csv where Assigned='User One' and DateValue([Date Created])>=#SD# and DateValue([Date Created])<#ED#
       NOTE: DateValue (Access Function) was wrapped around the Date Created Field in order for the field to be recognized as a date field.

 

Excel files:

Microsoft Excel 2003 (xls)
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\myfolder\myspreadsheet.xls;Extended Properties="Excel 8.0;HDR=Yes"

Microsoft Excel 2007, 2010, 2013 (xlsx) without Macros Enabled
NOTE: Microsoft Offic 365 Excel does not install the ACE OLEDB driver.  You will need to install it from Microsoft, here. 
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES";

Data Source is the path and file name of the Excel file

Extended Properties has the Excel version (12.0) and whether first row is header information or data, HDR=Yes means the first row has header information as shown below:

Example Query: SELECT [Curvalue] FROM [Sheet1$] WHERE [DATESTAMP] >= #SD# AND [DATESTAMP] < #ED#

This query will retrieve Curvalue from the Excel tab 'Sheet1'. Notice that the fields are encased with square brackets and the "Table" name is encased with square brackets with a dollar sign ($) included.

Example Query without headers: SELECT [F1], [F3] FROM [Sheet1$] WHERE [F1] >= #SD# AND [F1] < #ED#

This query will retrieve Field 1 (F1) and Field 3 (F3) which equate to the columns, the first column is F1 and the third column is F3.

NOTES:

In order to view 32 bit providers in 64 bit operating systems, run the following from the command prompt:

C:\Windows\syswow64\rundll32.exe "C:\Program Files (x86)\Common Files\System\Ole DB\oledb32.dll",OpenDSLFile C:\help\test.udl