Monday 31 January 2011

VBScript to convert the comma-delimited file to a carriage-return delimited file

'This script will convert the comma-delimited file to a carriage-return delimited file.

Const ForReading = 1
Const ForWriting = 2

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFSOWrite = CreateObject("Scripting.FileSystemObject")

'Set folder
objStartFolder = "C:\Folder"

Set objFolder = objFSO.GetFolder(objStartFolder)

Set colFiles = objFolder.Files

'Loop through all the files in the folder
For Each objFile in colFiles
    'This line will output the name of the file including the extension
    objFileNameParts = split(objFile.Name,".")
    'Remove the extension from the file name
    objFileName = objFileNameParts(0)   
    'msgbox("FileName: " & objFileName)
    objReadFilePath = objFolder & "\" & objFileName & ".csv"
    objWriteFilePath = objFolder & "_OUTPUT\" & objFileName & ".csv"
    'msgbox("ReadFilePath: "& objReadFilePath)   
    'msgbox("WriteFilePath: "& objWriteFilePath)
    Set objFile = objFSO.OpenTextFile(objReadFilePath, ForReading)   
    Do Until objFile.AtEndOfStream
        strLine = objFile.ReadLine
        'Replace commas(,) with carriage return(vbCrLF)
        strLine = Replace(strLine, ",", vbCrLF)       
        strNewText = strLine   
    Loop
    objFile.Close
    'Create a new file in not created before, otherwise rewrite in the existing file
    If objFSO.FileExists(objWriteFilePath) Then
        Set objFile = objFSO.OpenTextFile(objWriteFilePath, ForWriting)
        MSGBOX("FILE OPENED")
    Else
        set objFile = objFSO.CreateTextFile(objWriteFilePath)
        MSGBOX("FILE CREATED")
    End If
    objFile.WriteLine strNewText

    objFile.Close
Next