Solved

VB Script - Find and Replace in pipe delimited file

Posted on 2013-01-11
12
602 Views
Last Modified: 2013-01-17
Trying to get our source files in best possible shape so they load to database faster.  I have a text file that is pipe delimited.  It has 14 fields.  First 4 are metadata and last 10 are data. Here's what I need.

For each field:

Field 1 - Nothing - Stays as is
Field 2 - Change 'Fiscal Rcpt' to 'Fiscal' and 'Fiscal Sales' to 'Fiscal'.  So 'Fiscal Sales Aug' would  be 'Fiscal Aug' and so on.
Field 3 - Nothing - Stays as is
Field 4 - Change '2012' to '2012_New'
Field 5 to Field 14 - Change '0' to #Missing and/or '0.00' to #Missing.  These have to be exact matches only.  So 50 cant become 5#Missing.  Only '0' and '0.00' can be changed to #Missing.

This has to run as fast as possible as this is whole point as we are bumping up against SLA already.  I have a bunch more of these so some way where I can change a few things around for a different file would be helpful too.

So a way where I can identify the filename to apply this to and then the resulting file would just be prefixed with 'LD_'.   That would be great.

I appreciate all help you gurus have been providing.
0
Comment
Question by:elwayisgod
  • 7
  • 3
  • 2
12 Comments
 

Author Comment

by:elwayisgod
ID: 38768366
Here's an example of before and after files.
Before.txt
After.txt
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 38768676
Try this.  It's VBScript.



Dim fso, tsIn, tsOut, arr, TheLine, Counter

Set fso = CreateObject("Scripting.FileSystemObject")
Set tsIn = fso.OpenTextFile("c:\oldfile.txt")
Set tsOut = fso.CreateTextFile("c:\newfile.txt", True)

tsOut.WriteLine tsIn.Readline

Do Until tsIn.AtEndOfStream
    TheLine = tsIn.ReadLine
    If TheLine <> "" Then
        arr = Split(TheLine, "|")
        arr(1) = Replace(Replace(arr(1), "Fiscal Rcpt", "Fiscal"), "Fiscal Sales", "Fiscal")
        If arr(3) = "2012" Then arr(3) = "2012_New"
        For Counter = 4 To 13
            If arr(Counter) = "0" Or arr(Counter) = "0.00" Then arr(Counter) = "#Missing"
        Next
        tsOut.WriteLine Join(arr, "|")
    Else
        tsOut.WriteLine
    End If
Loop

tsIn.Close
tsOut.Close
Set tsIn = Nothing
Set tsOut = Nothing
Set fso = Nothing

Open in new window

0
 

Author Comment

by:elwayisgod
ID: 38768862
This looks really good.  You want to talk offline about side work.  Have a many (10) more.
0
 
LVL 9

Expert Comment

by:shorvath
ID: 38768902
took the long way around but added a dialog box and the ability to configure each field


Const ForReading = 1
Const ForWriting = 2

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set ObjCDG = CreateObject("UserAccounts.CommonDialog")

ObjCDG.Filter = "Text Documents|*.txt|All Files|*.*"
ObjCDG.FilterIndex = 3
ObjCDG.InitialDir = "C:\temp\Test\"
InitCDG = ObjCDG.ShowOpen

If InitCDG = False Then
    Wscript.Echo "Script Error: Please select a file!"
    Wscript.Quit
End If

Set objFileIN = objFSO.OpenTextFile(ObjCDG.FileName, ForReading)
Set objFileOUT = objFSO.OpenTextFile(ObjCDG.InitialDir & "LD_" & objFSO.GetFileName(ObjCDG.FileName), ForWriting, True)
	

Do Until objFileIN.AtEndOfStream
	strText = objFileIN.ReadLine
	aData = Split(strText,"|")

	' mdse_sty   Field 1 - Nothing - Stays as Is
	strNewText = aData(0) & "|"
	
	' fis_sls_mo  Field 2 - Change 'Fiscal Rcpt' to 'Fiscal' and 'Fiscal Sales' to 'Fiscal'.
	If instr(1, adata(1), "Fiscal Rcpt") > 0 Then
		strNewText = strNewText & Replace(adata(1), "Fiscal Rcpt", "Fiscal") & "|"
	ElseIf instr(1, adata(1), "Fiscal Sales") > 0 Then
		strNewText = strNewText & Replace(adata(1), "Fiscal Sales", "Fiscal") & "|"	
	End If	
		
	' mdse_typ   Field 3 - Nothing - Stays as Is
	strNewText = strNewText & aData(2) & "|"
	
	' fis_sls_yr   Field 4 - Change '2012' to '2012_New'
	If adata(3)= "2012" Then
		strNewText = strNewText & Replace(adata(3), "2012", "2012_New") & "|"
	Else	
		strNewText = strNewText & aData(3) & "|"
	End If
	
	'Field 5 to Field 14 - Change '0' to #Missing and/or '0.00' to #Missing. 
	' unit_sls_ttl
	If adata(4) = "0" Then
		strNewText = strNewText & "#Missing" & "|"
	ElseIf adata(4) = "0.00" Then
		strNewText = strNewText & "#Missing" & "|"
	Else
		strNewText = strNewText & adata(4) & "|"
	End If	
	
	' rtl_sls_ttl_local
	If adata(5) = "0" Then
		strNewText = strNewText & "#Missing" & "|"
	ElseIf adata(5) = "0.00" Then
		strNewText = strNewText & "#Missing" & "|"
	Else
		strNewText = strNewText & adata(5) & "|"
	End If	
		
	' unit_sls_regpromo
	If adata(6) = "0" Then
		strNewText = strNewText & "#Missing" & "|"
	ElseIf adata(6) = "0.00" Then
		strNewText = strNewText & "#Missing" & "|"
	Else
		strNewText = strNewText & adata(6) & "|"
	End If	
		
	' rtl_sls_regpromo_local
	If adata(7) = "0" Then
		strNewText = strNewText & "#Missing" & "|"
	ElseIf adata(7) = "0.00" Then
		strNewText = strNewText & "#Missing" & "|"
	Else
		strNewText = strNewText & adata(7) & "|"
	End If	
		
	' unit_sls_mkd
	If adata(8) = "0" Then
		strNewText = strNewText & "#Missing" & "|"
	ElseIf adata(8) = "0.00" Then
		strNewText = strNewText & "#Missing" & "|"
	Else
		strNewText = strNewText & adata(8) & "|"
	End If	
		
	' rtl_sls_mkd_local
	If adata(9) = "0" Then
		strNewText = strNewText & "#Missing" & "|"
	ElseIf adata(9) = "0.00" Then
		strNewText = strNewText & "#Missing" & "|"
	Else
		strNewText = strNewText & adata(9) & "|"
	End If	
		
	' cst_sls_ttl_local
	If adata(10) = "0" Then
		strNewText = strNewText & "#Missing" & "|"
	ElseIf adata(10) = "0.00" Then
		strNewText = strNewText & "#Missing" & "|"
	Else
		strNewText = strNewText & adata(10) & "|"
	End If	
		
	' cst_sls_regpromo_local
	If adata(11) = "0" Then
		strNewText = strNewText & "#Missing" & "|"
	ElseIf adata(11) = "0.00" Then
		strNewText = strNewText & "#Missing" & "|"
	Else
		strNewText = strNewText & adata(11) & "|"
	End If	
		
	' cst_sls_mkd_local
	If adata(12) = "0" Then
		strNewText = strNewText & "#Missing" & "|"
	ElseIf adata(12) = "0.00" Then
		strNewText = strNewText & "#Missing" & "|"
	Else
		strNewText = strNewText & adata(12) & "|"
	End If	
		
	' rec_order
	If adata(13) = "0" Then
		strNewText = strNewText & "#Missing" & "|"
	ElseIf adata(13) = "0.00" Then
		strNewText = strNewText & "#Missing" & "|"
	Else
		strNewText = strNewText & adata(13) & "|"
	End If	
		
	
	objFileOUT.WriteLine strNewText

Loop

objFileIN.Close
objFileOUT.Close

Open in new window

0
 

Author Comment

by:elwayisgod
ID: 38768921
In the same script is it possible to add this logic too, but the output file is a different name.  Field 2 and 4 slightly different.  I can make it a different question too.

Source File is same file:

Field 1 - Nothing - Stays as is
Field 2 - Change 'Fiscal Sales Dec' to 'LY Fiscal Dec' and 'Fiscal Sales Jan' to 'LY Fiscal Jan'.  
Field 3 - Nothing - Stays as is
Field 4 - Increase each value by 1 except 2012. Have to start at 2018 and work way back too, I think :)

2018 is 2019
2017 is 2018
2016 is 2017
2015 is 2016
2014 is 2015
2013 is 2014
2012 is 2013_New
2011 is 2012
2010 is 2011
2009 is 2010
2008 is 2009
2007 is 2008
2006 is 2007
2005 is 2006
2004 is 2005
2003 is 2004
 

Field 5 to Field 14 - Change '0' to #Missing and/or '0.00' to #Missing.  These have to be exact matches only.  So 50 cant become 5#Missing.  Only '0' and '0.00' can be changed to #Missing.
0
 

Author Comment

by:elwayisgod
ID: 38768928
Shorvath.  This has to run without any input from a user.  It will be a scheduled task.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 9

Expert Comment

by:shorvath
ID: 38768942
OK, so how will the original files be remove from the folder?  Sholud the script put the 'LD_' prefix in front of the new file and delete the old one?
0
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 38768980
Should be easy enough...


Dim fso, tsIn, tsOut1, tsOut2, arr1, arr2, TheLine, Counter

Set fso = CreateObject("Scripting.FileSystemObject")
Set tsIn = fso.OpenTextFile("c:\oldfile.txt")
Set tsOut1 = fso.CreateTextFile("c:\newfile1.txt", True)
Set tsOut2 = fso.CreateTextFile("c:\newfile2.txt", True)

TheLine = tsIn.Readline
tsOut1.WriteLine TheLine
tsOut2.WriteLine TheLine

Do Until tsIn.AtEndOfStream
    TheLine = tsIn.ReadLine
    If TheLine <> "" Then
        arr1 = Split(TheLine, "|")
        arr2 = Split(TheLine, "|")
        arr1(1) = Replace(Replace(arr1(1), "Fiscal Rcpt", "Fiscal"), "Fiscal Sales", "Fiscal")
        arr2(1) = Replace(Replace(arr2(1), "Fiscal Sales Dec", "LY Fiscal Dec"), "Fiscal Sales Jan", "LY Fiscal Jan")
        If arr1(3) = "2012" Then arr1(3) = "2012_New"
        If CLng(arr2(3)) = 2012 Then
            arr2(3) = "2013_New"
        Else
            arr2(3) = CStr(CLng(arr2(3)) + 1)
        End If
        For Counter = 4 To 13
            If arr1(Counter) = "0" Or arr1(Counter) = "0.00" Then arr1(Counter) = "#Missing"
            If arr2(Counter) = "0" Or arr2(Counter) = "0.00" Then arr2(Counter) = "#Missing"
        Next
        tsOut1.WriteLine Join(arr1, "|")
        tsOut2.WriteLine Join(arr2, "|")
    Else
        tsOut1.WriteLine
        tsOut2.WriteLine
    End If
Loop

tsIn.Close
tsOut1.Close
tsOut2.Close
Set tsIn = Nothing
Set tsOut1 = Nothing
Set tsOut2 = Nothing
Set fso = Nothing

Open in new window


If you would like to contact me "off line", my profile has contact information :)
0
 

Author Comment

by:elwayisgod
ID: 38768982
no.  Original files stay there.  Scripts just create two new files. in same directory.  Mathews script is very fast and I like it alot.  Just needs tweaked a tad
0
 
LVL 9

Expert Comment

by:shorvath
ID: 38769027
try this

Const ForReading = 1
Const ForWriting = 2

Set objFSO = CreateObject("Scripting.FileSystemObject")


sFolder = Wscript.Arguments.Item(0)
If sFolder = "" Then
    Wscript.Echo "No Folder parameter was passed"
    Wscript.Quit
End If      
Set folder = objFSO.GetFolder(sFolder)
Set files = folder.Files
 
 

 
For each folderIdx In files

 
      Set objFileIN = objFSO.OpenTextFile(sFolder & objFSO.GetFileName(folderIdx.Name), ForReading)
      Set objFileOUT = objFSO.OpenTextFile(sFolder & "LD_" & objFSO.GetFileName(folderIdx.Name), ForWriting, True)
            
      
      Do Until objFileIN.AtEndOfStream
            strText = objFileIN.ReadLine
            aData = Split(strText,"|")
      
            ' mdse_sty   Field 1 - Nothing - Stays as Is
            strNewText = aData(0) & "|"
            
            ' fis_sls_mo  Field 2 - Change 'Fiscal Rcpt' to 'Fiscal' and 'Fiscal Sales' to 'Fiscal'.
            If instr(1, adata(1), "Fiscal Rcpt") > 0 Then
                  strNewText = strNewText & Replace(adata(1), "Fiscal Rcpt", "Fiscal") & "|"
            ElseIf instr(1, adata(1), "Fiscal Sales") > 0 Then
                  strNewText = strNewText & Replace(adata(1), "Fiscal Sales", "Fiscal") & "|"      
            ElseIf instr(1, adata(1), "Fiscal Sales Dec") > 0 Then
                  strNewText = strNewText & Replace(adata(1), "LY Fiscal Dec", "Fiscal") & "|"      
            ElseIf instr(1, adata(1), "Fiscal Sales Jan") > 0 Then
                  strNewText = strNewText & Replace(adata(1), "LY Fiscal Jan", "Fiscal") & "|"      
            Else
                  strNewText = strNewText & adata(1) & "|"      
            End If      
                  
            ' mdse_typ   Field 3 - Nothing - Stays as Is
            strNewText = strNewText & aData(2) & "|"
            
            ' fis_sls_yr   Field 4 - Change '2012' to '2012_New'
            If IsNumeric(adata(3)) = True Then
                  If adata(3)= "2012" Then
                        strNewText = strNewText & Replace(adata(3), "2012", "2012_New") & "|"
                  Else      
                        strNewText = strNewText & cstr( cint(aData(3) + 1)) & "|"
                  End If
            Else
                  strNewText = strNewText & aData(3) & "|"
            End If
            
            'Field 5 to Field 14 - Change '0' to #Missing and/or '0.00' to #Missing.
            ' unit_sls_ttl
            If adata(4) = "0" Then
                  strNewText = strNewText & "#Missing" & "|"
            ElseIf adata(4) = "0.00" Then
                  strNewText = strNewText & "#Missing" & "|"
            Else
                  strNewText = strNewText & adata(4) & "|"
            End If      
            
            ' rtl_sls_ttl_local
            If adata(5) = "0" Then
                  strNewText = strNewText & "#Missing" & "|"
            ElseIf adata(5) = "0.00" Then
                  strNewText = strNewText & "#Missing" & "|"
            Else
                  strNewText = strNewText & adata(5) & "|"
            End If      
                  
            ' unit_sls_regpromo
            If adata(6) = "0" Then
                  strNewText = strNewText & "#Missing" & "|"
            ElseIf adata(6) = "0.00" Then
                  strNewText = strNewText & "#Missing" & "|"
            Else
                  strNewText = strNewText & adata(6) & "|"
            End If      
                  
            ' rtl_sls_regpromo_local
            If adata(7) = "0" Then
                  strNewText = strNewText & "#Missing" & "|"
            ElseIf adata(7) = "0.00" Then
                  strNewText = strNewText & "#Missing" & "|"
            Else
                  strNewText = strNewText & adata(7) & "|"
            End If      
                  
            ' unit_sls_mkd
            If adata(8) = "0" Then
                  strNewText = strNewText & "#Missing" & "|"
            ElseIf adata(8) = "0.00" Then
                  strNewText = strNewText & "#Missing" & "|"
            Else
                  strNewText = strNewText & adata(8) & "|"
            End If      
                  
            ' rtl_sls_mkd_local
            If adata(9) = "0" Then
                  strNewText = strNewText & "#Missing" & "|"
            ElseIf adata(9) = "0.00" Then
                  strNewText = strNewText & "#Missing" & "|"
            Else
                  strNewText = strNewText & adata(9) & "|"
            End If      
                  
            ' cst_sls_ttl_local
            If adata(10) = "0" Then
                  strNewText = strNewText & "#Missing" & "|"
            ElseIf adata(10) = "0.00" Then
                  strNewText = strNewText & "#Missing" & "|"
            Else
                  strNewText = strNewText & adata(10) & "|"
            End If      
                  
            ' cst_sls_regpromo_local
            If adata(11) = "0" Then
                  strNewText = strNewText & "#Missing" & "|"
            ElseIf adata(11) = "0.00" Then
                  strNewText = strNewText & "#Missing" & "|"
            Else
                  strNewText = strNewText & adata(11) & "|"
            End If      
                  
            ' cst_sls_mkd_local
            If adata(12) = "0" Then
                  strNewText = strNewText & "#Missing" & "|"
            ElseIf adata(12) = "0.00" Then
                  strNewText = strNewText & "#Missing" & "|"
            Else
                  strNewText = strNewText & adata(12) & "|"
            End If      
                  
            ' rec_order
            If adata(13) = "0" Then
                  strNewText = strNewText & "#Missing" & "|"
            ElseIf adata(13) = "0.00" Then
                  strNewText = strNewText & "#Missing" & "|"
            Else
                  strNewText = strNewText & adata(13) & "|"
            End If      
                  
            
            objFileOUT.WriteLine strNewText
      
      Loop
      
      objFileIN.Close
      objFileOUT.Close
      
      'objFSO.DeleteFile(folderIdx.Name, True
Next
0
 

Author Comment

by:elwayisgod
ID: 38769281
MathewPatrick,

This is looking really sweet.  Only two things left.. Sorry, as I forgot about these.

1 - There is a possibility that the Year Field 4 is 'Other Yr'.  If that is found then it stays the same in both output files.
2 - In output file #2, I actually need each line that is '2013' to create two duplicate lines.  One with the Year increasing by 1 to '2014' and one duplicate with Year equal to '2013_New'.

Is that possible?
0
 

Author Comment

by:elwayisgod
ID: 38769352
And on the when it's looking for a value to change can it not be case sensitive?  It didn't change 'Fiscal Rcpt Dec' when source file was 'Fiscal Rcpt DEC'.  Not sure how case will come in and whether it's consistent or not.  These are files with millions of rows so no way to check.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now