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: export each row into a separate flat file

export each row into a separate flat file 5 years 8 months ago #2095

  • FlowHeater-Team
  • FlowHeater-Team's Avatar
  • Offline
  • Administrator
  • Posts: 239
Hello

I am interested in the flowheater product, but do have a question. When exporting from a SQL database to a flat file, is there any way to split each row into a separate flat file?

Thanks!

John
Request via email!
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:export each row into a separate flat file 5 years 8 months ago #2096

  • FlowHeater-Team
  • FlowHeater-Team's Avatar
  • Offline
  • Administrator
  • Posts: 239
For this you have to use a little trick. The following C# .NET Script overrides the file name property in the TextFile Adapter on the WRITE side per each row.
int i = 1;

public object DoWork()
{
	// get the TextFile Adapter from the WRITE side
	TextFileAdapter adapter = (TextFileAdapter)AdapterWrite;

	// Build the new filename	
	// first row  = new_flat_file_1.txt
	// second row = new_flat_file_2.txt
	// and so on
	// you can change the file name in the next line
	string filename = String.Format("new_flat_file_{0}.txt", i);

	i++; //increment the running number
	
	// set new file name on the WRITE side
	adapter.Filename = filename;

	// return the first input parameter for furher processing
	return InValues[0].GetValue();
}

Move one .NET Script Heater into one existing pipe/connection. Double click this Heater and copy the script above into the Heater, that’s all. You can change the file name by change the script, have a look to the comment. To demonstrate this I’ve made a little example, see the attachment "export_flat_file.zip".

NOTE: This trick only works in mass data execution mode. Please be sure that you execute the definition with the massdata mode!

Attachment export_flat_file.zip not found

Attachments:
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:export each row into a separate flat file 5 years 5 months ago #2119

  • Herbert Berkley
  • Herbert Berkley's Avatar
  • Offline
  • New Member
  • Posts: 15
This works perfectly.. But what if the row contains a sequence that needs to represent a unique line within the created text file?

For example.. The entire row may be a series of rows placed together into one table.. Ie.. Common rows from multiple tables that require, in the output text file, a unique line in which to be read by another software.

Basically.. The output needs to look something like this..


[Region 0]test1;Juan Perez;PEAJ270285TZ4;3 norte;1005;N/A;Centro;72000;N/A;Puebla;Puebla;Mexico;N/A;N/A
[Region 1]MXN Moneda Nacional (Peso Mexicano);23/06/2011;IVA Normal;PAGO EN UNA SOLA EXHIBICION;Venta Normal;Efectivo;;Factura Normal;0;0;12345;0;79;MATRIZ;0;11;0;0;0;0;0
[Region 2];This;That;This;That;This;That;This;That;This;That;
[Region 3]PZA;articulo uno ;1;100;0;0;0;0;0;0;;;
PZA;articulo dos ;1;100;0;0;0;0;0;0;;;
PZA;articulo tres;1;100;0;0;0;0;0;0;;;
The administrator has disabled public write access.

Re:export each row into a separate flat file 5 years 5 months ago #2121

  • FlowHeater-Team
  • FlowHeater-Team's Avatar
  • Offline
  • Administrator
  • Posts: 239
Hi Herbert,

It’s possible but a bit tricky. For this you have to create two definition for each text file.
  1. For the header line
  2. For the data, you have to enable the "append to file" option and use the same file name
You can run these two definitions automatically by using the batch module.
@echo off
REM adapt the following to the actual path of FHBatch.exe
set FHBATCH="C:\Program Files\FlowHeater\BIN\FHBatch.exe"

%FHBATCH% export-header.fhd
%FHBATCH% export-data.fhd
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:export each row into a separate flat file 5 years 5 months ago #2122

  • Herbert Berkley
  • Herbert Berkley's Avatar
  • Offline
  • New Member
  • Posts: 15
This is fantastic.. I could, in fact, create a definition for each table and then have the data for each [region] populated in whatever way I needed, and added to the text files..

The question I have though is this.. You said, use the append to file option. So if I am, in some way yet to be fully determined, generating a text file for each invoice if the text names are dynamic based on invoice number how will the subsequent definitions be aware of where to append the data to?

Thanks,

Herb
The administrator has disabled public write access.

Re:export each row into a separate flat file 5 years 5 months ago #2124

  • Herbert Berkley
  • Herbert Berkley's Avatar
  • Offline
  • New Member
  • Posts: 15
I have discovered the answer to the above question.. The script you add, for creating individual files for a single row, will create the same file name format in each definition and assuming the tables you are importing have the same number of records the script will automatically append to the same values..

Where this gets a bit complex is in the table that has more than one row that needs to be inserted into each respective file..

For example.. I have 4 tables.. Table 1,2,3 represents a single row in the text file.. The text file is effectively invoice data, for a specific invoice.. The difficult part seems to come into the 4th row where you have more than one row that needs to be inserted into a specific text file..

How would one be able to accomplish this?

I do have all 4 tables inserted into a single table however the way it is organized is that it replicates the single instance rows to accommodate for the multiple instance rows associated with that invoice number.

Thanks
The administrator has disabled public write access.

Re:export each row into a separate flat file 5 years 5 months ago #2125

  • Herbert Berkley
  • Herbert Berkley's Avatar
  • Offline
  • New Member
  • Posts: 15
Here is the hurdle I currently face..

Thanks!

Herb
Attachments:
The administrator has disabled public write access.

Re:export each row into a separate flat file 5 years 5 months ago #2126

  • FlowHeater-Team
  • FlowHeater-Team's Avatar
  • Offline
  • Administrator
  • Posts: 239
Hi Herbert,

I’ve made in little example for you including an access example database. The example just works with 2 Tables, for more tables you have to use the same way like this.
Now the script takes the InvoiceNumber for building the export file name.

NOTE: The definition export-invoiceitem-header.fhd grouping and summing data for each invoice!

The following script below are used in all definitions
public object DoWork()
{
  // get the TextFile Adapter from the WRITE side
  TextFileAdapter adapter = (TextFileAdapter)AdapterWrite;

  // Build the new filename using the InvoiceNumber = first input parameter
  string filename = (string)InValues[0].GetString();
  if (filename == null)
    throw new Exception("not a valid invoice number!");
  
  filename += ".csv";  // you can changes the ending by yourself

  // set new file name on the WRITE side
  adapter.Filename = filename;

  // return the first input parameter = invoicenumber for furher processing
  return InValues[0].GetValue();
}

With the following batch CMD script you can run these definitions automatically.
echo off
set FHBATCH="C:\Program Files\FlowHeater V2\BIN\FHBatch.exe"

%FHBATCH% export-invoice-header.fhd
%FHBATCH% export-invoice-data.fhd

%FHBATCH% export-invoiceitem-header.fhd
%FHBATCH% export-invoiceitem-data.fhd

REM ... and so on for more tables

Hope this example comes close to what you want?

Attachment access_export_example.zip not found

Attachments:
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:export each row into a separate flat file 5 years 5 months ago #2127

  • Herbert Berkley
  • Herbert Berkley's Avatar
  • Offline
  • New Member
  • Posts: 15
This works perfectly.. However running the invoicenumber row through the .net heater means that it adds the invoicenumber in the write section of the definition. Is there a way to use invoice number to filter, create filename, append but then omit the writing of that field to the text file?

Thanks,

Herb
The administrator has disabled public write access.

Re:export each row into a separate flat file 5 years 5 months ago #2128

  • FlowHeater-Team
  • FlowHeater-Team's Avatar
  • Offline
  • Administrator
  • Posts: 239
Hi Herbert,

Of course :)

For this you have to use the following script. The script needs now two input parameters!
  1. Some other field e.g. the invoice date
  2. The invoice number field
You have to use this order.
public object DoWork()
{
  if (InValues.Length != 2)
    throw new Exception("2 Inputparameter expected!");

  // get the TextFile Adapter from the WRITE side
  TextFileAdapter adapter = (TextFileAdapter)AdapterWrite;

  // Build the new filename using the InvoiceNumber = second input parameter
  string filename = (string)InValues[1].GetString();
  if (filename == null)
    throw new Exception("not a valid invoice number!");
  
  filename += ".txt";  // you can changes the ending by yourself

  // set new file name on the WRITE side
  adapter.Filename = filename;

  // return the first input parameter for furher processing
  return InValues[0].GetValue();
}

NOTE: For the first input parameter you have to use some other (output) field, e.g. the invoice date. This is necessary because the execution of the script.
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:export each row into a separate flat file 5 years 5 months ago #2129

  • Herbert Berkley
  • Herbert Berkley's Avatar
  • Offline
  • New Member
  • Posts: 15
Thank you sir..

I'm having a difficult time interpreting your comments.. You say that two input parameters are needed now... Does this mean a .net script on the invoicenumber connected from read side to write side and then another .net script on the read side, invoice date or some other field, connected to the write side? Both .net scripts being equal to what you posted above?

Thanks again..
The administrator has disabled public write access.

Re:export each row into a separate flat file 5 years 5 months ago #2130

  • FlowHeater-Team
  • FlowHeater-Team's Avatar
  • Offline
  • Administrator
  • Posts: 239
You’re welcome.

No, you need just one .NET Script Heater with two input parameters from the READ side and the script above. Here one short example.

Attachment export_Invoice_Data.zip not found

Attachments:
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.107 seconds