Bulk Insert SQL Using Format File and Fixed Width Text File

Test · 发表于 2011-10-6 13:46:58

本帖最后由 Test 于 2011-10-6 16:24 编辑

Here is an example:
Let's say the fixed width text data file as:

MONTHEND BR BU INC DATE TONS COST
8/31/11 xdm CBD-MS 8/31/11 555.2356 1584281
8/31/11 xdm CBD-MS 9/ 1/11 275.7955 655156
8/31/11 xdm CBD-MS 10/ 1/11 664.0000 1681941

复制代码

This file created under Linux and only has 0xD(\n) to make a new line.

and we create a format file like this:

9.0
6
1 SQLCHAR 0 8 " " 1 MonthEnd ""
2 SQLCHAR 0 3 "" 2 BR SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 10 "" 3 BU SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 8 "" 4 Inc_Date ""
5 SQLCHAR 0 14 "" 5 TONS ""
6 SQLCHAR 0 12 "\n" 6 COST ""

复制代码

Please note the date data inside the source text file is as MM/DD/YY, it different from the DD/MM/YY we usually used.

So let have a test inside SQL server.

Set DateFormat MDY
if object_id('tempdb..#TxtData','U') is not null drop table #TxtData
create table #TxtData
(
--MonthEnd varchar(256)
MonthEnd datetime
,BR varchar(3)
,BU varchar(10)
,Inc_Date datetime
--,Inc_Date varchar(8)
,TONS float
,COST float
)
bulk insert #TxtData
from 'C:\Source.txt' -- The source text file.
with (formatfile='C:\Source.fmt', FIRSTROW = 2); --the format file shows above
select * from #TxtData
/*
Data format as
MONTHEND BR BU INC DATE TONS COST
8/31/11 xdm CBD-MS 8/31/11 555.2356 1584281
8/31/11 xdm CBD-MS 9/ 1/11 275.7955 655156
8/31/11 xdm CBD-MS 10/ 1/11 664.0000 1681941
Works Good with format file as:
9.0
6
1 SQLCHAR 0 8 " " 1 MonthEnd SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 3 "" 2 BR SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 10 "" 3 BU SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 8 "" 4 Inc_Date SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 14 "" 5 TONS ""
6 SQLCHAR 0 12 "\n" 6 COST ""
*/

复制代码

That's it.
Data inside SQL DB shows up here:

MonthEnd BR BU Inc_Date TONS COST
2011-08-31 00:00:00.000 xdm CBD-MS 2011-08-31 00:00:00.000 555.2356 1584281
2011-08-31 00:00:00.000 xdm CBD-MS 2011-09-01 00:00:00.000 275.7955 655156
2011-08-31 00:00:00.000 xdm CBD-MS 2011-10-01 00:00:00.000 664 1681941

复制代码

And, we can also use OPENROWSET to get text data directly. like:

SELECT *
FROM OPENROWSET(BULK 'C:\Source.txt',
FORMATFILE='C:\Source.fmt', FIRSTROW = 2) AS Test

复制代码

PS:
You might want to use XML format file, here I give out the example of text format file amd XML format with same data format for your reference.

Text format file:

9.0
11
1 SQLCHAR 0 12 "," 1 Fc_Year ""
2 SQLCHAR 0 12 "," 2 Fc_Month ""
3 SQLCHAR 0 12 "," 3 BU_idx ""
4 SQLCHAR 0 12 "," 4 Branch_idx ""
5 SQLCHAR 0 41 "," 5 Fc_Tons ""
6 SQLCHAR 0 41 "," 6 Fc_Cost ""
7 SQLCHAR 0 41 "," 7 Fc_Cost_ton ""
8 SQLCHAR 0 128 "," 8 Fc_create_id Latin1_General_CI_AS
9 SQLCHAR 0 24 "," 9 Fc_ctime ""
10 SQLCHAR 0 128 "," 10 Fc_update_id Latin1_General_CI_AS
11 SQLCHAR 0 24 "\r\n" 11 Fc_Uptime ""

复制代码

and XML format file as:

<?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="," MAX_LENGTH="12"/>
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="12"/>
<FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="12"/>
<FIELD ID="4" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="12"/>
<FIELD ID="5" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="41"/>
<FIELD ID="6" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="41"/>
<FIELD ID="7" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="41"/>
<FIELD ID="8" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="128" COLLATION="Latin1_General_CI_AS"/>
<FIELD ID="9" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="24"/>
<FIELD ID="10" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="128" COLLATION="Latin1_General_CI_AS"/>
<FIELD ID="11" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="24"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="Fc_Year" xsi:type="SQLINT"/>
<COLUMN SOURCE="2" NAME="Fc_Month" xsi:type="SQLINT"/>
<COLUMN SOURCE="3" NAME="BU_idx" xsi:type="SQLINT"/>
<COLUMN SOURCE="4" NAME="Branch_idx" xsi:type="SQLINT"/>
<COLUMN SOURCE="5" NAME="Fc_Tons" xsi:type="SQLDECIMAL" PRECISION="19" SCALE="5"/>
<COLUMN SOURCE="6" NAME="Fc_Cost" xsi:type="SQLDECIMAL" PRECISION="19" SCALE="5"/>
<COLUMN SOURCE="7" NAME="Fc_Cost_ton" xsi:type="SQLDECIMAL" PRECISION="38" SCALE="19"/>
<COLUMN SOURCE="8" NAME="Fc_create_id" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="9" NAME="Fc_ctime" xsi:type="SQLDATETIME"/>
<COLUMN SOURCE="10" NAME="Fc_update_id" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="11" NAME="Fc_Uptime" xsi:type="SQLDATETIME"/>
</ROW>
</BCPFORMAT>

复制代码

账号		自动登录	找回密码
密码			注册

Bulk Insert SQL Using Format File and Fixed Width Text File

浏览过的版块