Excel Import

MichelleK365 << Excel only allows me to use the PowerQuery… >>

But Excel also includes VBA.  So if your file is not larger than 2 GB, you can use VBA code to load the content to Excel (as that seems to be desirable) or just split the content with unbalanced quotes out from the rest into two different files.

Here's a procedure that writes the content to Main and Errors worksheets.  If you have more than 1,048,576 good lines (or error lines, I hope not), I included a couple commented-out lines of code to allow you to work with multiple passes of the data:

Sub LoadBigFile()
'   This procedure loads lines from a text file to one of two worksheets:
'       if the line contains no quotes or an even number of qutoes, it
'       is loaded to the main worksheet; if it contains an odd number of
'       quotes, it is loaded to the errors worksheet.

    Const QUOTE = """"
    
    Dim in2VBFileNum    As Integer
    
    Dim strLine     As String
    Dim in4Line     As Long
    Dim in4CharPosn As Long
    Dim in2Quotes   As Integer
    '
    Dim in4GoodLines    As Long
    Dim in4Errors   As Long
    
    '----   Process the file.
    '  --   Don't let Excel screen updating slow processing down:
    Application.ScreenUpdating = False
    '  --
    in2VBFileNum = FreeFile(0)
    Open "F:\Temp\BigFile.txt" For Input As in2VBFileNum
    Do Until EOF(in2VBFileNum)
        '  --   Read a line:
        Line Input #in2VBFileNum, strLine
        in4Line = in4Line + 1
        '  --
        'If in4Line > 750000 Then Exit Do
        'If in4Line <= 750000 Then GoTo NextLine
        
        '  --   Examine the content of the line:
        If InStr(1, strLine, QUOTE) = 0 Then
            '(An efficient way of determining that...)
            'The line contains no quotes.
            in2Quotes = 0
        Else
            'Count the quotes:
            in2Quotes = 0
            For in4CharPosn = 1 To Len(strLine)
                If Mid$(strLine, in4CharPosn, 1) = QUOTE Then
                    in2Quotes = in2Quotes + 1
                End If
            Next in4CharPosn
        End If
        '  --   Output this line, depending on that count:
        If in2Quotes Mod 2 = 1 Then
            in4Errors = in4Errors + 1
            Debug.Assert in4Errors <= 1048576
            With Sheets("Errors")
                .Cells(in4Errors, 1) = in4Line
                .Cells(in4Errors, 2) = strLine
            End With
        Else
            in4GoodLines = in4GoodLines + 1
            Debug.Assert in4GoodLines <= 1048576
            With Sheets("Main")
                .Cells(in4GoodLines, 1) = in4Line
                .Cells(in4GoodLines, 2) = strLine
            End With
        End If
NextLine:
    Loop
    Close in2VBFileNum
    '  --   Restore Excel screen updating:
    Application.ScreenUpdating = False
    
    '----   Report results:
    Call MsgBox("Processed " & Format$(in4Line, "##,###,##0") & " lines, with " _
            & Format$(in4Errors, "##,###,##0") & " unbalanced quotes" _
            , vbInformation)

End Sub

(You don't even have to save the result as a macro-enabled workbook; you can save the workbook without the VBA if it runs satisfactorily.)