读取分页文件到DataGridView [英] Read Tab Delimited File Into DataGridView

问题描述

以下代码我将一个制表符分隔的文件读入DataGridView。它工作正常，但有几个问题我不完全确定如何解决。

The following code I have reads a tab delimited file into a DataGridView. It works fine, but there are a couple of issues I'm not exactly sure how to address.

Dim query = From line In IO.File.ReadAllLines("C:\Temp\Temp.txt")
Let Data = line.Split(vbTab)
Let field1 = Data(0)
Let field2 = Data(1)
Let field3 = Data(2)
Let field4 = Data(3)

DataGridView1.DataSource = query.ToList
DataGridView1.Columns(0).Visible = False

我如何去添加字段（列）标题行中的字段？标题行目前包含110个字段，我不喜欢以类似的方式定义到 Let field1 = Data（0）

How do I go about adding fields (columns) based on the number of fields in the header row? The header row currently contains 110 fields, which I'd hate to define in a similar manner to Let field1 = Data(0)

我还需要跳过标题行，然后才显示这行。

I'd also need to skip the header row and only display the lines after this.

有没有更好的方法来处理这个， m目前在做什么？

Is there a better way to handle this then what I'm currently doing?

推荐答案

有几种工具来解析这种类型的文件。一个是OleDB。

There are several tools to parse this type of file. One is OleDB.

我无法弄清楚（删除）的答案是如何工作的，因为 HDR = No; 告诉文本驱动程序第一行不包含列名。但是，在读取没有IMEX的前8行之后，这有时被忽略。

I cant quite figure out how the (deleted) answer works because, HDR=No; tells the Text Driver the first row does not contain column names. But this is sometimes ignored after it reads the first 8 lines without IMEX.

但是， FMT = Delimited \看起来像是从C＃回答中复制的，因为VB不使用 \ 来转义字符，它也看起来像混淆列分隔符（逗号或选项卡在这种情况）和文本分隔符（通常）

However, FMT=Delimited\""" looks like it was copied from a C# answer because VB doesnt use \ to escape chars. It also looks like it is confusing the column delimiter (comma or tab in this case) and text delimiter (usually ")

如果文件是制表符分隔的，正确的值将是 FMT = TabDelimited 。我猜这些字段是用引号分隔的文本（例如法国巴黎2.25），OleDB通过报价剔除数据，而不是标签来意外得到相同的结果。

If the file is tab delimited, the correct value would be FMT=TabDelimited. I am guessing that the fields are text delimited with quotes (e.g. "France" "Paris" "2.25") and OleDB is chopping the data by quotes rather than tabs to accidentally get the same result.

正确的ACE字符串将是：

The correct ACE string would be:

Dim connstr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='C:\Temp';Extended Properties='TEXT;HDR=Yes;FMT=TabDelimited';"

---

只使用连接字符串将导入每个文件串。您也可以将OleDB转换为所读取的数据到任何数据类型，这样您就不必用大量的 Convert.ToXXXX 将代码丢弃， code> String 数据。

---

Using just the connection string will import each filed as string. You can also have OleDB convert the data read to whatever datatype it is meant to be so that you do not have to litter your code with lots of Convert.ToXXXX to convert the String data to whatever.

这需要使用 Schema.INI 定义文件。这将替换连接字符串中的大部分扩展属性，只留下扩展属性='TEXT';（这意味着使用TEXT驱动程序）创建文件名 Schema.INI 在与数据相同的文件夹中：

This requires using a Schema.INI to define the file. This replaces most of the Extended Properties in the connection string leaving only Extended Properties='TEXT';" (which means use the TEXT Driver). Create a file name Schema.INI in the same folder as the data:

[Capitals.txt] >
ColNameHeader = True

CharacterSet = 437

格式= TabDelimited

TextDelimiter =

DecimalSymbol =。 >
CurrencySymbol = $

Col1 =国家文字宽度254

Col2 =首都城市文字宽度254

Col3 =人口单个

Col4 =Fake整数

[Capitals.txt]
ColNameHeader=True
CharacterSet=437
Format=TabDelimited
TextDelimiter="
DecimalSymbol=.
CurrencySymbol=$
Col1="Country" Text Width 254
Col2="Capital City" Text Width 254
Col3="Population" Single
Col4="Fake" Integer

一个 Schema.INI 可以包含许多文件的布局。每个文件都有自己的标题为文件名的部分（例如 [FooBar.CSV] ， [Capitals.txt] etc）

大多数条目应该是不言而喻的，但是 FORMAT 定义列分隔符（ TabDelimited ， CSVDelimited 或自定义 Delimited（;））; TextDelimiter 当字符可能包含空格或其他特殊字符时，该字符用于包含列数据。像 CurrencySymbol 这样的东西让你允许一个外来的符号，可以省略。

Most of the entries should be self-explanatory, but FORMAT defines the column delimiter (TabDelimited, CSVDelimited or custom Delimited(;)); TextDelimiter is the character is used to enclose column data when it might contain spaces or other special characters. Things like CurrencySymbol lets you allow for a foreign symbol and can be omitted.

ColN = 列表可以重命名列并指定数据类型。这可能是很麻烦的进入100+列，但它可能主要是复制和粘贴。一旦完成，你总是拥有它，并且能够轻松地使用类型化的数据。

The ColN= listings are where you can rename columns and specify the datatype. This might be tedious to enter for 100+ columns, however it would probably be mostly copy and paste. Once it is done you'd always have it and be able to easily use typed data.

您不需要指定列名称/大小/类型使用Schema.INI 如果文件包含列名作为第一行（ColNameHeader = True），则可以使用模式简单地以清晰可读的方式指定各种参数，而不是将它们压入连接字符串。

You do not need to specify the column names/size/type to use a Schema.INI If the file includes column names as the first row (ColNameHeader=True), you can use the Schema simply to specify the various parameters in a clear and readable fashion rather than squeezing them into the connection string.

OleDB在与导入文件相同的文件夹中查找一个Schema.INI，然后查找一个具有用于table的确切名称的部分SQL：

OleDB looks for a Schema.INI in the same folder as the import file, and then looks for a section bearing the exact name of the "table" used in the SQL:

' form level DT var
Private capDT As DataTable

' procedure code to load the file:
Dim connstr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='C:\Temp';Extended Properties='TEXT';"
Dim SQL = "SELECT * FROM Capitals.txt"

capDT = New DataTable

' USING will close and dispose of resources
Using cn As New OleDbConnection(connstr),
            cmd As New OleDbCommand(SQL, cn)

    cn.Open()
    Using da As New OleDbDataAdapter(cmd)
        da.Fill(capDT)
    End Using

End Using   ' close and dispose

现在可以使用 DataTable 如果我们迭代列，您可以看到它们与模式中指定的类型相匹配：

The DataTable is now ready to use. If we iterate the columns, you can see they match the Type specified in the schema:

' display data types
For n As Int32 = 0 To capDT.Columns.Count - 1
     Console.WriteLine("name: {0}, datatype: {1}",
                        capDT.Columns(n).ColumnName,
                        capDT.Columns(n).DataType.ToString)
Next

输出：

name：国家/地区，数据类型：System.String

名称：Capital City，datatype：System.String >
名称：人口，数据类型：System.Single

名称：假，数据类型：System.Int32

name: Country, datatype: System.String
name: Capital City, datatype: System.String
name: Population, datatype: System.Single
name: Fake, datatype: System.Int32

另请参阅：

• Schema.INI 适用于大多数合法设置


• 代码页标识符 f或用于 CharacterSet

• Schema.INI for most legal settings
• Code Page Identifiers for the values to use for CharacterSet

这篇关于读取分页文件到DataGridView的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！