How do I specify a field type when importing a text file?
problem:
I have a text file to import Access, but a column of data is originally text, but the import database is automatically turned into a "double precision" type. How can I let each field generate according to the data type I need? How to let Access import data from text files, specifications from the architecture of I specified?
Method Two:
Use the schema.ini files to meet the requirements. Role: Schema.ini is used to provide archive information recorded in a text file. Each Schema.ini item is one of five features of the specified table: 1. Text file name 2, file format 3, field name, field length, field type 4, character set 5, special data type conversion specified file name The file name must be enclosed in square brackets, for example, if you want to use the data archive information file for Sample.txt, then its corresponding item should be
[Sample.txt] Specify file format
Format Description Table Format Schema.ini Format Description Tab Table Separator Separate Format = TabDelimitedCSV Separator Format = TabDelimitedCSD Separate File Format = CSVDELIMITED Custom Separation Fields Fields You can use any characters Separate, all characters can be used to separate, including spaces, but dual quotes (") except for format = delimited - or no separator-format = Delimited () fixed width file in fixed length Specify field You can have two ways to specify a field name in a character separated text file. In the first line in the text file, the field name is included, and the colNameHeader is True .2, specify each column with the number number and specify each A column name and data type You must specify each column with the number number and specify the name, data type, and length of each column (required to specify the length in a fixed-length text file) Note, set the colnamehead option, in Schema.ini The FIRSTROWHASNAMES option is ignored when Windows registration. You can also specify the data type of the field, use the MaxScanRows option to specify how many rows of data should be scanned when determining the data type of the column. SetupscanRows will scan the entire file. If the text file One line contains the field name, and to scan the entire file, the project will be defined as follows:
ColNameHeader = TrueMaxScanRows = 0 Next item is used to specify the fields in the table, use the column number (coln option to specify the column. Field length is a reference item in the "fixed separator text file", which is an optional item in "Character Separation Text File". Example: Define 2 fields, CustomerNumber is a text field with a length of 10, and CustomerName is a text field with a length of 30.
COL1 = Customernumber Text Width 10col2 = CustomerName Text Width 30 Syntax as follows: Coln = ColumnName Type [width #] The parameter is explained below:
Parameter Description ColumnName text, Identifies the field name, if you contain a space to enclose the TYPE data type: Microsoft Jet Data Type: Bit Byte Short Long Currency Single DoubleTime Text Memo ODBC Data Type: Char (Same As Text) Float Same As Double) Integer (Same As MEMO) Date Date Format where Date Format is the date of the date, such as: Date YYYY-MM-DD WIDTH string, the number of numbers is used to specify the field Length ("Fixed Split Text File" is required, "Text Deliver text file" is optional) # 整 整 数号, Identifier Field Length Specifies the Character Set Characterset item There are two options: ANSI | OEM Select ANSI character integration: CharacterSet = ANSI Special Data Type Conversion Special Data Type Conversion Mainly definitions such as how the currency data conversion or how to display, you can refer to the table below:
Option Description DateTimeFormatCan be set to a format string indicating dates and times. You should specify this entry if all date / time fields in the import / export are handled with the same format. All Microsoft Jet formats except AM and PM are supported. In the absence of a format string, the Windows Control Panel short date picture and time options are used.DecimalSymbolCan be set to any single character that is used to separate the integer from the fractional part of a number. NumberDigitsIndicates the number of decimal digits in the fractional THEN 1 And Greater Thanlet Than 1 And Greater Than -1 Should Contain Leading Zeros
. This value can either be False (no leading zeros) or True.CurrencySymbolIndicates the currency symbol to be used for currency values in the text file Examples include the dollar sign ($) and Dm.CurrencyPosFormatCan be set to any of the following values: · Currency symbol prefix with no separation ($ 1) · Currency symbol suffix with no separation (1 $) · Currency symbol prefix with one character separation ($ 1) · Currency symbol suffix with one character separation (1 $) CurrencyDigitsSpecifies the number of digits Used for the Fractional Part of A Currency Amount. Currencynegformatcan Be One of The Following Values: · ($ 1) · $ 1 · $ -1 · $ 1- · (1 $) · -1 $ · 1- $ · 1 $ - · -1 $ · $ 1 · 1 $ - $ 1- · $ -1 · 1- $ · ($ 1) · (1 $) This Example Shows The Dollar Sign, But you shop Replace It with the appropriate currencysymbol value In The Actual Program.currencythousandsyMbolindicates The Single-Character Symbol To Be Used for Separating Currency Values in The Text File By T Housands.currencydecimalsymbolcan be set to any selfharacter That is buy s 帖子 下 下 简 例, 例 例 表 类似 表 类似 下 类似 下 类似 下 类似 类似 类似 类似 类似 类似 类似 类似 类似 类似 类似 类似 类似 类似 类似 类似 类似 类似 类似 类似 类似 类似 类似 类似 类似 类似 类似 类似: Organic Chemistry Institute 2002-1-1 Luo Zhen Digital Machine Tool Research Institute 2004-1-1 Import Access Should be similar to the table below:
Name Unit Date Wang Hai Shanghai Organic Institute Institute 2002-1-1 Luo Zhen Digital Machine Tool Research Institute 2004-1-1 Schema.ini is similar to the following INI file:
[Contacts.txt] colnameHeader = trueFormat = Delimited ("") MaxScanRows = 0Characterset = ansicol1 = "Name" char width 10col2 = "Char Width 9col3 =" Contact Date "Date Width 8 Note As follows:
[Contacts.txt] /// Text file name colnameHeader = true /// With header format = DELIMITED () /// Space as a separator, if it is a semicolon, use format = delimited (;) to solve the MaxScanRows = 0 /// Scan the entire file, characterset = ANSI / / / ANSI character set col1 = "name" char width 10 /// field 1col2 = "unit" char width 9 /// field 2col3 = "contact date" Date Width 8 /// field 3 /// If there is more fields to col4 .... coln Note, Schema.ini must and require imported text files in the same directory. Since then, we can use the following statements to import data: currentProject.connection.execute "select * into newcontact from [text; fmt = Delimited; HDR = yes; Database = C: /;]. [Contacts # txt]; "Note that the following statement will result in the import failure to the 2000 format, which should be an Access itself: I don't know if I have solved this problem. You can tell me in the" Comment ". .
DOCMD.TRANSFERTEXT AcimportFixed,, "Contacts", "C: /Contacts.txt" or
DOCMD.TRANSFERTEXT AcimportFixed, "C: /. INI", "Contacts", "C: /Documents.txt" error message is: Runtime error '3625': Text file specification 'c: schema.ini' does not exist. You cannot use specifications to import, export, or links. Or runtime error '2511': This operation or method requires a Specification Name parameter. Also, please refer to: http://support.microsoft.com/default.aspx? Scid = KB; EN-US; 241477
http://www.access911.net webmaster collection
