Batch script to parse and transpose .csv

[SIMMS7400]

Hi Team -

I have a need to take a batch file and parse and transpose.

The export is a hierarchy export that looks like this:

Name Alias Pass Through PFP Status Pool - Target Property ASC_CMC_MODEL_T ASC_DEV_MODEL_T ASC_PRD_MODEL_T

PF_PORTFOLIO_TOP Top Node (PF) FALSE Active Pool N N N
PF_CORP_TOTAL Project Product Total FALSE Active Pool N N N
PF_COMM_TOTAL Commercial Total FALSE Active Pool N N N
PF_GA_TOTAL G&A Total FALSE Active Pool N N N
FIT4PLAN_2029 FALSE Active Pool N N N
FIT4PLAN_2030 FALSE Active Pool N N N

My intention is to get an output file in this format:

PF_PORTFOLIO_TOP Alias Top Node (PF)
PF_PORTFOLIO_TOP Pass Through FALSE
PF_PORTFOLIO_TOP PFP Status Active
PF_PORTFOLIO_TOP Pool - Target Property Pool
PF_PORTFOLIO_TOP ASC_CMC_MODEL_T N
PF_PORTFOLIO_TOP ASC_DEV_MODEL_T N
PF_PORTFOLIO_TOP ASC_PRD_MODEL_T N
PF_CORP_TOTAL Alias Project Product Total
PF_CORP_TOTAL Pass Through FALSE
PF_CORP_TOTAL PFP Status Active
PF_CORP_TOTAL Pool - Target Property Pool
PF_CORP_TOTAL ASC_CMC_MODEL_T N
PF_CORP_TOTAL ASC_DEV_MODEL_T N
PF_CORP_TOTAL ASC_PRD_MODEL_T N
PF_COMM_TOTAL Alias Commercial Total
PF_COMM_TOTAL Pass Through FALSE
PF_COMM_TOTAL PFP Status Active
PF_COMM_TOTAL Pool - Target Property Pool
PF_COMM_TOTAL ASC_CMC_MODEL_T N
PF_COMM_TOTAL ASC_DEV_MODEL_T N
PF_COMM_TOTAL ASC_PRD_MODEL_T N


I've been playing around with code and cant get anything to work. I also have 64 total columns.

Thanks - hoping someone has a solution!

[foxidrive]

It'd help if you explain which bits you are using from your file which bits are being added.

[Squashman]

I do not see how your example is a CSV. I see no commas at all.

[SIMMS7400]

I'm Sorry folks -

Copying in here doesn't seem to work. Let me try again

The first example is what I'm trying to transform. For this exercise, Ive limited the columns to (5), but there are a total of 61

Name,Alias,TEXT_JP,Alliance Partner,Brand Name

PF_PORTFOLIO_TOP,Top Node (PF),,,
PF_CORP_TOTAL,Project Product Total,,,
PF_COMM_TOTAL,Commercial Total,,,
PF_GA_TOTAL,G&A Total,,,

I'd like it to be in this format:

PF_PORTFOLIO_TOP,Alias,Top Node (PF)
PF_CORP_TOTAL,Alias,Project Product Total
PF_COMM_TOTAL,Alias,Commercial Total
PF_GA_TOTAL,Alias,G&A Total
PF_PORTFOLIO_TOP,TEXT_JP,
PF_CORP_TOTAL,TEXT_JP,
PF_COMM_TOTAL,TEXT_JP,
PF_GA_TOTAL,TEXT_JP,
PF_PORTFOLIO_TOP,TEXT_JP,
PF_CORP_TOTAL,TEXT_JP,
PF_COMM_TOTAL,TEXT_JP,
PF_GA_TOTAL,TEXT_JP,
PF_PORTFOLIO_TOP,Alliance Partner,
PF_CORP_TOTAL,Alliance Partner,
PF_COMM_TOTAL,Alliance Partner,
PF_GA_TOTAL,Alliance Partner,
PF_PORTFOLIO_TOP,Brand Name,
PF_CORP_TOTAL,Brand Name,
PF_COMM_TOTAL,Brand Name,
PF_GA_TOTAL,Brand Name,


Note: The name column will remain as is (the first column).

Hoping someone has some insight! Thanks!

[foxidrive]

The first example is what I'm trying to transform. For this exercise, Ive limited the columns to (5), but there are a total of 61

Name,Alias,TEXT_JP,Alliance Partner,Brand Name

PF_PORTFOLIO_TOP,Top Node (PF),,,
PF_CORP_TOTAL,Project Product Total,,,
PF_COMM_TOTAL,Commercial Total,,,
PF_GA_TOTAL,G&A Total,,,

I'd like it to be in this format:

PF_PORTFOLIO_TOP,Alias,Top Node (PF)
PF_CORP_TOTAL,Alias,Project Product Total
PF_COMM_TOTAL,Alias,Commercial Total
PF_GA_TOTAL,Alias,G&A Total
PF_PORTFOLIO_TOP,TEXT_JP,
PF_CORP_TOTAL,TEXT_JP,
PF_COMM_TOTAL,TEXT_JP,
PF_GA_TOTAL,TEXT_JP,
PF_PORTFOLIO_TOP,TEXT_JP,
PF_CORP_TOTAL,TEXT_JP,
PF_COMM_TOTAL,TEXT_JP,
PF_GA_TOTAL,TEXT_JP,
PF_PORTFOLIO_TOP,Alliance Partner,
PF_CORP_TOTAL,Alliance Partner,
PF_COMM_TOTAL,Alliance Partner,
PF_GA_TOTAL,Alliance Partner,
PF_PORTFOLIO_TOP,Brand Name,
PF_CORP_TOTAL,Brand Name,
PF_COMM_TOTAL,Brand Name,
PF_GA_TOTAL,Brand Name,


Note: The name column will remain as is (the first column).

It'd be helpful if you could paste a link to a before and after file on some webspace, like Dropbox etc.

[Squashman]

I am just not seeing the relationship between the input and output.

[SIMMS7400]

Hi -

The attached link is to a live spread sheet. You'll notice INPUT & OUTPUT tabs

https://docs.google.com/spreadsheets/d/ ... sp=sharing

Basically the OUTPUT is the transposed input. I need the output to be just (3) columns:

Column 1 - Member Name
Column 2 - Property/Attribute Name
Column 3 - Property/Attribute Value for that node

Does this make more sense?

[dbenham]

It is no surprise you can't get anything to work.

After writing a question (or answer), you should "step back", put yourself in the reader's shoes, and see if what you have written makes sense.

All your posts in this thread have gross inconsistencies that you should have picked up and fixed prior to submittal.

For example, in your last post with the spreadsheet, your output tab has almost no relationship with your input tab - the field names are totally different.


Dave Benham

[SIMMS7400]

Dave I'm sorry. Been a long week.

I"ve corrected my excel spread sheets; I'm not sure how those values got there. Spread sheets are correct now.

Thanks

[foxidrive]

Please contact a moderator to unlock this thread and provide a more accurate example of your task.

As it stands your example shows no relationship between the source and output and the thread is not constructive.

EDIT: I'm adding a comment here that wasn't clear earlier.
Your spreadsheet data I saw was in a GUI and your question related to a .csv file, so it's the .csv file that is required here.