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

My CSV has possible 2nd lines of comments

  • Christian Glahn
  • Topic Author
  • Offline
  • User
  • User
More
12 years 4 months ago #2179 by Christian Glahn
My CSV has possible 2nd lines of comments was created by Christian Glahn
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?

Please Log in to join the conversation.

More
12 years 4 months ago #2180 by FlowHeater-Team
Replied by FlowHeater-Team on topic Re:My CSV has possible 2nd lines of comments
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.

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

  • Christian Glahn
  • Topic Author
  • Offline
  • User
  • User
More
12 years 4 months ago #2181 by Christian Glahn
Replied by Christian Glahn on topic Re:My CSV has possible 2nd lines of comments
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.

Please Log in to join the conversation.

More
12 years 4 months ago #2183 by FlowHeater-Team
Replied by FlowHeater-Team on topic Re:My CSV has possible 2nd lines of comments
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.

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.

  • Christian Glahn
  • Topic Author
  • Offline
  • User
  • User
More
12 years 4 months ago #2185 by Christian Glahn
Replied by Christian Glahn on topic Re:My CSV has possible 2nd lines of comments
Here's the sample file.

Attachment _exportSampleforETLtools.zip not found

Attachments:

Please Log in to join the conversation.

More
12 years 4 months ago #2186 by FlowHeater-Team
Replied by FlowHeater-Team on topic Re:My CSV has possible 2nd lines of comments
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.

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

  • Christian Glahn
  • Topic Author
  • Offline
  • User
  • User
More
12 years 3 months ago #2196 by Christian Glahn
Replied by Christian Glahn on topic Re:My CSV has possible 2nd lines of comments
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?

-

Please Log in to join the conversation.

More
12 years 3 months ago #2197 by FlowHeater-Team
Replied by FlowHeater-Team on topic Re:My CSV has possible 2nd lines of comments
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

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

Time to create page: 0.461 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.