LAE

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: mikewillett

Hi,

I have some data in a "report-like" format. It has a few rows of un-needed header information, then the bulk of the data (pipe delimited), and then every 250 or so rows some unnecessary footer information....before repeating the same pattern over the course of the data.

How would I go about bringing this data in, exclude the unnecessary header and footer information and apply the delimiter?

I do have a number of files so doing this in a text editor before then importing is possible, just not ideal.

Mike

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: Tim Meagher

Hey,

So there are a number of ways to go about this.

If you had access to the LDR, I think that would be the best & easiest way, but I'm guessing that's not an option.

Alternately, you could use something like a python or java node to simply parse the file.

The way I would generally go about something like this if I was just using the normal core library would be as shown in the attached BRG (readDataFile.brg) & sample data (Random.txt).

Basically, I read in all of the lines of the file using a delimited node.
Then, using a filter node, I strip out the lines that I don't care about (containing header etc information).

After this, I pump that output into a java node which simply expands the line into a number of different fields.

The example I've got has each header/junk line containing the "--" characters, however you can use any regular expression in the JunkLineIdentifier parameter on the composite.

The TmpFieldName is just what the field containing the line of text will be called within the composite, prior to it being split into a number of different output fields.

The Line Delimiter is just a \r\n, and the fields are all comma separated.
This doesn't handle quotes, or escapes or anything like that.

The (optional) Fields parameter is then just a list specifying the names to use for each of the output fields. If this is specified, then the number of fields per line must match the number of fields specified.

In any case, this only handles the situation where all lines contain the same number of fields.

Anyway, I figure you should be able to use this as a basis for what you need to do.

There is most likely a better (and easier) way to do this... I just have a tendency to go into Java whenever I can't think of any easy way upfront.

If you know the number and names of the fields in advance, then you wouldn't need the java node, and could easily do this within the filter node. Just do a split based on the delimiter character.


Tim
Attachments:
Random.txt
readDataFile.brg

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: Tim Meagher

Sorry - forgot the footer information.
Obviously, if you can put together a regex which matches both the header & footer & none of the data rows, then you could do this all in the one parameter.

Otherwise, it's probably easiest just to declared another parameter on the composite specific to the footer, and then add another case to the if statement in the Remove unwanted lines filter node.

Tim.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: rpigneri

Dear Mike,

I have provided an alternative that does not use Java for those who are interested. In this version, I have replaced the Java node with an Expand from List node and an Agg that together translate the list of values from a single field to a number of fields.

This code assumes that you have a fixed number of fields, that all input fields are in the same order, and that they all use the same delimiter. If these assumptions are not true, then this logic will not work. Notice that you now have to provide the number of fields to the ReadFile composite along with the field delimiter.

Hope that helps,

Rocco
Attachments:
readDataFile--NoJava.brg

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: mwillett

Thanks everyone, I actually had data re-sent in a more structured manner so didn't need to use these nodes yet. Having said, that I am seeing more data coming in this format so I expect I will need to look into this properly in the near future.