FlowHeater - The Data Import / Export Specialist
See also the existing examples of using the MySQL Adapter Adapter, which may answer your question: CSV text file import , data migrating , exporting only new records

Differing import - export paths

  • FlowHeater-Team
  • Topic Author
  • Offline
  • Administrator
  • Administrator
More
12 years 5 months ago #2017 by FlowHeater-Team
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

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
  • Administrator
  • Administrator
More
12 years 5 months ago #2018 by FlowHeater-Team
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.
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
  • Administrator
  • Administrator
More
12 years 5 months ago #2019 by FlowHeater-Team
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
  • Administrator
  • Administrator
More
12 years 5 months ago #2020 by FlowHeater-Team
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.
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.264 seconds

other Languages

de

FlowHeater Home

de en

Imprint/Contact

Privacy Statement

Copyright © 2009-2023 by FlowHeater GmbH.
All rights reserved.

Follow us on

twitter  facebook

YouTube

 de en