Convert row with columns of data into column with multiple rows

[SIMMS7400]

Okay, here are my realtime examples.

The first section of 'code' is what my export file currently looks like: ( for examples purposes, I'm only using 6 columns; there are 61)
Note: the space under the header is intentional; that's the style of my export. A space between the header and first data row.

Code: Select all

"Name","Alias","TEXT_JP","Direct Flag","IP Owner","Modality"

"PFP-B1033400","TAK-331 - Bioanalytical Support (GLP)","Bioanalytical Support (GLP) (TAK-331)","DIRECT","JPTP001","Large Molecule"
"PFP-A1043200","C16028 Japan P2 study MLN9708 RRMM (Ixazomib)","C16028 Japan P2 study MLN9708 RRMM","DIRECT","USTP003","Small Molecule"
"PFP-A1011300","Norovirus vaccine - DoD (Government grant)","Norovirus vaccine - DoD (Government grant)","DIRECT","USTP013","Vaccine"
"PFP-A1012800","NOR-327","NOR-327 ","DIRECT","USTP013","Vaccine"

This is what I need to transform the above file into:

Code: Select all

"PFP-B1033400","Alias","TAK-331 - Bioanalytical Support (GLP)"
"PFP-B1033400","TEXT_JP","Bioanalytical Support (GLP) (TAK-331)"
"PFP-B1033400","Direct Flag","DIRECT"
"PFP-B1033400","IP Owner","JPTP001"
"PFP-B1033400","Modality","Large Molecule"
"PFP-A1043200","Alias","C16028 Japan P2 study MLN9708 RRMM (Ixazomib)"
"PFP-A1043200","TEXT_JP","C16028 Japan P2 study MLN9708 RRMM"
"PFP-A1043200","Direct Flag","DIRECT"
"PFP-A1043200","IP Owner","USTP003"
"PFP-A1043200","Modality","Small Molecule"
"PFP-A1011300","Alias","Norovirus vaccine - DoD (Government grant)"
"PFP-A1011300","TEXT_JP","Norovirus vaccine - DoD (Government grant)"
"PFP-A1011300","Direct Flag","DIRECT"
"PFP-A1011300","IP Owner","USTP013"
"PFP-A1011300","Modality","Vaccine"
"PFP-A1012800","Alias","NOR-327"
"PFP-A1012800","TEXT_JP","NOR-327 "
"PFP-A1012800","Direct Flag","DIRECT"
"PFP-A1012800","IP Owner","USTP013"
"PFP-A1012800","Modality","Vaccine"

I hope this helps!


Mod edit: The details in the post above have been changed so some following comments no longer apply

[Compo]

If the file is csv then surely we should be looking at Comma Separated Values, (and potentially values within double quotes).

This task will not be worth taking on based on what you've given us, I'd strongly suggest you provide genuine data examples, (i.e. real values real row names and real headers).

[Squashman]

And you have given no specifics on the format of the output file. I can only assume that you want the output to be space delimited. But I suppose you may want it in a fixed format where each field starts in a specific position on the line because the data my not all have the same length for each field.

[foxidrive]

If you use CODE tags instead of quoting the text then the spaces will be preserved.

Show us a sample input file and the resulting file as you would like it to appear.

Don't use spaces where there are no spaces, and do use commas and double quotes where they should appear as Compo has outlined.

Show more than one line in your example also.

[SIMMS7400]

Mod edit: The details in this post have been moved to the top of this thread.

[foxidrive]

That's a great example.

Are there any double quotes in your CSV file, with those large wads of texts with spaces in many of the fields?

[Squashman]

Hmmm, trying to find the thread where we discussed what the max tokens are for the FOR /F command. Sixty one might be too many. You also have to understand that the FOR /F command will see fields with blank values and shift your data. Essentially it will see two commas in a row as one. So if value 1 is blank, value 2 will get assigned to the token for value 1.

[SIMMS7400]

Hi Fox -

Thank you for the note.

All strings should have double quotes. Do you need me to amend my examples?

Thanks!

[foxidrive]

Yes, please edit your top post in this thread.

[Squashman]

Hmmm, trying to find the thread where we discussed what the max tokens are for the FOR /F command. Sixty one might be too many. You also have to understand that the FOR /F command will see fields with blank values and shift your data. Essentially it will see two commas in a row as one. So if value 1 is blank, value 2 will get assigned to the token for value 1.

Found what I was looking for. I know we discussed it here but I also remember Rob had it on his website as well.
Half way down, it talks about using 65+ tokens.
http://www.robvanderwoude.com/clevertricks.php

[SIMMS7400]

My examples have been modified.

Thanks!

[foxidrive]

True, Squashman.

Another way of parsing the file may work.


SIMMS7400, your changed text doesn't use double quotes. You have sets of two single quotes.

Please read this (changed recently): viewtopic.php?f=3&t=6108

[Compo]

In my opinion you should be ignoring a batch script, and transposing this data directly in Excel.

I would recommend you seek help from an Excel specific forum, there are many good ones around and with a well structured question you'll be sure to get the help you need.

[SIMMS7400]

Compo -

Thank you for the note. Unfortunately, the server in which I am exporting to does not have excel. Therefore, my only options right now is batch which I'm hoping we can accomplish.

Thank you.

[SIMMS7400]

True, Squashman.

Another way of parsing the file may work.


SIMMS7400, your changed text doesn't use double quotes. You have sets of two single quotes.

Please read this (changed recently): viewtopic.php?f=3&t=6108

Fox - examples have been corrected. Thanks!