Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Mar 2002
    Location
    Buenos Aires, Argentina
    Posts
    877
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Exporting SQL Server 2000 to Excel 2000

    Hi all,

    We have a recurring need to export data from SQL Server tables to Excel.
    We made a little script that does everything alright, except that it exports to the TXT format instead of exporting to Excel itself.
    The relevant part of the code is:

    EXEC master..xp_cmdshell 'bcp "select * from database..tablename" queryout '+ @FileName +' -c -Uusername -Ppassword'

    I think bcp only exports to TXT/CSV, but I wish I could export to Excel. The Excel files are to be created while the export process takes place, i.e., we don't want to create the Excel files in advanced so SQL Server can export to them. I'd also like to know whether one can export to different several worksheets within the same Excel file, but we can discuss that later.

    I think this can be done, since when exporting manually via DTSs SQL Server DOES export to Excel, even to several worksheets.

    Any ideas?
    <img src=/w3timages/blue3line.gif width=33% height=2>
    <img src=/S/flags/Argentina.gif border=0 alt=Argentina width=30 height=18> <big><font color=4682b4><font face="Comic Sans MS">Diegol</font face=comic></font color=4682b4> </big>

  2. #2
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Exporting SQL Server 2000 to Excel 2000

    You should be able to use a DTS package that's run by a scheduled job. The DTS package can output to Excel with no problems. I'm not sure about multiple pages in the same Excel file - I don't think it does that (at least not without some outside coding).

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Exporting SQL Server 2000 to Excel 2000

    Hi Diegol

    Mark is correct it can be done by DTS package. I have a person on my team who has to do it manually (long story why!)

    You will be restricted to 65536 records with an extract to Excel though. If this is not an issues you can use MS Enterprise manager to develop the DTS package for you using the wizards.

    Be aware, be very aware about extracts using the datetime stamp with null fields length, I still have the scars where my fingers were burnt several times <img src=/S/hiding.gif border=0 alt=hiding width=70 height=24>
    Jerry

  4. #4
    5 Star Lounger
    Join Date
    Mar 2002
    Location
    Buenos Aires, Argentina
    Posts
    877
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Exporting SQL Server 2000 to Excel 2000

    Hi Mark,

    We thought of exporting using an SQL + T-SQL script in the query analyzer because we want it to automatically:
    a) check in TABLE_A which tables it should export
    [img]/forums/images/smilies/cool.gif[/img] for each of those tables
    b1) check whether they exceed 65000 records (Excel supports up to 65536 per worksheet, but we like 65000 best <img src=/S/smile.gif border=0 alt=smile width=15 height=15>)
    b2) export each table in a single Excel file, with as many sheets as ceiling(cast(@recordcount as float)/65000).

    We got everything right, except the export to Excel thing. It's true, we can open TXTs/CSVs with Excel, but the catch is that the resulting file doesn't retain the formatting quite as much as a direct DTS export to Excel would.

    To tell the truth I haven't experienced much with DTS packages, but it sounds like this can't be done. It seems the FOR loop can't be skipped to achieve this.

    I'm quite new at SQL Server, but being such a powerful piece of software it's striking that you cannot instruct it with code to do something you can achieve manually. On the other hand I cannot find anything but bcp commands on the Net, so you're probably right.
    <img src=/w3timages/blue3line.gif width=33% height=2>
    <img src=/S/flags/Argentina.gif border=0 alt=Argentina width=30 height=18> <big><font color=4682b4><font face="Comic Sans MS">Diegol</font face=comic></font color=4682b4> </big>

  5. #5
    5 Star Lounger
    Join Date
    Mar 2002
    Location
    Buenos Aires, Argentina
    Posts
    877
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Exporting SQL Server 2000 to Excel 2000

    Hi Jerry,

    >You will be restricted to 65536 records with an extract to Excel though.

    Please read my repy to MarkJ above. Do you mean a DTS package export won't let me export the table if it exceeds 65536 records? If so, the DTS package solution won't do.

    I attach an image of a manual DTS export. Each of the resultados77_1, resultados77_2, resultados77_3 tables are in fact part of a single resultados77 table with about 190000 records. So we split resultados77 in 3 tables within SQL Server (select * into resultados77_1 from resultados77 where idfield <= 65000; select * into resultados77_2 from resultados77 where idfield > 65000 and idfield <= 130000; and so on) so we could export them to 3 separate Excel tables, within the same file. In fact, that's what SQL Server was doing when I captured the image.

    That's why it would be so good to be able to make the script work. We wouldn't have to go splitting each table exceeding 65536 records, then exporting to single files. It would be a real time saver.
    Attached Images Attached Images
    <img src=/w3timages/blue3line.gif width=33% height=2>
    <img src=/S/flags/Argentina.gif border=0 alt=Argentina width=30 height=18> <big><font color=4682b4><font face="Comic Sans MS">Diegol</font face=comic></font color=4682b4> </big>

  6. #6
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Exporting SQL Server 2000 to Excel 2000

    Hi Diegol,

    I have a few more thoughts to share on this since yesterday. I'm a little concerned with the limitations of this solution. The concept of splitting a table into multiple sheets on a spreadsheet defeats the idea of a database. Once the rows are split over multiple sheets, they are no longer considered a table and can no longer be used for sorting, filtering, or anything else involving summaries or aggregates.

    Is there any way you could export the data into a format such as Access? This way, you do not have any realistic limitations on the number of rows you can export and this will still allow you to keep the data together in one place.

    If Access is not acceptable, I would recommend using CSV. I realize you will can only see 65,536 rows in Excel, but there are other ways to view CSV in a grid besides Excel. For instance, you could use a very simple custom .NET Windows Application to dispaly this data in a grid without the row limitation.

    Finally, if none of these suggestions are acceptable, perhaps you can consider exporting a summary into Excel rather than the raw data. This way, you can still get the snapshot you need without having the large volume of rows.

    Just some thoughts...

  7. #7
    5 Star Lounger
    Join Date
    Mar 2002
    Location
    Buenos Aires, Argentina
    Posts
    877
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Exporting SQL Server 2000 to Excel 2000

    Hi Mark,

    Thanks for your thoughts.

    You're absolutely right about splitting the table. It's good you pointed that out.

    We receive raw data from another department, import data from Excel / CSV into SQL Server, run a process, export the outcome to Excel, put it on a CD and hand it back over to that department. That's is the way this has been handled before I came to the company and I think it's because of simplicity (people in the mentioned department don't have much instruction in computing, so it would seem they'd feel more comfortable using Excel than Access.) But it's just a matter of asking them whether an Access export alternative would be suitable. And maybe giving them some basic instruction about filtering in Access, which, as you say, is MUCH better than filtering a table split in 3 or more spreadsheets in Excel.

    I think CSV is not suitable because we need something friendlier for them to handle. If I had to deal with big CSVs, well, I don't know about programming in .NET, but I would manage with one of the viewers suggested in this thread. But as things are, I'll just use the SQL Server tables which I have access to.

    Next time I get a requirement to process raw data I'll tell them about the alternative. I don't know what they do with the processed data (if they keep it for themselves or if they in turn give it to other areas) and that's a key point to decide for or against the Access export.

    In case we decide to export to Access it's one less drawback to export via a DTS Package.
    <img src=/w3timages/blue3line.gif width=33% height=2>
    <img src=/S/flags/Argentina.gif border=0 alt=Argentina width=30 height=18> <big><font color=4682b4><font face="Comic Sans MS">Diegol</font face=comic></font color=4682b4> </big>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •