从文本文件读取数据和定界

问题描述

我有一个Excel 2010电子表格，我正在从.txt文件（以及将来的另一个.xls文件）中读取信息。

此文本文件每行3个元素firtname，surname和job title，每个元素用逗号分隔。我将数据读取并粘贴到Excel中，但是每一行都粘贴到一个单元格中。我正在寻找将每个元素粘贴到不同的列。我知道我应该尝试分隔，但是我根本无法弄清楚语法。

我的问题是我如何分离每个元素并将其粘贴到自己的细胞？我目前使用逗号分隔我的.txt文件中的每个元素，但是将来的文件可能会使用选项卡，全站，分号等。如何扩展它，以便涵盖所有基础？

下面是我的代码，在我的代码下是一个虚拟数据的示例

  Sub FetchDataFromTextFile（）
 Dim i As Long 
 Dim LineText As String 
打开C：\mytxtfile.txt输入为＃24 
i = 2 
虽然不是EOF（24） 
行输入＃24，LineText 
 ActiveSheet.Cells（i，2）.Value = LineText 
 P =拆分（记录，，）
i = i + 1 
 Wend 
关闭＃24 
 End Sub 
  

John，Doe，Boss

Johnny，Steele，经理

Jane，Smith，Employee

注意：在其他编程语言中有竞争力，但在大约6或7年内没有完成VB。我似乎不能把我的头围绕着VB语法，所以请像我这样的新手一样对待我。

解决方案

  Sub FetchDataFromTextFile（）
 Dim i As Long 
 Dim LineText As String 
打开C：\mytxtfile.txt输入为＃24 
i = 2 
虽然不是EOF（24）
行输入＃24，LineText 
 Dim arr 
 arr = Split（CStr（LineText），，）
对于j = 1 To 
 ActiveSheet.Cells（i，j）.Value = arr（j  - 1）
下一个j 
i = i + 1 
 Wend 
关闭＃24 
 End Sub 
  

对于

I have an Excel 2010 spreadsheet, and I am reading in information from a .txt file (and another .xls file in future).

This text file has 3 elements per row; firtname, surname and Job title, and each element is separated by a comma. I have the data reading and pasting into Excel, however each row is pasted into the one cell. I am looking to paste each element into different columns. I know that I should try and delimit, but I just can't figure out the syntax.

My question is how do I separate each element and paste it into it's own cell? I currently use commas to separate each element on my .txt file, but future files might use tabs, full-stops, semi-colons etc. How do I extend it so all bases are covered?

Below is my code, and under my code is a sample of dummy data

Sub FetchDataFromTextFile()
    Dim i As Long
    Dim LineText As String
    Open "C:\mytxtfile.txt" For Input As #24
    i = 2
    While Not EOF(24)
        Line Input #24, LineText
        ActiveSheet.Cells(i, 2).Value = LineText
        P = Split(Record, ",")
        i = i + 1
    Wend
    Close #24
End Sub

John, Doe, Boss

Johnny, Steele, Manager

Jane, Smith, Employee

NOTE: Competant in other programming languages, however not done VB in about 6 or 7 years. I can never seem to wrap my head around VB Syntax, so please treat me like a novice for this.

解决方案

Sub FetchDataFromTextFile()
    Dim i As Long
    Dim LineText As String
    Open "C:\mytxtfile.txt" For Input As #24
    i = 2
    While Not EOF(24)
        Line Input #24, LineText
            Dim arr
            arr = Split(CStr(LineText), ", ")
            For j = 1 To
                ActiveSheet.Cells(i, j).Value = arr(j - 1)
            Next j
            i = i + 1
    Wend
    Close #24
End Sub

For different delimiters, make use of the answers in here

这篇关于从文本文件读取数据和定界的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！