smart headers
-
Hi,
I’m working on the fallowing case:
I have to import data form table to text file in SQL server 2000 with stored procedure. Well.. that must be no problem. But my question is: is it impossible to add automatically smart headers such as (date, time, Row count…enz) to the end of the text file how I have to import to during the execution of the stored procedure.
thx.
Cees
-
Hello,
I suppose that from the viewpoint of SQL Server this is export, not import... and I'm a bit confused - what do you mean by smart headers, especially if they should be at the end of file? Header is mostly at the beginning, on top of page. Could you show us some short example, a few rows of data in the database (or result of some SQL) and how it should look in the text file?
-
look at this script from the contributions, which opens a file and appends to the end of it:
you could BCP to a file, and then use the script to append to teh bottom of it.
http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=1449
you could also use this to write a header, and then use bcp to append to teh end of that.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Vladan,
you are right about the export and headers things. well what i want to have is:
The table i have look like this:
Column1 Column2
1 Hi
2 Hallo
The text file should look like this:
Date: 09/18/2007
Time: 13:34
Total rows: 2
1 Hi
2 Hallo
-
Hi,
It looks like my question is too difficult or not so clear, so I will like repeat my question.
I want to export data from table in SQL server 2000 to text file. During the expert process I need to add some info about the exporting process on the header of the text file, as date, time and total row. It should look like this:
HEADER1:53910072007000000000086456
1;rr;dat;day
2;tt;that;day
I hope my question is a little clearer than before..
Thx,
Cees
-
Yes... easily done... but you need to learn what ">>" means from the CMD prompt (or xp_CmdShell). Lookup "Using command redirection operators" in Windows help (under the Windows [Start] button...)
--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) -
bcp doesn't allow you to append files, but the COPY command certainly is able to do it.
here's a complete example: it uses the function i suggested above.
psuedocode: bcp, make header, combine two files together.
--table to capture xp_cmdshell output
CREATE TABLE #results(resultstext varchar(1000) )
declare @sql varchar(4000),
@rowcount int
--sample query: you would do the same to your existing bcp
--note i erased the password for the -P flag...make sure to fix
set @sql = 'bcp "SELECT TOP 5 * FROM SYSOBJECTS" queryout "c:\body.txt" -c -U"sa" -P""'
--export via bcp
insert into #results
EXEC master..xp_cmdshell @sql
SET @rowcount=@@rowcount --used only as example that that is NOT the rows you wanted to count
print @rowcount -- this is the rows from xp_cmdshells work, NOT the bcp!!!!
--you have to get it from the output of xp_cmdshell
--select * from #results where resultstext like '%rows copied.'
select @rowcount = convert(int,substring(resultstext,1,charindex(' ',resultstext) ) ) from #results where resultstext like '%rows copied.'
--header preamble
SET @sql = 'HEADER1:53910072007000000000086456'
SELECT DBO.Ufn_WriteToFile('c:\header.txt',@sql)
--header date
SET @sql = 'Date: ' + convert(varchar,getdate(),101)
SELECT DBO.Ufn_WriteToFile('c:\header.txt',@sql )
--header time
SET @sql ='Time: ' + convert(varchar,getdate(),108)
SELECT DBO.Ufn_WriteToFile('c:\header.txt',@sql )
--header rowcount
SET @sql ='Total rows: ' + convert(varchar,@rowcount)
SELECT DBO.Ufn_WriteToFile('c:\header.txt',@sql)
--combine the header and query together into a final file.
set @sql ='copy c:\header.txt + c:\body.txt c:\results.txt'
EXEC master..xp_cmdshell @sql
drop table #results
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Hi,
It looks like my question is too difficult or not so clear, so I will like repeat my question.
I want to export data from table in SQL server 2000 to text file. During the expert process I need to add some info about the exporting process on the header of the text file, as date, time and total row. It should look like this:
HEADER1:53910072007000000000086456
1;rr;dat;day
2;tt;that;day
I hope my question is a little clearer than before..
Considering that you just changed the header requirements in a major fashion, I'm not so sure that you're clear on what you need...
If we consider ...
HEADER1:53910072007000000000086456
... to be the actual header that you want, would you at least describe the layout of the header by character postition so that we may give you an example that "is a little clearer than before.."? The date of 10072007 is pretty obvious... but what's all the other stuff?
--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) -
Lowell,
Thank you very much.. It works..
You don't have any idea how long I was busy with this script.. 
thx again..
Cees
-
Lowell does do a great job, but lemme ask you Cees... have you calculated the header or are you going to manually change it in the code?
--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) -
awesome! glad it worked out for you.
It was a neat challenge to try and figure that one out. It's really just grabbing building blocks of code I used before, and putting them in the right order.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Dear Jeff,
I have to calculated, 53910072007 parts is date and time (hmiddmmyyyy) and 000000000086456 parts is total row. With Lowell scripts and after a little changing I can make the following header HEADER1:5391007200786456. Aim searching now to a way to add the 0000000000 part to the header too. 000000000086456 parts moest be always 15 character.
thx.
Cees
-
to do what yoiu are asking, you need to take the RIGHT() 15 chars of 15 '0' + the convert of your value.
for example
SELECT RIGHT('000000000000000' + CONVERT(VARCHAR,GETDATE(),112) ,15)
look at this thread for exactly that example:
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=9&messageid=400056#bm400103
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Ok, Cees... I was just making sure that you had everything that you needed. And Lowell's snippet above will take care of the zeros...
--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) -
Lowell,
I thing my question was not so clear, as i say before the 000000000086456 parts is always 15 character, that mean when i get a higher row number for example 765486456 then I have automatically to fill the rest of the character zeros so it must look like this 000000765486456.
anyway thx for you above tip..
thx..again..
Cees
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply