If this row is the first row in a new group then increment the previous row's group number. The first container uses a series of multirow tools to mark out the various groups in your file. join the header rows to the data rows on groupID which prepends the header field info to each data row Process all the data rows and union all the resulting datarowsĤ. Process all the header rows and join all the header fields into one record per groupī. Process each of the group types in turn.Ī. The ids have to be unique within each group type but can overlap across group types.ģ. Look for these markers to identify where each group startsĢ. These types of files general have unique line formats for the various groups so that humans can tell what the current section of the report deals with.

Identify the various group types in your file. CityTotal, CountyTotal and Statetotal groups are similar with a number of header rows that apply to number of data rows.ġ. The Cit圜ounty group of rows is a group that has 3 lines of header that needs to be applied to the following 5 lines of data. These rows are related because the value 'Georgia' which appears lines 5, 30, etc., needs to be applied to all the rows that follow it until we get to another state. In your file a State group is all the records for a given state. To handle these kinds of files, I've found that it's best to read the entire file in as a series lines with no internal delimiters and then build streams within the workflow to deal with separate line formats and row group.Ī row group is group of rows that are some how related to each other. The flat file format has no way to define different formats for different lines and your file has several differing row formats and several different groups of rows that have to be processed individually. This forum discussion got me as far as I am, so my thanks to you: Unfortunately, when use this as a data input in Alteryx, the result is completely wrong: (I have the string data after 'USER INFO: I' set to 11 because any characters after 11 are junk data.) Here is the FLAT file I'm using after my manual edits:

Convert delimited text file to excel