none
How to import .txt file into excel sheet?

    Question

  • Hi everyone,

    I made a simulation tool exports .txt data files, data is split by ";". And I want to add a report-making function in this tool in excel format which will include all .txt files. I wrote this tool in VB.NET. 

    Currently in this tool I read data in .txt file and store them in several list(of double), and then write them into excel cells in iterations. It works but very slow. Is there any way to copy the .txt file to excel sheets directly?

    I found some information like below but I open only .txt not in excel in the end

            Dim oApp As New Excel.Application
            Dim oWB As Excel.Workbook
            Dim oWS As Excel.Sheets
            oWB = oApp.Workbooks.Open("D:\1.CSV", Format:=6, Delimiter:=";")
            oApp.Visible = True
            oWB.Activate()
    Thank you!

    Tuesday, February 12, 2019 4:58 AM

Answers

All replies

  • Hi,

    see the following link:

    https://stackoverflow.com/questions/41305356/vb-net-to-import-text-file-into-excel-delimited-by-spaces

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by goldenview Tuesday, February 12, 2019 8:43 AM
    Tuesday, February 12, 2019 6:17 AM
    Moderator
  • Thank you for your reply, the QueryTable works. Hear I want to share the code is the right way to do this import:

    Imports Microsoft.Office.Interop.Excel
    Imports System.IO
    Module Module1
        Sub Main()
            Dim targetWorksheet As Worksheet = GetTargetWorksheet("c:\test.xls")
            If targetWorksheet Is Nothing Then
                Debug.WriteLine("Target sheet is Nothing.")
                Exit Sub
            End If
    
            Try
                Dim qt As QueryTable
                qt = targetWorksheet.QueryTables.Add(
            Connection:="TEXT;d:\1.csv",
            Destination:=targetWorksheet.Range("$A$1"))
    
                With qt
                    .Name = "Import"
                    .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 = False
                    .TextFileSemicolonDelimiter = True
                    .TextFileCommaDelimiter = False
                    .TextFileSpaceDelimiter = False
                    .TextFileColumnDataTypes = GetColumnDataTypes(targetWorksheet.Columns.Count)
                    .TextFileTrailingMinusNumbers = True
                    .Refresh(BackgroundQuery:=False)
                End With
    
            Catch ex As Exception
                Debug.WriteLine("The file could not be read:")
                Debug.WriteLine(ex.Message)
            End Try
        End Sub
    
        Private Function GetColumnDataTypes(queryTableColumnsCount As Long) As Object
            Dim textDataTypes As xlColumnDataType()
            textDataTypes = Enumerable.Repeat(xlColumnDataType.xlTextFormat, queryTableColumnsCount).ToArray()
            Return textDataTypes
        End Function
    
        Private Function GetTargetWorksheet(targetPath As String) As Worksheet
            Try
                Dim excelApplication = New Application
                excelApplication.Visible = True
                Dim excelWorkbook As Workbook
                excelWorkbook = excelApplication.Workbooks.Add()
                excelWorkbook.SaveAs(targetPath)
                Dim excelWorksheet As Worksheet = excelWorkbook.Worksheets.Add()
                excelWorksheet.Name = "Import"
                Return excelWorksheet
            Catch ex As Exception
                Debug.WriteLine("The excel worksheet could not be created:")
                Debug.WriteLine(ex.Message)
            End Try
            Return Nothing
        End Function
    
    End Module
    

    Tuesday, February 12, 2019 8:45 AM