Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

BCP comma delimited file into a table Expand / Collapse
Author
Message
Posted Wednesday, February 6, 2013 12:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 15, 2013 11:03 PM
Points: 4, Visits: 15
I 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 column

Here is my bcp:
bcp mydatabase.dbo.stg_assess_value in AVALUE.TXT -S myserver -U myusername -P mypassword -f Assess_Value.fmt

Format file: Assess_Value.fmt
7.0
2
1 SQLCHAR 0 4 "," 1 tax_year
2 SQLCHAR 0 5 "\r" 2 fips_code

Input file: AVALUE.TXT
2013,22067
2013,22068
2013,22069
2013,22060
2013,22061

SQL Server table: mydatabase.dbo.stg_assess_value
CREATE 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.
Post #1416277
Posted Wednesday, February 6, 2013 1:28 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 9:19 AM
Points: 1,307, Visits: 4,508
At 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

2	SQLCHAR	 0	5	"/r/n"	2	fips_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.



Post #1416303
Posted Wednesday, February 6, 2013 9:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 15, 2013 11:03 PM
Points: 4, Visits: 15
Ian, thanks for the reply.

I tried \r on the last line of the format file. I get the same error message.
Post #1416582
Posted Wednesday, February 6, 2013 9:40 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:44 AM
Points: 12,905, Visits: 32,158
i 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.

bcp mydatabase.dbo.stg_assess_value in c:\Data\AVALUE.TXT -S myserver -U myusername -P mypassword -f c:\Data\Assess_Value.fmt



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.:
BULK INSERT stg_assess_value FROM 'c:\Data\AVALUE.TXT'
WITH (
DATAFILETYPE = 'char',
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n',
FIRSTROW = 1
)



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1416602
Posted Wednesday, February 6, 2013 5:54 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 15, 2013 11:03 PM
Points: 4, Visits: 15
Lowell, 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...
Post #1416773
Posted Wednesday, February 6, 2013 10:27 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 15, 2013 11:03 PM
Points: 4, Visits: 15
I 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 file

resulting format file looked like this:

11.0
2
1 SQLCHAR 0 4 "," 1 tax_year ""
2 SQLCHAR 0 5 "\r" 2 fips_code ""
Post #1416806
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse