export each row into a separate flat file

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!

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.

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!

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;;;

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 .

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

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

here is the hurdle i currently face..

thanks!

herb

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
With the following batch CMD script you can run these definitions automatically.
Hope this example comes close to what you want?

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

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.

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.

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

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.