Hello Mr Richter,
With the Batch Module you have the possibility of using calling parameters:
/ReadFileName file or /WriteFileName file
This overwrites the filename and path that is defined and stored in the Definition prior to execution. However, this only functions together with the TextFile Adapter or the Excel Adapter.
Presumably you also wish to dynamically create a new table on the WRITE side before the import? That can be achieved using the .NET Script Heater and a little code.
First create your CSV import Definition in the usual way. Once you have tested the import, drag and drop a .NET Script Heater onto an existing connection and insert the .NET Script code that follows this message.
Important: The .NET Script Heater must be dragged to an existing connection, because otherwise the code is not dynamically executed!
The code creates a new table t_DynImport + current date and time for each import. For the table structure, the fields on the WRITE side are used. Just take a look at it. Modifications of the table name and the table structure, e.g. the primary key, can be relatively easily carried out.
| Code: |
bool bFirst = true;
public object DoWork()
{
if (bFirst)
{
// only for the first row
bFirst = false;
bool primary = true;
string tabelle = "t_DynImport_" + DateTime.Now.ToString("yyyyMMdd_hhmmss");
BaseDBAdapter adapter = (BaseDBAdapter)AdapterWrite;
adapter.SQL = "select * from " + tabelle;
string sql = "create table " + tabelle + " (";
int len = 0;
int count = 0;
foreach(Field f in adapter.Fields)
{
BaseDBAdapterField field = (BaseDBAdapterField)f;
if (count > 0)
sql += ", ";
sql += "[" + field.Name + "] ";
switch(field.DataType)
{
case DataType.String:
len = field.Length;
if (len == 0)
len = 100;
//SQL data type for strings
sql += "varchar(" + len.ToString() + ")";
break;
case DataType.Date:
case DataType.Time:
case DataType.DateTime:
//SQL data type for datetime
sql += "datetime";
break;
case DataType.Bool:
//SQL data type for Bool: for MSSQL using bit!
sql += "boolean";
break;
case DataType.Int:
//SQL data type for Int
sql += "Integer";
break;
case DataType.Double:
case DataType.Currency:
//SQL data type for Double and Currency, also posible numeric(18,2) ...
sql += "Float";
break;
}
if (primary) {
primary = false;
sql += "not null ";
}
count++;
}
sql += " );";
//File.WriteAllText("C:\\Temp\\sql.txt", sql);
// create table
adapter.Execute(sql);
}
return InValues[0].GetValue();
}
|
