CSV导入Excel拆分异常求助：注释字段含换行符

Got it, let's tackle this problem head-on. The root cause here is that your comment field contains line breaks inside quoted values, and the basic Split() function doesn't understand CSV's core rule: quoted fields can include line breaks, commas, and other special characters without breaking the structure. When you split on newlines, you're chopping those comment fields mid-value, which causes the weird split results you're seeing.

Plus, I noticed your CSV has some full-width double quotes (like ”id” instead of "id")—those will throw off most CSV parsers, so first thing: replace all those full-width quotes with standard English half-width ones (") using Notepad or a text editor.

Here are two solid solutions to fix this:

1. Use VBA's TextFieldParser (Built-In CSV Parser) #

This is the most reliable way to handle CSV files with quoted line breaks in VBA. It's designed specifically for this kind of structured text parsing.

Here's a complete code example you can adapt:

Sub ParseCSVWithEmbeddedLineBreaks()
    Dim csvPath As String
    Dim textParser As Object
    Dim outputRow As Integer
    Dim parsedFields As Variant
    
    ' Update this to your CSV file path
    csvPath = "C:\Path\To\Your\File.csv"
    
    ' Initialize the TextFieldParser (late binding, no reference needed)
    Set textParser = CreateObject("Microsoft.VisualBasic.FileIO.TextFieldParser")
    textParser.SetTextFieldParser csvPath
    
    ' Configure parser settings to match your CSV
    textParser.Delimiters = Array(",")
    textParser.TextQualifier = """" ' Critical: tells parser to respect quoted fields
    textParser.TrimWhiteSpace = True ' Optional: cleans up extra spaces around values
    
    outputRow = 1 ' Start writing data to row 1 of your active sheet
    
    ' Loop through each row of data
    Do While Not textParser.EndOfData
        On Error Resume Next ' Catch any parsing errors (e.g., malformed lines)
        parsedFields = textParser.ReadFields
        On Error GoTo 0
        
        ' Write the parsed fields to Excel
        If Not IsEmpty(parsedFields) Then
            Range(Cells(outputRow, 1), Cells(outputRow, UBound(parsedFields) + 1)).Value = parsedFields
            outputRow = outputRow + 1
        End If
    Loop
    
    ' Clean up
    textParser.Close
    Set textParser = Nothing
    MsgBox "CSV parsed successfully!", vbInformation
End Sub

How this works: #

• The TextFieldParser automatically recognizes that line breaks inside quoted fields are part of the field value, not a new row.
• It handles the comma delimiters correctly even when commas are inside quoted fields (though your example doesn't have that, it's a nice bonus).

2. Use Excel's Power Query (No Code Needed) #

If you don't want to mess with VBA, Power Query (built into Excel 2016+) is a super easy way to import CSV files with embedded line breaks correctly:

• Go to the Data tab > Get Data > From File > From CSV
• Select your CSV file and click Import
• Power Query will automatically detect the quoted fields and preserve line breaks inside them. You can then load the cleaned data directly into your worksheet.

Quick Tip: #

Always avoid parsing CSV manually with Split() unless you're 100% sure there are no special characters (line breaks, commas, quotes) inside fields. CSV has more rules than just "split on commas/newlines"—built-in parsers handle all those edge cases for you.

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