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

-- create the bcp file
exec master..xp_cmdshell 'echo 1,a,b,c > c:\bcp.txt'
exec master..xp_cmdshell 'echo 2,d,e,f >> c:\bcp.txt'
exec master..xp_cmdshell 'echo 3,g,h,i >> c:\bcp.txt'

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

-- import the data
master..xp_cmdshell 'bcp ##a in c:\bcp.txt -c -t,'
bulk insert ##a from 'c:\bcp.txt' with (FIELDTERMINATOR  = ',')


Extra columns in table without format file

-- create the destination table
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

master..xp_cmdshell 'bcp ##a in c:\bcp.txt -c -t,'
Error = [Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification
Error = [Microsoft][ODBC SQL Server Driver]Unexpected EOF encountered in BCP data-file

bulk insert ##a from 'c:\bcp.txt' with (FIELDTERMINATOR  = ',')
Bulk insert data conversion error (type mismatch) for row 1, column 2 (i).
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)

-- create the destination table and view (both in tempdb in this case)
create table a (id int identity, i int, s varchar(2), t varchar(2), u varchar(2), dte datetime default getdate())
create view v
as
select i, s, t, u from a
go

master..xp_cmdshell 'bcp tempdb..v in c:\bcp.txt -c -t,'
bulk insert tempdb..v from 'c:\bcp.txt' with (FIELDTERMINATOR  = ',')


Use of format file

Another way is to use a format file to omit columns

Format 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

-- create the bcp file
a
b
c

-- create the destination table
create table ##a (col1 varchar(10))

-- create the format file
8.0
1
1 SQLCHAR 0 0 "\r\n" 1 col1 Latin1_General_CI_AS

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

Import with multiple columns
-- create the bcp file
1,a,b,c
2,d,e,f
3,g,h,i

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

-- create the format file
8.0
4
1 SQLCHAR 0 0 "," 1 i Latin1_General_CI_AS
2 SQLCHAR 0 0 "," 2 s Latin1_General_CI_AS
3 SQLCHAR 0 0 "," 3 t Latin1_General_CI_AS
4 SQLCHAR 0 0 "\r\n" 4 u Latin1_General_CI_AS

master..xp_cmdshell 'bcp ##a in c:\bcp.txt -fc:\bcpfmt.txt'
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
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
Give 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

master..xp_cmdshell 'bcp ##a in c:\bcp.txt -fc:\bcpfmt.txt'
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
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

master..xp_cmdshell 'bcp ##a in c:\bcp.txt -fc:\bcpfmt.txt'
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"

-- create the destination table
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


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