Answered by:
How to import .txt file into excel sheet?

-
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!
Question
Answers
-
Hi,
see the following link:
Best Regards,
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
All replies
-
Hi,
see the following link:
Best Regards,
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
-
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