Dynamic data import / export


simply more productive
Download full trial Version
Welcome, Guest
Username: Password: Remember me
Welcome to the FlowHeater support forum.

Questions about using FlowHeater are answered here.

TOPIC: How to get a default value, or simply last_insert_id

How to get a default value, or simply last_insert_id 6 years 4 days ago #2056

  • Francis
  • Francis's Avatar
  • Offline
  • New Member
  • Posts: 13
Ok, here's my 2 questions

Is there anyway to get a default value based on the read adapter filename by launching a conversion with the script batch executer ? Like an argument that you can access like adapterRead.getArgument[0].ToString().

Seems not, so I tried to insert this value in the first row, in a column random, but when I try to get this value by using mysql Last_Insert_ID(), i can't access this value because I guess, flow heater can only get last insert ID of the conversion before, and not the current conversion.

for exemple, i have those excel to import


So I use the forfiles *.*xls command in the batch executer to get these ( around 20 excels like this ).

But I need to use the value C-CCJJ and C-FDIO to do some lookup that refers to a conversion I launch before it.

This value is not in my excel. So if I really have no choice, I'll add it in the excel by copying the same value to all row, but it's not the option that I want :S

I tried as I said to write the value one time in the excel, just as a X-Value. It works for the first row, but I try to access this value to copy it in all rows with 2 select to get my field

obj = AdapterWrite.Execute( select max(ID) from tbl ). This ID should refer to the last row I entered that should contains my value.

And AdapterWrite.Execute( select myField where ID = obj.ToString()).If I debug this, the ID returned is always the last ID that conversion before the one I built just inserted, and not the one just inserted by the current conversion. Same if I use 'Select Last_Id()'

So is there someway to have a kind of property getArgument[0] that can get this dynamic value that changes between excels to import.

Second question ----

Is there a way to get current ID row of a read Adapter ?? like a property AdapterRead.getCurrentID() that would return the row currently importing. With this, it would be SO EASY to deal with 'unique' record that may appears in my excel files.

A little suggestion too would be to add row number the 'show input' adapter from each side and in the 'Preview data' when converting.

Thx for helping me ! I'm about to contact you to make an arrangement for licenses, I made some hardcore templates that I need to test with a huge excel importation. My boss would like to me convince him about the great things that tool can do.
The administrator has disabled public write access.

Re:How to get a default value, or simply last_insert_id 6 years 4 days ago #2057

  • FlowHeater-Team
  • FlowHeater-Team's Avatar
  • Offline
  • Administrator
  • Posts: 233
You can obtain the current input/import file name by using the .NET Script Heater and the following C# script code. Note: You don’t need an input value for this. The Output value contains the file name without path and extension. This also works if you use the forfiles command in batch processing mode.
public object DoWork()
    string filename = String.Empty;

    // for excel adapter
    ExcelAdapter adapter = (ExcelAdapter)AdapterRead;

    //filename = Path.GetFileName(adapter.Database);
    filename = Path.GetFileNameWithoutExtension(adapter.Database);

    // for textfile apater
    //TextFileAdapter adapter = (TextFileAdapter)AdapterRead;
    //filename = Path.GetFileName(adapter.Filename);
    //filename = Path.GetFileNameWithoutExtension(adapter.Filename);

    return filename;

Parameters: We planned to pass parameters in a further version. By now you can read parameters from an external text file and a short .NET Script. Please have a look to the examples enclosed in the attachment.

About the problem with the mysql last_insert_id():
Your way is correct but I think you have to switch the execution mode from memory (default) to mass data. Background: In memory mode FlowHeater reads first all data. After this the conversion starts, the value stored also in memory and at the end each transformed row are imported or updated. In mass data mode FlowHeater reads one row, transform and run heater functions, import or update this row, read the next row …

Hope this help.

Thanks for the suggestion. I´ve added this to our Todo list.

Attachment Parameter.zip not found

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.073 seconds