Dynamically remove rows 1 to X from a CSV file

[acowper]

Hi

Does anyone have a more elegant way than in the attached workflow of doing the following:

- Reading in a CSV file that has an unknown number of garbage rows at the top

- Find the row that begins with a certain string (which also happens to be the table headers)

- Remove the unwanted rows

- Move the headers up to be the field names

My workflow currently

1. reads the file in as ascii text

2. adds a recordId

3. finds the recordid of the header row

4. makes a 'join multiple' back with the main data file by record position to place the start position value at the first row

5. 'fills down' the start position

6. filters on recordId gt / equal startposition

7. splits columns by comma and moves headers up to by field names

I am sure steps 2-6 can be done more elegantly, any ideas? 

More generally, is there a better way to filter rows that have a row number greater than some dynamic value without using a macro to pass in that dynamic value as a parameter?

Cheers

[MarqueeCrew]

hi acowper,

After you add the recorid (i haven't viewed your module), take the data into a filter.  (RECORDID < 100)  - if 100 is too low, make it 1,000.

find the minimum record id.  appendfields to put it onto each of the records from the original input file.

take that data into a filter where RECORDID >= SOURCE_RECORDID (that's the appended field).

Use as dynamic rename to take the name from the 1st row.

Use a select to drop the SOURCE_RECORDID field.

Just a thought between movies.

Thanks,

Mark

[jdunkerley79 · Accepted solution]

You can do this in a slightly easier way using a multiple row formula to create a flag and then filter once flag is true:

Having done this then use a text to columns to explode to columns and use a dynamic rename set to get headers from the first row of data.

Attached as a workflow

[s_pichaipillai]

Hi acowper

Try This one too

This will work ONLY if you know your Column List

What i am doing here is,

Add ROw ID , Transpose the data then Identify the Header start Position 

Finally Grab your data from the Indentified Position(Macro) 

I have written Comments on the workflow

.

please test it :)

attaching the Package with Example files

[acowper]

Awesome! I've never thought to use multi-row with a boolean expression, super useful.

[cbridges]

Great solution, and this discussion highlights the benefit of just being able to tell Alteryx where the data starts :-)

http://community.alteryx.com/t5/Alteryx-Product-Ideas/Specify-the-row-where-data-starts/idi-p/1478

[_cobys]

Just used this and it works great. 

Thanks for the post. 

[arlindab]

This is just the solution I was looking for. Quick and easy. Thanks.