使用 bcp 将 csv 文件导入 sql 2005 或 2008
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/924733/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Use bcp to import csv file to sql 2005 or 2008
提问by MichaelD
I have a csv file and i need to import it to a table in sql 2005 or 2008. The column names and count in the csv are different from the table column names and count. The csv is splitted by a ';' .
我有一个 csv 文件，我需要将它导入到 sql 2005 或 2008 中的表中。csv 中的列名和计数与表列名和计数不同。csv 被一个 ';' 分割 .
Example
例子
CSV FILEcontents:
CSV 文件内容：
FirstName;LastName;Country;Age
Roger;Mouthout;Belgium;55
SQL Person Table
SQL 人员表
Columns: FName,LName,Country
回答by edosoft
You can use a format file when importing with bcp:
使用 bcp 导入时可以使用格式文件：
Create a format file for your table:
为您的表创建一个格式文件：
bcp [table_name] format nul -f [format_file_name.fmt] -c -T
9.0
4
1 SQLCHAR 0 100 "," 1 FName SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 100 "," 2 LName SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 100 "," 3 Country SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 100 "\r\n" 0 Age SQL_Latin1_General_CP1_CI_AS
Edit the import file. The trick is to add a dummy row for the field you want to skip, and add a '0' as server column order.
编辑导入文件。诀窍是为要跳过的字段添加一个虚拟行，并添加一个“0”作为服务器列顺序。
Then import the data using this format file, specifying your inputfile, this format file and the seperator:
然后使用此格式文件导入数据，指定您的输入文件、此格式文件和分隔符：
bcp [table_name] in [data_file_name] -t , -f [format_file_name.fmt] -T
回答by Dan Kennedy
I'd create a temporary table, bulk insert the lot, select into the new table what you need and drop the temporary table.
我会创建一个临时表，批量插入批次，在新表中选择您需要的内容并删除临时表。
Something like
就像是
CREATE TABLE dbo.TempImport
(
FirstName varchar(255),
LastName varchar(255),
Country varchar(255),
Age varchar(255)
)
GO
BULK INSERT dbo.TempImport FROM 'PathToMyTextFile' WITH (FIELDTERMINATOR = ';', ROWTERMINATOR = '\n')
GO
INSERT INTO dbo.ExistingTable
(
FName,
LName,
Country
)
SELECT FirstName,
LastName,
Country
FROM dbo.TempImport
GO
DROP TABLE dbo.TempImport
GO
回答by Paul Harrington
I now prefer to use XML format files like this with BULK INSERT or OPENROWSET:
我现在更喜欢将这样的 XML 格式文件与 BULK INSERT 或 OPENROWSET 一起使用：
<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="1" xsi:type="CharTerm" TERMINATOR="|" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="37"/>
<FIELD ID="3" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="41"/>
<FIELD ID="4" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="17"/>
<FIELD ID="5" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="10" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="i" xsi:type="SQLCHAR"/>
<COLUMN SOURCE="2" NAME="j" xsi:type="SQLUNIQUEID"/>
<COLUMN SOURCE="3" NAME="k" xsi:type="SQLNUMERIC" PRECISION="18" SCALE="0"/>
<COLUMN SOURCE="4" NAME="l" xsi:type="SQLBINARY"/>
<COLUMN SOURCE="5" NAME="m" xsi:type="SQLVARYCHAR"/>
</ROW>
</BCPFORMAT>
Then you can use the server-side BULK INSERT command as follows:
然后可以使用服务器端的 BULK INSERT 命令，如下所示：
BULK INSERT foo FROM '\mydomain.com\bar\bletch' WITH (FORMATFILE='foo.xml', ERRORFILE='foo.errors', FIRSTROW = 1, BATCHSIZE=10000)
alternatively, if you want to modify the data 'in-flight', you can use the
或者，如果您想在“飞行中”修改数据，您可以使用
INSERT foo(i, j,k)
SELECT foo_delimited.i, foo_delimited.j, foo_delimited.k * 2
OPENROWSET(BULK 'foo',
FORMATFILE= 'foo.xml')
AS foo_delimited
回答by gerardnico
For info, with the same structure, you can use this kind of statement:
对于信息，具有相同的结构，您可以使用这种语句：
bcp schema.Table in "/Samples/AdventureWorksDW/DimCurrency.csv" \
-S db.url \
-d databaseName \
-U userName \
-P pwd \
-t ; `# The field separator ` \
-c `# Insert as character and doesn't ask the data type` \
-q `# With quote -- seems to be mandatory`
See example
查看示例
