TOPIC: My CSV has possible 2nd lines of comments

Here's a bit of what my files could look like....
Not all records will have the comment line, the ones that do could have just a few words, some may have up to 15k.

Cust#,Name,Date,Product,Model,MFGR Date
Comments regarding the purchase, text only

What I'm looking for is a way to move that comment to a new column in the outbound CSV file.

----
I'm going to be working with up to 26 files with different CSV elements, each one having a second line of comments possible at the end of the file.

There's no newline character in the file, I was thinking I could just remove that and the items would shift - but that doesn't work.

----
Can anyone help answer this?

Hi Christian,

It's possible but I need a bit more information. On witch content can FlowHeater decide what a comment line is? Could you post a short example please?

I've made a brief example how it's generally works. Please have a look to the attachment. The example decides on the first character in the first field if the current line a comment line. If FlowHeater founds the character # this line is a comment line and the GroupBy Heater grouping this content to the previous line. With the IF-THEN-ELSE Heater FlowHeater decide which line and field store on which column on the WRITE side.

I've attached a text file. It's a sample of the export I receive from the software I'm creating extracts from.
I've removed 7 lines of header information, and removed blank lines - manually. If left alone there's 7 lines of summary information, then the header row, followed by 4 blank lines and then each data row is double-spaced.

I could continue to do those pieces manually, prior to using FlowHeater processing. I could also manually remove the ____ which indicates a NULL in the field.

What I'm looking for as an output file is a CSV where the comment field is inline with the data elements of the CSV, as a last column.

My project involves processing files with this particular layout, and up to 26 reports from the same program - each report may have different fields of the same type, but may have fewer fields before the comment line below.

I'm sure this can be done, with some kind of script or code, but I don't have those skills.

Hi Christian,

I think FlowHeater can do it, but there’s no file attached! Before you attach a file you have to zip these file(s). Please post your example file again.

Here's the sample file.

Hi Christian,

I've made a brief example how it´s works. Please have a look to the attachment.

Do to this you need the GroupBy Heater . The GroupBy Heater decides with the extract (Substring) of the fourth character content of the first column. If the charcter is not "-" the next line will be grouped to the actual processing line.

For each field you need a condition like that.
If first row (Condition = 1) then put this content to the certain field on the WRITE (e.g. ID, Name, Job ...)
If second row (Condition = 2) then put this content to the certain field for the comment line

The NULL Value becomes transformed with the help of the Replace Heater .

Note: If you need more space on the designer you can use layers. This I've also used in the example. It's also possible to filter empty lines and or header lines automatically.

The heater works great!

Forgive me for being a noob though.

The files I'll be working on are not all identical.
Though they will all start with ID, NAME and ORIG-Date.
Then there will be different numbers of fields, and then the comment line.

I'm not certain how to duplicate the heater that's making the argument to move the 2nd line to a comment field.
Can that be explained please?

Next, is it possible to have the comment portion be the last column in each row, rather than in the middle?

-

Hi Christian,

You can move output fields on every position you want. Open the Configurator from the TextFile Adapter and go to the "Fields / Data type" Tab. Select the field you want to move and click on the Button Up and/or Down to move this field to another position.

To handle a different numbers of fields you have to use a little trick. Create a definition with the maximum of field you expected. These fields are connected to just one String Append Heater . Here you have to use a separator for each field, in your case one tabulator.
Note: To use the tabulator sign in the String Append Heater you have to use Copy&Paste his sign from Notepad!

This output you have to connect with just one field on the WRITE Side.
Note: If you need dynamic column headers names you have to process these headers also with this method.

I've made a brief example how it`s works. Please have a look to the attachment.

How does the comment line find the way to the output column?
The GroubBy Heater groups the whole 2 lines from READ Site to just one line on the WRITE. If there a comment line usually the content from the last grouped line are stored on the WRITE. To decide what line and field content from the READ side you want to store in the WRITE side line you have to use an IF-THEN-ELSE Condition for each field. In this condition you can decide with the help of the AutoID Heater (with GroupBy Option) what line / field content you want to move to the WRITE side.

Condition = 1 = only the field content from the first grouped line goes to the WRITE line
Condition = 2 = only the field content from the second grouped line goes to the WRITE line