Parse comma delimited export into new format?

Discussion forum for all Windows batch related topics.

Moderator: DosItHelp

Post Reply
Message
Author
SIMMS7400
Posts: 546
Joined: 07 Jan 2016 07:47

Parse comma delimited export into new format?

#1 Post by SIMMS7400 » 14 Sep 2016 08:06

Hi Folks -

I was wondering if this would even be feasible with batch; allow me to explain my ask in a manner so that Fox's panties don't get bunched up :lol:

In one system I manage, I have a bunch of validations I wrote (in Oracle language) to ensure metadata (members/nodes) are appended with the correct attributes in the system. If the validation fails, I have it exported to a comma delimited flat file in a specific column format to see where the validation failed.

Currently, I have 33 nodes that have a failed one of my validations. The export file looks like this:

Code: Select all

"Grand Parent","Parent Node","Name","ASC_CMC_MODEL_T","PFP_CMC_PARENT","PFP_CMC_GRANDPARENT"

"PFC-00539","PFI-00539-10013","PFP-C1032200","Y","N","N"
"PFC-00539","PFI-00539-10013","PFP-C1032400","Y","N","N"
"PFC-00541","PFI-00541-10010","PFP-A1045900","Y","N","Y"
"PFC-00541","PFI-00541-10010","PFP-C1028300","Y","N","Y"
"PFC-00541","PFI-00541-10010","PFP-C1028600","Y","N","Y"
"PFC-00541","PFI-00541-10010","PFP-C1028700","Y","N","Y"
"PFC-00541","PFI-00541-10010","PFP-C1028800","Y","N","Y"
"PFC-00541","PFI-00541-10010","PFP-C1028900","Y","N","Y"
"PFC-00541","PFI-00541-10010","PFP-C1029000","Y","N","Y"
"PFC-00541","PFI-00541-10010","PFP-C1029100","Y","N","Y"
"PFC-00542","PFI-00542-10015","PFP-C1030000","Y","N","N"
"PFC-00543","PFI-00543-10014","PFP-C1030900","Y","N","N"
"PFC-00543","PFI-00543-10014","PFP-C1031000","Y","N","N"
"PFC-00543","PFI-00543-10014","PFP-C1033400","Y","N","N"
"PFC-00543","PFI-00543-10014","PFP-C1033500","Y","N","N"
"PFC-00543","PFI-00543-10014","PFP-C1033600","Y","N","N"
"PFC-00543","PFI-00543-10014","PFP-C1033700","Y","N","N"
"PFC-00543","PFI-00543-10014","PFP-C1033800","Y","N","N"
"PFC-00543","PFI-00543-10014","PFP-C1033900","Y","N","N"
"PFC-00046","PFI-00046-10009","PFP-C1027200","Y","N","Y"
"PFC-00113","PFI-00113-10017","PFP-C1029400","Y","N","Y"
"PFC-00124","PFI-00124-00237","PFP-A0198400","Y","N","Y"
"PFC-00263","PFI-00263-10007","PFP-C1026600","Y","N","Y"
"PFC-00263","PFI-00263-10007","PFP-C1026700","Y","N","Y"
"PFC-00263","PFI-00263-10007","PFP-C1026800","Y","N","Y"
"PFC-00263","PFI-00263-10007","PFP-C1026900","Y","N","Y"
"PFC-00263","PFI-00263-10007","PFP-C1027000","Y","N","Y"
"PFC-00263","PFI-00263-10007","PFP-C1027100","Y","N","Y"
"PFC-00263","PFI-00263-10007","PFP-C1027600","Y","N","Y"
"PF_RESEARCH","PFR-3093-200","PFP-C1033200","Y","N","Y"
"PF_RESEARCH","PFR-3492-200","PFP-C1034000","Y","N","Y"
"PF_RESEARCH","PFR-3618-100","PFP-C1028000","Y","N","Y"
"PF_RESEARCH","PFR-3739-100","PFP-C1027400","Y","N","Y"


Not sure if this will help, but wanted to include what the validation syntax is:

Code: Select all

Name Contains PFP-* And ASC_CMC_MODEL_T Equal Y And (PFP_CMC_PARENT Equal N Or PFP_CMC_GRANDPARENT Equal N)


ASC_CMC_MODEL_T is an attribute

To give some clarity on the above lineage, PFP* nodes are level 0 (bottom level), PFI* nodes are the parents to the PFP* nodes, and PFC* nodes are the parents of PFI* nodes.

So, the directory tree/hierarchy branch looks like this:

PFC-XXXXX
...|_PFI-XXXXX-XXXXX
......|_PFP-XXXXXXXX

The validation essentially says, if the node is a PFP* and the attribute ASC_CMC_MODEL_T is set to Y, it's parent (PFI*) and grandparent (PFC*) must also have a value of Y set for ASC_CMC_MODEL_T, if not, fail.

With that said, instead of going into the system and setting them individually, I generate a file format for mass change requests and then perform an import.

Using the above export file with the failed validation, the new format to be uploaded for the changes would look like the following:

Code: Select all

Changeprop,TREX-WorkingVersion,Portfolio,PFI-00539-10013,Custom.ASC_CMC_MODEL_TREX,Y
Changeprop,TREX-WorkingVersion,Portfolio,PFI-00539-10013,Custom.ASC_CMC_MODEL_TREX,Y
Changeprop,TREX-WorkingVersion,Portfolio,PFI-00541-10010,Custom.ASC_CMC_MODEL_TREX,Y
Changeprop,TREX-WorkingVersion,Portfolio,PFI-00541-10010,Custom.ASC_CMC_MODEL_TREX,Y
Changeprop,TREX-WorkingVersion,Portfolio,PFI-00541-10010,Custom.ASC_CMC_MODEL_TREX,Y
Changeprop,TREX-WorkingVersion,Portfolio,PFI-00541-10010,Custom.ASC_CMC_MODEL_TREX,Y
Changeprop,TREX-WorkingVersion,Portfolio,PFI-00541-10010,Custom.ASC_CMC_MODEL_TREX,Y
Changeprop,TREX-WorkingVersion,Portfolio,PFI-00541-10010,Custom.ASC_CMC_MODEL_TREX,Y
Changeprop,TREX-WorkingVersion,Portfolio,PFI-00541-10010,Custom.ASC_CMC_MODEL_TREX,Y
Changeprop,TREX-WorkingVersion,Portfolio,PFI-00541-10010,Custom.ASC_CMC_MODEL_TREX,Y
Changeprop,TREX-WorkingVersion,Portfolio,PFI-00542-10015,Custom.ASC_CMC_MODEL_TREX,Y
Changeprop,TREX-WorkingVersion,Portfolio,PFI-00543-10014,Custom.ASC_CMC_MODEL_TREX,Y
Changeprop,TREX-WorkingVersion,Portfolio,PFI-00543-10014,Custom.ASC_CMC_MODEL_TREX,Y
Changeprop,TREX-WorkingVersion,Portfolio,PFI-00543-10014,Custom.ASC_CMC_MODEL_TREX,Y
Changeprop,TREX-WorkingVersion,Portfolio,PFI-00543-10014,Custom.ASC_CMC_MODEL_TREX,Y
Changeprop,TREX-WorkingVersion,Portfolio,PFI-00543-10014,Custom.ASC_CMC_MODEL_TREX,Y
Changeprop,TREX-WorkingVersion,Portfolio,PFI-00543-10014,Custom.ASC_CMC_MODEL_TREX,Y
Changeprop,TREX-WorkingVersion,Portfolio,PFI-00543-10014,Custom.ASC_CMC_MODEL_TREX,Y
Changeprop,TREX-WorkingVersion,Portfolio,PFI-00543-10014,Custom.ASC_CMC_MODEL_TREX,Y
Changeprop,TREX-WorkingVersion,Portfolio,PFI-00046-10009,Custom.ASC_CMC_MODEL_TREX,Y
Changeprop,TREX-WorkingVersion,Portfolio,PFI-00113-10017,Custom.ASC_CMC_MODEL_TREX,Y
Changeprop,TREX-WorkingVersion,Portfolio,PFI-00124-00237,Custom.ASC_CMC_MODEL_TREX,Y
Changeprop,TREX-WorkingVersion,Portfolio,PFI-00263-10007,Custom.ASC_CMC_MODEL_TREX,Y
Changeprop,TREX-WorkingVersion,Portfolio,PFI-00263-10007,Custom.ASC_CMC_MODEL_TREX,Y
Changeprop,TREX-WorkingVersion,Portfolio,PFI-00263-10007,Custom.ASC_CMC_MODEL_TREX,Y
Changeprop,TREX-WorkingVersion,Portfolio,PFI-00263-10007,Custom.ASC_CMC_MODEL_TREX,Y
Changeprop,TREX-WorkingVersion,Portfolio,PFI-00263-10007,Custom.ASC_CMC_MODEL_TREX,Y
Changeprop,TREX-WorkingVersion,Portfolio,PFI-00263-10007,Custom.ASC_CMC_MODEL_TREX,Y
Changeprop,TREX-WorkingVersion,Portfolio,PFI-00263-10007,Custom.ASC_CMC_MODEL_TREX,Y
Changeprop,TREX-WorkingVersion,Portfolio,PFR-3093-200,Custom.ASC_CMC_MODEL_TREX,Y
Changeprop,TREX-WorkingVersion,Portfolio,PFR-3492-200,Custom.ASC_CMC_MODEL_TREX,Y
Changeprop,TREX-WorkingVersion,Portfolio,PFR-3618-100,Custom.ASC_CMC_MODEL_TREX,Y
Changeprop,TREX-WorkingVersion,Portfolio,PFR-3739-100,Custom.ASC_CMC_MODEL_TREX,Y
Changeprop,TREX-WorkingVersion,Portfolio,PFC-00539,Custom.ASC_CMC_MODEL_TREX,Y
Changeprop,TREX-WorkingVersion,Portfolio,PFC-00539,Custom.ASC_CMC_MODEL_TREX,Y
Changeprop,TREX-WorkingVersion,Portfolio,PFC-00542,Custom.ASC_CMC_MODEL_TREX,Y
Changeprop,TREX-WorkingVersion,Portfolio,PFC-00543,Custom.ASC_CMC_MODEL_TREX,Y
Changeprop,TREX-WorkingVersion,Portfolio,PFC-00543,Custom.ASC_CMC_MODEL_TREX,Y
Changeprop,TREX-WorkingVersion,Portfolio,PFC-00543,Custom.ASC_CMC_MODEL_TREX,Y
Changeprop,TREX-WorkingVersion,Portfolio,PFC-00543,Custom.ASC_CMC_MODEL_TREX,Y
Changeprop,TREX-WorkingVersion,Portfolio,PFC-00543,Custom.ASC_CMC_MODEL_TREX,Y
Changeprop,TREX-WorkingVersion,Portfolio,PFC-00543,Custom.ASC_CMC_MODEL_TREX,Y
Changeprop,TREX-WorkingVersion,Portfolio,PFC-00543,Custom.ASC_CMC_MODEL_TREX,Y
Changeprop,TREX-WorkingVersion,Portfolio,PFC-00543,Custom.ASC_CMC_MODEL_TREX,Y


So, to generate in import format, I take all PFI* nodes and PFC* nodes with a value of "N" from the original export file, compile them as shown above, and import.

My question is, is there a way to parse my original export and generate a new file like above? The only thing that will change over time would be the node names (column 4) everything else is static.

Please note, if it's easier, you can just take the first two columns from the original export and use that. There's no need to include syntax to only pull out the PFI* or PFC* with a value of "N". Resetting a node to Y if it's already set to Y has no negative impact and I assume would be easier to build this script that way.

Please let me know if you need any additional clarity.

Thanks!
Top
Squashman
Expert
Posts: 4486
Joined: 23 Dec 2011 13:59

Re: Parse comma delimited export into new format?

#2 Post by Squashman » 14 Sep 2016 08:21

Maybe I am misunderstanding your question but this seems like a simple one line FOR /F command.
What are you having problems with?
Top
SIMMS7400
Posts: 546
Joined: 07 Jan 2016 07:47

Re: Parse comma delimited export into new format?

#3 Post by SIMMS7400 » 14 Sep 2016 08:45

I was having trouble trying to ignore hte first 2 lines of my export file.

I've been able to get it, but I believe the code can be improved.

Code: Select all

@echo off
echo Removing...
for /f "skip=2 delims=*" %%a in (Input.csv) do (
echo %%a >>newfile.txt
) >nul
echo Lines removed, rebuilding file...
xcopy newfile.txt Input.csv /y >nul
echo File rebuilt, removing temporary files
del newfile.txt /f /q >nul

FOR /f "tokens=1 delims=," %%B in (Input.csv) do @echo Changeprop,TREX-WorkingVersion,Portfolio,%%B,Custom.ASC_CMC_MODEL_TREX,Y  >> DRM_Action_Script.csv
FOR /f "tokens=2 delims=," %%B in (Input.csv) do @echo Changeprop,TREX-WorkingVersion,Portfolio,%%B,Custom.ASC_CMC_MODEL_TREX,Y  >> DRM_Action_Script.csv

Top
Squashman
Expert
Posts: 4486
Joined: 23 Dec 2011 13:59

Re: Parse comma delimited export into new format?

#4 Post by Squashman » 14 Sep 2016 08:59

Code: Select all

FOR /f "skip=2 tokens=1-6 delims=," %%B in (Input.csv) do (
   echo Changeprop,TREX-WorkingVersion,Portfolio,%%B,Custom.ASC_CMC_MODEL_TREX,Y  >> DRM_Action_Script.csv
   echo Changeprop,TREX-WorkingVersion,Portfolio,%%C,Custom.ASC_CMC_MODEL_TREX,Y  >> DRM_Action_Script.csv
)
Top
SIMMS7400
Posts: 546
Joined: 07 Jan 2016 07:47

Re: Parse comma delimited export into new format?

#5 Post by SIMMS7400 » 14 Sep 2016 08:59

I got it a little cleaner...

Code: Select all

@echo off

More +2 Input.csv > NewFile.csv
FOR /f "tokens=1 delims=," %%B in (NewFile.csv) do @echo Changeprop,TREX-WorkingVersion,Portfolio,%%B,Custom.ASC_CMC_MODEL_TREX,Y  >> DRM_Action_Script.csv
FOR /f "tokens=2 delims=," %%B in (NewFile.csv) do @echo Changeprop,TREX-WorkingVersion,Portfolio,%%B,Custom.ASC_CMC_MODEL_TREX,Y  >> DRM_Action_Script.csv 
)
Top
SIMMS7400
Posts: 546
Joined: 07 Jan 2016 07:47

Re: Parse comma delimited export into new format?

#6 Post by SIMMS7400 » 14 Sep 2016 09:01

Squash -

Thank you, that works great! Much appreciated!
Top
Compo
Posts: 600
Joined: 21 Mar 2014 08:50

Re: Parse comma delimited export into new format?

#7 Post by Compo » 14 Sep 2016 09:05

If your newly created DRM_Action_Script.csv doesn't have to be in a specific order you could even let it do the N validation of your NewFile.csv for you:

Code: Select all

@Echo Off
SetLocal EnableExtensions DisableDelayedExpansion

(Set CTP=Changeprop,TREX-WorkingVersion,Portfolio,)
(Set CY=,Custom.ASC_CMC_MODEL_TREX,Y)

>DRM_Action_Script.csv (For /F Usebackq^ Tokens^=^1^,3^,9^,11^ Delims^=^" %%A In (
   "NewFile.csv") Do (If %%C Equ N Echo=%CTP%%%B%CY%
   If %%D Equ N Echo=%CTP%%%A%CY%))
EndLocal
Exit/B
Top
Post Reply

Return to “DOS Batch Forum”