Skip to main content
simply more productive

The Data Specialist

Thanks to our intuitive transformation designer, you can convert data effortlessly to a variety of formats without knowing how to program. It’s not only efficient, it’s also easy to use.
Flowheater Download

export each row into a separate flat file

  • FlowHeater-Team
  • Topic Author
  • Offline
  • Admin
  • Admin
More
13 years 1 week ago #2095 by FlowHeater-Team
export each row into a separate flat file was created by FlowHeater-Team
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.

Please Log in to join the conversation.

  • FlowHeater-Team
  • Topic Author
  • Offline
  • Admin
  • Admin
More
13 years 1 week ago #2096 by FlowHeater-Team
Replied by FlowHeater-Team on topic Re:export each row into a separate flat file
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.
Code:
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


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

Please Log in to join the conversation.

More
12 years 9 months ago #2119 by Herbert Berkley
Replied by Herbert Berkley on topic Re:export each row into a separate flat file
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;;;

Please Log in to join the conversation.

  • FlowHeater-Team
  • Topic Author
  • Offline
  • Admin
  • Admin
More
12 years 9 months ago #2121 by FlowHeater-Team
Replied by FlowHeater-Team on topic Re:export each row into a separate flat file
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 .
Code:
@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.

Please Log in to join the conversation.

More
12 years 9 months ago #2122 by Herbert Berkley
Replied by Herbert Berkley on topic Re:export each row into a separate flat file
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

Please Log in to join the conversation.

More
12 years 9 months ago #2124 by Herbert Berkley
Replied by Herbert Berkley on topic Re:export each row into a separate flat file
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

Please Log in to join the conversation.

More
12 years 9 months ago #2125 by Herbert Berkley
Replied by Herbert Berkley on topic Re:export each row into a separate flat file
here is the hurdle i currently face..

thanks!

herb
Attachments:

Please Log in to join the conversation.

  • FlowHeater-Team
  • Topic Author
  • Offline
  • Admin
  • Admin
More
12 years 9 months ago #2126 by FlowHeater-Team
Replied by FlowHeater-Team on topic Re:export each row into a separate flat file
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
Code:
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.
Code:
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


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

Please Log in to join the conversation.

More
12 years 9 months ago #2127 by Herbert Berkley
Replied by Herbert Berkley on topic Re:export each row into a separate flat file
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

Please Log in to join the conversation.

  • FlowHeater-Team
  • Topic Author
  • Offline
  • Admin
  • Admin
More
12 years 9 months ago #2128 by FlowHeater-Team
Replied by FlowHeater-Team on topic Re:export each row into a separate flat file
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.
Code:
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.

Please Log in to join the conversation.

More
12 years 9 months ago #2129 by Herbert Berkley
Replied by Herbert Berkley on topic Re:export each row into a separate flat file
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..

Please Log in to join the conversation.

  • FlowHeater-Team
  • Topic Author
  • Offline
  • Admin
  • Admin
More
12 years 9 months ago #2130 by FlowHeater-Team
Replied by FlowHeater-Team on topic Re:export each row into a separate flat file
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


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

Please Log in to join the conversation.

Time to create page: 0.365 seconds

FlowHeater - The Data Specialist

Efficient data integration and transformation with FlowHeater – the perfect solution for a seamless transfer of your data.

Rechtliches

Support & Contact

Kontaktinformation

Telefon:0951 / 99339792 E-Mail:This email address is being protected from spambots. You need JavaScript enabled to view it.

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