Exporting SQL Server 2000 to Excel 2000

Topic

New Reply

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?

Reply | Quote

Replies

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

Reply | Quote

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
for each of those tables
b1) check whether they exceed 65000 records (Excel supports up to 65536 per worksheet, but we like 65000 best )
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.

Reply | Quote

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…

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote