InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 12:22:35 GMT20http://www.sqlservercentral.com/Forums/Topic1416277-3077-1.aspxI figured it out...For SQL 2012: 1. The format file needed to start with 11.0.2. I removed any tabs between the columns in the format file (not sure if this was necessary)3. I had to add quotes at the end of each line in the format fileresulting format file looked like this:11.021 SQLCHAR 0 4 "," 1 tax_year ""2 SQLCHAR 0 5 "\r" 2 fips_code ""Wed, 06 Feb 2013 22:27:22 GMTdv_peekinghttp://www.sqlservercentral.com/Forums/Topic1416277-3077-1.aspxLowell, thanks for the reply.I actually simplified the data for the posting here. I actually have about 20 columns of data and some with quotes. So, I decided to use cbp with a format file.I thought this would be simple and someone would have pointed out something that I missed...Driving me crazy...Wed, 06 Feb 2013 17:54:52 GMTdv_peekinghttp://www.sqlservercentral.com/Forums/Topic1416277-3077-1.aspxi see two issues:first, teh data is so simple, i don't see any need to use a format file; i only use format files if it's delimited with quotes or something.second, there's no path information for the files, so unless you actually placed them in the same folder that bcp.exe exists in, i'd expect it to fail.[quote]bcp mydatabase.dbo.stg_assess_value in [color="#FF0000"]c:\Data\[/color]AVALUE.TXT -S myserver -U myusername -P mypassword -f [color="#FF0000"]c:\Data\[/color]Assess_Value.fmt[/quote]i would just switch to bulk insert for this one; i think it would be faster, and it's more TSQL-ish to me anyway.:[code]BULK INSERT stg_assess_value FROM 'c:\Data\AVALUE.TXT' WITH ( DATAFILETYPE = 'char', FIELDTERMINATOR = ',', ROWTERMINATOR = '\n', FIRSTROW = 1 ) [/code]Wed, 06 Feb 2013 09:40:12 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1416277-3077-1.aspxIan, thanks for the reply.I tried \r on the last line of the format file. I get the same error message. :(Wed, 06 Feb 2013 09:14:41 GMTdv_peekinghttp://www.sqlservercentral.com/Forums/Topic1416277-3077-1.aspxAt a guess, unless you are terminating rows with just a carriage return instead of a carriage return and line feed, the last line of your format file should read [code="sql"]2 SQLCHAR 0 5 "/r/n" 2 fips_code[/code]Note... the forward slashes should really be backward slashes, but they keep getting translated to something else, so I presume I need to escape them somehow in my reply, but don't know how.Wed, 06 Feb 2013 01:28:15 GMTIan Scarletthttp://www.sqlservercentral.com/Forums/Topic1416277-3077-1.aspxI must be overlooking something really simple, but I am stuck. keep getting the error: Error = [Microsoft][SQL Server Native Client 11.0]BCP host-files must contain at least one columnHere is my bcp:bcp mydatabase.dbo.stg_assess_value in AVALUE.TXT -S myserver -U myusername -P mypassword -f Assess_Value.fmtFormat file: Assess_Value.fmt7.0 2 1 SQLCHAR 0 4 "," 1 tax_year 2 SQLCHAR 0 5 "\r" 2 fips_codeInput file: AVALUE.TXT2013,220672013,220682013,220692013,220602013,22061SQL Server table: mydatabase.dbo.stg_assess_valueCREATE TABLE [dbo].[stg_assess_value]( [tax_year] [decimal](4, 0) NOT NULL, [fips_code] [decimal](5, 0) NOT NULL)I am using SQL Server 2012, but I am not doing anything version specific.Any help would be appreciated. Thanks.Wed, 06 Feb 2013 00:24:20 GMTdv_peeking