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!

Answer 1

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 [e-mail].

Answer 2

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