设为首页收藏本站|繁體中文

Excel 技巧网

QQ登录

只需一步,快速开始

手机号码,快捷登录

查看: 11324|bwin官网地址: 11

[Excel VBA] Schema.ini文件详解

[复制链接]
发表于 2011-4-8 08:59:39 | 显示全部楼层 |阅读模式
  • 署名作者: amulee
  • 版权声明: 版权归本站与作者共有 除本站官方外非作者本人转载须经许可并注明出处
  • 本文来自:
  • 引用作品:
  • 适用版本: 2010 2007 2003以前版本 
  • 语言环境: 简体中文
  • 学习方法: 掌握Excel技巧的关键是动手操作 | 下载 ≠ 知识


  • 免费注册成为本站会员,享用更多功能,结识更多Office办公高手!

    您需要 登录 才可以下载或查看,没有帐号?注册

    x
    本帖最后由 amulee 于 2011-4-13 15:57 编辑

    关于Schema.ini文件,中文的描述很少,也有很多人并不认识这个文件。该文件在使用SQL对文本文件进行查询时起了重大的作用。

    本文介绍关于Schema.ini文件的基本概念和基本使用方法。本文关于Schema.ini文件的基本概念部分主要来源于微软官方网站的翻译(笔者按照自己的理解来进行翻译)。(官方网站链接:http://msdn.microsoft.com/en-us/ ... 3%28v=VS.85%29.aspx

    当使用Text引擎进行SQL查询时(即利用SQL查询文本文件),其数据格式是由一个schema文件控制的,这个文件命名为schema.ini,并且和文本文件存放于一个文件夹中。这个schema提供了IISAM驱动信息并告知SQL引擎文件的格式,如:字段名、字段类型和编码格式等等。当字段数据采用固定长度格式是,必须使用schema.ini。当数据中有时间日期、货币、数字等类型的数据,或者需要格式化这些数据的显示,也应当使用schema.ini

    实质上,当进行SQL查询时,引擎会自动从注册表中获取默认格式的设置。默认的文件数据格式即当使用CREATE TABLE语句创建表格时得到的数据格式一致。但系统也会同时扫描当前文件夹内是否有schema.ini文件,当schema.ini文件中的信息与注册表中的信息不一致的时候,则系统会将采用schema.ini文件中的信息,并覆盖注册表中的相应信息。

    schema.ini文件可以包括以下信息的一个或多个:
    >文件名 [The textfile name]
    >文件格式 [Thefile format]
    >字段名、长度、类型 [Thefield names, widths, and types]
    >定义编码 [Thecharacter set]
    >特殊的数据类型转换 [Specialdata type conversions]


    定义文件名
    Schema.ini文件中总是以方括号括起来的文件名为开头
    Sample.txt文件名表示为如下:
    1. [Sample.txt]
    复制代码
    定义文件格式
    文件格式选项Format用来定义文本文件中数据的分隔符。用户可以使用除双引号()外的任何字符作为分隔符。而在读取文件时没有对该选项明确定义,Text驱动模式将采用注册表中默认的字符作为分隔符,一般为逗号(,)。下表为该选项的各种设置。


      
    格式

      
      
    文本文件说明

      
      
    Schema.ini文件中定义

      
      Tab Delimited
      
      字段以Tab字符分割

      
      Format=TabDelimited
      
      CSV Delimited

      
      字段以逗号字符分割

      
      Format=CSVDelimited
      
      Custom Delimited
      
      除双引号外的其他分隔符(一个字符长度)

      
      Format=Delimited(自定义分隔符)
      或没有特别指定分隔符:
      Format=Delimited(  )
      
      Fixed Length
      
      字段为固定长度

      
      Format=FixedLength
      


    定义字段
    可以采用以下两种方式定义字段:
    >当数据包含标题行时,可以设置:
    1. ColNameHeader = True
    复制代码
    >分别定义各字段的名称和类型。
    在固定长度格式下,必须定义字段名称、长度和类型。

    字段类型也可以让系统自己决定。利用MaxScanRows选项来告诉系统扫描多少行数据来决定字段类型。当MaxScanRows选项设置为0时,表示扫描整个文件。
    以下表达式表示文件中第一行包含标题,且扫描整个文件来决定字段类型:
    1. ColNameHeader=True
    2. MaxScanRows=0
    复制代码
    当文件为固定长度字段时,则必须采用列号(Coln)选项来定义字段;当采用自定义分隔符时,该定义为可选。下面表达式表示文件有两列,一列是长度为10的文本类型的CustomerNumber字段,一列是长度为30的文本类型地CustomerName字段:
    1. Col1=CustomerNumberText Width 10
    2. Col2=CustomerName TextWidth 30
    复制代码
    Coln语法为:
    1. Coln=ColumnName type [Width#]
    复制代码
    下表为Coln选项各参数说明:
      参数

      
      说明

      
      ColumnName

      
      字段名(列名)。当该名称中含有空格时,则两端必须加上双引号。

      
      type

      
      数据类型如下::
      Jet引擎数据类型:

      Bit
      Byte
      Short
      Long
      Currency
      Single
      Double
      DateTime
      Text
      Memo
      ODBC 数据类型

      Char (Text相同)
      Float (Double相同)
      Integer ( Short相同)
      LongChar (Memo相同)
      Date (日期格式)

      
      Width
      
      表明字段长度的关键字(自定义分隔符文件中可选;固定长度文件中必选).
      
      #

      
      Width连用,表示字段长度的数据

      

    定义编码
    有两种可选的编码格式:ANSIOEM。下列表达式表示编码为ANSI
    1. CharacterSet=ANSI
    复制代码
    定义数据类型和格式转换
    Schema.ini文件中有几个选项可以定义数据转换和显示方式。请参见下表。
      选项

      
      说明

      
      DateTimeFormat  
      
      用来设置时间和日期格式的显示

      
      DecimalSymbol  
      
      小数分隔符
      
      NumberDigits  
      
      表示小数部分位数
      
      NumberLeadingZeros  
      
      当数字为-11区间内时,指定是否显示之前的0。可设置为TrueFalse

      
      CurrencySymbol  
      
      货币符号。如($)和DM
      
      CurrencyPosFormat  
      
      设置货币符号的格式:
      放在数字之前,没有空格 ($1)
      放在数字之后,没有空格 (1$)
      放在数字之前,有空格 ($ 1)
      放在数字之后,有空格 (1 $)
      
      CurrencyDigits  
      
      指定货币格式小数部分的位数  

      
      CurrencyNegFormat  
      
      可以设置为以下值:
      ($1)
      –$1
      $–1
      $1–
      (1$)
      –1$
      1–$
      1$–
      –1 $
      –$ 1
      1 $–
      $ 1–
      $ –1
      1– $
      ($ 1)
      (1 $)
      这个例子显示了美元符号,但是可以用其他实际的货币符号。

      
      CurrencyThousandSymbol  
      
      指定千分符

      
      CurrencyDecimalSymbol  
      
      指定货币小数和整数的分隔符  

      
    当忽略选项设置时,将采用控制面板中的设置作为默认设置

    评分

    参与人数 1魅力值 +5 收起 理由
    + 5
    太专业了

    查看全部评分

     楼主| 发表于 2011-4-8 09:00:01 | 显示全部楼层
    本帖最后由 amulee 于 2011-4-8 09:17 编辑

    【创建Schema.ini文件

    对于Excel用户来说,利用SQL查询文本文件时,文件格式设置(即设定字段分隔符)经常使用,其他设置选项可以不进行任何设置。下面介绍一个字段分隔符的设置。

    当前文件夹下有两个文本文件,其数据内容一致,唯一不同的就是分隔符号的不同。TextTab.txt中以Tab分隔各字段,CSV.txt中以逗号分隔各字段。如图所示:

    Schema.ini文件详解

    Schema.ini文件详解

    Schema.ini文件详解

    Schema.ini文件详解


    当采用以下语句查询时,结果分别如图:
    1. Sub 查询Tab()
    2.     Dim AdoConn As New ADODB.Connection
    3.     Dim strConn As String
    4.     Dim strSQL As String
    5.     strSQL = "Select * FROM [TextTab.txt]"
    6.     AdoConn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    7.                 "Data Source=" & ThisWorkbook.Path & Application.PathSeparator & _
    8.                   ";Extended Properties=""TEXT;HDR=Yes;"";"
    9.     Cells.Clear
    10.     Range("A1").CopyFromRecordset AdoConn.Execute(strSQL)
    11.     AdoConn.Close
    12. End Sub

    13. Sub 查询CSV()
    14.     Dim AdoConn As New ADODB.Connection
    15.     Dim strConn As String
    16.     Dim strSQL As String
    17.     strSQL = "Select * FROM [CSV.txt]"
    18.     AdoConn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    19.                 "Data Source=" & ThisWorkbook.Path & Application.PathSeparator & _
    20.                   ";Extended Properties=""TEXT;HDR=Yes;"";"
    21.     Cells.Clear
    22.     Range("A1").CopyFromRecordset AdoConn.Execute(strSQL)
    23.     AdoConn.Close
    24. End Sub
    复制代码


    Schema.ini文件详解

    Schema.ini文件详解
    CSV查询结果

    Schema.ini文件详解

    Schema.ini文件详解
    Tab查询结果

    可以发现,上述的两个文件查询结果是不同的。原因在于注册表中默认的字段分隔符为逗号,当查询CSV时可以正确输出结果,而查询Tab时,由于其间没有逗号,所以将两个字段作为一个字段输出。

    为了避免该问题的发生,可以在当前文件夹内创建Schema.ini文件。打开记事本,写入如下语句:
    1. [TextTab.txt]
    2. ColNameHeader = True
    3. Format = TabDelimited
    复制代码


    另存为:格式为所有文件,文件名为
    Schema.ini,如图:

    Schema.ini文件详解

    Schema.ini文件详解


    创建Schema.ini文件后,再次对Tab文件进行查询,即可发现结果正常输出。

    Schema.ini用于多个文本文件

    当同一目录下有多个文件需要进行设置时,可以在同一个Schema.ini文件中进行设置,如图所示:

    Schema.ini文件详解

    Schema.ini文件详解


    此时,若采用以下语句,则可以对两个分隔符设置不同的文件进行统一查询,且能返回正确结果:
    1. Sub 查询SAPCE()
    2.     Dim AdoConn As New ADODB.Connection
    3.     Dim strConn As String
    4.     Dim strSQL As String
    5.     strSQL = "Select * FROM [SPACE.txt] Union All Select * FROM [TextTab.txt]"
    6.     AdoConn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    7.                 "Data Source=" & ThisWorkbook.Path & Application.PathSeparator & _
    8.                   ";Extended Properties=""TEXT;HDR=Yes;"";"
    9.     Cells.Clear
    10.     Range("A1").CopyFromRecordset AdoConn.Execute(strSQL)
    11.     AdoConn.Close
    12. End Sub
    复制代码


    Schema.ini.rar (17.17 KB, 下载次数: 23)
     楼主| 发表于 2011-4-8 09:00:28 | 显示全部楼层
    本帖最后由 amulee 于 2011-4-8 09:27 编辑

    利用VBA创建Schema.ini文件

    在许多时候,Schema.ini文件并不需要保留,因为它的存在意义只限于瞬间的查询。因而,可以利用VBA创建一个临时的Schema.ini文件,待查询完毕后删除。请参考以下代码:

    1. Sub 利用VBA创建临时Schema文件()
    2.     Dim AdoConn As New ADODB.Connection
    3.     Dim strSQL As String
    4.     Dim FS As Integer
    5.     Dim FName As String
    6.     Dim strSchema As String
    7.     '创建Schema.ini文件
    8.     strSchema = "[TextTab.txt]" & vbCrLf & _
    9.                 "ColNameHeader = True" & vbCrLf & _
    10.                 "Format = TabDelimited"
    11.     FS = FreeFile
    12.     FName = ThisWorkbook.Path & Application.PathSeparator & "\Schema.ini"
    13.     Open FName For Output As #FS
    14.     Print #FS, strSchema
    15.     Close #FS
    16.     '查询
    17.     strSQL = "Select * FROM [TextTab.txt]"
    18.     AdoConn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    19.                 "Data Source=" & ThisWorkbook.Path & Application.PathSeparator & _
    20.                   ";Extended Properties=""TEXT;HDR=Yes;"";"
    21.     Cells.Clear
    22.     Range("A1").CopyFromRecordset AdoConn.Execute(strSQL)
    23.     AdoConn.Close
    24.     '删除Schema.ini
    25.     Kill FName
    26. End Sub
    复制代码


    利用VBA创建临时Schema文件.rar (17.59 KB, 下载次数: 29)
     楼主| 发表于 2011-4-8 10:31:45 | 显示全部楼层
    本帖最后由 amulee 于 2011-4-8 10:42 编辑

    【利用Schema.ini设定字段类型

    如下列文本文件中包含有姓名和身份证号码:

    Schema.ini文件详解

    Schema.ini文件详解


    Excel工作簿中代码如下:
    1. Sub 查询身份证()
    2.     Dim AdoConn As Object
    3.     Dim AdoRst As Object
    4.     Dim strConn As String
    5.     Dim strSQL As String
    6.     Set AdoConn = CreateObject("ADODB.Connection")
    7.     strSQL = "Select * FROM [Custom.txt]"
    8.     AdoConn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    9.                 "Data Source=" & ThisWorkbook.Path & Application.PathSeparator & _
    10.                   ";Extended Properties=""TEXT;HDR=Yes;"";"
    11.     Cells.Clear
    12.     Set AdoRst = AdoConn.Execute(strSQL)
    13.     For i = 1 To AdoRst.Fields.Count
    14.         Cells(1, i) = AdoRst.Fields(i - 1).Name
    15.     Next i
    16.     Range("A2").CopyFromRecordset AdoRst
    17.     AdoRst.Close
    18.     AdoConn.Close
    19.     Set AdoRst = Nothing
    20.     Set AdoRst = Nothing
    21. End Sub
    复制代码


    由于Excel自动转换数据格式的特性,会将身份证号码误认为数字进行导入,从而导致信息缺失,如图:

    Schema.ini文件详解

    Schema.ini文件详解


    此时,可以利用Schema.ini文件对字段进行类型说明,如以下代码:
    1. [Custom.txt]
    2. ColNameHeader = True
    3. Format = Delimited( )
    4. Col1=姓名 Text
    5. Col2=身份证号码 Text
    复制代码


    此时,即可返回正确结果:

    Schema.ini文件详解

    Schema.ini文件详解


    查询身份证.rar (17.31 KB, 下载次数: 20)
     楼主| 发表于 2011-4-8 10:32:54 | 显示全部楼层
    本帖最后由 amulee 于 2011-4-8 10:55 编辑

    【冲突解决

    如下列文件

                                   
    登录/注册后可看大图


    VBA代码如下:
    1. Sub 查询身份证()
    2.     Dim AdoConn As Object
    3.     Dim AdoRst As Object
    4.     Dim strConn As String
    5.     Dim strSQL As String
    6.     Set AdoConn = CreateObject("ADODB.Connection")
    7.     strSQL = "Select * FROM [Custom.txt]"
    8.     AdoConn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    9.                 "Data Source=" & ThisWorkbook.Path & Application.PathSeparator & _
    10.                   ";Extended Properties=""TEXT;HDR=No;"";"
    11.     Cells.Clear
    12.     Set AdoRst = AdoConn.Execute(strSQL)
    13.     For i = 1 To AdoRst.Fields.Count
    14.         Cells(1, i) = AdoRst.Fields(i - 1).Name
    15.     Next i
    16.     Range("A2").CopyFromRecordset AdoRst
    17.     AdoRst.Close
    18.     AdoConn.Close
    19.     Set AdoRst = Nothing
    20.     Set AdoRst = Nothing
    21. End Sub
    复制代码


    此时,在数据连接时已经设定无标题行。
    HDR=No

    但在Schema.ini文件中设置如下:
    1. [Custom.txt]
    2. ColNameHeader = True
    3. Format = Delimited( )
    4. Col1=姓名1 Text
    5. Col2=号码 Text
    复制代码


    上述设置,将数据设置为有标题行,且第一列字段名为“姓名1”,第二列为“号码”,此时查询结果如图:

    Schema.ini文件详解

    Schema.ini文件详解


    可见,当发生冲突时,查询引擎将以Schema.ini文件中设置为准。尽管在数据连接中设置了无标题行,但若在Schema中设置了有标题行,则将显示标题行。另外实际的标题行名称(字段名)将以Schema中设置为准。

    本例中第二列为身份证号码,如果仅以以下语句设置,而忽略第一列:
    1. [Custom.txt]
    2. ColNameHeader = True
    3. Format = Delimited( )
    4. Col2=号码 Text
    复制代码


    则结果将如同没有设置一般,如图:

                                   
    登录/注册后可看大图


    如果Schema如下:
    1. [Custom.txt]
    2. ColNameHeader = True
    3. Format = Delimited( )
    4. Col1=姓名 Text
    复制代码


    则结果只会返回第一列,如图:

    Schema.ini文件详解

    Schema.ini文件详解


    可见,如果设置了列的信息,就应完整设置所有列,而不应该跳开某列,否则将返回与预期不同结果。

    附件: 查询身份证.rar (17.31 KB, 下载次数: 11)
     楼主| 发表于 2011-4-8 10:55:59 | 显示全部楼层
    本帖最后由 amulee 于 2011-4-8 11:32 编辑

    利用Schema.ini格式化数据

    文件夹中有一个Data.txt文件,其内容如下:

    Schema.ini文件详解

    Schema.ini文件详解

    另有一个New.txt文件,其内容为空。

    VBA代码如下。其功能是将Data中的数据导入到New中:
    1. Sub 执行()
    2.     Dim AdoConn As Object
    3.     Dim AdoRst As Object
    4.     Dim strConn As String
    5.     Dim strSQL As String
    6.     Set AdoConn = CreateObject("ADODB.Connection")
    7.     strSQL = "Insert Into [New.txt] Select * FROM [Data.txt]"
    8.     AdoConn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    9.                 "Data Source=" & ThisWorkbook.Path & Application.PathSeparator & _
    10.                   ";Extended Properties=""TEXT;HDR=Yes"";"
    11.     Cells.Clear
    12.     AdoConn.Execute strSQL
    13.     AdoConn.Close
    14.     Set AdoRst = Nothing
    15. End Sub
    复制代码


    在这一导入过程中,可以对数据进行格式化。并且可以标注New文件的字段数量等信息,代码如下:
    1. [Data.txt]
    2. ColNameHeader = True
    3. Format = Delimited( )
    4. Col1=姓名 Text
    5. Col2=时间 DateTime
    6. Col3=金额 Currency
    7. Col4=数量 Single

    8. [New.txt]
    9. ColNameHeader = True
    10. Format = Delimited( )
    11. Col1=姓名 Text
    12. Col2=时间 DateTime
    13. Col3=金额 Currency
    14. Col4=数量 Single
    15. DateTimeFormat=yyyymmdd
    16. NumberDigits=2
    17. DecimalSymbol=@
    18. CurrencyDigits=2
    19. CurrencySymbol=$
    复制代码


    上述设置表示Data文件和New文件均有四列,均以空格为分隔符。在New中设定了数据格式。
    执行VBA代码后,可以发现New文件中导入的数据已经按照新的格式进行了设置,如图:

    Schema.ini文件详解

    Schema.ini文件详解


    格式化数据.rar (16.32 KB, 下载次数: 16)

    评分

    参与人数 3技术分 +2 魅力值 +19 收起 理由
    + 5
    解释的太专业了,谢谢
    + 1
    + 4
    好文,拜读了。
    + 1
    + 10
    好文章

    查看全部评分

    发表于 2011-4-8 22:06:41 | 显示全部楼层
    好文章,建议加精。
    在用MQ导入文本文件时,对这个文件的格式要比较了解。
    发表于 2011-8-24 20:28:59 | 显示全部楼层
    !!!!!!!!!!!!!!!!!!!11
    发表于 2011-10-1 01:01:31 | 显示全部楼层
    在用MQ导入.txt文件时,Schema.ini文件失效。
    按照错误提示对Schema.ini文件进行了修改。OK了 。灰常感谢阿木老师
    发表于 2012-3-16 09:07:05 | 显示全部楼层
    拜读大师的大作!
    您需要登录后才可以回帖 登录 | 注册

    本版积分规则

    关闭

    站长推荐上一条 /1 下一条

    Excel技巧网的会员探讨问题仅代表其个人意见,与网站的立场无关。任何违反国家和地方相关法律法规的言论,本站有义务协助政府相关部门追究发言者的责任!
    本站中非注明转载文章与案例的版权为作者与Excel技巧网共有。若非原文作者,本站之外任何单位或个人未经允许,不得将其用于商业用途。
    若非原文作者,任何形式的非商业性转载必须获得Excel技巧网或作者允许,并注明作者和出处。
    会员发表的帖子如涉及版权纠纷,须自行负责。详情请参考注册时的网站服务条款。
    本站特聘法律顾问:沈学律师