Differing import - export paths
- FlowHeater-Team
- Topic Author
- Offline
- Admin
Less
More
14 years 2 months ago #2017
by FlowHeater-Team
Best wishes
Robert Stark
Did this answer your question? We would be grateful if you provide a brief comment as feedback. It may also help others who may have encountered a similar problem.
Differing import - export paths was created by FlowHeater-Team
Translated submission in the German forum. You can read the original entry
here
.
Dear Sir or Madam,
I have been using your product for a long time and am extremely happy with it. Is it also possible to use different input and/or output files/databases?
e.g. I don’t want to open and amend the Script each time and modify the paths, rather install an automated solution along the following lines:
1. Set up convert_csv2MySQL.fhd
2. Use this file for:
Today convert_csv2MySQL.fhd file07.csv 2010_07
Next month convert_csv2MySQL.fhd file08.csv 2010_08
Kind regard
Uwe Richter
Dear Sir or Madam,
I have been using your product for a long time and am extremely happy with it. Is it also possible to use different input and/or output files/databases?
e.g. I don’t want to open and amend the Script each time and modify the paths, rather install an automated solution along the following lines:
1. Set up convert_csv2MySQL.fhd
2. Use this file for:
Today convert_csv2MySQL.fhd file07.csv 2010_07
Next month convert_csv2MySQL.fhd file08.csv 2010_08
Kind regard
Uwe Richter
Best wishes
Robert Stark
Did this answer your question? We would be grateful if you provide a brief comment as feedback. It may also help others who may have encountered a similar problem.
Please Log in or Create an account to join the conversation.
- FlowHeater-Team
- Topic Author
- Offline
- Admin
14 years 2 months ago #2018
by FlowHeater-Team
Best wishes
Robert Stark
Did this answer your question? We would be grateful if you provide a brief comment as feedback. It may also help others who may have encountered a similar problem.
Replied by FlowHeater-Team on topic Re:Differing import - export paths
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.
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();
}
Best wishes
Robert Stark
Did this answer your question? We would be grateful if you provide a brief comment as feedback. It may also help others who may have encountered a similar problem.
Please Log in or Create an account to join the conversation.
- FlowHeater-Team
- Topic Author
- Offline
- Admin
14 years 2 months ago #2019
by FlowHeater-Team
Best wishes
Robert Stark
Did this answer your question? We would be grateful if you provide a brief comment as feedback. It may also help others who may have encountered a similar problem.
Replied by FlowHeater-Team on topic Re:Differing import - export paths
Is there also code in VB?
Best wishes
Robert Stark
Did this answer your question? We would be grateful if you provide a brief comment as feedback. It may also help others who may have encountered a similar problem.
Please Log in or Create an account to join the conversation.
- FlowHeater-Team
- Topic Author
- Offline
- Admin
14 years 2 months ago #2020
by FlowHeater-Team
Best wishes
Robert Stark
Did this answer your question? We would be grateful if you provide a brief comment as feedback. It may also help others who may have encountered a similar problem.
Replied by FlowHeater-Team on topic Re:Differing import - export paths
Here is the code in VB.NET. The code is easily modified, but with this version the table is not generated in test mode!
Further note: The code presently generates no primary keys or possible automatic fields.
Further note: The code presently generates no primary keys or possible automatic fields.
Code:
Dim bFirst as Boolean = true
Public Function DoWork() As Object
' only for the first call and call not if testmode
If bFirst = True And AdapterWrite.OnlyTest = False then
bFirst = false
Dim primary as boolean
primary = true
Dim table as String
table = "t_DynImport_" + DateTime.Now.ToString("yyyyMMdd_hhmmss")
Dim adapter as BaseDBAdapter
adapter = AdapterWrite
adapter.SQL = "select * from " + table
Dim sql as String
sql = "create table " + table + " ("
Dim len as Integer
Dim count as Integer
len = 0
count = 0
For Each f as Field in adapter.Fields
Dim field as BaseDBAdapterField
field = f
If count > 0 Then
sql = sql + ", "
End If
sql = sql + "[" + field.Name + "] "
Select Case field.DataType
Case DataType.String
len = field.Length
If len = 0 Then
len = 100
End If
'SQL data type for strings
sql = sql + "varchar(" + len.ToString() + ")"
Case DataType.Date, DataType.Time, DataType.DateTime
'SQL data type for datetime
sql = sql + "datetime"
Case DataType.Bool
'SQL data type for Bool: use bit for MS SQL Server
sql = sql + "boolean"
Case DataType.Int
'SQL data type for Int(eger)
sql = sql + "integer"
Case DataType.Double, DataType.Currency
'SQL data type for Double and Currency
sql = sql + "float"
End Select
If primary = True Then
primary = False
sql = sql + " not null "
End If
count = count + 1
Next f
sql = sql + " );"
'create new SQL Table
adapter.Execute(sql)
End If
DoWork = InValues(0).GetValue()
End Function
Best wishes
Robert Stark
Did this answer your question? We would be grateful if you provide a brief comment as feedback. It may also help others who may have encountered a similar problem.
Please Log in or Create an account to join the conversation.
Time to create page: 0.266 seconds