Excel VBA读取SQL文件遇编码问题：特殊前缀字符致语法错误

Hey there, let's break down how to fix those annoying  characters messing up your SQL queries in VBA! These characters are actually the UTF-8 Byte Order Mark (BOM)—a hidden prefix that can throw off SQL parsers and cause unexpected issues when reading files in VBA. Here's how to tackle both your problems:

Problem 1: SQL Syntax Error Due to BOM Prefix #

That  prefix is a UTF-8 BOM added by some text editors when saving files. SQL engines don't recognize this as valid syntax, which is why you're getting a ">>"附近语法错误 (syntax error near ">>")—the parser is choking on those unrecognized characters.

Quick Solutions: #

• Manual cleanup: Open your SQL file in a text editor like Notepad++, go to Encoding > Convert to UTF-8 without BOM, then save. This removes the BOM entirely at the source.
• VBA-side removal: If you can't modify the file directly, add a check to strip the BOM after reading the SQL string:

  ' After loading strSQL from the file
  If Left(strSQL, 3) = ChrW(239) & ChrW(187) & ChrW(191) Then
      strSQL = Mid(strSQL, 4) ' Cut off the first 3 BOM characters
  End If
  

Problem 2: VBA Reading Files Adds BOM Characters #

Your original VBA code is probably using the default Open statement, which doesn't handle UTF-8 BOM properly. Instead, use ADODB.Stream to read the file with explicit encoding control—this avoids pulling the BOM into your SQL string.

Updated VBA Code: #

Replace your file-reading logic with this improved version:

Option Explicit

Sub SomeExtract()
    ' Disable app settings for performance
    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
        .Calculation = xlCalculationManual
        .EnableEvents = False
    End With

    Dim strSQL As String, filePath As String
    Dim connSQL As ADODB.Connection, serverName As String, databaseName As String, userID As String
    Dim stream As ADODB.Stream

    ' Set your SQL file path here
    filePath = "C:\Your\File\Path\query.sql"

    ' Use ADODB.Stream to read UTF-8 correctly (skips BOM)
    Set stream = New ADODB.Stream
    With stream
        .Charset = "UTF-8"
        .Open
        .LoadFromFile filePath
        strSQL = .ReadText ' Reads content without including the BOM
        .Close
    End With
    Set stream = Nothing

    ' Optional safety check: Remove BOM if it still slips through
    If Left(strSQL, 3) = ChrW(239) & ChrW(187) & ChrW(191) Then
        strSQL = Mid(strSQL, 4)
    End If

    ' Rest of your connection/query execution code goes here...

    ' Restore app settings
    With Application
        .ScreenUpdating = True
        .DisplayAlerts = True
        .Calculation = xlCalculationAutomatic
        .EnableEvents = True
    End With
End Sub

Why This Works: #

ADODB.Stream with Charset = "UTF-8" automatically detects and skips the UTF-8 BOM, so your strSQL variable will start with the actual SQL code instead of the invalid prefix.

Bonus Preventive Tip: #

When saving SQL files, always select UTF-8 without BOM in your text editor (Notepad++, VS Code, etc.). This stops the BOM from being added in the first place, eliminating the issue entirely.

内容的提问来源于stack exchange，提问作者ArsedianIvan