CSV Delimited by :

[dpatton]

Hello, 

I am trying to transform a CSV file that is delimited by a ":"

How can I transform this:

120::OWNER:CASHDBO:TABLE_NAME:CASH_ACTIVITY:Total_Number_of_Fields:187:Number_of_Fields_With_Values:105:Number_of_Fields_Null:82:Percentage_Used:56%:Total_Records:4812176:

120::OWNER:HOLDINGDBO:TABLE_NAME:LOT_LEVEL_POSITION:Total_Number_of_Fields:204:Number_of_Fields_With_Values:41:Number_of_Fields_Null:163:Percentage_Used:20%:Total_Records:164175394:

120::OWNER:HOLDINGDBO:TABLE_NAME:NAV:Total_Number_of_Fields:170:Number_of_Fields_With_Values:70:Number_of_Fields_Null:100:Percentage_Used:41%:Total_Records:640078:

120::OWNER:HOLDINGDBO:TABLE_NAME:PD_CURVE_ANALYTICS:Total_Number_of_Fields:80:Number_of_Fields_With_Values:19:Number_of_Fields_Null:61:Percentage_Used:24%:Total_Records:36517713:

120::OWNER:HOLDINGDBO:TABLE_NAME:POSITION:Total_Number_of_Fields:38:Number_of_Fields_With_Values:21:Number_of_Fields_Null:17:Percentage_Used:55%:Total_Records:441477:

to this:

OWNER	TABLE_NAME	Total Number of Fields	Number of Fields With Values	Number of Fields Null	Percentage Used	Total Records
CASHDBO	CASH_ACTIVITY	187	105	82	56%	4812176
HOLDINGDBO	LOT_LEVEL_POSITION	204	41	163	20%	171392223
HOLDINGDBO	NAV	170	70	100	41%	642949
HOLDINGDBO	PD_CURVE_ANALYTICS	80	19	61	24%	37116937
HOLDINGDBO	POSITION	38	21	17	55%	446641

The pattern is as follows, Column Header:data:column header:data

Thanks for the help!

Dustin Patton

[IraWatt]

Hey @dpatton,

Here's one way of doing this:

There's a lot going on here make sure to ask if any questions or isses :)

HTH,

Ira

[smoskowitz]

You need the Text to Columns tool in the Parse tab. You can then delimit by colon (or whatever the delimiter is.) 

Thanks,

Seth