Dynamically remove rows 1 to X from a CSV file

acowper

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

Remove initial rows.yxmd

Common Use Cases

Accepted answers

jdunkerley79

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

CSV Headers.yxmd

All comments

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

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

CSV Headers.yxmd

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_DynamicHeader.yxzp

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

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703