We help IT Professionals succeed at work.

How to remove BOM hex characters from csv file

Does anyone have any help on how to remove BOM (EF BB BF) hex values from the beginning of a csv file?  I have a static csv file that is generated daily and I need to remove those hex values which are at the beginning of the file only.  Not a VB programmer but that would work best for automating to run daily on our server.
Comment
Watch Question

Bill PrewTest your restores, not your backups...
Expert of the Year 2019
Top Expert 2016

Commented:
Can you provide a sample file ?

~bp

Author

Commented:
Sure thing - here you go
invfeed.csv
Test your restores, not your backups...
Expert of the Year 2019
Top Expert 2016
Commented:
Here's an approach I have seen used and seemed to work for me on your test file.  It should be safe than just removing the first 3 bytes of the file and writing the rest out.

Run it with either one or two parms on the command line.  If two filenames specified then it will read the first file, adjust it and write to the second file.  If only one file specified then it will overwrite the file with the changed versions.

' Define needed constants
Const ForReading = 1
Const ForWriting = 2
Const TriStateUseDefault = -2
Const adTypeText = 2

' Get input file name from command line parm, if 2 parms entered
' use second as new output file, else rewrite to input file
If (WScript.Arguments.Count > 0) Then
  strInFile = WScript.Arguments(0)
Else
  WScript.Echo "No filename specified."
  WScript.Quit
End If
If (WScript.Arguments.Count > 1) Then
  strOutFile = WScript.Arguments(1)
Else
  strOutFile = strInFile
End If

' Convert UTF-8 file to ANSI file
Set objStreamFile = CreateObject("Adodb.Stream")
With objStreamFile
    .CharSet = "UTF-8"
    .Type= adTypeText
    .Open
    .LoadFromFile strInFile
    strContent = .readText
    .Close
End With
Set objStreamFile = Nothing

' Write out after "conversion"
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile(strOutFile, ForWriting, True)
objFile.Write strContent
objFile.Close

Open in new window

~bp

Author

Commented:
Bill, this ran perfectly.  Thanks so much!
Bill PrewTest your restores, not your backups...
Expert of the Year 2019
Top Expert 2016

Commented:
Welcome, thanks for the feedback.

~bp

Author

Commented:
Bill is there any way this can be run where the parm1 and parm2 files can be a unc path?

Author

Commented:
Actually Bill my issue is that the script provided worked fine on my Win7 machine but not on Windows Server 2003.  Do you happen to know why?  I get a Windows Script Host error - "Execution of the Windows Script Host failed.  (The parameter is incorrect.)."

Author

Commented:
Please disregard my two previous comments.  This script works fine on Windows Server 2003 as well as unc path's for the parms
Bill PrewTest your restores, not your backups...
Expert of the Year 2019
Top Expert 2016

Commented:
Okay, good to hear.

~bp