vb.net to import text file into excel delimited by spaces

Question

was wondering if anyone has an example vb code to import a text file into excel delimited by spaces - regardless of number of spaces. In the text file there are for example 100k lines and in each line, each word can be separated by one, two or three etc spaces.

the result of the import into excel is that each line from the text file is in each row, and each word from each line separated by spaces are in each column of that row.

I tried to accomplish this by reading each line in the text file and then to parse each word for each line, and put these into a variable and then write it to excel. I think this way takes longer, and I am in the middle of parsing each line. But I think importing the text file into excel delimited by spaces is quicker if this can be done. The reason I use vb instead of vba is because vb can create an executable file which can be run by scheduler. Thanks

Dim reader As New System.IO.StreamReader("C:\test.txt")
Dim allLines As List(Of String) = New List(Of String)
Dim stringreader As String
Dim a As String
    stringreader = filereader.ReadLine()

    Do While Not reader.EndOfStream
      allLines.Add(reader.ReadLine())
      stringreader = reader.ReadLine()
      MsgBox("The first line of the file is                " & stringreader)
    Loop

Answer 1

In this example StreamReader and Excel are opened first. Then new Workbook and new Worksheet are added. Finally the text file is read line by line. Each line is split on spaces and written to Excel Worksheet. After the text file was processed the Stream is closed and the Excel with the results remains opened. HTH

Install Office Primary Interop Assemblies for your version of Excel.

(Example uses reference to Ecel 2007 PIA: C:\Windows\assembly\GAC\Microsoft.Office.Interop.Excel\12.0.0.0__71e9bce111e9429c\Microsoft.Office.Interop.Excel.dll)

Imports System.IO
Imports ExcelInterop = Microsoft.Office.Interop.Excel

Module Module2
   Sub Main()
        Dim reader As StreamReader = New StreamReader("C:\test.txt")
        Dim targetWorksheet As ExcelInterop.Worksheet = GetTargetWorksheet("c:\test.xls")
        if targetWorksheet Is Nothing Then
            Exit Sub
        End If
        Try
            Dim line As String
            Dim lineIndex As Long = 1
            Do While reader.Peek() >= 0
                line = reader.ReadLine()
                WriteToExcel(line, targetWorksheet, lineIndex)
                lineIndex += 1 
            Loop
        Catch ex As Exception
            Debug.WriteLine("The file could not be read:")
            Debug.WriteLine(ex.Message)
        finally
            If Not reader Is Nothing Then
                reader.Close()
            End If
        End Try
    End Sub

   Private Sub WriteToExcel(line As String, targetWorksheet As ExcelInterop.Worksheet, lineIndex As Long)
        Dim column As Integer = 1
        For Each part As String In line.Split(" ")
            targetWorksheet.Cells(lineIndex, column).Value =part
            column += 1
       Next
   End Sub

    Private Function GetTargetWorksheet(targetPath As String) As ExcelInterop.Worksheet
        Try
            Dim excelApplication = New ExcelInterop.Application
            excelApplication.Visible = True
            Dim excelWorkbook As ExcelInterop.Workbook
            excelWorkbook = excelApplication.Workbooks.Add()
            excelWorkbook.SaveAs(targetPath)
            Dim excelWorksheet As ExcelInterop.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

EDIT:

It is possible to use QueryTables of Excel to import text data. There are some settings to consider, like TextFileColumnDataTypes. Here in this example all the columns are set to xlColumnDataType.xlTextFormat.

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;C:\test.txt", _
        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 = False
            .TextFileCommaDelimiter = False
            .TextFileSpaceDelimiter = True
            .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