浅谈ADO提取txt(分隔符的问题)

fxl447098457

今天小谈一下Ado提取txt文本中遇到的一些问题。不知道大家有没有遇到提取txt出现提取的一行内容放在一个单元格里面了。我也是 碰到了好多次这样的问题，才做了一点研究。文本用ado提取涉及到了默认的引擎的文本格式分隔符号。这里大家可以通过在注册表里面搜索engines，delimited等关键字得到路径，然后查到自己的默认的分隔符。我的win7 64位配上32位office的路径是：HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\ClickToRun\REGISTRY\MACHINE\Software\Wow6432Node\Microsoft\Office\16.0\Access Connectivity Engine\Engines\Text路径下的format项就是设置默认的分隔符。查了一下jet 4.0的默认路径是：HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Text下,由于没有条件,这里就没有求证了。进行修改如图可以看到我的系统默认分隔符：

我们可以通过修改该项来改为自己需要的分隔符。然而大家都知道注册表这东西动来动去麻烦。能不动他最好。当然如果你经常需要进行操作的文本都是某种固定的分隔符的话，那你可以修改注册表以绝后患。
通过一番探索，发现微软早就有了相关的解决方案，那就是定义一个Schema.ini文件来解决这个问题。下面我要说一下有关Schema.ini的几点：

1.只起作用于文本文本驱动。也就是只作用于txt,csv等文本文件。
2.总是命名为“Schema.ini”，并且要和你的文本数据源放在同一文件目录下面。
3.为ISAM提供文件的主要格式信息。如列名称，数据类型以及其他的一些数据特点(比如长度）。
首先看看我写的一个schema.ini.

[提取指定数据行的全部数据.txt]
format=TabDelimited
COL1=F1 Text
COL2=F2 Text
COL3=F3 Text

上面内容比较简单，由此我们来看看Schema.ini的内容构成：
（1）文本文件的名称，不需要带路径。记住要放在方括号[]里面。
（2）文件格式。也就是指定文件的分隔符。
（3）字段名，类型，以及字段长度。
（4）字符编码的设定
（5）特殊的数据类型转换。
上面的列字段熟悉EXCEL SQL的人知道是没有标题的。文件总共有3列,且通过schema.ini定义了类型都为text,你可以根据自己需要定义数据类型。示例里面没有限定字段的长度。我们可以通过限定width来限定长度。如下：
Col1=Num Text Width 20
Col2=Name Text Width 50  
下面我们来看看schema.ini里面的format有那些参数可选以及format的相关语句是如何写的：

格式分隔符	表格格式	Schema.ini格式语法
tab分隔符	按照制表符划分字段	Format=TabDelimited
csv分隔符	按照逗号划分字段	Format=CSVDelimited
自定义分隔符	按照用户输入的除了双引号之外的任何字符划分,包括空白	Format=Delimited(自定义字符)或者 Format=Delimited()
固定长度分隔	固定长度的字段	Format=FixedLength

只要知道excel里面的分列操作对这个理解是不成问题的。基本就是按照分隔符号划分字段区域和按照固定长度来划分。
定义文本第一列为标题可以使用：
ColNameHeader=True
也可以不写通过Ado的连接语句里面的HDR=YES/NO来说明。
MaxScanRows用于指定检查的行数以决定数据类型。
MaxScanRows=0 就是检查0行，一般后面自己会定义列类型的话这里就没必要陈述了。
下面就讲述数据的类型了。数据按照Jet/ACE和ODBC驱动进行划分。具体见下面的表格详述：

参数	描述
列名	列的文本名称。如果列名称包含空格，需用双引号标记。如：COL1="成  绩"
Jet/ACE数据类型	Byte,Short,Long,Currency,Single,Double,DateTime,Text,Memo
ODBC数据类型	Char ( 同Text)，Float (同Double)，Integer (同 Short)，LongChar (same as Memo),Date date format
Width	width表明后面的数值用于指定列的宽度,对于分隔符文件可选，对于固定长度的文件是必要参数
#	用于指定列宽的整数值,当有width时是必要参数

指定字符编码类型。如：

CharacterSet=ANSI

最后就是数据类型的转换。主要针对的是日期时间，数值，货币等类型的转换设定。详情看下表：

fxl447098457

有关schema.ini的相关内容介绍到这里差不多就结束了。下面我借用论坛上的一个例子来说一下：
要提取的是多个txt按照列去追加。打开其中一个文件排版如下：

可以看到文件包含3列，并且没有标题。那么我们可以写下schema.ini。如下面：
[提取指定数据行的全部数据.txt]
format=TabDelimited
COL1=F1 Text
COL2=F2 Text
COL3=F3 Text

为了简便，我就都定义为text类型。
人家的是多个txt文件，我们就需要加入新的txt的文件信息到schema.ini里面。那么该怎么做呢。我们可以通过普遍的txt文件写入方法循环写入就可以了。下面代码中的str为文件的名称。

Open ThisWorkbook.Path & "\schema.ini" For Output As #1
Print #1, "[" & str & "]"
Print #1, "format=TabDelimited"
Print #1, "COL1=F1 Text"
Print #1, "COL2=F2 Text"
Print #1, "COL3=F3 Text"
Close

更新了schema.ini后我们就可以开始后面的Ado提取自己需要的数据了。

Sub mydata()
Dim Cnn As Object, Rst As Object, SQL$, Fn$, str$, rng As Range
Set Cnn = CreateObject("ADODB.Connection")
Cells.ClearContents
If Val(Application.Version) < 12 Then

Cnn.Open "Provider=Microsoft.jet.OLEDB.4.0;Data Source=" & ThisWorkbook.Path & _
";Extended Properties='text;HDR=no;FMT=Delimited()';Persist Security Info=False"

Else

Cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & _
";Extended Properties='text;HDR=no;FMT=Delimited()';Persist Security Info=False"

End If

Fn = ThisWorkbook.Path & "\*.txt": str = Dir(Fn)

Do While Len(str)
Open ThisWorkbook.Path & "\schema.ini" For Output As #1 '写入文件信息到schema.ini文件里
Print #1, "[" & str & "]"
Print #1, "format=TabDelimited"
Print #1, "COL1=F1 Text"
Print #1, "COL2=F2 Text"
Print #1, "COL3=F3 Text"
Close
SQL = "Select  f2,f3 from " & str & " where f1='19993000'"
Set Rst = Cnn.Execute(SQL)
If Len([a2]) = 0 Then Set rng = [a2] Else Set rng = Cells(2, Columns.Count).End(xlToLeft).Offset(, 1)
rng.Offset(-1) = str
rng.CopyFromRecordset Rst
Rst.Close
str = Dir()
Loop
Cnn.Close
Set Cnn = Nothing
Set Rst = Nothing
End Sub

今天其实主要介绍的是怎么去使用schema.ini来自定义处理字段信息。基本内容就介绍到这里了。喜欢探索的坛友可以自行研究。我最后附上上面代码的附件吧。 自定义schema.ini--Ado提取txt.rar (15.01 KB, 下载次数: 49)

2017-12-1 15:21 上传

点击文件名下载附件

232670631

doushi lihai de ren
bixu xuexi

Q155133209

您好，我想问下 如果按长度分割 应该怎么操作呢？
Print #1, "[" & str & "]"
Print #1, "format=FixedLength"
Print #1, ”Col1=Num Text Width 20“
改成这样好像不太行

Q155133209

比如我想将这个TXT的ID 为1，3，4 的提取出来 就不太好操作 好像不是制表符分隔的，长度第一列好像是6位 后面的都是7位。。因为文件本身很大  读取到数组再拆分再循环就很卡很卡 可能都会崩溃  所以想学习下 怎么用ADO 直接连接TXT 然后筛选出来  

aails6543

非常好的学习资料ADO分隔符