July 7th,2010 by Allan Bogh

Some database software export utilities will create CSV files that have illegal characters in them. These CSVs can cause other software to produce errors when importing the null characters. 

If you open one of these bad files up in Notepad++ you might notice a record like this:

    NULL"123","John        ","Doe        "

The following VBScript will remove the null characters from the CSV file and allow it to be imported by the other application (whatever that may be).

RemoveNull.vbs

Const ForReading = 1
Const ForWriting = 8
Dim FileLocation, OutputFile

If WScript.Arguments.Count = 2 Then
    FileLocation = WScript.Arguments.Item(0)
    OutputFile = WScript.Arguments.Item(1)

    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objInFile = objFSO.OpenTextFile(FileLocation, ForReading)
    Set objOutFile = objFSO.OpenTextFile(OutputFile, ForWriting, True)

    Do While objInFile.AtEndOfStream <> True
        strLine = objInFile.ReadLine
        strLine = Replace(strLine,vbNullChar,"")
        objOutFile.WriteLine strLine
    Loop

    objInFile.Close
    objOutFile.Close
    Set objFSO = Nothing
Else
    WScript.Echo "Usage: cscript -nologo RemoveNulls.vbs Infile.csv Outfile.csv"
End If

To run the script, open a command prompt, then type:

cscript -nologo RemoveNulls.vbs "C:\Somedir\Infile.csv" "C:\Somedir\Outfile.csv"

The Outfile.csv file will match your original CSV file, however it will be stripped of NULL characters.