justifying text in queries for sql result sets

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

justifying text in queries for sql result sets

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-08-08 : 09:01:59

justin writes "I am completing a data extract and need to format text in the result set. I need to format text to be right justified with leading blanks, also return values with leading zeros. This result set is too large to complete and make the edits in excel, and it also needs to be automated. Therefore the best solution is to include the edits in my query to pull the data. Please advise.

Thanks in advance,

Justin Wilson
Consultant
Urban Science
jjwilson@urbanscience.com"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-08-08 : 09:08:25

If you're using DTS to export the data, then it's better to write an ActiveX task to open the Excel sheet and change the cell formats accordingly. Left-padding with spaces is at best an unreliable way to right-align data in an Excel sheet.

The following snippet can be pasted into an ActiveX task in your DTS package:

Function Main()
Dim sht, excel
Set excel=CreateObject("Excel.Application")
excel.Workbooks.Open("C:\test.xls") 'change the file name to match yours
Set sheet = excel.ActiveWorkbook.ActiveSheet
sheet.Range("D:D").NumberFormat = "000000" formats column D with leading zero format
sheet.Range("F:F").HorizontalAlignment = -4152 'formats column F as right-aligned
excel.ActiveWorkbook.Save
excel.ActiveWorkbook.Close
Set excel=Nothing
Main = DTSTaskExecResult_Success
End Function

Add this task, and then create an On Success taskflow from the Excel step to this one. After the transfer is complete, this code will run and will format the Excel sheet automatically. Simply change the column designations as appropriate.

I tested this using the sales table from the pubs database, if you want to throw together an example to see how it works.

Edited by - robvolk on 08/08/2002 09:09:46

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources