BCP Format File

Question

Post reply

BCP Format File

bicky1980

SSCrazy

Points: 2079
More actions
March 4, 2009 at 8:28 am

#197667

Hello
Is anyone able to send me an example of a Format file to be used in conjuction with the BCP command to create a comma delimited text file which is quote qualified
Thanks in advance
😉
Bicky1980

Viewing 15 posts - 1 through 15 (of 24 total)

You must be logged in to reply to this topic. Login to reply

darshan28 Ten Centuries Points: 1021 More actions · Answer 1

Hi,

For Exporting data

bcp AdventureWorks.Sales.SalesOrderDetail out c:\Currency.txt -c -S student\sqlserver2005

Use IN instead of OUT to import data

bcp AdventureWorks.Sales.SalesOrderDetail in c:\Currency.txt -c -S student\sqlserver2005

Thanks

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 2

daredon28 (3/4/2009)
Hi,
For Exporting data
bcp AdventureWorks.Sales.SalesOrderDetail out c:\Currency.txt -c -S student\sqlserver2005
Use IN instead of OUT to import data
bcp AdventureWorks.Sales.SalesOrderDetail in c:\Currency.txt -c -S student\sqlserver2005
Thanks

That's just not gonna handle "Quote Qualified" like Bicky asked for...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

bicky1980 (3/4/2009)
Hello
Is anyone able to send me an example of a Format file to be used in conjuction with the BCP command to create a comma delimited text file which is quote qualified
Thanks in advance
😉
Bicky1980

Bicky... as \t is the delimiter symbol for the TAB character in format files, so is \" the delimiter symbol for the quotes in the file. If the very first column is quoted, the first character, which is a quote, should go to column 0 in the table.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

bicky1980 SSCrazy Points: 2079 More actions · Answer 4

Hi all

When I run:

bcp AdventureWorks.Sales.SalesOrderDetail out c:\Currency.txt -c -S student\sqlserver2005

I get the error: "Incorrect syntax near '.'."

So the format file isn't created! I am using SQL Server 2005...

I saw this in other examples on the web but again got the same error?!?

Any more help?

Thanks

Bicky

twillcomp SSC Eights! Points: 989 More actions · Answer 5

To generate a format file you need to use 'format' instead of 'out'.

This example is from BOL and BOL is your friend here for all the possible flags for generating character or xml based format files. Basically, -c for character, -f filename and -T for trusted connection.

bcp AdventureWorks.HumanResources.Department format nul -c -f Department-f-c.fmt -T

Tony

bicky1980 SSCrazy Points: 2079 More actions · Answer 6

Tried the latest suggestion but I am still getting the same error - Sorry but this is the first time I have delved into the BCP Format files - But really do need to know...

twillcomp SSC Eights! Points: 989 More actions · Answer 7

Sorry, I should have read your error message.

bcp is a commandline executable run from a dos prompt not from within SSMS.

Tony

bicky1980 SSCrazy Points: 2079 More actions · Answer 8

Ok I am now trying to run via command line in the following way:

declare @cmdcommand nvarchar(1000)

set @cmdcommand='bcp bicky.dbo.output format nul -c -f bcpformatfile.fmt -T'

exec @cmdcommand

But now getting the following error: "Could not find server 'bcp bicky' in sysservers"

Would you be able to post the correct syntax to use

Thanks for all your help

Bicky

twillcomp SSC Eights! Points: 989 More actions · Answer 9

That is still executing the statement within SQL server.

You need to pass the command to the cmdshell.

exec master..xp_cmdshell @cmdcommand

Note that xp_cmdshell may not be enabled on your systems. If you have control over this it is Surface Area Configuration but if you don't then you need to discuss with your DBA.

bicky1980 SSCrazy Points: 2079 More actions · Answer 10

Sorry My bad!!

Right I have created the Format file now (Thanks for the help)

I now need to ensure the file is Comma Delimited with Quote Qualifiers

The format File I have now looks like this:

9.0

12

1 SQLCHAR 0 100 "\t" 1 title Latin1_General_CI_AS

2 SQLCHAR 0 100 "\t" 2 forename Latin1_General_CI_AS

3 SQLCHAR 0 100 "\t" 3 surname Latin1_General_CI_AS

4 SQLCHAR 0 500 "\t" 4 pafad1 Latin1_General_CI_AS

5 SQLCHAR 0 500 "\t" 5 pafad2 Latin1_General_CI_AS

6 SQLCHAR 0 500 "\t" 6 pafad3 Latin1_General_CI_AS

7 SQLCHAR 0 500 "\t" 7 pafad4 Latin1_General_CI_AS

8 SQLCHAR 0 500 "\t" 8 pafad5 Latin1_General_CI_AS

9 SQLCHAR 0 500 "\t" 9 pafad6 Latin1_General_CI_AS

10 SQLCHAR 0 100 "\t" 10 postcode Latin1_General_CI_AS

11 SQLCHAR 0 12 "\t" 11 urn ""

12 SQLCHAR 0 500 "\r" 12 emailaddress Latin1_General_CI_AS

So looking at Jeff's advice I think I need to replace the "\t" with "\" - Is this right?

Thanks

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 11

Not exactly... post the first 5 rows of one of the data files you intend to import with this and we'll show you how...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

bicky1980 SSCrazy Points: 2079 More actions · Answer 12

Hello Jeff

I am not trying to import I am trying to export to a comma delimited Quote qualified text file...

The fields in my table are:

Title

Forename

Surname

Ad1

Ad2

Ad3

Ad4

Ad5

Ad6

Postcode

Email

URN

And need the data to look like:

"Mr","A","Sample","1 Test Street","","","","Sample Town","Sample County","Sample Postcode","Sample@Sample.com","1"

Hope this helps - and thanks

Bicky

twillcomp SSC Eights! Points: 989 More actions · Answer 13

I can't get my head around the double quotes cos bcp doesn't seem to like them in the terminator field, but you could replace the "/t" with "," to get comma delimited and for those columns you need to enclose in quotes, use ",'" or "','" or "'," as appropriate. (I'm sure these double and single quotes won't show up).

eg

9.0

4

1 SQLCHAR 0 7 ",'" 1 DepartmentID ""

2 SQLCHAR 0 100 "','" 2 Name ""

3 SQLCHAR 0 100 "'," 3 GroupName ""

4 SQLCHAR 0 24 "\r" 4 ModifiedDate ""

This will give you

1,'Engineering','Research and Development',1998-06-01 00:00:00.000

2,'Tool Design','Research and Development',1998-06-01 00:00:00.000

3,'Sales','Sales and Marketing',1998-06-01 00:00:00.000

bicky1980 SSCrazy Points: 2079 More actions · Answer 14

From looking in other places for a solution this is what I have so far:

The terminators needs to be :

/t"\",\""

And can do this directly from the BCP command without a format file but the leading and trailing quote is missing. I use this:

DECLARE @bcpcommand NVARCHAR(1000)

SET @bcpcommand = 'bcp "select * from BICKY.DBO.TABLENAME order by urn" queryout c:\work\test.txt -c -t"\",\"" -T"'

EXEC master..xp_cmdshell @bcpCommand

Then having looked at other solutions it has been suggested in the format file I add a column 0 to the formatfile with the following information:

0 SQLCHAR 0 0 "\"" 1 first_quote ""

But now this is where I get stuck

Any Help??