• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 548
  • Last Modified:

Script to modify text file?

Good afternoon,

I'm not sure if this is possible. But here goes...

We have 3 .txt files that are generated from our system. Contained within each, in a random order is about 6000 rows of customer data, this amount of course differs everyday as we gain and lose customers.

Each morning we must open the .txt files one by one using something like Utraledit and go to a specific column to fill a single character for all rows of the information. By creating this character we notice it also creates a space at the end for each line which needs to be removed, so we will then remove the space at the end for each. See below example.

example
My question is can this process be automated using a script, without any user interaction whatsoever?

Please let me know if you have any additional questions.

Some Additional information regarding the current 3 files:

1.These files are generated each morning to a path on the server (Example: X:\Quili\raw_data\)

2.The file names of each of the .txt files are different each day using the naming convention of BZYYYYMMDD.txt and UIYYYYMMDD.txt and ZZYYYYMMDD.txt

3.Once we do the above the procedure with these files and process them through another system all 3 of these files are then moved automatically to another folder. So the X:\Quili\raw_data folder is always empty after we process the 3 files in the system each day.

Do let me know if this is possible.

Regards,
Nobushi
0
nobushi
Asked:
nobushi
  • 8
  • 5
  • 3
  • +2
1 Solution
 
Bill PrewCommented:
So are you just asking to update all the files in a folder, adding a Z into column 55.  That would certainly be easily doable in VBScript.

~bp
0
 
threesixteenCommented:
First of all, it sounds like your text editor is the one adding that blank whitespace at the end.  You may want to consider going through the text editor settings to see if it's inserting that whitespace at the end for you.  Alternatively, a different text editor might do the trick.

However, if that isn't an option, you could write a script that goes through your text files and use regular expressions to replace the whitespace at the end of the string and replace it with nothing.
0
 
nobushiAuthor Commented:
Basically yes.

Though each file will be different depending on the column and the letter to be updated with.

Example: ZZYYYYMMDD.txt will need a W in column 45 and UIYYYYMMDD.txt will need a Z in column 80 and BZYYYYMMDD.txt will need a KHS in column 840
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Bill PrewCommented:
So is it always the first two characters of the file name that dictate what to insert and at what column?

Are those the only three cases, or are there others?

And are the new characters always being added to the end of a line (meaning no data exists after that column), or are we really "replacing" the columns you mention above with the new characters?

Still very doable in a VBS script which I can help with

~bp
0
 
Steve KnightIT ConsultancyCommented:
Silly question here... what produces those files - a third party or something in house?  wouldn't it be simpler to just get that changed!!

Steve
0
 
nobushiAuthor Commented:
Yes for your first question.

For your second question it is only these 3 files:

BZYYYYMMDD.txt
UIYYYYMMDD.txt
ZZYYYYMMDD.txt

For the third question. That is correct there is no data at that column number where the character needs to be inputted. Though regarding the empty space after the character is inputed, i dont know if it is my text editor doing that. But basically the next column over (which has a space) will need to be removed.

Good to know its doable, i would be very greatful if you could help me with this.

Thanks
0
 
Bill PrewCommented:
Sure, have to step away for a bit but will work something up tonight.

~bp
0
 
nobushiAuthor Commented:
OK thank you Bill.

Steve i completely agree with you. This would be easiest to have our 3rd party program mended to do this.

But we are having problems with the developer so this seems like the best approach for right now till we find a replacement.
0
 
Steve KnightIT ConsultancyCommented:
Fair enough!
0
 
Rainer JeschorCommented:
Hi,
attached a sample script. Due to the fact that I have no real sample files you might have to adjust the coding. Also there is no error handling...

Option Explicit
Const ForReading = 1
Const ForWriting = 2

Dim objFSO, pFolder, cFile, objWFSO, objFileInput, objFileOutput,strLine
Dim strInputPath, strOutputPath , sName, sExtension
Dim iPos, rChar

'Adjust to your basic folder
strInputPath = "D:\FilesToProcess"
strOutputPath = "D:\FilesProcessed"

'Create the filesystem object
Set objFSO = CreateObject("Scripting.FileSystemObject")
'Get a reference to the processing folder
Set pFolder = objFSO.GetFolder(strInputPath)
                
'loop through the folder and get the file names to be processed
For Each cFile In pFolder.Files
  MsgBox cFile.Name
  ProcessAFile cFile
Next

Sub ProcessAFile(objFile)
	Set objWFSO = CreateObject("Scripting.FileSystemObject")
	Set objFileInput = objWFSO.OpenTextFile(objFile.Path, ForReading)
	sExtension = objWFSO.GetExtensionName(objFile.Name)
	sName = Replace(objFile.Name, "." & sExtension, "")
	Set objFileOutput = objFSO.OpenTextFile(strOutputPath & "\" & sName & "_mod." & sExtension, ForWriting, True) 
	
	Select Case Left(objFile.Name,2)
		Case "BZ"
			iPos = 840
			rChar = "KHS"
		Case "UI"
			iPos = 80
			rChar = "Z"
		Case "ZZ"
			iPos = 45
			rChar = "W"
	End Select
	
	Do While Not objFileInput.AtEndOfStream
		strLine = objFileInput.ReadLine
		If Len(strLine) >= iPos Then
			objFileOutput.WriteLine(Left(strLine,iPos-1) & rChar)
		End If
	Loop
	objFileInput.Close
	objFileOutput.Close
	Set objFileInput = Nothing
	Set objFileOutput = Nothing
End Sub

Open in new window


HTH
Rainer
0
 
nobushiAuthor Commented:
Thank you RainerJ. This works perfect.

Only thing is that we need it to have the same file name for all 3 files.

I attempted to make this line:

Set objFileOutput = objFSO.OpenTextFile(strOutputPath & "\" & sName & "_mod." & sExtension, ForWriting, True) 

Open in new window


like this:

Set objFileOutput = objFSO.OpenTextFile(strOutputPath & "\" & sName & "." & sExtension, ForWriting, True) 

Open in new window


Removing the "_mod" but it appears the script will delete everything within the file so that is has 0 bytes.

Would it be possible for you to advise on how I can make the script delete the origionals and rename the modified files to the origional name? Or possibly just have it overwrite with the origional name?

Please let me know.

Kind Regards,
Nobushi
0
 
Rainer JeschorCommented:
Hi,

I have slightly modified the script.
You can not overwrite the file during reading it line for line.
Therefore I would still recommend to save the modified version in a different folder just to avoid any conflicts and at the end the source file is deleted and the new one is moved.

Option Explicit
Const ForReading = 1
Const ForWriting = 2

Dim objFSO, pFolder, cFile, objWFSO, objFileInput, objFileOutput,strLine
Dim strInputPath, strOutputPath , sName, sExtension
Dim strSourceFileComplete, strTargetFileComplete, objSourceFile, objTargetFile
Dim iPos, rChar

'Adjust to your basic folder
strInputPath = "D:\FilesToProcess"
strOutputPath = "D:\FilesProcessed"

'Create the filesystem object
Set objFSO = CreateObject("Scripting.FileSystemObject")
'Get a reference to the processing folder
Set pFolder = objFSO.GetFolder(strInputPath)
                
'loop through the folder and get the file names to be processed
For Each cFile In pFolder.Files
  MsgBox cFile.Name
  ProcessAFile cFile
Next

Sub ProcessAFile(objFile)
	Set objWFSO = CreateObject("Scripting.FileSystemObject")
	Set objFileInput = objWFSO.OpenTextFile(objFile.Path, ForReading)
	strSourceFileComplete = objFile.Path
	sExtension = objWFSO.GetExtensionName(objFile.Name)
	sName = Replace(objFile.Name, "." & sExtension, "")

	strTargetFileComplete = strOutputPath & "\" & sName & "_mod." & sExtension
	Set objFileOutput = objFSO.OpenTextFile(strTargetFileComplete, ForWriting, True) 
	
	Select Case Left(objFile.Name,2)
		Case "BZ"
			iPos = 840
			rChar = "KHS"
		Case "UI"
			iPos = 80
			rChar = "Z"
		Case "ZZ"
			iPos = 45
			rChar = "W"
	End Select
	
	Do While Not objFileInput.AtEndOfStream
		strLine = objFileInput.ReadLine
		If Len(strLine) >= iPos Then
			objFileOutput.WriteLine(Left(strLine,iPos-1) & rChar)
		End If
	Loop
	objFileInput.Close
	objFileOutput.Close
	Set objFileInput = Nothing
	Set objFileOutput = Nothing

	Set objSourceFile = objWFSO.GetFile(strSourceFileComplete)
	objSourceFile.Delete
	Set objSourceFile = Nothing
	
	Set objTargetFile = objWFSO.GetFile(strTargetFileComplete)
	objTargetFile.Move strSourceFileComplete	
	Set objTargetFile = Nothing
	Set objWFSO = Nothing
End Sub

Open in new window


HTH
Rainer
0
 
nobushiAuthor Commented:
Hi Rainer,

I have been testing in a test enviornment with only these 3 files and all works well.

But the directory where these 3 files live on the production enviornment also have some other .txt files in there, so what is happening is the script is going through each of the other .txt files that do not need any modifications to them.

Are you able to make your script only look at the 3 files and not any other file that is in that directory?

Thanks,
Nobushi
0
 
Rainer JeschorCommented:
Just replace the select case area with

Select Case Left(objFile.Name,2)
		Case "BZ"
			iPos = 840
			rChar = "KHS"
		Case "UI"
			iPos = 80
			rChar = "Z"
		Case "ZZ"
			iPos = 45
			rChar = "W"
                Case else
                        Exit sub
	End Select

Open in new window

0
 
nobushiAuthor Commented:
Thank you for your response Rainer.

I have added the:

Case Else
       Exit Sub

at the end of that section as you suggested but the script is still touching the other text files.

Any other ideas?
0
 
Rainer JeschorCommented:
Could you please add sample filenames which are also touched?
0
 
nobushiAuthor Commented:
Sure.

Basically it's all "files" in that directory, excluding the UI, BZ and ZZ files, also excluding the sub directories that are in there. The files being touched are below with the "_mod" in the file name, including your vbs script.

AC20120816.TXT
AC20120816_mod.TXT
AMEXPORT.exe
AMEXPORTAUTO.exe
AMEXPORTAUTO_mod.exe
AMEXPORT_mod.exe
quilifileimprtmod.bat
quilifileimprtmod_mod.bat
EERainersScript.vbs
EERainersScript_mod.vbs
BR20120816.TXT
BR20120816_mod.TXT
BZ20120816.TXT
CO20120816.TXT
CO20120816_mod.TXT
CU20120816.TXT
CU20120816_mod.TXT
OF20120816.TXT
OF20120816_mod.TXT
publish.htm
publish_mod.htm
setup.exe
setup_mod.exe
UI20120816.TXT
ZZ20120816.TXT

This list may grow in the future, so if you can just have the script touch the 3 files its intended for that would be best.

Do let me know if you require anything further.

Kind Regards,
Nobushi
0
 
Rainer JeschorCommented:
Hi,
please try this:

Option Explicit
Const ForReading = 1
Const ForWriting = 2

Dim objFSO, pFolder, cFile, objWFSO, objFileInput, objFileOutput,strLine
Dim strInputPath, strOutputPath , sName, sExtension
Dim strSourceFileComplete, strTargetFileComplete, objSourceFile, objTargetFile
Dim iPos, rChar
Dim fileMatch

'Adjust to your basic folder
strInputPath = "D:\FilesToProcess"
strOutputPath = "D:\FilesProcessed"

'Create the filesystem object
Set objFSO = CreateObject("Scripting.FileSystemObject")
'Get a reference to the processing folder
Set pFolder = objFSO.GetFolder(strInputPath)
                
'loop through the folder and get the file names to be processed
For Each cFile In pFolder.Files
  MsgBox cFile.Name
  ProcessAFile cFile
Next

Sub ProcessAFile(objFile)
	fileMatch = false

	Select Case Left(objFile.Name,2)
		Case "BZ"
			iPos = 840
			rChar = "KHS"
			fileMatch = true
		Case "UI"
			iPos = 80
			rChar = "Z"
			fileMatch = true
		Case "ZZ"
			iPos = 45
			rChar = "W"
			fileMatch = true
	End Select

	If fileMatch = true Then

		Set objWFSO = CreateObject("Scripting.FileSystemObject")
		Set objFileInput = objWFSO.OpenTextFile(objFile.Path, ForReading)
		strSourceFileComplete = objFile.Path
		sExtension = objWFSO.GetExtensionName(objFile.Name)
		sName = Replace(objFile.Name, "." & sExtension, "")

		strTargetFileComplete = strOutputPath & "\" & sName & "_mod." & sExtension
		Set objFileOutput = objFSO.OpenTextFile(strTargetFileComplete, ForWriting, True) 
		
	
		Do While Not objFileInput.AtEndOfStream
			strLine = objFileInput.ReadLine
			If Len(strLine) >= iPos Then
				objFileOutput.WriteLine(Left(strLine,iPos-1) & rChar)
			End If
		Loop
		objFileInput.Close
		objFileOutput.Close
		Set objFileInput = Nothing
		Set objFileOutput = Nothing

		Set objSourceFile = objWFSO.GetFile(strSourceFileComplete)
		objSourceFile.Delete
		Set objSourceFile = Nothing
		
		Set objTargetFile = objWFSO.GetFile(strTargetFileComplete)
		objTargetFile.Move strSourceFileComplete	
		Set objTargetFile = Nothing
		Set objWFSO = Nothing
	End If
End Sub
                                            

Open in new window

0
 
nobushiAuthor Commented:
Fantastic!

All is well now.

Thank you so much Rainer.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 8
  • 5
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now