Hello, I am working on a project which takes a > 1M line tab delimited text file to several Excel worksheets.

I got the file to work somewhat: you see, the file has some empty fields between each other and my project treats it as one tab. This, in return, makes the fields go to different columns. I noticed this was due to the .TextFileConsecutiveDelimiter = True. However, when I comment out this line and try to export the file again I get an error telling me there's too much data on the first sheet. This is far before all files are imported to the Excel file. The only way I can fix this is to uncomment the aforementioned line, but I am back to having the columns in the wrong places.

I am stuck, can someone please help me? Thank you so much!

PD: the files needed are located on a folder in the project itself. They're created when the user imports the necessary large file.

Code:
Private Sub createExcel(pagina)
        encabezados.Clear()
        Dim listaEncabezados = sacarEncabezadosCargaAmazon()
        If Not listaEncabezados.EOF Then
            While Not listaEncabezados.EOF
                For i = 1 To listaEncabezados.Fields.Count - 1
                    encabezados.Add(listaEncabezados.Fields(i).Name)
                Next
                listaEncabezados.MoveNext()

            End While
        End If
        Dim hoja = 1
        Dim excel As Excel.Application = New Microsoft.Office.Interop.Excel.Application()
        If excel Is Nothing Then
            MessageBox.Show("Error. Excel 2016 no está instalado en esta computadora", "Error")
            Exit Sub
        End If
        Dim iSheetsPerBook As Integer
        Dim misValue As Object = System.Reflection.Missing.Value

        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet

        excel.SheetsInNewWorkbook = pagina

        For i = 1 To pagina
            iSheetsPerBook = excel.SheetsInNewWorkbook
            xlWorkBook = excel.Workbooks.Add(misValue)
            excel.SheetsInNewWorkbook = iSheetsPerBook
        Next

        For x = 1 To pagina
            Dim archivo = String.Format("{0}\ArchivosExcel\excel{1}.txt", dondeEstaElArchivo, x)
            xlWorkSheet = xlWorkBook.Worksheets(hoja)
            Dim qt As QueryTable = xlWorkSheet.QueryTables.Add(Connection:=String.Format("TEXT;{0}", archivo), Destination:=xlWorkSheet.Range("$A$2"))

            With qt
                .Name = "Import" & hoja
                .FieldNames = True
                .RowNumbers = False
                .FillAdjacentFormulas = False
                .PreserveFormatting = True
                .RefreshOnFileOpen = False
                .RefreshStyle = XlCellInsertionMode.xlInsertDeleteCells
                .SavePassword = False
                .SaveData = True
                .AdjustColumnWidth = True
                .RefreshPeriod = 0
                .TextFilePromptOnRefresh = False
                .TextFilePlatform = 1252
                .TextFileStartRow = 1
                .TextFileParseType = XlTextParsingType.xlDelimited
                .TextFileTextQualifier = XlTextQualifier.xlTextQualifierDoubleQuote
                .TextFileConsecutiveDelimiter = True
                .TextFileTabDelimiter = True
                .TextFileSemicolonDelimiter = False
                .TextFileCommaDelimiter = False
                .TextFileSpaceDelimiter = False
                .TextFileColumnDataTypes = GetColumnDataTypes(xlWorkSheet.Columns.Count)
                .TextFileTrailingMinusNumbers = True
                .BackgroundQuery = True
                .Refresh()
            End With

            Dim columnaEncabezado = 1
            For Each enc As String In encabezados
                xlWorkSheet.Cells(1, columnaEncabezado) = enc
                columnaEncabezado += 1
            Next

            hoja += 1
        Next


        Dim saveDialog As New SaveFileDialog()
        saveDialog.Title = "Por favor elija donde quiere guardar el archivo"
        saveDialog.Filter = "Excel 2007-on (*.xlsx)|*.xlsx"
        Dim respuesta = saveDialog.ShowDialog()
        If respuesta = DialogResult.Cancel Or saveDialog.FileName = "" Then
            Exit Sub
        End If

        Dim fileSave = saveDialog.FileName
        xlWorkBook.SaveAs(fileSave)
        xlWorkBook.Close(True, misValue, misValue)
        excel.Quit()

        releaseObject(xlWorkSheet)
        releaseObject(xlWorkBook)
        releaseObject(excel)
    End Sub