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: Delete a row when a specific value is found

Delete a row when a specific value is found 5 years 11 months ago #2077

  • FlowHeater-Team
  • FlowHeater-Team's Avatar
  • Offline
  • Administrator
  • Posts: 240
Translated submission in the German forum. You can read the original entry here.

Hello,

First of all I would like to express my admiration.
Your software is by far the best MySQL import tool I have ever encountered. TOP!

FlowHeater will spare me many hours of work and frustration in the future.

However, I do have one query.
When I perform an Update to MySQL database from an Excel table I would like to do the following:

Delete a row when a specific value is found in a given column.

Is this feasible?

Daniel
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:Delete a row when a specific value is found 5 years 11 months ago #2078

  • FlowHeater-Team
  • FlowHeater-Team's Avatar
  • Offline
  • Administrator
  • Posts: 240
Hello Daniel,

Thanks very much for your compliments. We always enjoy receiving feedback like yours.

Your requirement is certainly doable; it just needs a little manual preparation. You need a .NET Script Heater, a Filter Heater and the following short C# script.
public object DoWork()
{
bool bRet = false;

if (InValues.Length != 2)
throw new Exception("2 input parameter expected");

// do not execute in test mode
if (AdapterWrite.OnlyTest)
return bRet;

// 1. input parameter = filter / delete criteria
string filter = (string)InValues[0].GetString();

// for example; if filter = 5 then delete this record
if ( filter == "5" )
{
// 2. input parameter = PrimaryKey
string id = (string)InValues[1].GetString();

// build DELETE SQL
// !!! CAUTION:  you have do use your table and field names !!!
string sql = "delete from YOUR_TABELLE where id = " + id.Replace(".", "").Replace(",", "");

// if your field type is char or varchar use this syntax
//string sql = "delete from YOUR_TABELLE where id = '" + id + "'";


// Execute the SQL statement on the WRITE
AdapterWrite.Execute(sql);

// filter this record
bRet = true;
}

return bRet;
}

Drag a .NET Script Heater and a Filter Heater onto the Designer. Connect the Excel field deciding your deletion criteria to the first input of the .NET Script Heater. As its second input connect the Excel field that contains the Primary Key information for the Update. Next connect the output of the.NET Script Heater to the Filter Heater. Now you have to copy and paste the above script code into the .NET Script Heater properties and manually adapt it for your MySQL table name, your MySQL Primary Key field name and your filter/deletion criteria. Take note of the comments in the script.

CAUTION: Please take care when making use of this method. If an incorrect SQL WHERE condition is entered here you could inadvertently delete the wrong database rows or even the entire table contents!
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.072 seconds