Dynamic data import / export

datenbank

simply more productive
Up to 10,000 records can be processed with the freeware version!
Welcome, Guest
Username: Password: Remember me
Welcome to the FlowHeater support forum.

Questions about using FlowHeater are answered here.

TOPIC: Differing import - export paths

Differing import - export paths 6 years 3 months ago #2017

  • FlowHeater-Team
  • FlowHeater-Team's Avatar
  • Offline
  • Administrator
  • Posts: 240
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
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.
The administrator has disabled public write access.

Re:Differing import - export paths 6 years 3 months ago #2018

  • FlowHeater-Team
  • FlowHeater-Team's Avatar
  • Offline
  • Administrator
  • Posts: 240
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.
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.
The administrator has disabled public write access.

Re:Differing import - export paths 6 years 3 months ago #2019

  • FlowHeater-Team
  • FlowHeater-Team's Avatar
  • Offline
  • Administrator
  • Posts: 240
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.
The administrator has disabled public write access.

Re:Differing import - export paths 6 years 3 months ago #2020

  • FlowHeater-Team
  • FlowHeater-Team's Avatar
  • Offline
  • Administrator
  • Posts: 240
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.
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.
The administrator has disabled public write access.
Moderators: FlowHeater-Team
Time to create page: 0.074 seconds