BCP using format files

Test

If the format of the file matches the table then it is simple to bcp the file without use of a format file.Whenever you have problems with a bcp start with a simple structure and build on that until you find the error.Start with a single row. If that does not work remove columns until it does. The fault is usually obvious if you narrow it down.You can use bcp or bulk insert to import files. I find bulk insert a bit more flexible and it is a little faster.Try running "select @@rowcount" after both of these. The bcp will give the number of lines displayed (8) whereas bulk insert will give the number of records inserted.

Simple bulk insert

1. -- create the bcp file
   
2. exec master..xp_cmdshell 'echo 1,a,b,c > c:\bcp.txt'
   
3. exec master..xp_cmdshell 'echo 2,d,e,f >> c:\bcp.txt'
   
4. exec master..xp_cmdshell 'echo 3,g,h,i >> c:\bcp.txt'
   
5. 
   
6. -- create the destination table
   
7. create table ##a (i int, s varchar(2), t varchar(2), u varchar(2))
   
8. 
   
9. -- import the data
   
10. master..xp_cmdshell 'bcp ##a in c:\bcp.txt -c -t,'
    
11. bulk insert ##a from 'c:\bcp.txt' with (FIELDTERMINATOR  = ',')

复制代码

Extra columns in table without format file

1. -- create the destination table
   
2. create table ##a (id int identity, i int, s varchar(2), t varchar(2), u varchar(2), dte datetime default getdate())

复制代码

If you try the same command with this table you will get the following errors

1. master..xp_cmdshell 'bcp ##a in c:\bcp.txt -c -t,'
   
2. Error = [Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification
   
3. Error = [Microsoft][ODBC SQL Server Driver]Unexpected EOF encountered in BCP data-file
   
4. 
   
5. bulk insert ##a from 'c:\bcp.txt' with (FIELDTERMINATOR  = ',')
   
6. Bulk insert data conversion error (type mismatch) for row 1, column 2 (i).
   
7. Bulk insert data conversion error (type mismatch) for row 2, column 2 (i).
   

复制代码

A simple way around this is to create a view and use that for the bcp (of course this is only available for permanent tables)

1. -- create the destination table and view (both in tempdb in this case)
   
2. create table a (id int identity, i int, s varchar(2), t varchar(2), u varchar(2), dte datetime default getdate())
   
3. create view v
   
4. as
   
5. select i, s, t, u from a
   
6. go
   
7. 
   
8. master..xp_cmdshell 'bcp tempdb..v in c:\bcp.txt -c -t,'
   
9. bulk insert tempdb..v from 'c:\bcp.txt' with (FIELDTERMINATOR  = ',')
   

复制代码

Use of format file

Another way is to use a format file to omit columnsFormat file structure (see bol)

8.0                                                        version
1                                                        number of columns
1 SQLCHAR 0 1 "\r\n" 1 col1 Latin1_General_CI_AS        col number, file data type, prefix length, data length, terminator, table col order, table col name, file collation

Note - can use version 7.0 and omit the collation.Simple import using format file

1. -- create the bcp file
   
2. a
   
3. b
   
4. c
   
5. 
   
6. -- create the destination table
   
7. create table ##a (col1 varchar(10))
   
8. 
   
9. -- create the format file
   
10. 8.0
    
11. 1
    
12. 1 SQLCHAR 0 0 "\r\n" 1 col1 Latin1_General_CI_AS
    
13. 
    
14. master..xp_cmdshell 'bcp ##a in c:\bcp.txt -fc:\bcpfmt.txt'
    
15. bulk insert ##a from 'c:\bcp.txt' with (formatfile = 'c:\bcpfmt.txt')

复制代码

Import with multiple columns

1. -- create the bcp file
   
2. 1,a,b,c
   
3. 2,d,e,f
   
4. 3,g,h,i
   
5. 
   
6. -- create the destination table
   
7. create table ##a (i int, s varchar(2), t varchar(2), u varchar(2))
   
8. 
   
9. -- create the format file
   
10. 8.0
    
11. 4
    
12. 1 SQLCHAR 0 0 "," 1 i Latin1_General_CI_AS
    
13. 2 SQLCHAR 0 0 "," 2 s Latin1_General_CI_AS
    
14. 3 SQLCHAR 0 0 "," 3 t Latin1_General_CI_AS
    
15. 4 SQLCHAR 0 0 "\r\n" 4 u Latin1_General_CI_AS
    
16. 
    
17. master..xp_cmdshell 'bcp ##a in c:\bcp.txt -fc:\bcpfmt.txt'
    
18. bulk insert ##a from 'c:\bcp.txt' with (formatfile = 'c:\bcpfmt.txt')

复制代码

Extra columns in table

Just omit the extra columns from te format file-- create the bcp file

1,a,b,c
2,d,e,f
3,g,h,i

-- create the destination table

1. create table ##a (id int identity, i int, s varchar(2), t varchar(2), u varchar(2), dte datetime default getdate())

复制代码

-- create the format fileGive the column number and names for the table as the columns to be inserted into

8.0
4
1 SQLCHAR 0 0 "," 2 i Latin1_General_CI_AS
2 SQLCHAR 0 0 "," 3 s Latin1_General_CI_AS
3 SQLCHAR 0 0 "," 4 t Latin1_General_CI_AS
4 SQLCHAR 0 0 "\r\n" 5 u Latin1_General_CI_AS

1. master..xp_cmdshell 'bcp ##a in c:\bcp.txt -fc:\bcpfmt.txt'
   
2. bulk insert ##a from 'c:\bcp.txt' with (formatfile = 'c:\bcpfmt.txt')

复制代码

Extra columns in table, extra columns in data file

All columns in the data file must be defined in the format file but those not imported have a 0 col order.-- create the bcp file

xx,1,a,b,c,xx
xx,2,d,e,f,xx
xx,3,g,h,i,xx

-- create the destination table

1. create table ##a (id int identity, i int, s varchar(2), t varchar(2), u varchar(2), dte datetime default getdate())

复制代码

-- create the format file

8.0
6
1 SQLCHAR 0 0 "," 0 x Latin1_General_CI_AS
2 SQLCHAR 0 0 "," 2 i Latin1_General_CI_AS
3 SQLCHAR 0 0 "," 3 s Latin1_General_CI_AS
4 SQLCHAR 0 0 "," 4 t Latin1_General_CI_AS
5 SQLCHAR 0 0 "," 5 u Latin1_General_CI_AS
6 SQLCHAR 0 0 "\r\n" 0 x Latin1_General_CI_AS

1. master..xp_cmdshell 'bcp ##a in c:\bcp.txt -fc:\bcpfmt.txt'
   
2. bulk insert ##a from 'c:\bcp.txt' with (formatfile = 'c:\bcpfmt.txt')

复制代码

Quote delimitted fields in data file

-- create the bcp file
"a",1,"b","c"
"d",2,"e","f"
"g",3,"h","i"

1. -- create the destination table
   
2. create table ##a (s varchar(5), i int, t varchar(5), u varchar(1000))

复制代码

-- create the format file

8.0
5
1 SQLCHAR 0 0 "\"" 0 x Latin1_General_CI_AS
2 SQLCHAR 0 0 "\"," 1 s Latin1_General_CI_AS
3 SQLCHAR 0 0 ",\"" 2 i Latin1_General_CI_AS
4 SQLCHAR 0 0 "\",\"" 3 t Latin1_General_CI_AS
5 SQLCHAR 0 0 "\"\r\n" 4 u Latin1_General_CI_AS

1. master..xp_cmdshell 'bcp ##a in c:\bcp.txt -fc:\bcpfmt.txt'
   
2. bulk insert ##a from 'c:\bcp.txt' with (formatfile = 'c:\bcpfmt.txt')

复制代码

Author Nigel Rivett
http://www.nigelrivett.net/SQLTsql/BCP_quoted_CSV_Format_file.html

星博春

继续关注一下这方面的信息