Creating the "BAT-AddStudentsToClasses.csv" File USING MS Excel

Option Explicit

Public Sub Convert()

    Const FC As Long = 1    'first col (ID)

    Const FR As Long = 2    'first row

    Const NEXT_COL As Long = FC + 1

    Const DB_WS_NAME As String = "KidsClasses"

    Dim ws As Worksheet, db As Worksheet, lr As Long, lc As Long, maxRow As Long

    Dim arr1 As Variant, arr2 As Variant, i As Long, j As Long, k As Long

    Set ws = Worksheets("SheetToConvert")   'main sheet -----------------------------------------

    lr = ws.Cells(ws.UsedRange.Rows.Count + 1, FC).End(xlUp).Row

    lc = ws.UsedRange.Columns.Count

    If lr >= FR Then

        maxRow = (lr - (FR - 1)) * (lc - FC) + FR   'set result area

        Application.ScreenUpdating = False: Application.DisplayAlerts = False

        For Each db In Worksheets

            If db.Name = DB_WS_NAME Then

                db.Delete: Exit For

            End If

        Next

        Set db = Worksheets.Add(After:=ws): db.Name = DB_WS_NAME

        arr1 = ws.Range(ws.Cells(FR, FC), ws.Cells(lr, lc)).Value2

        arr2 = db.Range(db.Cells(FR, FC), db.Cells(maxRow, NEXT_COL)).Value2

        k = FR - 1

        For i = FR - 1 To lr - (FR - 1) 'all rows

            For j = NEXT_COL To lc      'all cols

                If Len(arr1(i, j)) = 0 Then Exit For  'exit inner For (this row is done)

                arr2(k, FC) = arr1(i, FC)

                arr2(k, NEXT_COL) = arr1(i, j)

                k = k + 1

            Next

        Next

        db.Range(db.Cells(FR, FC), db.Cells(maxRow, NEXT_COL)).Value2 = arr2

        Application.DisplayAlerts = True: Application.ScreenUpdating = True

    End If

End Sub