使用bcp将csv文件导入到sql 2005或2008

发布于 2024-07-21 22:25:42 字数 274 浏览 7 评论 0原文

我有一个 csv 文件，我需要将其导入到 sql 2005 或 2008 中的表中。csv 中的列名称和计数与表列名称和计数不同。 csv 由“;”分隔。

示例

CSV 文件内容：

FirstName;LastName;Country;Age
Roger;Mouthout;Belgium;55

SQL 人员表

Columns: FName,LName,Country

原文

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 ';' .

Example

CSV FILEcontents:

FirstName;LastName;Country;Age
Roger;Mouthout;Belgium;55

SQL Person Table

Columns: FName,LName,Country

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

九命猫 2024-07-28 22:25:42

使用 bcp 导入时可以使用格式文件：

为表创建格式文件：

 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

编辑导入文件。诀窍是为要跳过的字段添加一个虚拟行，并添加一个“0”
作为服务器列顺序。

然后使用此格式文件导入数据，指定您的输入文件、此格式文件和分隔符：

bcp [table_name] in [data_file_name] -t , -f [format_file_name.fmt] -T

You can use a format file when importing with 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.

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

回复收藏 0 原文

云雾 2024-07-28 22:25:42

我将创建一个临时表，批量插入批次，在新表中选择您需要的内容并删除临时表。

就像是

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

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

回复收藏 0 原文

ペ泪落弦音 2024-07-28 22:25:42

我现在更喜欢将 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>

然后您可以使用服务器端 BULK INSERT 命令，如下所示：

BULK INSERT foo FROM '\\mydomain.com\bar\bletch' WITH (FORMATFILE='foo.xml', ERRORFILE='foo.errors',  FIRSTROW = 1, BATCHSIZE=10000)

或者，如果您想“动态”修改数据，您可以使用

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

I now prefer to use XML format files like this with BULK INSERT or 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 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

回复收藏 0 原文

失与倦＂ 2024-07-28 22:25:42

有关信息，具有相同的结构，您可以使用此类语句：

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

请参阅示例

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

回复收藏 0 原文

~没有更多了~