Results 1 to 10 of 10

Thread: Error importing text file to Excel Sheets

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Mar 2016
    Location
    San Diego
    Posts
    16

    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

  2. #2
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,046

    Re: Error importing text file to Excel Sheets

    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
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  3. #3

    Thread Starter
    Junior Member
    Join Date
    Mar 2016
    Location
    San Diego
    Posts
    16

    Re: Error importing text file to Excel Sheets

    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

  4. #4

    Thread Starter
    Junior Member
    Join Date
    Mar 2016
    Location
    San Diego
    Posts
    16

    Unhappy Re: Error importing text file to Excel Sheets

    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

  5. #5

    Thread Starter
    Junior Member
    Join Date
    Mar 2016
    Location
    San Diego
    Posts
    16

    Re: Error importing text file to Excel Sheets

    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.

  6. #6
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,046

    Re: Error importing text file to Excel Sheets

    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 ?
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  7. #7

    Thread Starter
    Junior Member
    Join Date
    Mar 2016
    Location
    San Diego
    Posts
    16

    Re: Error importing text file to Excel Sheets

    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

  8. #8
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,046

    Re: Error importing text file to Excel Sheets

    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
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  9. #9

    Thread Starter
    Junior Member
    Join Date
    Mar 2016
    Location
    San Diego
    Posts
    16

    Re: Error importing text file to Excel Sheets

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

  10. #10
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,196

    Re: Error importing text file to Excel Sheets

    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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width