How to Transform Colon Delimited CSV into Usable Data

[dpatton]

Hello,

I am receiving a CSV file that is delimited by colon's. The pattern goes, column header:data:column header:data

Example

I want to transform this data:

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

Any help would be greatly appreciated. 

Thanks!

Dustin Patton

[Ladarthure]

Hi @dpatton,

to do this, read the file using an input data and change the delimiter in the input tool (option 5) to semi colon, it should do the trick. Do remember to u

[IraWatt]

Hey @dpatton, seems like a duplicate post, check my answer on your other post! :D

[estherb47]

Hi @dpatton . As @Ladarthure wisely advised above, do change the delimiter to see if that works for you.

Looking at the dataset, you might then need to then play around with your data to ensure that the rows and columns ultimately align. Some tools to look into would be Record ID (to rebuild the original order), Transpose, and Cross Tab. Make Columns (from the Laboratory tool palette) might also prove useful.

Give these a go and let us know how you're doing. You'll learn best by trying things out, and Alteryx can't break your data, so it's a safe place to play.

Cheers!

Esther

[dpatton · Accepted solution]

@Ladarthure @IraWatt @estherb47 , thank you all for your quick responses. 

Here is the solution I came up with. The key was to change the Delimiter to ":" and to deselect the First Row Contains Field Names.

[estherb47]

Well done, @dpatton !!

[Aguisande]

Guys! I love this post!

@dpatton : Seeing you coming to a solution to your own problem, just with some guidance, makes me so proud of being part of this Community!

This proves that it's never about throwing some workflows as a response or asking "do my job by me (and I need it by friday!)"; It's all about HELPING people figure out their own solutions!

Thanks for this post to all of you