Saving file into another file using VBScript after modification

Question

I have some XML files in a folder \\demo.US\Modified\. The files in the folder are:

USA.xml
Canada.xml
Mexico.xml

The code below is changing the encoding from UTF-8 to windows-1252 and is creating a modified file mod.xml.

This mod.xml file have data from all three XML files concatenated.

I need help so I can save files separately.

If value of objFile.Name is USA.xml then it should save modified file name as USA_mod.xml. the output for \\demo.US\Modified\ folder after execution is complete should have mod files in it as below.

USA.xml
Canada.xml
Mexico.xml
USA_mod.xml
Canada_mod.xml
Mexico_mod.xml

The code I used is as follows.

Set objFSO = CreateObject("Scripting.FileSystemObject")

objStartFolder = "\\demo.US\Modified\"
Set objFolder = objFSO.GetFolder(objStartFolder)

Set colFiles = objFolder.Files

For Each objFile In colFiles
    WScript.Echo objFile.Name

    Set objFile = objFSO.OpenTextFile(objStartFolder & objFile.Name, 1)
    Set outFile = objFSO.OpenTextFile(objStartFolder & "mod.xml", 2, True)

    Do Until objFile.AtEndOfStream
        strContent = strContent & objFile.ReadLine
    Loop
    MsgBox strContent
    strContent = Replace(strContent, "encoding=""UTF-8""", "encoding=""windows-1252""")
    outFile.WriteLine strContent
    outFile.Close
    objFile.Close
Next

You can't change the encoding of a file by doing some string replacement. On top of that, vanilla VBScript does not have the ability to do what you want. There is no valid reason to ever want to change the encoding of XML files, unless you are not processing them properly. If that's the case, change the processing code, not the files. — Tomalak
– Tomalak, Commented Jul 8, 2019 at 17:54
These XML files are used in Qlikview for some visualization purpose, there are some special characters appearing in the files that is causing an issue. file loads fine when encoding is changed to windows-1252,,,,this is the reason i am changing encoding from UTF-8 to windows-1252 — mb1987
– mb1987, Commented Jul 8, 2019 at 18:04
What I mean by that: There is not a single XML parser in the world that cannot handle UTF-8 endcoded XML files. Not one. Your wish to change the file encoding can only mean that you are planning to process the files with something other than an XML parser. And you should not do that. (And I'm not even getting into the fact that UTF-8 can contain characters that cannot even exist in Windows-1252.) — Tomalak
– Tomalak, Commented Jul 8, 2019 at 18:04
Then the ETL job has a bug that should probably be looked into, to fix the problem at the source, rather than bend over backwards to fix the symptoms. — Mathieu Guindon
– Mathieu Guindon, Commented Jul 8, 2019 at 18:50
I'm flummoxed by how "a different timezone" is an impediment to contacting someone in this electronic age. You realize that when it's 15:00(UTC-5) for me, someone else could be answering/commenting on this post at 01:00(UTC+5), 'cause they're a night owl or work a night shift? They might even see it the next morning... — FreeMan
– FreeMan, Commented Jul 8, 2019 at 19:08

Ansgar Wiechers · Accepted Answer · 2019-07-09 21:13:57Z

As others have already pointed out, you shouldn't do what you're attempting to do here, because it is very likely to create more problems down the road. Find the cause of the issue and fix that instead of trying to handle symptoms. You have been warned.

With that said, the reason why the content of all input files is written to the same output file is because you always specify the same output file. That file should contain only the content of the last input file, though, because you open the file for writing (thus erasing previous content) rather than for appending.

Replace these lines:

Set objFile = objFSO.OpenTextFile(objStartFolder & objFile.Name, 1)
Set outFile = objFSO.OpenTextFile(objStartFolder & "mod.xml", 2, True)

with this:

Set inFile = objFile.OpenAsTextStream
outFilename = objFSO.BuildPath(objStartFolder, objFSO.GetBaseName(objFile) & "_mod.xml")
Set outFile = objFSO.OpenTextFile(outFilename, 2, True)

and also replace the other occurrences of objFile after that with inFile (always avoid changing the value of a loop variable), and the code should do what you expect it to do. But again, be warned that the output may not be valid XML.

Hi Ansgar, Thanks for your input, I tried modifying the code but it is giving an error : Type mismatch objFSO.GetBaseName CODE: 800A000D
@mb1987 I missed that you were replacing the file object in the loop variable with the text stream from opening that file. Never do that. Always use a different variable.

mb1987 · Accepted Answer · 2019-07-09 18:42:50Z

I managed to made it working, below is the code I used

Dim objFSO, filePath, objFile, colFiles, s , FName
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set filePath = objFSO.GetFolder("\\demo.US\Modified\")
Set colFiles = filePath.Files

For Each FName in colFiles

set objFile = objFSO.OpenTextFile(FName.Path,1)
set outFile = objFSO.OpenTextFile(LEFT(FName.Path,instr(FName.Path,".xml")-1) &"_mod.xml",2,True)

do until objFile.AtEndOfStream
strContent=objFile.ReadLine
Loop
strContent = Replace(strContent, "encoding=""UTF-8""", "encoding=""windows-1252""") 
outFile.WriteLine strContent
outFile.Close
objFile.Close  
Next

Collectives™ on Stack Overflow

Saving file into another file using VBScript after modification

2 Answers 2

2 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Related