VBA code that loops through an Excel table and generates list separated by "|" statements based on the data.

ExportTableToPipeSeparated.bas

Sub ExportTableToPipeSeparated()

    Dim wb As Workbook
    Set wb = ThisWorkbook
    
    Dim ws As Worksheet
    Dim wsName As String
    wsName = "your_worksheet_name"  'Replace with your sheet name
    Set ws = wb.Sheets(wsName)
    ws.Select
    
    Dim lastRow As Long
    Dim anchorColumn As String
    anchorColumn = "B" ' Define column that has full value (without blank cells)
    lastRow = ws.Cells(ws.Rows.Count, anchorColumn).End(xlUp).row
    
    Dim lastCol As Long
    lastCol = ws.Cells(1, Columns.Count).End(xlToLeft).Column
    
    Dim rangeName As String
    rangeName = "your_defined_range" 'add defined range for scope of data that wants to process       
    
    Dim outputText As String
    Dim i As Long
    Dim j As Long
    
    Dim startRow As Long
    Dim startCol As Long
    startRow = 2 'Assuming the first row contains headers, so starting from row 2
    startCol = 1 'Define here for data to insert start from which column
    
    Dim fileName As String
    fileName = ThisWorkbook.Path & "\sql\tags_" & Utility.FormattedDateTime() & ".txt"
    
    Open fileName For Output As #1
    
    Dim sqlStatement As String
    For i = startRow To lastRow
        sqlStatement = ""
        
        For j = startCol To lastCol
            Dim currentValue As String
            currentValue = ws.Range(rangeName).Cells(i, j).Value
            
            If IsEmpty(ws.Range(rangeName).Cells(i, j).Value) Then
                sqlStatement = sqlStatement & "NULL"            
            Else
                sqlStatement = sqlStatement & currentValue
            End If
            
            If j < lastCol Then
                sqlStatement = sqlStatement & "|"
            End If
        Next j
        Print #1, sqlStatement
    Next i
    
    Close #1
    
    MsgBox "SQL statements have been exported to " & fileName

End Sub