Use Microsoft Jet Text IISAM to link and open text files that are bounded by various characters and pitching. In the source file, comma, tab, or custom different delimiters are valid.
First, specify connection information.
When you use the Microsoft Jet to access the file, you still use the OpenDatabase method. Use the parameters in this method to specify connection information for text files. The content is as follows:
· Source Database Type: Text
· Database Name: The full path of the directory, contains the text files you want to access in this directory.
· Source Table Name: The name of the text file, including the extension. If the extension is not specified, use the default extension. TXT.
If two delimiters appear in the character boundary file, the Microsoft Jet treats it as a null value. If there is no data (spaces) in the data column of the fixed-length file, the Microsoft Jet also treats it as a null value. Microsoft Jet determines the format of a text file by reading a file or using a scheme description information file. The program description information file is always named Schema. INI, and always stored in the same directory with the text data source, which provides many information to IISAM, including the general format, column name, and data type information of the file, and other data characteristics. When accessing the fixed length data, there must be SCHEMA. INI files; when including DateTime, Currency, Decimal data, or wishing more to control the data in the table, you should use Schama. INI file.
This paragraph is taken from the online manual of VisualBasic 5.0. The "Scheme Description Information" of this dialect is always named schema.ini "may be incorrect. According to the author test, the program description information file should have the same file basic name as the text file to be linked or opened, and its extension is. INI. Note that Microsoft Jet does not support multi-user access text files. After opening a text file with Microson Jet, you will have access to the file. Microsoft Jet has some restrictions on various text tables and objects, including:
· Field: no more than 255 characters
· Field name: no more than 64 characters
· Field width: no more than 32,766 characters
· Record size: no more than 65,000 bytes
Second, the program describes the information file. Access to text files is implemented by a program description file. That is to say, in order to access the external text file, a scheme description information file must be provided. This file provides a scheme description recorded in a text file, including the following aspects.
Specify the file name. The first item of the program description information file is the name of the text source file, which is placed in square brackets. For example, if you want to access names Sample. TXT text file, the item should be written as: [SAMPLE. TXT] Specifies the file format.
The format option in the scenario description information file specifies the format of the text file, and the Text IISAM can automatically read formats from most character boundary files. In a text file, you can use any individual characters other than dual quotation marks as a delimiter. Scheme Description Information file format setting value overwrite the corresponding settings of all files in the Windows registry. Valid values in the format option As listed in Table.
Format setting characters
Format specifier format
TabDelimited file field uses a table specified
The field of the csvdelimited file with comma delimited (with a comma-bound value)
The field of the DELIMITED (*) file is bound by an asterisk, which can be used in addition to double quotes
Any character other than the asterisk
The field of the FixedLength file is fixed length
For example, in order to specify a comma-separated format, you can in Schema. Add the following line in the INI file: format = csvdelimited
3. Specified field
In the character boundary text file, you can specify a field name by following two ways:
• Place the field name in the first line of the table and set the colnameheader to true.
· Specify each column with a number and specify the column name and data type.
Note that each column must be specified with a number, and the name of the column and the data type must be specified, and the width to specify its width for the fixed length file. The data type of the field can also be determined by the Microsoft Jet. That is, using the MAXSCANROWS option indicates how many lines should be scanned when determining the type of column. If you set maxScanRows to 0, the Microsoft JET will scan the entire file. The MaxScanRows setup value in the scheme description information file overwrites the corresponding settings of each file in the Windows Registry. E.g:
ColNameHeader = TRUE
MaxScanRows = 0
The data of the first row in the Microsoft JET is indicated to determine the field name, and scan the entire file to determine the data type used. The various fields of the table (text file) are specified by the column number (Coln) option, which is optional for the character boundary file, and for the fixed length file is required. The format of coln is as follows:
Coln = columnname type [width #]
The COLN items include the following parts:
· ColumnName: The text name of the column. If the column name contains embedded spaces, you must be placed in double quotes.
· Type: Data type, can be the following type. Microsoft Jet Data Type: Bit, Byte, Short, Long, Currencv, Single, Double, DateTime, Text; MemoodBC Data Type: Char (same as Text), Float (same as Double), Integer (same as Short), Longchar ( The same as MEMO), Date Date Format.
• Width: String value width, pointing to the width of the specified column (which is optional for character boundary files, and for the fixed length file).
#: Is an integer that specifies the width of the column (if width is specified, you must add an integer of the column width).
example:
COL1 = Customernumber Text Width 10
COL2 = Customername Text Width 30
This example gives an item corresponding to two fields in the scenario description information file, which is: 10 characters of CustomerNumber text fields and 30 characters of CustomerName text fields.
4. Select character set
There are two character sets that can be used, namely ANSI and OEM, which can be selected from it, and set by the CHARACTERSET. For example: CharacterSet = ANSI sets the character set to ANSI. If you select an OEM character set, the program description information file's CharacterSet setting value covers the corresponding set value of each file in the Windows Table. Related Website: http://support.microsoft.com/kb/q149090/written an example: schema.ini file: [ff.txt] format = tabdelimitedColnameHeader = false col1 = "bh" shortcol2 = "rq" DateTimeCol3 = "sj" datetimecol4 = "bc" charff.txt file: 1 2004-9-21 0:00 1900-1-1 16:02 101 2004-9-21 0:00 1900-1-1 16:02 102 2004- 9-21 0:00 1900-1-1 16:02 101 2004-9-21 0:00 1900-1-1 16:02 10 In the query analyzer execution: Select * from txt ... [FF # txt] / * Results: 10 1 2004-09-21 00: 00: 00 1900-01-01 16: 02: 00.00010 1 2004-09-21 00: 00: 02: 00-01-01 16: 02: 00.00010 2 2004- 09-21 00: 00: 00.000 1900-01-01 16: 02: 00.00010 1 2004-09-21 00: 00: 02: 00-01-01 16: 02: 00.000 * /
Or: Select * from OpenDataSource ('microsoft.jet.oledb.4.0', 'text; hdr = no; Database = D: / DATA /') ... [FF # txt]
