DosTips.com

A Forum all about DOS Batch
https://www.dostips.com/forum/

Convert row with columns of data into column with multiple rows

https://www.dostips.com/forum/viewtopic.php?f=3&t=7265

Page 1 of 3

Convert row with columns of data into column with multiple rows

Posted: 06 Jul 2016 07:27
by 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

Re: Convert row with columns of data into column with multiple rows

Posted: 06 Jul 2016 07:45
by 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).

Re: Convert row with columns of data into column with multiple rows

Posted: 06 Jul 2016 07:48
by 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.

Re: Convert row with columns of data into column with multiple rows

Posted: 06 Jul 2016 08:01
by 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.

Re: Convert row with columns of data into column with multiple rows

Posted: 06 Jul 2016 08:03
by SIMMS7400
Mod edit: The details in this post have been moved to the top of this thread.

Re: Convert row with columns of data into column with multiple rows

Posted: 06 Jul 2016 08:09
by 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?

Re: Convert row with columns of data into column with multiple rows

Posted: 06 Jul 2016 08:12
by 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.

Re: Convert row with columns of data into column with multiple rows

Posted: 06 Jul 2016 08:12
by SIMMS7400
Hi Fox -

Thank you for the note.

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

Thanks!

Re: Convert row with columns of data into column with multiple rows

Posted: 06 Jul 2016 08:15
by foxidrive
Yes, please edit your top post in this thread.

Re: Convert row with columns of data into column with multiple rows

Posted: 06 Jul 2016 08:19
by Squashman
Squashman wrote: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

Re: Convert row with columns of data into column with multiple rows

Posted: 06 Jul 2016 08:22
by SIMMS7400
My examples have been modified.

Thanks!

Re: Convert row with columns of data into column with multiple rows

Posted: 06 Jul 2016 08:24
by 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

Re: Convert row with columns of data into column with multiple rows

Posted: 06 Jul 2016 08:26
by 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.

Re: Convert row with columns of data into column with multiple rows

Posted: 06 Jul 2016 08:41
by 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.

Re: Convert row with columns of data into column with multiple rows

Posted: 06 Jul 2016 08:59
by SIMMS7400
foxidrive wrote: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!
All times are UTC-06:00
Page 1 of 3
Powered by phpBB® Forum Software © phpBB Limited
https://www.phpbb.com/