Moving data to 1 line

[pklinedinst]

I have a text file that has over 12,000 lines and the totals are on three lines. I want to find the word Total and move the data from the next 2 lines up to the Total line in different columns. Does anyone know how this can be done?

[DawnDuong]

Hi @pklinedinst 

I am assuming here that you have at least 2 columns, of which 1 column contains some descriptive text and the other contains values. Can you clarify what you mean by "move the data from the next 2 lines up to the Total line"? Do you mean add the values? It's best if you can provide some sample input vs. output so that the problem statement is clearer.

Dawn.

[pklinedinst]

I have 1 column that contains the whole line of data it looks like this and it is called Field_1:

I want the 2nd and 3rd row to be on the same line as Category Total in a separate column. That way I can put each total in a separate column like:

	Count	Other	Actual	Paid	Discount	Gain	Escrow	Transferred	Disc Bal	Gross Int	SF	Net Int	SMP	Loss
Category Total	3	0.00	2,736,000.00	2,736,000.00	0.00	0.00	0.00	2,736,000.00	0.00	-219.19	0.00	-219.19	DL	0.00

Thanks for your help.

[john_watkins]

I created a sample file in XLSX with a single column and put multiple rows in one column.   I was able to simply use a Data Cleansing tool to remove tabs, LINE BREAKS, and duplicate whitespace and it returned the data on one row.   You can then parse the column using the Text-to-Columns tool to put each of them into their own respective columns.   I assume you'd follow up with a Select Tool to rename the fields appropriately.

[pklinedinst]

Is there a way to say if Field_1 contains Category then go down 1 row and move the data up a row to a new column and then go down 2 rows and move that up 2 rows to a new column. This text file has all different kinds of data on it. I only want the 3 lines every time there is a category changed. There is nothing in the other rows to specify that it belongs to the category total.

[john_watkins · Accepted solution]

I would need a sample of the file as I'm not sure I understand.  I thought you mentioned that all three "rows" are in one physical column.   Now if you are talking about three physical rows you could use the Mulit-Row Formula tool which lets you read and reference lines above and below the current one being read.   You can write formulas likc IF row+1 (prior row) contains "Category" then "Category" + row+1 value.   That is where I'd look next, but a sample file would make it much easier.

[Samanthaj_hughes]

I would go the multi row formula tool direction and flag the rows I am interested in and then filter them out. John has the idea here. ^^

[pklinedinst]

I tried the if statement earlier and I couldn't get it to work but now it is working. Thank you for you help.