Creating text files for each row of the ouput
-
Hi Chaps
Do we have a way to send query output to text files? I mean now I can send the whole output to one text file. But what I want is create a text file for each and every row of the output??
SET @cmd = 'OSQL -S support_svr1 -d NCLREMOTE_SP6D '
+ ' -U sa -P password'
+ ' -Q "select notes from person where len(notes) > 1000"'
+ ' -o c:\Notes.txt'
EXEC master..xp_cmdshell @cmd, NO_OUTPUT
Now I want text files to be generated for every row in the output. This is to store the notes field in a text file as some of the notes are exceeding 1000 chars.
One text file per person. Any ideas?
Thanks
-
Yes... you can insert the output of a SELECT into a temp table with an IDENTITY column to keep track of which rownumber you are working on. Then, loop through the table executing OSQL once for each row using a calculated file name in conjunction with the -o parameter.
--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) -
Hi Jeff
Any chance of the code?
thanks
-
Sure... what's the Primary Key of the person 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) -
Another alternative would be - BCP out the entire data and then split the file into multiple files. You may consider using DTS tasks for this.
-
Sorry Jeff
i'm late in replying... i appreciate your help.
the primary key for the person table is id_number.
and i have the following code:
this code is not working - creating only one file (as i dont have a calculated file name field)
--DBCC DROPCLEANBUFFERS
--DBCC FREEPROCCACHE
--set nocount on
declare @err int
--Declare a cursor to loop through the rows
declare c1 scroll cursor for
select id_number, surname, forenames, notes from person where len(notes) > 1000 order by id_number
--print 'test'
--Error Handling
SELECT @err = coalesce(nullif(@err, 0), @@error)
if @@error <> 0 print 'The error is' + ltrim(str(@err))
--Declare cursor variables
declare @id_number varchar(20)
declare @surname varchar(100)
declare @forenames varchar(100)
declare @notes varchar(5000)
--print 'test1'
--open the cursor
open c1
--Error Handling
SELECT @err = coalesce(nullif(@err, 0), @@error)
if @@error <> 0 print 'The error is' + ltrim(str(@err))
--Fetch rows into the cursor variables
fetch first from c1 into @id_number,@surname,@forenames,@notes
print 'The first person is' + ' ' + ' : '+ ' ' + @surname + ' ' + @forenames + ' '+ ',' + 'ID Number' + ' ' + ' : ' + @id_number + ' ' + char(13)+ char(13) + char(10) + @notes
--Error Handling
SELECT @err = coalesce(nullif(@err, 0), @@error)
if @@error <> 0 print 'The error is' + ltrim(str(@err))
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
while (@@fetch_status = 0)
begin
declare @cmd varchar(1000)
select @cmd = 'bcp "select id_number,surname,forenames,notes from NCLREMOTE_SP6D..person where id_number = "' + @id_number + '"and len(notes) > 1000 order by id_number" queryout c:\Person_notes.txt -c -S support_svr1 -U sa -P seaward'
EXEC master..xp_cmdshell @cmd
fetch next from c1 into @id_number,@surname,@forenames,@notes
select char(13) + char(13) + char(10)
print 'The next person is' + ' ' + ' : '+ ' ' + @surname + ' ' + @forenames + ' '+ ',' + 'ID Number' + ' ' + ' : ' + @id_number + ' ' + char(13)+ char(13) + char(10) + @notes
end
close c1
deallocate c1
thanks a lot
Vijay
-
Guys
any ideas on the above? i'm desperate now!
thanks
-
Of course it's only creating one file... that's because you hardcoded the output file name as c:\Person_notes.txt.
You need to make the file name dynamic, as well. Perhaps using the person's ID as part of the file name... for example...
select @cmd = 'bcp "select id_number,surname,forenames,notes from NCLREMOTE_SP6D..person where id_number = "' + @id_number + '"and len(notes) > 1000 order by id_number" queryout c:\Person_notes' + CAST(ID_Number AS VARCHAR(10)) + '.txt -c -S support_svr1 -U sa -P seaward'
By the way... you've exposed your SA password... I suggest you change it right away AND you that you remove the -U and -P parameter. Use the -T (trusted connection) parameter, instead. That does two things for you... first, it keeps you from exposing passwords in clear code and, second, it keeps you from having to change code if the password changes.
My other recommendation is that you limit your line lengths to no more than 120 characters (10pt landscape with .5" margins will still allow the code to print without wrapping, for troubleshooting/documentation purposes). Break long formula lines before the "+" and other operators and line things up for readability. It will also keep you from having to scroll right to view 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) -
Hi Jeff
Thanks for your response. But it is not creating even one file now let alone all the files.
Looks like the cursor is not looping through the bcp line of the code. Could you please look at the 'while' block again? BTW, the sa password is not the one i've hardcoded above. it is just an example.
Thanks a lot again
Vijay
-
Have you tried to print the command? Do that and try running that command from command shell and see what error you get. It could be realted to some synrax error in the SQL Command.
Thanks
Sreejith
-
Sorry... was in a hurry and forgot that you were using a
cursor 
and I used a column name instead of a variable... try this instead...select @cmd = 'bcp "select id_number,surname,forenames,notes from NCLREMOTE_SP6D..person where id_number = "' + @id_number + '"and len(notes) > 1000 order by id_number" queryout c:\Person_notes' + @ID_Number + '.txt -c -S support_svr1 -U sa -P seaward'
--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) -
Hi Jeff
I used variable though u initially suggested column name as i know i'm using a cursor
(i know it is a bit naff!)i should have posted my code again. here it is:
Sorry to be a PAIN but the thing is it is still NOT working...
Any other bright ideas are appreciated...
--DBCC DROPCLEANBUFFERS
--DBCC FREEPROCCACHE
set nocount on
--Declare a cursor to loop through the rows
declare c1 scroll cursor for
select id_number, surname, forenames, notes from person where len(notes) > 1000 order by id_number
--Error Handling
if @@error <> 0 goto errhandler
declare @err int
--Declare cursor variables
declare @id_number varchar(30)
declare @surname varchar(100)
declare @forenames varchar(100)
declare @notes varchar(5000)
--open the cursor
open c1
--Error Handling
if @@error <> 0 goto errhandler
--Fetch rows into the cursor variables
fetch first from c1 into @id_number,@surname,@forenames,@notes
print '-------------------------------------------------------------------------------------------------------------------'
print 'The first person is' + ' ' + ' : '+ ' ' + @surname + ' ' + @forenames + ' '+ ',' + 'ID Number' + ' ' + ' : ' + @id_number + ' ' + char(13)+ char(13) + char(10) +
'-------------------------------------------------------------------------------------------------------------------' + char(13) + char(13) + @notes
--Error Handling
if @@error <> 0 goto errhandler
while (@@fetch_status = 0)
begin
declare @cmd varchar(2000)
select @cmd = 'bcp "select id_number,surname,forenames,notes from NCLREMOTE_SP6D..person where id_number = "' + @id_number +'" and len(notes) > 1000" queryout c:\Person_notes'+ @id_number +'.txt -c -S support_svr1 -T'
EXEC master..xp_cmdshell @cmd
fetch next from c1 into @id_number,@surname,@forenames,@notes
select char(13) + char(13) + char(10)
print '-------------------------------------------------------------------------------------------------------------------'
print 'The next person is' + ' ' + ' : '+ ' ' + @surname + ' ' + @forenames + ' '+ ',' + 'ID Number' + ' ' + ' : ' + @id_number + ' ' + char(13)+ char(13) + char(10) +
'-------------------------------------------------------------------------------------------------------------------' + char(13) + char(13) + @notes
end
--Close the cursor
close c1
deallocate c1
errhandler:
SELECT @err = coalesce(nullif(@err, 0), @@error)
print 'The error is' + ltrim(str(@err))
Thanks a lot again for your time
Vijay
-
Hi Sreejit
thanks for your response. i tried to print the cmd and i got a syntax error saying ' incorrect syntax near .001' as the id numbers are of the type "002061.2003801210040763.001".
-
Can you post some sample data, hopefully in the form of INSERT statements, along with the CREATE statement for the table you're trying to get the data from, please?
--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) -
Hi Jeff
Sample data:
id_number surname forenames
002054.2003801645150037.001 Nicorescu Alina
002054.2003801732590187.001 Bularca Ionela Bianca
002054.2003801736180077.001 Bruno Antonio
002054.2003805417190443.001 BIESZKE-WIERZBA JOANNA
002054.2003805716460903.001 Suarsana I Kadek
Create table statement for person table
CREATE TABLE [PERSON] (
[ID_NUMBER] [nchar] (27) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PRFSCID_CODE] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PLEAVE_RTYPE] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LEAVE_REAS] [int] NULL ,
[TITLE_RANK] [nvarchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SURNAME] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[FORENAMES] [nvarchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[INITIALS] [nvarchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DATE_LEAVING] [datetime] NOT NULL CONSTRAINT [DF__PERSON__DATE_LEA__0D99FE17] DEFAULT ('31 Dec 9999'),
[PART_TIME_ACCEPTABLE] [bit] NOT NULL CONSTRAINT [DF__PERSON__PART_TIM__0E8E2250] DEFAULT (0),
[PART_TIME] [bit] NOT NULL CONSTRAINT [DF__PERSON__PART_TIM__0F824689] DEFAULT (0),
[PART_TIME_HOURS] [float] NULL ,
[DATE_JOINED] [datetime] NULL ,
[EXTRA_CODE_1] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EXTRA_CODE_2] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PERSON_TYPE] [nchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[GRADE_SHT_TITLE] [nvarchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TKT_GRD_SHT_TITLE] [nvarchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EMPLOYEE_TYPE] [int] NULL ,
[SEX] [nvarchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DATE_BIRTH] [datetime] NULL ,
[MARITAL_STATUS] [nvarchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SURPLUS_SDATE] [datetime] NULL ,
[SUPERN_SDATE] [datetime] NULL ,
[DATE_OTT_DUS] [datetime] NULL ,
[PROMOTION_RYEAR] [nvarchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ERET_DATE] [datetime] NULL ,
[RET_DATE] [datetime] NULL ,
[RET_DEC] [nvarchar] (13) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ASR_DESPATCH] [datetime] NULL ,
[ASR_ISSUE] [datetime] NULL ,
[ASR_ACTUAL_RETURN] [datetime] NULL ,
[DATE_AV_POST] [datetime] NULL ,
[DISABLED_NUMBER] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SENIORITY_DATE] [datetime] NULL ,
[COMP_NAME] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DATE_CONTRACT] [datetime] NULL ,
[STUDENT_TYPE_ID] [nvarchar] (27) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DIETARY_REQUIREMENTS] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SPECIAL_NEEDS] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[COUNTRY_OF_ORGIN] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TELEPHONE_NUMBER] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[STUDENTS_COMPANY] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NOTES] [nvarchar] (2048) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PREVIOUS_EMPLOYEE] [bit] NOT NULL CONSTRAINT [DF__PERSON__PREVIOUS__162F4418] DEFAULT (0),
[APPLICANT_ID] [nchar] (27) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BLOOD_TYPE] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[E_MAIL_NAME] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SOB_TYPE_ID] [nchar] (27) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LIFETIME_SOB] [bit] NOT NULL CONSTRAINT [DF__PERSON__LIFETIME__17236851] DEFAULT (0),
[NATIONALITY_ID] [nchar] (27) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PERSON_CHECK_BOX_1] [bit] NOT NULL CONSTRAINT [DF__PERSON__PERSON_C__18178C8A] DEFAULT (0),
[PERSON_CHECK_BOX_2] [bit] NOT NULL CONSTRAINT [DF__PERSON__PERSON_C__190BB0C3] DEFAULT (0),
[PERSON_CHECK_BOX_3] [bit] NOT NULL CONSTRAINT [DF__PERSON__PERSON_C__19FFD4FC] DEFAULT (0),
[COMPANION_ID_NUMBER] [nchar] (27) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IS_SMOKER] [bit] NOT NULL CONSTRAINT [DF__PERSON__IS_SMOKE__1AF3F935] DEFAULT (0),
[TRAVEL_PROVIDER_ID] [nvarchar] (27) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HAIR_REASON_ID] [nchar] (27) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EYE_REASON_ID] [nchar] (27) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PERSON_WEIGHT] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PERSON_HEIGHT] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SHOE_SIZE] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UNIFORM_SIZE] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PLACE_OF_BIRTH] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ITEMTAG_ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[ITEMDB_ID] [numeric](4, 0) NULL CONSTRAINT [DF__PERSON__ITEMDB_I__1BE81D6E] DEFAULT (0),
[DATE_CREATED] [datetime] NULL ,
[DATE_LAST_MOD] [datetime] NULL ,
[LAST_EDIT_BY] [int] NULL CONSTRAINT [DF__PERSON__LAST_EDI__0076A3D1] DEFAULT (user_id()),
[ACTIVITY_ID] [numeric](18, 0) NULL ,
[ACTIVITY_DB] [numeric](4, 0) NULL ,
[SPARE_CHAR_1_255] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SPARE_CHAR_1_100] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SPARE_CHAR_1_50] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SPARE_INT_1] [int] NULL ,
[SPARE_INT_2] [int] NULL ,
[B1M] [int] NULL ,
[B2M] [int] NULL ,
[B3M] [int] NULL ,
[B4M] [int] NULL ,
[B5M] [int] NULL ,
[B6M] [int] NULL ,
[B7M] [int] NULL ,
[B8M] [int] NULL ,
CONSTRAINT [pk_PERSON] PRIMARY KEY CLUSTERED
(
[ID_NUMBER]
 
ON [PRIMARY] ,CONSTRAINT [ck_PERSON_RET_DEC] CHECK ([RET_DEC] = 'Re-Employment' or ([RET_DEC] = 'Extension' or ([RET_DEC] = 'Final' or [RET_DEC] = 'N/A'))),
CONSTRAINT [ck_PERSON_SEX] CHECK ([SEX] = 'Female' or [SEX] = 'Male')
) ON [PRIMARY]
GO
Thanks
Vijay
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply