This reference section describes additional connection string information when using EDT to load data directly from a CSV / Text file.
 |
The Excel Database Tasks (EDT) software can load data from ANY source either as an Excel report,
or Validate and send the data to any destination Table or Stored Procedure.
Supporting MS SQL Server, Oracle, MySQL, Access, DB2 databases.
|
Download EDT Free Trial
|
Connections string can be pasted into the EDT Connection string text box as highlighted below.
After modifying the connection string, click the Test button to verify the connection:
Microsoft Jet OLE DB 4.0
Delimited columns
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\txtFilesFolder\;
Extended Properties="text;HDR=Yes;FMT=Delimited";
The delimiter can be specified in the registry at the following location:
HKEY_LOCAL_MACHINE \ SOFTWARE \ Microsoft \ Jet \ 4.0 \ Engines \ Text
"Format" = "TabDelimited"
or
"Format" = "Delimited(;)"
Delimited columns
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\txtFilesFolder\;
Fixed length columns
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\txtFilesFolder\;
Extended Properties="text;HDR=Yes;FMT=Fixed";
Headers
"HDR=Yes;" indicates that the first row contains column names, not data. "HDR=No;" indicates the opposite.To specify each columns length use the Schema.ini file. See description below. Important note!
The quota " in the string needs to be escaped using your language specific escape syntax.
.NET Framework Data Provider for OLE DB
Use an OLE DB provider from .NET
Provider=any oledb provider's name;OledbKey1=someValue;OledbKey2=someValue;
See the respective OLEDB provider's connection strings options. The .net OleDbConnection will just pass on the connection string to the specified OLEDB provider.
Microsoft Text ODBC Driver
Standard
Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=c:\txtFilesFolder\;
Extensions=asc,csv,tab,txt;
.NET Framework Data Provider for ODBC
Use an ODBC driver from .NET
Driver={any odbc driver's name};OdbcKey1=someValue;OdbcKey2=someValue;
See the respective ODBC driver's connection strings options. The .net Odbc Connection will just pass on the connection string to the specified ODBC driver.
.NET txtReader for Text Files
Standard
Data Source='C:\MyFolder';Delimiter=',';Has Quotes=True;Skip Rows=0;
Has Header=True;Comment Prefix='';
Column Type=String,String,String,Int32,Boolean,String,String;Trim Spaces=False;
Ignore Empty Lines=True;
"Comment Prefix" means that if a line in the text file starts with the provided prefix value the whole line will be treated as a comment and will not be read.