VB Script Mods Needed

Hi,

I need to modify existing vbscript to include additional functionality:

Modify source file:
1 - Format of the 6th field header needs to be in MMM-YY format. Ex)  JAN-15
2 - For the first field 'Company', if there isn't 3 numbers/chars, then prefix with zero's until three numbers/chars is reached.  Ex) if it's '0' then it needs to be '000'.  If it's '12' then it needs to be '012'.
3 - For the second field 'Cost_Center', if there isn't 4 numbers/chars, then prefix with zero's until four numbers/chars is reached.  Ex) if it's '0' then it needs to be '0000'.  If it's '12' then it needs to be '0012'.

Output File Changes:
1-Remove the header row
2-12 files generated with naming convention:  HAForecast_MMM-YY.csv   Ex)  HAForecast_JAN-15.csv
Split-Source.csv
Split.vbs
elwayisgodAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Robberbaron (robr)Commented:
a couple of items...
1, formatting the input date can be done, just not simple with VBS.
2. with CSV, upon import into excel or most other apps, the preceeding 00 will be dropped in any case as it is considered a number.  So adding them has no effect.  Only alternate is to force the item to be parsed as a string by surrounding in quotes, is this what u want ?
3. removing header is simple and you should have done this yourself as I consider this a Help site, not a Do-it-for-me-for-free site.
0
elwayisgodAuthor Commented:
In regards to each point:

1 - Comes in backwards in source file and they won't change source file.  Not sure how to switch it to MMM-YY
2 - Not importing into Excel thus not an issue.  The tool that uses the file will not drop the leading zero's... allegedly :)
3 - I'm not a VBA programmer at all, but I was asked to help resolve.  Sorry for infringing.  I did offer up 500pts and am willing to break this into 5 questions at 500 ea or hire someone for reasonable fee.

I do appreciate all help as this is NOT my wheelhouse.
0
Robberbaron (robr)Commented:
try this version... but still need confirmation of formatting the numbers.
'----------------------------------------------------------------------------------------------------
' Created by:	Edgewater Ranzal
' Created On:	10/24/2014
' Purpose:	Parse the HA Forecast File
'----------------------------------------------------------------------------------------------------
' v2 April 2015   robberbaron @ ee

'##############################
' Main Execution
'##############################

'Ensure the right number of command line variables was passed.
set objArgs = WScript.Arguments

'Exit if Count does not equal 3
If objArgs.Count <> 3 Then
	'Warn the user that 3 objects was not encountered
	WScript.Echo "Must contain 3 values (FileName, SourcePath, TargetPath)"
Else
	'Write out the 3 values
	wScript.Echo "File Name = " & WScript.Arguments.Item(0)
	strFileName = WScript.Arguments.Item(0) 
	wScript.Echo "Source Path = " & WScript.Arguments.Item(1)
	strSourcePath = WScript.Arguments.Item(1)
	wScript.Echo "Target Path = " & WScript.Arguments.Item(2)
	strTargetPath = WScript.Arguments.Item(2)

	'Create a file system object
	Set FSO = CreateObject("Scripting.FileSystemObject")
	
	'Append the \ to the SourcePath
	If Right(strSourcePath, 1) <> "\" Then
		strSourcePath = strSourcePath & "\"
	End If
	
	'Append the \ to the TargetPath
	If Right(strTargetPath, 1) <> "\" Then
		strTargetPath = strTargetPath & "\"
	End If
		
	'Run if the file exists
	If FSO.FileExists(strSourcePath & strFileName) Then
		'************************************
		' Create Files from Header
		'************************************
		
		'Open the Source file for reading
		Set strFileRead = FSO.OpenTextFile(strSourcePath & strFileName)
		
		'Read the header line
		strLine = strFileRead.ReadLine
		
		'Convert header to an Array
		arrLineHeader = Split(strLine, ",")
		
		
		
		'Loop through the header and create alternative text files
		For x = 7 to uBound(arrLineHeader)
		    'v2 rearrange header to be MMM-YY
		    dt1 = arrLineHeader(x)
			dt2 = mid(dt1,3) & left(dt1,2)
			Execute(CStr("Set strFileWrite" & x & " = FSO.CreateTextFile(""" & strTargetPath & "HAForecast_" & dt2 & ".csv"")"))
		Next

		'************************************
		' Create Text File Header
		'************************************
		'Write it to their respective files		 
		For x = 7 to UBound(arrLineHeader)
			strLine = arrLineHeader(2)
			strLine = strLine & "," & arrLineHeader(3)
			strLine = strLine & "," & arrLineHeader(5)
			strLine = strLine & "," & arrLineHeader(6)
			strLine = strLine & "," & arrLineHeader(1)
			strLine = strLine & "," & arrLineHeader(x)
		    'v2  dont write header
			'Execute(CStr("strFileWrite" & x & ".WriteLine strLine"))
		Next		
		
		'************************************
		' Create Text File w/ Data
		'************************************
		Do Until strFileRead.AtEndOfStream
			'Read the Line
			strLine = strFileRead.ReadLine
			
			'Convert it to an Array
			arrLineData = Split(strLine, ",")
			
			'Write it to their respective files		 
			For x = 7 to UBound(arrLineHeader)
				strLine = arrLineData(2)
				strLine = strLine & "," & right("000" & arrLineData(3),3)       'v2 pad zeros
				strLine = strLine & "," & right("0000" & arrLineData(5),4)      'v2 pad zeros
				strLine = strLine & "," & arrLineData(6)
				strLine = strLine & "," & arrLineData(1)
				strLine = strLine & "," & arrLineData(x)
			
				Execute(CStr("strFileWrite" & x & ".WriteLine strLine"))
			Next
		 Loop	 
	
	Else
		WScript.Echo "No Source File Exists."
	
	End If
	
	'Destroy Objects
	For x = 7 to uBound(arrLineHeader)
		Execute(CStr("strFileWrite" & x & ".Close"))
		Execute(CStr("Set strFileWrite" & x & " = Nothing"))
	Next
	strFileRead.Close
	set strFileRead = Nothing
	Set FSO = Nothing

End If

Open in new window

0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

elwayisgodAuthor Commented:
This looks really good.  Discovered a problem with source file wasn't aware of causing issue.  Not sure if easy.

The fields 4 and 6 can also have 'Default' in them instead of numbers.  Any way to ignore if 'Default'?

Example Row of data:
W_Live Forecast_(10-06-2014),CF DIV / DISTR,Default,Default,Default,Default,Default,0,0,1,0,0,1,0,0,6,0,0,1

Jan Output of that row:
Default,ult,ault,Default,CF DIV / DISTR,0

Output Filenames are off a tad:
HAForecast_-Apr14.csv
HAForecast_Aug14.csv
etc.  

Except Jan is weird:
HAForecast__N-14JA.csv
0
elwayisgodAuthor Commented:
My bad.  Disregard the 'Jan is weird' comment.  Source file error as I was tinkering.  I apologize.  Also Aug is HAForecast_-Aug14.csv not as listed above.  Again apologies.  Looking at code to see if I can figure out that part.
0
Robberbaron (robr)Commented:
what do you want in place of default ?  Drop the whole line if the first code is default ?

easy fix for the date line...
0
elwayisgodAuthor Commented:
Blind Squirrel finds a nut :)    This put's it in right format :)    Or is there a 'Best Practice' method? Had to get rid of the leading '-'

dt1 = arrLineHeader(x)
dt2 = mid(dt1,3) & "-" & left(dt1,2)
dt3 = right(dt2,6)
Execute(CStr("Set strFileWrite" & x & " = FSO.CreateTextFile(""" & strTargetPath & "HAForecast_" & dt3 & ".csv"")"))
0
elwayisgodAuthor Commented:
If it's Default in field 4 it becomes '000'.  If it'sdefault in field 6 it becomes 'NOCO'.

Does that make sense?
0
Robberbaron (robr)Commented:
yep.
		    dt1 = arrLineHeader(x)  'MMM-YY format
			dt2 = mid(dt1,4) & "-" & left(dt1,2)  'YYY-MM format

Open in new window

in the date field, dont need the last part i think.

i have rearranged a little to speed up slightly.  try it out
'----------------------------------------------------------------------------------------------------
' Created by:	Edgewater Ranzal
' Created On:	10/24/2014
' Purpose:	Parse the HA Forecast File
'----------------------------------------------------------------------------------------------------
' v2 April 2015   robberbaron @ ee

'##############################
' Main Execution
'##############################

'Ensure the right number of command line variables was passed.
set objArgs = WScript.Arguments

'Exit if Count does not equal 3
If objArgs.Count <> 3 Then
	'Warn the user that 3 objects was not encountered
	WScript.Echo "Must contain 3 values (FileName, SourcePath, TargetPath)"
Else
	'Write out the 3 values
	wScript.Echo "File Name = " & WScript.Arguments.Item(0)
	strFileName = WScript.Arguments.Item(0) 
	wScript.Echo "Source Path = " & WScript.Arguments.Item(1)
	strSourcePath = WScript.Arguments.Item(1)
	wScript.Echo "Target Path = " & WScript.Arguments.Item(2)
	strTargetPath = WScript.Arguments.Item(2)

	'Create a file system object
	Set FSO = CreateObject("Scripting.FileSystemObject")
	
	'Append the \ to the SourcePath
	If Right(strSourcePath, 1) <> "\" Then
		strSourcePath = strSourcePath & "\"
	End If
	
	'Append the \ to the TargetPath
	If Right(strTargetPath, 1) <> "\" Then
		strTargetPath = strTargetPath & "\"
	End If
		
	'Run if the file exists
	If FSO.FileExists(strSourcePath & strFileName) Then
		'************************************
		' Create Files from Header
		'************************************
		
		'Open the Source file for reading
		Set strFileRead = FSO.OpenTextFile(strSourcePath & strFileName)
		
		'Read the header line
		strLine = strFileRead.ReadLine
		
		'Convert header to an Array
		arrLineHeader = Split(strLine, ",")
		
		
		
		'Loop through the header and create alternative text files
		For x = 7 to uBound(arrLineHeader)
		    'v2 rearrange date header to be MMM-YY
		    dt1 = arrLineHeader(x)  'MMM-YY format
			dt2 = mid(dt1,4) & "-" & left(dt1,2)  'YYY-MM format
			Execute(CStr("Set strFileWrite" & x & " = FSO.CreateTextFile(""" & strTargetPath & "HAForecast_" & dt2 & ".csv"")"))
		Next

		'************************************
		' Create Text File Header
		'************************************
		'Write it to their respective files		 
		For x = 7 to UBound(arrLineHeader)
			strLine = arrLineHeader(2)
			strLine = strLine & "," & arrLineHeader(3)
			strLine = strLine & "," & arrLineHeader(5)
			strLine = strLine & "," & arrLineHeader(6)
			strLine = strLine & "," & arrLineHeader(1)
			strLine = strLine & "," & arrLineHeader(x)
		    'v2  dont write header
			'Execute(CStr("strFileWrite" & x & ".WriteLine strLine"))
		Next		
		
		'************************************
		' Create Text File w/ Data
		'************************************
		Do Until strFileRead.AtEndOfStream
			'Read the Line
			strLine = strFileRead.ReadLine
			
			'Convert it to an Array
			arrLineData = Split(strLine, ",")

			xAccount  = arrLineData(1) 'the 2rd field
			xCompany = arrLineData(2) 'the 3rd field
			xCost_Center = arrLineData(3) 'the 4th field
			xFuture2 = arrLineData(4) 'the 5th field
			xPlant = arrLineData(5) 'the 6th field
			xProducer = arrLineData(6) 'the 7th field
			
			'check if Cost_Center is tagged Default
			if(xCost_Center="Default") then
				xCost_Center = "000"
			end if
			'check if Producer is tagged Default
			if(xPlant="Default") then
				xPlant = "NOCO"
			end if		
			
			'Write it to their respective files		 
			For x = 7 to UBound(arrLineHeader)
				strLine = xCompany
				strLine = strLine & "," & right("000" & xCost_Center,3)       'v2 pad zeros
				strLine = strLine & "," & right("0000" & xPlant,4)      'v2 pad zeros
				strLine = strLine & "," & xProducer
				strLine = strLine & "," & xAccount
				strLine = strLine & "," & arrLineData(x)

				Execute(CStr("strFileWrite" & x & ".WriteLine strLine"))
			Next
		 Loop	 
	
	Else
		WScript.Echo "No Source File Exists."
	
	End If
	
	'Destroy Objects
	For x = 7 to uBound(arrLineHeader)
		Execute(CStr("strFileWrite" & x & ".Close"))
		Execute(CStr("Set strFileWrite" & x & " = Nothing"))
	Next
	strFileRead.Close
	set strFileRead = Nothing
	Set FSO = Nothing

End If

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Robberbaron (robr)Commented:
my code was ok, but comment incorrect.  and better to limit character grabbed to 3.
		    'v2 rearrange date header to be MMM-YY
		    dt1 = arrLineHeader(x)  'YY-MMM format
			dt2 = mid(dt1,4,3) & "-" & left(dt1,2)  'MMM-YY format

Open in new window


check the Apr-14 , look for the NOCO lines.   is the Default for company ok also ?
0
Robberbaron (robr)Commented:
further tidy with more comments on special cases.

'----------------------------------------------------------------------------------------------------
' Created by:	Edgewater Ranzal
' Created On:	10/24/2014
' Purpose:	Parse the HA Forecast File
'----------------------------------------------------------------------------------------------------
' v2 April 2015   robberbaron @ ee
' v3  April 2015  rearranged loop a little

'##############################
' Main Execution
'##############################

'Ensure the right number of command line variables was passed.
set objArgs = WScript.Arguments

'Exit if Count does not equal 3
If objArgs.Count <> 3 Then
	'Warn the user that 3 objects was not encountered
	WScript.Echo "Must contain 3 values (FileName, SourcePath, TargetPath)"
Else
	'Write out the 3 values
	wScript.Echo "File Name = " & WScript.Arguments.Item(0)
	strFileName = WScript.Arguments.Item(0) 
	wScript.Echo "Source Path = " & WScript.Arguments.Item(1)
	strSourcePath = WScript.Arguments.Item(1)
	wScript.Echo "Target Path = " & WScript.Arguments.Item(2)
	strTargetPath = WScript.Arguments.Item(2)

	'Create a file system object
	Set FSO = CreateObject("Scripting.FileSystemObject")
	
	'Append the \ to the SourcePath
	If Right(strSourcePath, 1) <> "\" Then
		strSourcePath = strSourcePath & "\"
	End If
	
	'Append the \ to the TargetPath
	If Right(strTargetPath, 1) <> "\" Then
		strTargetPath = strTargetPath & "\"
	End If
		
	'Run if the file exists
	If FSO.FileExists(strSourcePath & strFileName) Then
		'************************************
		' Create Files from Header
		'************************************
		
		'Open the Source file for reading
		Set strFileRead = FSO.OpenTextFile(strSourcePath & strFileName)
		
		'Read the header line
		strLine = strFileRead.ReadLine
		
		'Convert header to an Array
		arrLineHeader = Split(strLine, ",")
		
		
		
		'Loop through the header and create alternative text files
		For x = 7 to uBound(arrLineHeader)
		    'v2 rearrange date header to be MMM-YY
		    dt1 = arrLineHeader(x)  'YY-MMM format
			dt2 = mid(dt1,4,3) & "-" & left(dt1,2)  'MMM-YY format
			Execute(CStr("Set strFileWrite" & x & " = FSO.CreateTextFile(""" & strTargetPath & "HAForecast_" & dt2 & ".csv"")"))
		Next

		'************************************
		' Create Text File Header
		'************************************
		'Write it to their respective files		 
		For x = 7 to UBound(arrLineHeader)
			strLine = arrLineHeader(2)
			strLine = strLine & "," & arrLineHeader(3)
			strLine = strLine & "," & arrLineHeader(5)
			strLine = strLine & "," & arrLineHeader(6)
			strLine = strLine & "," & arrLineHeader(1)
			strLine = strLine & "," & arrLineHeader(x)
		    'v2  dont write header
			'Execute(CStr("strFileWrite" & x & ".WriteLine strLine"))
		Next		
		
		'************************************
		' Create Text File w/ Data
		'************************************
		Do Until strFileRead.AtEndOfStream
			'Read the Line
			strLine = strFileRead.ReadLine
			
			'Convert it to an Array
			arrLineData = Split(strLine, ",")

			xAccount  = arrLineData(1) 'the 2rd field
			xCompany = arrLineData(2) 'the 3rd field
			xCost_Center = arrLineData(3) 'the 4th field
			xFuture2 = arrLineData(4) 'the 5th field
			xPlant = arrLineData(5) 'the 6th field
			xProducer = arrLineData(6) 'the 7th field
			
			'check if Cost_Center is tagged Default
			if(xCost_Center="Default") then
				xCost_Center = "000"
			 else
				xCost_Center = right("000" & xCost_Center,3)      'v2 pad zeros
			end if
			
			'check if xPlant is tagged Default
			if(xPlant="Default") then
				xPlant = "NOCO"
			 else
				xPlant = right("0000" & xPlant,4) 'v2 pad zeros
			end if				
	
			
			'Write it to their respective files		 
			For x = 7 to UBound(arrLineHeader)
				strLine = xCompany  'start the output
				strLine = strLine & "," & xCost_Center 
				strLine = strLine & "," & xPlant     
				strLine = strLine & "," & xProducer
				strLine = strLine & "," & xAccount
				strLine = strLine & "," & arrLineData(x)  'add the month field

				Execute(CStr("strFileWrite" & x & ".WriteLine strLine"))
			Next
		 Loop	 
	
	Else
		WScript.Echo "No Source File Exists."
	
	End If
	
	'Destroy Objects
	For x = 7 to uBound(arrLineHeader)
		Execute(CStr("strFileWrite" & x & ".Close"))
		Execute(CStr("Set strFileWrite" & x & " = Nothing"))
	Next
	strFileRead.Close
	set strFileRead = Nothing
	Set FSO = Nothing

End If

Open in new window

0
elwayisgodAuthor Commented:
Works great and is very fast.  Greatly apprciate the help!!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.