Error importing text file to Excel Sheets

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

---

Hi

for testing setup a DataTable and put the Tabdelim. Textfile in a Datagridview first (see Button1)
I added 4 Fields in my Test file

if the Data is correct export it (see Button2)

Code:

---

Imports System.IO
Imports Microsoft.Office.Interop


Public Class Form1

    Private Dt As New System.Data.DataTable
    'read the Tab file
    Private fn As String = "E:\tabDelim.txt"

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim r As System.Data.DataRow
        With Dt
'4 Fields in my sample Data
            .Columns.Add("F1", GetType(String))
            .Columns.Add("F2", GetType(String))
            .Columns.Add("F3", GetType(String))
            .Columns.Add("F4", GetType(String))

            Dim sr As New IO.StreamReader(fn)
            Do While sr.Peek() <> -1
                r = .NewRow
                r.ItemArray = sr.ReadLine().Split(New Char() {vbTab(0)}, 4, System.StringSplitOptions.None)
                .Rows.Add(r)
            Loop
            sr.Close()
            'now you have a Datatable to work with
            DataGridView1.DataSource = Dt
        End With

    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        'Author/Source: kpmc / DGV -CopyClipboard
        DataGridView1.DataSource = Dt
        DataGridView1.RowHeadersVisible = False
        DataGridView1.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableAlwaysIncludeHeaderText
        Clipboard.Clear()

        'apply any RowFilters
        DataGridView1.SelectAll()
        Clipboard.SetDataObject(DataGridView1.GetClipboardContent)
        DataGridView1.ClearSelection()

        If Clipboard.GetDataObject IsNot Nothing Then

            Dim _Excel As New Excel.Application With { _
                .Visible = True}

            Dim _WorkBook As Excel.Workbook
            _WorkBook = _Excel.Workbooks.Add()
            Dim _Worksheet As Excel.Worksheet
            _Worksheet = CType(_WorkBook.Worksheets(1), Excel.Worksheet)

            _Worksheet.Range("A1").Select()
            _Worksheet.Paste()

            _Worksheet.UsedRange.Columns.ColumnWidth = 50
            _Worksheet.UsedRange.Columns.AutoFit()
            _Worksheet.UsedRange.Rows.AutoFit()



        End If
    End Sub


    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'Dim r As System.Data.DataRow
        'With Dt
        '    .Columns.Add("F1", GetType(String))
        '    .Columns.Add("F2", GetType(String))
        '    .Columns.Add("F3", GetType(String))
        '    .Columns.Add("F4", GetType(String))

        '    Dim sr As New IO.StreamReader(fn)
        '    Do While sr.Peek() <> -1
        '        r = .NewRow
        '        r.ItemArray = sr.ReadLine().Split(New Char() {vbTab(0)}, 4, System.StringSplitOptions.None)
        '        .Rows.Add(r)
        '    Loop
        '    sr.Close()
        '    'now you have a Datatable to work with
        ''    DataGridView1.DataSource = Dt
        'End With


        ''Author/Source: kpmc / DGV -CopyClipboard
        'DataGridView1.DataSource = Dt
        'DataGridView1.RowHeadersVisible = False
        'DataGridView1.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableAlwaysIncludeHeaderText
        'Clipboard.Clear()

        ''apply any RowFilters
        'DataGridView1.SelectAll()
        'Clipboard.SetDataObject(DataGridView1.GetClipboardContent)
        'DataGridView1.ClearSelection()

        'If Clipboard.GetDataObject IsNot Nothing Then

        '    Dim _Excel As New Excel.Application With { _
        '        .Visible = True}

        '    Dim _WorkBook As Excel.Workbook
        '    _WorkBook = _Excel.Workbooks.Add()
        '    Dim _Worksheet As Excel.Worksheet
        '    _Worksheet = CType(_WorkBook.Worksheets(1), Excel.Worksheet)

        '    _Worksheet.Range("A1").Select()
        '    _Worksheet.Paste()

        '    _Worksheet.UsedRange.Columns.ColumnWidth = 50
        '    _Worksheet.UsedRange.Columns.AutoFit()
        '    _Worksheet.UsedRange.Rows.AutoFit()



        'End If
    End Sub

End Class

---

HTH

Hello ChrisE, thank you for your reply.

Is there a way I can use the DataTable directly without resorting to a datagridview? I am working with too much data and I don't want the application to be slower due to this.

Thank you again

Hello ChrisE, thank you for your reply.

Is there a way I can use the DataTable directly without resorting to a datagridview? I am working with too much data and I don't want the application to be slower due to this. :cry:

Thank you again

Hello again ChrisE, I tried your method anyway and it ran out of memory on the first try :(

Each file has a limit of one million lines, so that could be the reason.

Hi,

do you really want Excel sheets with 1 Mio. lines ?

anyway why not sort the problem first with the blank spaces.
do you know Powershell ?

Hello,

I need to have as much information as possible with as little sheets as possible, so I need to say yes.

And, to answer your second question, I don't know Powershell

why not use a Database ?
Access ,Sql-Server

you could sort and group the Data there and then Export to Excel ?

I think Access has a Import Function for Text files

Thank you for reminding me. I am using MySQL to write the text files. ADODB

If all you want to do is put the text file data into an Excel file then just open the text file in excel and use the SaveAs method on the WorkBook.

Code:

---

Imports Excel = Microsoft.Office.Interop.Excel
Imports Microsoft.Office
Public Class Form9

    Public Sub OpenExcelDemo(ByVal FileName As String)
        If IO.File.Exists(FileName) Then
            Dim xlApp As Excel.Application = Nothing
            Dim xlWorkBook As Excel.Workbook = Nothing
            xlApp = New Excel.Application
            xlApp.DisplayAlerts = False
            xlWorkBook = xlApp.Workbooks.Open(FileName)
            xlApp.Visible = True

            'MessageBox.Show("File was opened")
            xlWorkBook.SaveAs(Filename:="C:\Ajunk2015\TestSaveAs.xlsx", FileFormat:=Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook)
            xlApp.Quit()
            ReleaseComObject(xlWorkBook)
            ReleaseComObject(xlApp)
        Else
            MessageBox.Show("'" & FileName & "' not located. Try one of the write examples first.")
        End If
    End Sub
    Public Sub ReleaseComObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        End Try
    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        OpenExcelDemo("C:\ajunk2015\book1.txt")
    End Sub
End Class

---