Need help in developing Vb script

Okay, give this a try. Save as a VBS file, and then run as follows from a command line or BAT script:

cscript EE29193174.vbs file1.txt file2.txt file3.txt

Option Explicit

' Define needed I/O constants
Const ForReading = 1
Const ForWriting = 2
Const ForAppending = 8
Const TriStateUseDefault = -2

' Define global variables
Dim objFSO
Dim strInFile1, strInFile2, strOutFile
Dim objInFile, objOutFile
Dim strLine, arrFields, i
Dim Delim

Delim = vbTab

' Create filesystm object
Set objFSO = CreateObject("Scripting.FileSystemObject")

' Get parms from command line
If (WScript.Arguments.Count < 3) Then
    WScript.Echo "Usage: " & Wscript.ScriptName & " <input-file-1> <input-file-2> <output-file>"
    WScript.Quit
Else
    strInFile1 = objFSO.GetAbsolutePathname(WScript.Arguments(0))
    strInFile2 = objFSO.GetAbsolutePathname(WScript.Arguments(1))
    strOutFile = objFSO.GetAbsolutePathname(WScript.Arguments(2))
End If

' Make sure input files exists
If Not objFSO.FileExists(strInFile1) Then
   Wscript.Echo "*ERROR* Input file does not exist. (" & strInFile1 & ")"
   Wscript.Quit
End If
If Not objFSO.FileExists(strInFile2) Then
   Wscript.Echo "*ERROR* Input file does not exist. (" & strInFile2 & ")"
   Wscript.Quit
End If

' Open output file
Set objOutFile = objFSO.OpenTextFile(strOutFile, ForAppending, True)

ProcessFile strInFile1
ProcessFile strInFile2

' Close output file
objOutFile.Close

Sub ProcessFile (strInFile)

    ' Open input output file
    Set objInFile = objFSO.OpenTextFile(strInFile, ForReading, False, TriStateUseDefault)

    ' Loop through all input file lines
    Do While objInFile.AtEndOfStream <> True
        ' Read next line
        strLine = objInFile.ReadLine

        ' No need to edit blank lines
        If strLine <> "" Then
            ' Split coulmns by TAB character
            arrFields = Split(strLine, Delim)

            ' Trim each column
            For i = LBound(arrFields) To UBound(arrFields)
                arrFields(i) = Trim(arrFields(i))
            Next

            ' Join columns back together with TAB character
            strLine = Join(arrFields, Delim)
        End If

        ' Write out this line
        objOutFile.WriteLine strLine
    Loop

    ' Close input file
    objInFile.Close

End Sub

»bp

ASKER

Thanks for the Response !!

If in case, I have a scenario as below that is I want to append all files under a folder to a single file by trimming the space how can I accomplish. I have developed as below. How can I trim for all files and append it to a single file?

Can you please suggest on this. Thanks in advance !!

Peter Chan

Hi,
Can you try to trim each field using ltrim and rtrim commands?

Can you please suggest on this. Thanks in advance !!

First, when you post code in questions please place it in a "code block" using the Code Snippet" icon on the tool bar. If you need help on using the question comment editor use the question mark for a small summary of the toolbar.

As far as the code goes you posted, was that intended to be a complete working script? If so there is a lot of errors and missing stuff you need to fix first. The very first line is invalid, you have an open paren and no closing paren, an open quote and no closing quote, it looks like you are concatenating, but I don't see the ampersand operator, etc.

Also, a number of variables don't seem to be defined but are referenced, like "fso", "arrL1", "name", etc. I always recommend using:

Option Explicit

At the top of the script and forcing yourself to define variables in Dim statements before using. You will save time in the long run as the complier will trap typos and references to variables that were never set to a value that will be harder to track down at runtime.

It looks like you only want one line added to the output file for any duplicate lines in the input files?

What is it that you want to "trim" in this version of the code? Every line of each of the files you read? If so then I would suggest you develop a small function (leveraging the code I already provided) that takes in the original line, parses it up and does the trimming, and then returns the new formatted line of data. The you can just do something like this in your code, calling the function:

strV = TrimFields(Test.ReadLine)

Hope this helps.

ASKER

Thanks a lot for the update !!

I will update the logic as you mentioned in the first update after the strV = Test.ReadLine. Hope so it will be fine.
I will check and get back on this.

I'll take a look...

»bp

ASKER

Thanks a lot

When I am running the script it is running for a very long time no progress. And my source files column data is as below

The code you posted has several syntax errors in it that prevent it from even compiling, so it would never execute. For example these lines:

aliasFolder=(Wscript.Arguments(0)"\Test")
. . . 
if (instr(XYZ,"XXX")> 0 and instr(XYZ,".TXT)>0) Then
. . .
Loop

Also LogFile is never created / opened, and I don't see any file name for a log file?

Are you sure you are posting the code you are actually executing?

»bp

ASKER

Thanks

How are you running this?

»bp

Okay, rather than rewrite the code you provided, I took the earlier solution I provided (to the question as you originally stated it) and modified it to what I think are the changed requirements.. Save as a VBS file, and then run as follows from a command line or BAT script:

cscript EE29193174.vbs "C:\temp" "output.txt"

The first parm is the folder to look for the files in, and the second parm is the output file path to place the merged results in.

Also notice the use of Dim statements for all variables, indentation, comments and sub-procedures to make the code more readable and easier to debug.

Option Explicit

' Define needed I/O constants
Const ForReading = 1
Const ForWriting = 2
Const ForAppending = 8
Const TriStateUseDefault = -2

' Define global variables
Dim objFSO, objDict
Dim strInFolder, strOutFile
Dim objInFolder, objInFile
Dim Delim

Delim = vbTab

' Create filesystm object
Set objFSO = CreateObject("Scripting.FileSystemObject")

' Get parms from command line
If (WScript.Arguments.Count < 2) Then
    WScript.Echo "Usage: " & Wscript.ScriptName & " <input-folder <output-file>"
    WScript.Quit
Else
    strInFolder = objFSO.GetAbsolutePathname(WScript.Arguments(0))
    strOutFile = objFSO.GetAbsolutePathname(WScript.Arguments(1))
End If

' Make sure input folder exists
If Not objFSO.FolderExists(strInFolder) Then
   Wscript.Echo "*ERROR* Input folder does not exist. (" & strInFolder & ")"
   Wscript.Quit
End If

' Create dictionary used to filter out duplicate data lines from input files
Set objDict = CreateObject("Scripting.Dictionary")
objDict.CompareMode = vbTextCompare

' Access input folder, process each matching file found
Set objInFolder = objFSO.GetFolder(strInFolder)
For Each objInFile In objInFolder.Files
    If (UCase(objFSO.GetExtensionName(objInFile.Path)) = "TXT") And (InStr(1, objInFile.Name, "SLF2", vbTextCompare) > 0) Then
        ProcessFile objInFile.Path, objDict
    End If
Next

' Write output file (merge of all input files)
WriteOutputFile strOutFile, objDict


Sub ProcessFile (strFile, objDict)
    ' Local variables
    Dim objFile, strLine, arrFields, i

    ' Open input file
    Set objFile = objFSO.OpenTextFile(strFile, ForReading, False, TriStateUseDefault)

    ' Loop through all input file lines
    Do While objFile.AtEndOfStream <> True
        ' Read next line
        strLine = objFile.ReadLine

        ' No need to edit blank lines
        If strLine <> "" Then
            ' Split coulmns by TAB character
            arrFields = Split(strLine, Delim)

            ' Trim each column
            For i = LBound(arrFields) To UBound(arrFields)
                arrFields(i) = Trim(arrFields(i))
            Next

            ' Join columns back together with TAB character
            strLine = Join(arrFields, Delim)
        End If

        ' Add to dictionary if not already there
        If Not objDict.Exists(strLine) Then
            objDict.Add strLine, True
         End If
   
    Loop

    ' Close input file
    objFile.Close

End Sub

Sub WriteOutputFile(strFile, objDict)
    ' Local variables
    Dim objFile, strLine

    ' Open output file
    Set objFile = objFSO.OpenTextFile(strFile, ForWriting, True)

    ' Write header info
    objFile.WriteLine "$ALT_NAME   'SLF2'"
    objFile.WriteLine ""

    ' Write all unigue lines from dictionary
    For Each strLine In objDict.Keys
       objFile.WriteLine strLine
    Next

    ' Write trailer info
    objFile.WriteLine "$END"

    ' Close output file
    objFile.Close
End Sub

»bp

ASKER

I am looking for an existing script to be modified rather than developing a completely new script. I am triggering the job from one of the schedulers to get the vb script run. Any idea why the script is running when I am removing option explicit but the script is not trimming the spaces. But when I add option explicit command it is running for a long time and no progress vb script.

can you please suggest your thoughts on this.

The last script you posted still had several syntax errors in it that would prevent it from running to completion.

I expect that reason it takes longer to end without the option explicit is because you haven’t declared all the variables, so when you specify option explicit that will cause a compile time error which happens before any code is run.

Without the option explicit it ignores the missing variables until they potentially cause a runtime error when they are executed. so some code could be executing for a while before an error occurs.

You should test the script from a command line window so that you can see errors that the script raises.

Also, is what you posted the full script, there is no additional code, right?

»bp

We seem to be having a communication breakdown.

I took your latest code from the above comment, and when I tried to run it unchanged I got compile errors.

Attached is the minimum set of changes (your code on left side, my changed code on right side) I had to do to get it to at least compile and start to execute.

Report.html

So whatever you are doing there it isn't actually running any of the code, it won't compile the way you posted it.

I think I asked this earlier and didn't get an answer, how are you testing the execution of this script? You should be doing it from a command line so you can see the errors displayed, like this:

ASKER

I am getting below error.

Sounds like you didn't pass the two parms on the command line that are needed.

You should add an edit for that also, checking for those required parms before you try and reference them.

»bp

ASKER

How can we directly specify in the code rather than passing as an argument..

Something like:

logFolder = "C:\Temp\ScriptLogs\CFR"
metaFolder = "C:\Temp\Files\meta"
aliasFolder = "C:\Temp\Test"
tgtFolder = "C:\Temp\Tgt"
name = "somename"

»bp

ASKER

I am getting the below error when I tried to complile the program. Sorry for late response.

Microsoft VBScript runtime error: Variable is not defined: 'metafolder'

But I have defined the variable. I have started the program as below and complied as.

cscript test.vbs C:\Users\Desktop\Script Test

And tried executing as below. When I have tried to compile the program I got same error as above that
is Vbscript runtime error: Variable is undefined:

You have only assigned the variable a value, you haven't done a DIM on it first.

You need to do a DIM for every variable you use in your code, before it is used.

Option Explicit

Dim metaFolder
Dim logFolder
Dim aliasFolder
Dim tgtFolder

metaFolder = (Wscript.Arguments(0)&"\Files\meta
logFolder = (Wscript.Arguments(0)&"\ScriptLogs\CFR")
aliasFolder = (Wscript.Arguments(0)"\Test")
tgtFolder = (Wscript.Arguments(0)"&\Tgt")

»bp

ASKER

I have updated the dim and started compiling but for the following command as well error popping up that is variable undefined

LogFile.WriteLine("Creating alias File for "&cubeNm)
So I have defined by below statement

dim LogFile.

After updating when I have tried to complile I got the below error.

Microsoft Vbscript error runtime: object required: ''

You need to open the log file and SET that variable to the result of the open method. I included all that in the alternate code I posted a whole bunch of comments ago...

ASKER

Okay thanks.. Will check

It looks like you have a naming problem with the log file. When you opened it for writing you used:

set Log = fso.CreateTextFile(logFolder&"\Load.txt",True)

but when you actually wrote to it you used another variable name:

LogFile.WriteLine("Creating Alias File for "&name)

make them the same...

»bp

ASKER

Updated the require changes and there were no compilation errors.The script is executing but the spaces are not trimming while it is appending to a file. Please help.

Can you please post the complete code that you are running now.

»bp

I feel like we are going round and round in circles.

The code you just posted does not compile, so will not execute. I saved it to a filed named EE29193174e.vbs and tried to execute it. I got the following error:

which makes sense since I can see that there is a missing double quote on the line mentioned in the error:

I know we have already discussed this line as a problem.

I also see other issues that I mentioned earlier as well, for example on this line:

The $name needs to be &name to concatenate it.

There may still be other errors, I didn't look at every single line.

As it stands the code you posted could not have executed for you.

»bp

ASKER

Sorry the actual code which I have executed as the correct code. Since I have manually written the code here got some errors.. But the code is complied successfully.. Please find the screeenshot below.

Okay, but you need to share THAT EXACT CODE YOU ARE EXECUTING with me here if you want me to help you determine why it isn't functioning properly.

»bp

Okay, I ran a small test here and it seemed to trim the extra spaces off the input data when it created the SLF2_Test.alt file. Can you attach your input test files and the output file you are getting there to a comment here?

»bp

ASKER

InputFile1: Name of the file is SLF2_GAAP_IP.TXT

"IJ" "4"
"KL" "5"
"MN" "6"
"OP" "7"
"QR" "8"
"ST" "9"
"AB" "1"
InputFile2: Name of the file is SLF2_GAAP_TEST.TXT

" AB" "1"
"cd" "2"
"EF" "3"
"GH" "4"

Output: Name of the file is SLF2_Test

$ALT_NAME 'SLF2'

"IJ" "4"
"KL" "5"
"MN" "6"
"OP" "7"
"QR" "8"
"ST" "9"
"AB" "1"
" AB" "1"
"cd" "2"
"EF" "3"
"GH" "4"
$END

I would actually need the files uploaded and attached, not just pasted in. Since they have a TAB character as a delimiter it is important to look at the actual files and see where the TAB characters are and if there are still unwanted spaces around the data, etc.

Use the Attach File tool at the bottom of the comment window and attach the input as well as output files.

»bp

SLF2_GAAP_IP.TXT.txt SLF2_GAAP_TEST.TXT.txt SLF2_Test.alt

ASKER

Okay, I see this issue.

When we split the input lines at the TAB delimiters, it treats the double quotes just like any other characters in each field. So when you have something like:

" AB"

and then do a TRIM() on that, there are no leading or trailing spaces. It starts with a double quote and ends with a double quote, which is fine from the TRIM() perspective.

I will add a little more code to extract any spaces after or before the double quotes since I now understand that is what you need...

»bp

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Thanks will check and get back to you

Thanks a lot for all your help, support, patience and for your expertise level..

Welcome, glad I was able to help.

And welcome to Experts Exchange❗

»bp

ASKER

Do we have any good stuff over online to get knowledge in VB script.
If any request you to please suggest. Any books as well is fine.

Vinay,

That's a good question. I didn't have anything handy but tried to pull a few things together, hope they are somewhat useful.

One thing you have to understand about VB Script (VBS) at the start is that it is more often associated with HTML and web pages. It can be used in web pages to enhance the functionality of the page and add capability beyond what basic HTML provides. As a result when many people refer to "VB Script" they often think of it in the context of being used on a web page. However much of the same language is also used by what Microsoft calls the "Windows Scripting Host" (WSH), and this allows VB Script syntax to be used outside of HTML and web pages, and rather used for stand alone scripts on Windows computers. This can be very powerful.

The good news is much of the same languages and syntax that applies to VB Script in web pages also applies to VB Script in WSH. The bad news is much of the online information and tutorials are targeted to web page usage rather than WSH, so you have to weed out the HTML stuff and focus on the VB Script stuff.

Okay, I haven't spent a lot of time organizing these, and there may be some overlap, but if you want to browse them and see what might be of use to you it should be a starting point. Questions welcome...

Somewhat "introductory" information about WSH and VBS, but worth looking at as a starting point. Don't try to understand everything on the first pass, but get the general concepts and come back later for more detail.

A number of the Microsoft provided introduction material, and reference material. Great to be familiar with.

Some good non Microsoft reference material sites.

I like UDEMY for low cost online courses, and they have a few that relate to VBS that you might consider. You can see the outline of the course, description, reviews etc before deciding if it might be worthwhile for you.

Lastly, a couple of good references that you may also want to browse. These are a bit more relative to the web page usage of VBS, but mos tof the same information applies to VBS in WSH, so there is still a lot of good info.

Hope that helps some, good luck.

»bp

ASKER

Thanks a lot. Also, I have a small requirement, if I would like to pick the files in the folder which is having name same as the name variable.

For eg: In the above script, we have passed the Test value for the variable name and I want to append a file which is having the text files with the name as SLF2_ **Test**_.TXT. How can I accomplish it?

name = "Test"

It looks like that is already being done where you assign aliasFile.

name = "Test"
aliasFile = aliasFolder&"\SLF2_"&name&".alt"

ASKER

The below code is used for appending the files available in the folder to the alias file.

aliasFile = aliasFolder&"\SLF2_"&name&".alt"

But for eg: I have defined a variable
name = "Test"

I want to append the files in the folder which is having the files with Test only.

For eg:
In a folder, I've 3 files like below.

File1: SLF2_Test_BA.TXT
File2: SLF2_ABC_Test.TXT
File3: SLF3_XYZ.TXT

I want only File1 and File2 should be appended but not File3

Sounds like you want to only select files that also have "test" in their name. If that is the case then just add that to the criteria you already have in that IF statement, like below. I included a couple of other options in the InStr() parameters, so that it would do a case insensitive comarison when looking for those required sub-strings, because I think that's really what you would want also.

if instr(1,aliasSrcFile,"SLF2",vbTextCompare)> 0 and instr(1,aliasSrcFile,".TXT",vbTextCompare)>0 and instr(1,aliasSrcFile,"test",vbTextCompare)>0 Then

»bp

ASKER

Thanks for the update.. Fixed my issue

Great, glad that helped.

»bp

ASKER

I need help in further improvising the script that is for eg: you have provided me the following code for trimming leading/trailing spaces in the text between " "

Function Clean(Text)
   Clean = Trim(Text)
   Do While Instr(Clean, """ ") > 0
      Clean = Replace(Clean, """ ", """")
   Loop   
   Do While Instr(Clean, " """) > 0
      Clean = Replace(Clean, " """, """")
   Loop   
End Function

But I have a scenario for eg:
I have a delimiter tab at the trail/lead between "" how can I trim the tab operator.

eg:
Delimited by tab

"Ab" "XY "
"YZ " " WX"
I want to check both operator either space or tab in-between " " and trim it accordingly.
Request you to please help me on this.

So are you saying there are TAB characters being used between fields, and also within the actual data of certain fields? So, in this picture the orange arrows are TAB characters, is this what you are describing your data looks like?

If so that is could to be very tricky. You would have to determine which TABs are field delimiters and must stay, and which are in the field data and should be removed.

That would take quite a bit of code, I'd recommend creating a new question for that if you want it.

Also, does every field in the data file always have double quotes around it, or only some?

And if there are TABs in the field data, but not at the beginning or end of the field, you want to keep those, or remove them?

Be as complete as possible when you describe your data, and provide a robust sample, and the desired results.

»bp

ASKER

Yes for separating a field I am using tab character and there is a possibility of a tab at the start/end within double quotes which is actual data. I want to trim the tab within double quotes.

Yes every field in the data file always have double quotes.

I want to remove start/end tab character within the double quotes.

If there is a other delimiter as well within the double quotes even that should be removed.