显示标签为“headers”的博文。显示所有博文
显示标签为“headers”的博文。显示所有博文

2012年3月25日星期日

BCP Syntax

I am trying to use the BCP utility to copy a stored procedure out to a text
file.
1) Is there a way to include the field headers in the text file?
2) Is there a way to turn off the quotes between data?
So far, here is the syntax I have so far:
bcp "database..sp" queryout "c:\test.txt" -t, -Sserver -Uuser -Ppassword
Thank you,
JLFlemingOne option is to get the column names from the metadata & use UNION operator
to get a single resultset like:
SELECT -1 AS "sort_col",
MAX( CASE ORDINAL_POSITION WHEN 1 THEN col1 END ) AS "col1",
MAX( CASE ORDINAL_POSITION WHEN 2 THEN col2 END ) AS "col2",
..
MAX( CASE ORDINAL_POSITION WHEN n THEN coln END ) AS "coln"
FROM TABLE_NAME = 'tbl'
UNION
SELECT 0, col1, col2,... coln
FROM tbl ;
You can warp this into a view & BCP it out pretty easily, but watch out of
type mismatches with SYSNAME types. The above shows a general approach, but
you can avoid any reference to the meta-data by directly typing out your
column names like:
SELECT -1 AS "sort_col", "col1", "col2", ... "coln"
UNION
SELECT 0, col1, col2, ... coln
FROM tbl
ORDER BY "sort_col" ;
Another option is to create a ASCII file with the headers & then BCP out the
data to another file. Simply use the DOS COPY command like:
copy header.txt + body.txt data.txt
Anith

2012年3月20日星期二

BCP out and headers

I'm no dummy when it comes to this stuff but for the life of me I can't figure out how (if it is possible) to get column headers to go when exporting data. Here is the syntax I'm using:

BCP "select ATSCLAIMNUMBER, ALTERNATECLAIMNUMBER, LNAME , FNAME, MNAME, convert(varchar(10),LOSSDATE,101), convert(varchar(10),ERNOTIFIEDDATE,101), convert(varchar(10),CLOSINGDATE,101), STATUSCODE, INDPAID,MEDPAID,REHABPAID,EXPPAID,LEGALPAID,TOTALP AID,INDFUTURERES,MEDFUTURERES,REHABFUTURERES,EXPFU TURERES,LEGALFUTURERES,TOTALFUTURERES,EXCESSRECOVE RY1,EXCESSRECOVERY2,EXCESSRECOVERY3,EXCESSRECOVERY 4,EXCESSRECOVERY5,EXCESSRECOVERY,OTHERRECOVERY1,OT HERRECOVERY2,OTHERRECOVERY3,OTHERRECOVERY4,OTHERRE COVERY5,OTHERRECOVERY FROM ##MMAACTUARY" queryout C:\mma03182004.TXT /c /t, /r \n /U demo /P demo /S ATSDEV\ATS2K

It spits out the file fine. This is all built with dynamic SQL and needs to email the file which is does perfectly. My only problem is that there are no headers which I can't have. Any suggestions?How about using osql with a "-s," parameter? Might want to add a -n -w 5000 also.|||I think I remember a post like this earlier. As I recall, Brett Kaiser recommended a strategy whereby the SELECT statement was UNIONed to another select statement with the column names as the result set. The problem with this strategy (as I recall) was that all the columns had to be typed as varchar. So something like:

SELECT
'MyColumn' as Column1,
'TwoColumn' as Column2

UNION

SELECT
Cast(ATSCLAIMNUMBER as varchar(20)),
Cast(ALTERNATECLAIMNUMBER as varchar(20))
FROM
##MMAACTUARY

Not elegant, but I think it worked.

HTH,

hmscott|||It's not what I imagined but it does look like it would work. I really don't care at this point how it happens so long as it does! I'll try this and report back with the results.|||Originally posted by hmscott
I think I remember a post like this earlier. As I recall, Brett Kaiser recommended a strategy whereby the SELECT statement was UNIONed to another select statement with the column names as the result set. The problem with this strategy (as I recall) was that all the columns had to be typed as varchar. So something like:

SELECT
'MyColumn' as Column1,
'TwoColumn' as Column2

UNION

SELECT
Cast(ATSCLAIMNUMBER as varchar(20)),
Cast(ALTERNATECLAIMNUMBER as varchar(20))
FROM
##MMAACTUARY

Not elegant, but I think it worked.

HTH,

hmscott

Hey, thnaks fo rremebering...and you know what?

bcp -c

and, what's not elegant about it?

I use Union ALL btw

AND to make sure the rows come out correctly...

For fixed width:

SELECT Col1,Col2,Col3 FROM (
SELECT 'Heading1' AS Col1
, 'Heading2' AS Col2
, 'Heading3' AS Col3
, 1 AS RowOrder
UNION ALL
SELECT Col1
, CONVERT(varchar(25), myDatetimeCol99)
, CONVERT(varchar(25), myIntCol99)
, 2 AS RowOrder
FROM myTable99
) AS XXX
Order by RowOrder

For Delimeted:

SELECT datarow FROM (
SELECT 'Heading1,Heading2,Heading3' AS Datarow
, 1 AS Roworder
UNION ALL
SELECT Col1+','
+','CONVERT(varchar(25), myDatetimeCol99)
+','CONVERT(varchar(25), myIntCol99)
, 2 AS RowOrder
FROM myTable99
) AS XXX
Order by RowOrder

And usually with delimeted, I wrap all the data in quotes

''''+ Col1 + ''''
','+ ''''+ Col2 + ''''|||Hey, and dig it, you can even add a trailer with audit counts...

Just make it datarow 3 and do a select count(*)...you could even sum amount if you want to go crazy...

AND you could add a row "type" to each one, to make it easier for extraction of the non data row

WHERE SUBSTRING(datarow,1,1) IN ('H','T')|||Brett,

My humblest apologies for suggesting that your solution was "not elegant". :-}

Actually, I need to spend some more time learning BCP and get off of my DTS crutch.

BTW, I changed jobs and have been thrown into what seems to be a real lion's den.

Ugh.

Regards,

hmscott|||Originally posted by hmscott
Brett,

My humblest apologies for suggesting that your solution was "not elegant". :-}

Actually, I need to spend some more time learning BCP and get off of my DTS crutch.

BTW, I changed jobs and have been thrown into what seems to be a real lion's den.

Ugh.

Regards,

hmscott

IMNSHO, I wouldn't be to quick to give up DTS. I don't think there is much you can do with BPC that you can't do with DTS. There is a bunch you can do with DTS that you can't do with BCP. There isn't an "ELEGANT" solution here just not as ugly.|||It does work but the string got too long. So, what si did was create a seondary table and inserted the header values. This table only has one row so when using that select statement unioned with the real select statement it works perfectly. If any one wants a copy of the procedure email me jfogel3@.msn.com|||Originally posted by hmscott
BTW, I changed jobs and have been thrown into what seems to be a real lion's den.


Sorry to hear that...

I avoid DTS for any production related issues...seen to many thing I couldn't explain...

Mostly with connections and changing them...Seems like it's lookin at an earlier version sometimes...no thanks...

And I love Nigels sig...

Cursors are useful if you don't know SQL
DTS can be used in a similar manner
Beer is not cold and fizzy

Or a paraphrase...since it seems like sqlteam is down...again....

Oh, and no apologies please....

(otherwise I'll have to do it all the time...)|||ooopps...here's the thread

They're back up...

and it's...

Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

2012年3月11日星期日

bcp header

Is there any way of including headers when we export a table using bcp
? Can't find the argument in bcp utility books online ?

Thanks for the helpPatrik (patrik.maheux@.umontreal.ca) writes:
> Is there any way of including headers when we export a table using bcp
> ? Can't find the argument in bcp utility books online ?

BCP does not unfortunately not support headers very well. If they can
be molded into the format of the rest of the file, you can sneak by.

Here are some examples:

value1,value2,value3
12,23,234

This is easy, use the -F option to start on row 2. Here is a tricker one:

value1,value2,value3
"text",12,"more test"

For this format you need a format file anyway for the data. This format
file will specify that each record begins with an empty column with " as
terminator. The result is that the header slips into that empty column
for the first row. You should not use -F.

Here is a dead end:

value1,value2,value3
12,"my text",12

I can't find a way to bulk-load this file, because the header does not
match the format of the data rows, and you can hide it an empty column.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

2012年3月8日星期四

BCP fmt and Create Table

Hello,
I am being sent a fmt file and a data file without headers which I
would like to import into my sql 2000 server. However the data files
have couple of 100's of columns, how could I avoid manually creating a
table. Can I create a table using a fmt file thru bcp/bulk insert?

Thanks
sd<danths@.gmail.com> wrote in message
news:1117041460.483784.193120@.g47g2000cwa.googlegr oups.com...
> Hello,
> I am being sent a fmt file and a data file without headers which I
> would like to import into my sql 2000 server. However the data files
> have couple of 100's of columns, how could I avoid manually creating a
> table. Can I create a table using a fmt file thru bcp/bulk insert?
> Thanks
> sd

I don't believe there's an automatic way to create a table using only a
format file, but why not just ask the person sending the data to include a
CREATE TABLE script?

If that's not possible (which would be strange), one solution would be to
write a script to parse format files and produce the corresponding CREATE
TABLE statement - if you'll need to do this often, and if the data will
always be in different formats, it could be worth it.

Alternatively, for a one-off task, it might be easier to use DTS via the
Import and Export Data Wizard. That will give you the option to create the
destination table, although depending on what's in the format file, you
might have to spend a lot of time rearranging column order and so on.

But getting the table script is probably the best option, otherwise there's
no way to know what the exact data types are, what the key columns are,
which columns are NULL/NOT NULL, if there are any CHECK constraints etc.

Simon

bcp Export to CSV with headers

I’m trying to find a way to export all data from a table in to a csv file with a header. I’m not concerned at this point with dynamic headers (that would be nice but at this point I’m not picky). I would just like to find a way to programmatically drop a table to a csv file.

use tempdb

go

create view vw_csvexport

as

select

'"' + convert(varchar(50), Name) + '"',

'"' + convert(varchar(50), ID) + '"'

from TestHeaderTable

union all

SELECT top 100 percent

Name = '"' + name + '"',

ID = '"' + convert(varchar(50), ID) + '"'

FROM TestDataTable

go

DECLARE @.bcpCommand varchar(2000)

SET @.bcpCommand = 'bcp tempdb..vw_export out

c:\test002.txt -c -T -S SERVER'

EXEC master..xp_cmdshell @.bcpCommand

I get this error when I run this query

Create View or Function failed because no column name was specified for column 1.

If I’m going about this the completely wrong direction let me know so I can get heading the right direction to get this done.

Try the following...

xp_cmdshell 'osql /S<ServerName> /E /Q"set nocount on select top 10 Convert(Varchar(50),name) as name, id from sysobjects" /oc:\test.txt'

|||Hi,

For your view try this and you will be alright:

create view vw_csvexport

as

select

'"' + convert(varchar(50), Name) + '"' as vwNAME,

'"' + convert(varchar(50), ID) + '"' as vwID

from TestHeaderTable

union all

SELECT top 100 percent

Name = '"' + name + '"',

ID = '"' + convert(varchar(50), ID) + '"'

FROM TestDataTable

go


Actually in the view your returning table should always have specific column name, and since you had not specify any, you had the error.


Cheers