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
Solved! Go to Solution.
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
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
Awesome! I've never thought to use multi-row with a boolean expression, super useful.
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
Just used this and it works great.
Thanks for the post.
This is just the solution I was looking for. Quick and easy. Thanks.