Format the data from Textfile to a CSV file

Question

I'm writing a script in VB Script to format Active directory data. I am using a Bat Script to produce the output in txt and I want to make a VB Script to format the data with headers.

My Batch script is

dsquery * -limit 0 -filter (objectclass=computer) -attr Name distinguishedName operatingSystem operatingSystemVersion whenChanged whenCreated > fullq.txt

The VB Script that I have is this currently

Option Explicit

Dim FSO, TextPath, CSVPath
Dim Textline, oText, oCSV
Dim CN, OU, i

Set FSO = CreateObject("Scripting.FileSystemObject")

TextPath = "fullq.txt"
Set oText = FSO.OpenTextFile(TextPath,1)

CSVPath = "fullq.csv"
Set oCSV = FSO.CreateTextFile(CSVPath, 2 ,False)

oCSV.WriteLine("Name,DistinguishedName,OperatingSystem,OperatingSystemVersion,whenChanged,whenCreated")
oCSV.WriteLine

oText.SkipLine
Do Until oText.AtEndOfStream
    Textline = oText.ReadLine()

    OU = Split(TextLine, ",")
    CN = Mid(OU(0), 5)

    oCSV.Write CN & ","
    For i = 1 To UBound(OU)
        oCSV.Write OU(i)
        If i < UBound(OU) Then
            oCSV.Write("/")
        End If
    Next
    oCSV.Writeline
Loop

oCSV.WriteLine
oCSV.Close

The problem i have is the operatingSystem, operatingSystemVersion, whenCreated and whenChanged data is in distinguishedName column. I want it to be in their own respective column. The code above is mainly for DistinguishedName. I added new attribute which are operatingSystem, operatingSystemVersion, whenChanged, whenCreated. For distinguishedName, i would like to replaces the comma with "/" whereas the rest of the attributes I would like it to be written in their own respective column. I cant use PowerShell as the company blocked it.

The Output in the CSV look like this:

Name,DistinguishedName,OperatingSystem,OperatingSystemVersion,whenChanged,WhenCreated

PC1,OU=1/OU=2/OU=3/DC=4/DC=COM    Window    123    12/12/12 10:10:10 12/12/12 10:10:10 , , ,

• Note that the comma represent new column

As written above, i tried this VB Script but the operatingSystem, operatingSystemVersion, whenCreated and whenChanged data is in distinguishedName column. I want it to be in their own respective column.

This is how the the fullq.txt would look like:

Name        DistinguishedName              OperatingSystem        OperatingSystemVersion   whenChanged        WhenCreated
PC1         OU=1/OU=2/OU=3/DC=4/DC=COM     Window                    123 
      12/12/12 10:10:10     12/12/12 10:10:10 

Answer 1

Given the fullq.txt sample provided, the input can be split on " " (two spaces) and then ignore empty entries and trim results:

Const Excel = True

Set oFSO = CreateObject("Scripting.FileSystemObject")

TextPath = "fullq.txt"
Set oText = oFSO.OpenTextFile(TextPath,1)

CSVPath = "fullq.csv"
Set oCSV = oFSO.CreateTextFile(CSVPath,2,True)

If Excel Then oCSV.WriteLine "SEP=,"
oCSV.WriteLine "Name,DistinguishedName,OperatingSystem,OperatingSystemVersion,whenChanged,whenCreated"

oText.SkipLine

Do Until oText.AtEndOfStream
    NewLine = ""
    Textline = oText.ReadLine
    aTextLine = Split(Textline,"  ")
    For i = 0 To UBound(aTextLine)
      If aTextLine(i)<>"" Then
        If NewLine<>"" Then NewLine = NewLine & ","
        NewLine = NewLine & Trim(aTextLine(i))
      End If
    Next
    oCSV.Writeline NewLine
Loop

oText.Close
oCSV.Close

Note that the sample input contained no commas, so I removed the code that splits on commas.