OUTPUT OF A QUERY/STORED PROCEDURE INTO A EXCEL FILE

  • 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?

     

  • 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.

  • 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.

  • 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.

    http://www.sqldts.com/

    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

  • 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

     

  • It will insert all the records that you get for a given query.

  • 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.

  • 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?

  • 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).

  • 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??

  • 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.

  • 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.

  • 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.

     

  • 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