\r\n4. The first line in the format file (reading 9.0) is very probably incorrect. The number in the first line needs to match the SQL Server version, so 9.0 is for SQL Server 2005, 10.0 - for 2008 and 2008 R2, 11.0 - for 2012, 12.0 for 2014 and 13.0 for 2016. Here is the format file which works with your sample data. In my case the SQL Server version is 2016 so I use 13.0 in the first line. Also, the syntax of the bulk insert in question is invalid, it misses parentheses around the format file specifications. Here is the script and format file which work: BULK INSERT dbo.TableA FROM 'C:\Temp\xyzjob123456678x.txt' WITH (FORMATFILE = 'C:\Temp\Format.fmt'); Format file: 13.0 6 1 SQLCHAR 0 02 "" 1 stfips "" 2 SQLCHAR 0 02 "" 2 areatype "" 3 SQLCHAR 0 06 "" 3 area "" 4 SQLCHAR 0 04 "" 4 periodyear "" 5 SQLCHAR 0 02 "" 5 periodtype "" 6 SQLCHAR 0 02 "\r\n" 6 period "" Hope this helps. Oleg
14.0
4
1 SQLINT 0 4 "," 1 RecordID ""
2 SQLDECIMAL 1 19 "," 2 DecColOne ""
3 SQLNUMERIC 1 19 "," 3 NumColTwo ""
4 SQLDATETIME 1 8 "\r\n" 4 DateCol ""
In any case, it is always safer to use bcp itself to generate correct format files. For example, running this from the command prompt (after typing correct table, database and server names) will produce a valid format file (comma-delimited data): bcp YourDB.dbo.SomeTable format nul -S SrvName -T -n -t "," -r "\n" -f "c:\temp\SomeTable.fmt"
18 People are following this question.