How to import CSV data to SQL Server

When SCV is imported into the database, the number is automatically considered as int, but all values beyond the int range are read as null into the table.

 

The solution is to create a file named schema. ini under the CSV file. The format is as follows:

 

 

 

The schema. ini format is as follows (refer to: msdn topic schema. ini file ):

Schema. INI is used to provide record type information in text data. Each schema. ini entry indicates one of the five features of the table:

Text File Name
If you want to use schema for sample.txt, the corresponding schema entry should be

Using sample.txt]

File Format
The command is as follows:

Format = Value

Value can be one of the following values:

Tabdelimited is separated by tab.

Csvdelimited separated by commas

Fixedlength Fixed Length

Delimited (c) specifies a character, where C can be any character other than double quotation marks (") or empty

Field name, field width, and type
Format: coln = field name data type [width]

The field name can be any character. If the field name contains spaces, enclose them with double quotation marks.

The data type can be:

Bit

Byte

Short (integer)

Long

Currency

Single

Double (float)

Datetime (date dateformat)

Text (char)

Memo (longchar)

Where dateformat is a date format string such as: Date YYYY-MM-DD

Character Set
Format: characterset = ANSI | OEM

There are only two formats: ANSI and OEM.

Special Data Type Conversion
The conversion of special data types is generally used less, mainly for custom data formats such as date and currency. It is not described in detail here. Please check the msdn help: schema. ini file yourself

　

　

The following is a simple example with a table named contacts.txt similar to the following:

First namelast namehiredate

Nancy davolio 10-22-91

Robert King 10-23-91

The schema. ini file is similar to the following INI file (I added a comment ):

Export contacts.txt] // name of the text file to be imported

Colnameheader = true // whether a data header exists

Format = fixedlength // fixed Field Length

Maxscanrows = 0 // maximum number of imported rows

Characterset = OEM // Character Set

Col1 = "first name" char width 10 // The first column format

Col2 = "last name" char width 9 // second column format

Col3 = "hiredate" date width 8 // The third column format

/// So on

We can automatically create this schema. ini file based on the data!

Note that schema. ini must be in the same directory as the text file to be imported !!! If the directory is not the same, you must specify the full path of Schema. ini!

After that, we can use the following statement to import data:

Docmd. transfertext acimportfixed, "contacts", "C: documents.txt"

Or

Docmd. transfertext acimportfixed, "C: documents. ini", "contacts", "C: documents.txt"

The following describes the transfertext syntax (from access help ):

Docmd. transfertext [transfertype] [, specificationname], tablename, filename [, hasfieldnames] [, htmltablename] [, codePage]

Transfertype: actexttransfertype.

Actexttransfertype can be one of the following actexttransfertype constants:

Acexportdelim

Acexportfixed

Acexporthtml

Acexportmerge

Acimportdelim default

Acimportfixed

Acimporthtml

Aclinkdelim

Aclinkfixed

Aclinkhtml

If this parameter is left blank, the default constant (acimportdelim) is used ).

Specificationname can be of the variant type. String expression, indicating the name of the import or export specifications created and saved in the current database. For a fixed-length text file, you must specify a parameter or use the schema. ini file. The file must also be saved in the same folder of the imported, linked, or exported text file. To create a solution file, you can use the text import/export Wizard to create the file. You can leave this parameter blank for separated text files and Microsoft Word Mail data files to select the default import/export specifications.

Tablename is of the variant type. String expression, indicating the name of the Microsoft Access Table to which you want to import text data, export text data from it, or link text data, or you can export the result to the Microsoft Access query name of the text file.

Filename is of the variant type. String expression, indicating the complete name (including path) of the text file to be imported, exported to, or linked ).

Hasfieldnames can be of the variant type. Use true (-1) to use the first line of the text file as the field name when importing, exporting, or linking. Use false (0) to treat the first line in a text file as normal data. If this parameter is left blank, the default value (false) is used ). This parameter will be ignored by Microsoft Word Mail Merge data files. The first line of these files must contain the field name.

Htmltablenam is of the variant type. String expression, indicating the name of the table or list in the HTML file to be imported or linked. This parameter is ignored unless the transfertype parameter is set to acimporthtml or aclinkhtml. If you leave this parameter blank