Post reply

Import all the db tables to Excel sheet

  • March 15, 2006 at 11:33 pm

    #365566

    I have 1000  tables in my db and I need to import these tables to Excel Sheet

    What 's the procedure for that .Is there any built in SP.

    I know that it's possible by using DTS Wizard of SQLServer2000

    I donno how to use that ! Can any one help me in this regard?

    Thanks in advance

     

    Kindest Regards,

    Himabindu Vejella
    spaces.msn.com

  • March 16, 2006 at 8:12 am

    #627032

    HI,

     

    In enterprise manager expand till tables and right click and select export data.

    In DTS wezard u can select the source and destination and number of tables u want to transfer.

     

    HTH

    from

    Killer

  • March 17, 2006 at 12:56 am

    #627204

    Ya I know the Procedure

    But I want only the tablenames in an excel format not with data

    Like tablename in one column and its description in other

    Is there any SP for that

    Kindest Regards,

    Himabindu Vejella
    spaces.msn.com

  • March 17, 2006 at 6:09 am

    #627270

    you still need to use DTS, but instead of selecting a table, you can choose the second option "Use a query to specify the data to transfer"

    in my example, i chose my SQL server as the source, and after i identified that i wanted a 97-2000 excel document,selected the second option, then  i pasted this query:

    select sysobjects.name as TableName,

    syscolumns.name as ColumnName,

    syscolumns.colid as ColumnNumber

    from sysobjects

    inner join syscolumns on sysobjects.id=syscolumns.id

    where sysobjects.xtype='U'

    order by sysobjects.name,syscolumns.colid

    that gave me results like this in the excel document:

    TableNameColumnNameColumnNumber
    capsJOBNUMBER1
    capsCENTURY2
    capsTYEAR3
    capsTMONTH4

    the functionality exists to export data to excel, but there is no prebuilt sp for this, because it is very user specific...especially the export to excel part....the typical target of the results of a sql statement is a recordset/datatable, or another table within the server. You can make an SP to do this yourself of course.

    use a SQL approriate for the data you want and use DTS.

    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!

  • March 17, 2006 at 7:58 am

    #627320

    Try using the sp_tables procedure in a stored procedure and put the results into a temp table.  You could then use bcp to move the data to a CSV file and open it in Excel.

    ----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011

  • March 17, 2006 at 8:45 am

    #627359

    Sorry Himabindu,

    this is off the subject but, Hawg's binary caught my attention...I'm sure many have done the same thing...pretty slick

    string   value           

    -------- ----------- ----

    01010011 83          S

    01010001 81          Q

    01001100 76          L

    00100000 32          

    01010011 83          S

    01100101 101         e

    01110010 114         r

    01110110 118         v

    01100101 101         e

    01110010 114         r

    00100000 32          

    01010010 82          R

    01101111 111         o

    01100011 99          c

    01101011 107         k

    01110011 115         s

  • March 20, 2006 at 2:29 am

    #627525

    Hi Scoot,

    I didn't get what u meant

    Kindest Regards,

    Himabindu Vejella
    spaces.msn.com

  • March 27, 2006 at 1:18 am

    #628824

    Good Job scoot....

     

  • March 27, 2006 at 7:16 pm

    #629060 

    01010111
01101000
01101111
00100111
01110011

01010011
01100011
01101111
01101111
01110100
00111111







    **ASCII stupid question, get a stupid ANSI !!!**

  • March 28, 2006 at 7:11 am

    #629144

    01010111    W

    01101000    h

    01101111    o

    00100111    '

    01110011    s

    01010011    S

    01100011    c

    01101111    o

    01101111    o

    01110100    t

    00111111    ?

     

    ----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011

  • March 28, 2006 at 7:32 am

    #629158

    01001001

    01110011

    01110100

    01101000

    01100101

    01110010

    01100101

    01100001

    01101110

    01100101

    01100011

    01101000

    01101111

    01101000

    01100101

    01110010

    01100101

    00111111

    ..this can get to be pretty addictive..







    **ASCII stupid question, get a stupid ANSI !!!**

  • March 28, 2006 at 8:09 am

    #629179

    01001001    I

    01110011    s

    01110100    t

    01101000    h

    01100101    e

    01110010    r

    01100101    e

    01100001    a

    01101110    n

    01100101    e

    01100011    c

    01101000    h

    01101111    o

    01101000    h

    01100101    e

    01110010    r

    01100101    e

    00111111    ?

    ----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011

  • March 28, 2006 at 8:43 am

    #629196

    For the last time...

    01000101-01101110-01100100

    01101111-01100110

    01100010-01110010-01100101-01100001-01101011-00100001

    01000111-01101111-01110100

    01110100-01101111

    01110111-01101111-01110010-01101011-0011101000101000







    **ASCII stupid question, get a stupid ANSI !!!**

  • March 28, 2006 at 8:58 am

    #629208

    01000101-01101110-01100100

    01101111-01100110

    01100010-01110010-01100101-01100001-01101011-00100001

    01000111-01101111-01110100

    01110100-01101111

    01110111-01101111-01110010-01101011-0011101000101000

    E-n-d

    o-f

    b-r-e-a-k-!

    G-e-t

    t-o

    w-o-r-k-

     

     

    ----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011

  • March 29, 2006 at 10:56 am

    #629592

    Folk, DTS is the best bet. Just take time to go through the below links. You will be able to do it easily. Just create a package and you are done.

    http://www.microsoft.com/india/msdn/articles/105.aspx

    http://vyaskn.tripod.com/sql_server_dts_best_practices.htm#books

    Get busy living ....or get busy dying....

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply