BCP Format File

Question

BCP Format File

Viewing 10 posts - 16 through 24 (of 24 total)

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

Jeff Moden SSC Guru Points: 1004361 More actions · Answer 1

Sorry it took me so long to get back to this...

No... no \t's here... I believe this is what you're looking for. Note that the forum ate the backslash-n that came after the \r...

9.0

13

1 SQLCHAR 0 1 "\"" 0 LeadQuote Latin1_General_CI_AS

2 SQLCHAR 0 100 "\",\"" 1 title Latin1_General_CI_AS

3 SQLCHAR 0 100 "\",\"" 2 forename Latin1_General_CI_AS

4 SQLCHAR 0 100 "\",\"" 3 surname Latin1_General_CI_AS

5 SQLCHAR 0 500 "\",\"" 4 pafad1 Latin1_General_CI_AS

6 SQLCHAR 0 500 "\",\"" 5 pafad2 Latin1_General_CI_AS

7 SQLCHAR 0 500 "\",\"" 6 pafad3 Latin1_General_CI_AS

8 SQLCHAR 0 500 "\",\"" 7 pafad4 Latin1_General_CI_AS

9 SQLCHAR 0 500 "\",\"" 8 pafad5 Latin1_General_CI_AS

10 SQLCHAR 0 500 "\",\"" 9 pafad6 Latin1_General_CI_AS

11 SQLCHAR 0 100 "\",\"" 10 postcode Latin1_General_CI_AS

12 SQLCHAR 0 12 "\",\"" 11 urn ""

13 SQLCHAR 0 500 "\"\r" 12 emailaddress Latin1_General_CI_AS

--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 2

This is very annoying!!

Right I have created the format file in the way you suggested now Jeff (Thanks)

But on trying to execute the following BCP Command:

declare @bcpcommand nvarchar(1000)

SET @bcpcommand = 'bcp "ASHLEY.DBO.Tablename" out c:\work\test.txt -f c:\work\bcpformatfile.fmt -T'

EXEC master..xp_cmdshell @bcpCommand

I get the error: Error = [Microsoft][SQL Native Client]Host-file columns may be skipped only when copying into the Server

Any ideas?

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

Heh... dammit... I didn't test it and I forgot about that little nuance... My humble appolgogies...

Here's a new BCP Format file with no "skips"...

9.0

12

1 SQLCHAR 0 100 "\",\"" 1 title Latin1_General_CI_AS

2 SQLCHAR 0 100 "\",\"" 2 forename Latin1_General_CI_AS

3 SQLCHAR 0 100 "\",\"" 3 surname Latin1_General_CI_AS

4 SQLCHAR 0 500 "\",\"" 4 pafad1 Latin1_General_CI_AS

5 SQLCHAR 0 500 "\",\"" 5 pafad2 Latin1_General_CI_AS

6 SQLCHAR 0 500 "\",\"" 6 pafad3 Latin1_General_CI_AS

7 SQLCHAR 0 500 "\",\"" 7 pafad4 Latin1_General_CI_AS

8 SQLCHAR 0 500 "\",\"" 8 pafad5 Latin1_General_CI_AS

9 SQLCHAR 0 500 "\",\"" 9 pafad6 Latin1_General_CI_AS

10 SQLCHAR 0 100 "\",\"" 10 postcode Latin1_General_CI_AS

11 SQLCHAR 0 12 "\",\"" 11 urn ""

12 SQLCHAR 0 500 "\"\r" 12 emailaddress Latin1_General_CI_AS

When you do your query as part of the BCP command, start it with...

SELECT '"'+Title,

... to include the leading quote.

Again, this forum ate the backslash "N" after the backslash "R" and should be included.

--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: 1004361 More actions · Answer 4

If we can't get this bugger up and running in short order, we'll do the QUOTENAME thing for the export so we don't have to mess with the Format file... but I believe the format file will do it's trick correctly now.

--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 5

OK seems to be nearly working now I am just having trouble inserting the leading quote

I tried running the following:

declare @bcpcommand nvarchar(1000)

SET @bcpcommand = 'bcp "SELECT ""+title,forename,surname,pafad1,pafad2,pafad3,pafad4,pafad5,pafad6,postcode,urn,emailaddress from ASHLEY.DBO.tablename" queryout c:\work\ashtest.txt -f c:\work\bcpformatfile.fmt -T'

EXEC master..xp_cmdshell @bcpCommand

But I now get the error;

'Error = [Microsoft][SQL Native Client][SQL Server]Unclosed quotation mark after the character string '+title,forename,surname,pafad1,pafad2,pafad3,pafad4,pafad5,pafad6,postcode,urn,emailaddress from ASHLEY.DBO.Tablename'.'

bicky1980 SSCrazy Points: 2079 More actions · Answer 6

Update - I have got around this for now by adding an additional column and populating the column with a double quote. I then adjusted the command to read:

declare @bcpcommand nvarchar(1000)

SET @bcpcommand = 'bcp "SELECT quote+title,forename,surname,pafad1,pafad2,pafad3,pafad4,pafad5,pafad6,postcode,urn,emailaddress from ASHLEY.DBO.tablename" queryout c:\work\ashtest.txt -f c:\work\bcpformatfile.fmt -T'

PRINT @BCPCOMMAND

EXEC master..xp_cmdshell @bcpCommand

Don't know whether this could have been done without altering the table but if anyone has any suggestions please go ahead and suggest...Thanks:D

Jeff Moden SSC Guru Points: 1004361 More actions · Answer 7

Yep... no need for the extra column...

SET @bcpcommand = 'bcp "SELECT ''"''+title,forename,surname,pafad1,pafad2,pafad3,pafad4,pafad5,pafad6,postcode,urn,emailaddress from ASHLEY.DBO.tablename" queryout c:\work\ashtest.txt -f c:\work\bcpformatfile.fmt -T'

--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 8

Hello Jeff

I tried that but I get the BCP error (the one which advises the correct syntax to use) Can you test the command and hopefully provide one that works

Thanks

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

Try char(34) to make the select command generate a double quote 'during the query'. This avoids the problem of the commandline seeing that double quote. I have also added quotes around the file names just in case you intend to use pathnames that have spaces in them.

SET @bcpcommand = 'bcp "SELECT char(34)+title, forename, surname, pafad1, pafad2, pafad3, pafad4, pafad5, pafad6, postcode, urn, emailaddress from ASHLEY.DBO.tablename" queryout "c:\work\ashtest.txt" -f "c:\work\bcpformatfile.fmt" -T'

bicky1980 SSCrazy Points: 2079 More actions · Answer 10

Thanks alot twillcomp - That has sorted the issue

The process is now working a treat!

Thanks everyone for all your help - You have been Great!!