Out as Excel File

Question

Post reply

Out as Excel File

saravan

SSC Enthusiast

Points: 181
More actions
June 14, 2005 at 9:12 pm

#67106

Hi,
I want sql out as Excel file. Is there any script or better way to do it.
I cannot use DTS Package, becos i want 'N' No. of execl file based on a condition. Is there a way to Call DTS Package within a 'Do While Loop'.
For Example. In a Table Temp i have Country, Customer Name, Amount. Here the Country is the Primary Key. I want out of the Table as Excel file for the 'N' of countries available in the Table.
Pls Advise. Thx
Regards
Sara

Viewing 10 posts - 1 through 9 (of 9 total)

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Does it have to be in excel??

Cause this seem to be a really great task for any reporting tool out there.

saravan SSC Enthusiast Points: 181 More actions · Answer 2

yes pls, i need the output as excel or csv file.

The main purpose behind is, these country wise (xls/csv) files will be delivered to respective country owners by a Notes Agent.

Thx

Paul Cresham Hall of Fame Points: 3623 More actions · Answer 3

Could you not do this within a DTS package using a Dynamic Properties Task and a bit of ActiveX?

Loop through the table, call a transformation for each country found (after changing the Dynamic Properties Task to change the filename), then at the end of the recordset finish the package?

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 4

Or use xp_cmdshell if have to use it in a stored procedure or query analyzer. Call dts in a loop for each country.

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=190039#bm190692

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=190039

Regards,
gova

Paul Cresham Hall of Fame Points: 3623 More actions · Answer 5

Trouble is, you'd still need varying filenames, so it's either still a Dynamic Properties Task or a separate package for each country, as far as I can see...

saravan SSC Enthusiast Points: 181 More actions · Answer 6

Thanks a lot, Will try the above.

In the mean time i hav used BCP, calling thru sp, where i have fetch the country in a cursor & execute one by one. the only Issue is, excel output doesn't have column name. DTS will solve the column name issue.

ND-215452 SSCrazy Points: 2037 More actions · Answer 7

have you considered using osql or isql commands??

Basically write your query (what/how you want it to output) to a file.

something like this (not sure syntax is correct!):

isql -Sserver_name -Ddbname -Uuser -iyourquery.sql -oyourresult.csv -Edelimiter

David A. Long SSCrazy Eights Points: 8421 More actions · Answer 8

Check out script: http://www.sqlservercentral.com/scripts/contributions/763.asp

Change the SQL to what ever you want.

The tricks are: to EXEC dynamic SQL, because at parse time the Excel "table" does not exist; CREATE tableName becomes the Tab name and the Column names are the Excel Header labels.

Andy

saravan SSC Enthusiast Points: 181 More actions · Answer 9

Thank u all for u'r tricks & tips.

I hav solved the purpose by calling DTS package in SP, where country wise data is supplied thru cursor.

Within the DTS Package, i am dynamically establishing the excel file (as Country_yyyymmdd.xls)

Thanks again to all.

Regards

Saravan