November 2, 2005 at 4:02 am
Hi,
THIS IS THE DATA IN MY TABLE
ENO ENAME
1 NAME1
2 NAME2
3 NAME3
4 NAME4
I WANT TO WRITE A STORED PROCEDURE
THIS WILL BE IN THE LOOP
set @I=1
WHILE (I<5)
SELECT * FROM EMP WHERE ENO=@I
set @I=@I+1
LOOP
THE ABOVE STATEMEMNT WILL EXECUTE 4 TIMES ,EACH TIME IT WILL GIVE ONE RECORD,
EACH RECORD I WANT TO STORE IN AN EXCEL FILE.
IN THE ABVOE I WANT TO STORE 4 RECORDS IN 4 EXCELFILES WITH DIFFERENT NAME.
Is it possible in sql server?
November 2, 2005 at 6:16 am
Don't know why you would want to do that but Yes, it is possible.
Couple of ways:
1) Use DTS and make use of global variables and come up with a different naming scheme for each excel spreadsheet. The code will have to loop and execute the statement once per record.
2) Use bcp to extract the data out into different csv files. So, form a dynamic SQL which changes the name of the output file per execution and the bcp command execution string could then be made part of the while loop itself.
November 2, 2005 at 6:26 am
Hi,
Thanks for replies...
I given a small example in my first question but i want to insert a lot of records .
1)From DTS by using global variables we can insert only one file but i want to insert more records ,more files has to be created .can u let me know how to do?
2)I dont have much idea about bcp .I will look into bcp.If u have idea let me know how to do in bcp.
In the excel file i will do some modifications,after generating the excel file.
If u have any idea how to do with DTS please let me know.
November 2, 2005 at 6:59 am
1) Using DTS, you will modify the value of the global variable per execution.
So the pseudo logic would look like this:
a) Get the total count for the number of iterations to be performed
b) Set the global variable for the file name
c) Set the global variable for the ENO
d) Execute SQL task --> transform to the excel spreadsheet.
e) Check for the counter
f) Loop back to b and change the global variable value for the filename and the value for the ENO.
g) Repeat steps till you exhaust the list.
has an example of how to do the looping around logic.
2) The bcp solution is much simpler but you would use xp_cmdshell :
create table emp (eno int identity(1,1), ename varchar(10))
insert into emp (ename) values ('X')
insert into emp (ename) values ('Y')
insert into emp (ename) values ('Z')
go
DECLARE @I varchar(10), @command varchar(4000)
set @I=1
WHILE (@I<5)
begin
select @command = 'master..xp_cmdshell ''bcp "SELECT * from pubs.dbo.emp where eno = '+@I+'" queryout c:\EMP_'+@I+'.csv -n -S(local) -E'''
exec (@command)
set @I=@I+1
end
Hth
November 2, 2005 at 7:05 am
Thank u for ur replies,
I will let u know after executing the things,but i have one doubt
The following stament will return more than 1 records:
select @command = 'master..xp_cmdshell ''bcp "SELECT * from pubs.dbo.emp where eno = '+@I+'" queryout c:\EMP_'+@I+'.csv -n -S(local) -E'''
Will it insert all the records to excel file or not??
I want data in excel file like this
A B C D
1 eno ename
2 1 aswani
3 2 angelIII
November 2, 2005 at 7:24 am
It will insert all the records that you get for a given query.
November 2, 2005 at 7:24 am
You can also run the queries from inside Excel, I'm using 97.0. You have to play around a little but once you get it going it's easy. To get started look at the help topic "importing Date"\"external data".
Or click on "data" at the tool bar then "get external data". And use the Wizard. Save a simple query as a shell and then just paste your new ones in it as you go. Once you learn how to do it just run your queries from there straight into Excel.
November 2, 2005 at 8:06 am
Hi rsharma,
Thanks for ur quick replies.
select @command = 'master..xp_cmdshell ''bcp "SELECT * from pubs.dbo.emp where eno = '+@I+'" queryout c:\EMP_'+@I+'.csv -n -S(local) -E'''
The above statement is not inserting the data in correct format.
I want data in excel file like this
A B C D
1 eno ename
2 1 aswani
3 2 angelIII
Is it possible?
November 2, 2005 at 2:12 pm
Sorry - instead of "-n", use "-c"...
drop table emp
create table emp (eno int identity(1,1), ename varchar(10))
insert into emp (ename) values ('X')
insert into emp (ename) values ('Y')
insert into emp (ename) values ('Z')
go
DECLARE @I varchar(10), @command varchar(4000)
set @I=1
WHILE (@I<5)
begin
select @command = 'master..xp_cmdshell ''bcp "SELECT ''''COL1'''', ''''COL2'''' union all select cast(eno as varchar(10)), ename from pubs.dbo.emp where eno = '+@I+'" queryout c:\EMP_'+@I+'.xls -c -S(local) -E'''
exec (@command)
set @I=@I+1
end
This produces output like:
COL1 COL2
1 X
in the first xls file. You can play around and do further formatting as per your needs. If you need to do more, look into the "-f" option (the format-file option in bcp - all this is available in SQL Server Books Online).
November 2, 2005 at 10:56 pm
Thanks a lot sharma,
Its working fine ,but again small requirement after exporting to excel formatting became problem.
I will explain
in col2 ename characters are 20 but it is displaying only 6 characters,when i open a excel file it has to display in correct format.How to do??
November 3, 2005 at 3:11 am
one more doubt
select @command = 'master..xp_cmdshell ''bcp "SELECT ''''COL1'''', ''''COL2'''' union all select cast(eno as varchar(10)), ename from pubs.dbo.emp where eno = '+@I+'" queryout c:\EMP_'+@I+'.xls -c -S(local) -E'''
exec (@command
when i execute the above statment:
An excel file is creating but for me a excel file will already there i want to insert the data into the existing excel file is is possible or not?
Reason why i want is i have some macro in a template excel file.
November 3, 2005 at 1:00 pm
Regarding the first question, the column is a variable length column so you will get only what you have in the field - if you want, you can cast that column..example:
--left justify
select cast('xx' as char(20))
--right justify
select cast(replicate(' ', 20-len('xx')) + 'xx' as char(20))
or you can also do this using format files (look up BOL for format files).
For the other question, if you want to preserve history - one way to do this is that you can keep the data from the previous run into a table and then run the bcp off of that table -- that way, whenever the new excel file gets generated, it has all the data that you need rather than over-writing it with new data each time.
November 4, 2005 at 3:33 am
Hi sharma,
I will say the exact requirement.
In a particular excel a macro is there ,it will run whenver excel opens.
In that excel file data(columns and rows are blank).
I want to copy the data by using bcp utility into the excel which consists macro.
select @command = 'master..xp_cmdshell ''bcp "SELECT ''''COL1'''', ''''COL2'''' union all select cast(eno as varchar(10)), ename from pubs.dbo.emp where eno = '+@I+'" queryout c:\EMP_'+@I+'.xls -c -S(local) -E'''
exec (@command)
The above statement is creating new excel file,but i want to insert the data into excel file which is already there in this "c:\" folder.
Thanks for ur patience and for giving replies to me.
November 4, 2005 at 6:29 am
Then you have two choices (can't use bcp then):
1) Have a stored procedure or a script that runs and populates the tables (could be different tables or the same table with a column that is used to differentiate the data i.e. XLS1 stands for the data that needs to go into excel spreadsheet1, XLS2 stands for the data that needs to go into excel spreadsheet 2 and so on).
Then, in your macro in the excel, you can establish the connection to the SQL Server database and have that as a simple query - wherein the spreadsheet1 will query for the XLS1 data, spreadsheet2 will query for XLS2 data and so on and so forth. There won't be a need to use bcp then - just plain simple SQL scripts that will log the data into a table and the excel spreadsheet will query it and fill the spreadsheet when it is opened up.
2) Use DTS using the method that I mentioned in the beginning. That way, it will append to the spreadsheet each time that it is run.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply