Add a Starting Record to a file

[ewassell]

I have a file that contains all the info below - the header files need to be first and then the rows with the data come after but the first line needs to be LEDES1998B by itself.  Any idea how to do this?  I have it all except how do I get this first line that just says LEDES1998B?

LEDES1998B

INVOICE_DATE|INVOICE_NUMBER|CLIENT_ID|LAW_FIRM_MATTER_ID|INVOICE_TOTA
L|BILLING_START_DATE|BILLING_END_DATE|INVOICE_DESCRIPTION|LINE_ITEM_NUMBER|EXP/FE
E/INV_ADJ_TYPE|LINE_ITEM_NUMBER_OF_UNITS|LINE_ITEM_ADJUSTMENT_AMOUNT|LINE_ITEM_TO
TAL|LINE_ITEM_DATE|LINE_ITEM_TASK_CODE|LINE_ITEM_EXPENSE_CODE|LINE_ITEM_ACTIVITY_
CODE|TIMEKEEPER_ID|LINE_ITEM_DESCRIPTION|LAW_FIRM_ID|LINE_ITEM_UNIT_COST|TIMEKEEP
ER_NAME|TIMEKEEPER_CLASSIFICATION|CLIENT_MATTER_ID

19990225|96542|00711|0528|1684.45|19990101|19990131|For services rendered|1|F|2.00|-70|630|19990115|L510||A102|22547|Research Attorney’s fees, Set off claim|24-6437381|350|Arnsley, Robert|PT|423-987

19990225|96542|00711|0528|1684.45|19990101|19990131|For services rendered|2|F|2.00|0|700|19990115|L510||A102|22547|Research attorney's fees, Trial pleading|24-6437381|350|Arnsley, Robert|PT|423-987

[caltang]

Something like this?

[caltang]

If your data comes together, then you can use a filter tool instead to filter out LEDES1998B, and use a record ID as shown above.

[ArnaldoSandoval]

Hi @ewassell 

There was several ways to go about your issue, mine is based on your data from a single file, I read the file twice with different configurations, check them, the first time it just peek the first row the second time the just the remaining records with header.

This is the output:

WARNING:

My solution, and the other one posted only work for the sample data you shared, it will fail if more invoices and leading files are received.

hth

Arnaldo

[RobertOdera]

Hi, @ewassell 

Kindly consider the below.

I hope you find it helpful - Cheers!

[ewassell]

Thank You! This is almost it but if I save the outgoing file as a .csv file with a delimiter of |, it shows the pipes after the LEDES1998B in the same line. How do I get rid of them?

[ArnaldoSandoval]

Hi @ewassell 

One way to strip those |s from the output CSV files is by splitting the stream, the first record with LEDES1998B is written by itself to the output csv, the remaining records are appended to the output csv. but because Ateryx CSV output does not support appending, so we need a macro to achieve the appending, 

I found the AppendtoCSV here in the Alteryx's Community (I lost its link), I modified it to output pipe | separator.

While testing I found that your fields LINE_ITEM_DESCRIPTION and TIMEKEEPER_NAME contains commas, so I replaced their commas with spaces NOTE: I am using the latest version of Alteryx, one of the workflows posted here do not need the comma replacement, who knows why?

Hope this helps,

Arnaldo