Hi Keith,
I´m afraid at present only the
SQLite Adapter
has the possibility to create not exiting tables before importing. The OleDB or all other database Adapters are currently not able to create tables or add some missing fields.
Note: When changing the type of Adapter only general information such as field names / types and database connection settings etc. as applicable are retained, any settings that specifically apply to an Adapter type are lost.
However, you can use a little workaround
It would be possible to create the desired dBASE table (uisng the
OleDB Adapter
) with the help of the
.NET Script Heater
and a little C# script like below.
The Script checks whether the dBASE data file exist for the table (e.g. C:\Temp\dBase_test.dbf) and if not build a dynamically “SQL CREATE TABLE” statement with the fields information from the present definition. (see my remark in the script below)
You have just align two configuration settings. First in the script you have to define the path were your dBase DBF database files are located. Second, you have to enter in den WRITE Adapter properties the desired dBASE table name. (see screenshot below)
I´ve modified your definition with the script, I guess it should work for you. (see attachment)
C# script to create dynamically dBASE tables befor importing
// modify here the path were your dBASE files located
string dbfiles = @"D:\CLC_Test";
public object DoWork()
{
// accessing the WRITE Adapter
OleDBAdapter adapter = (OleDBAdapter)AdapterWrite;
// optain the table name
string tablename = adapter.SQL;
if (File.Exists(Path.Combine(dbfiles, tablename + ".dbf")))
{
// if the dBASE table file already exist no further action!
return null;
}
// build the create table statement
string sql = "create table [" + tablename + "] (";
int len = 0;
int count = 0;
foreach(Field f in adapter.Fields)
{
if (count > 0)
sql += ", ";
DatabaseAdapterField field = (DatabaseAdapterField)f;
sql += "[" + field.Name + "] ";
switch(field.DataType)
{
case DataType.String:
len = field.Length;
if (len == 0)
len = 100;
//SQL Datentyp for strings
sql += "varchar(" + len.ToString() + ")";
break;
case DataType.Date:
case DataType.Time:
case DataType.DateTime:
//SQL Datentyp for datetime
sql += "datetime";
break;
case DataType.Integer:
case DataType.Boolean:
//SQL Datentyp for Int (integer)
sql += "Integer";
break;
case DataType.Double:
case DataType.Decimal:
case DataType.Currency:
//SQL Datentyp für Double and Currency perhaps use numeric(18,2) ...
sql += "Float";
break;
}
if (field.PrimaryKey)
sql += "not null ";
count++;
}
sql += " );";
// execute the SQL statement and create the table
adapter.Execute(sql);
// result not needed!
return null;
}