Solved

SQL statement - exporting to Excel

Posted on 2014-04-04
4
370 Views
Last Modified: 2014-04-08
I ran a SQL query and copied to Excel.  When I did this, I lost the leading zeros on 3 columns --Dept, Division, and ClinProv.  What can I change in this query to get the leading zeros back?

[Journal Entry], RTRIM(CAST(DATEPART(mm, T1.[TRX Date]) AS varchar(2))) + '/' + RTRIM(CAST(DATEPART(dd, T1.[TRX Date]) AS varchar(2)))
                      + '/' + RTRIM(CAST(DATEPART(yy, T1.[TRX Date]) AS varchar(4))) AS [TRX Date], T1.[TRX Date Month], T1.[TRX Date Year], T1.[Account Number],
                      T1.[Account Description], T1.[Credit Amount], T1.[Debit Amount], T1.[Net Amount], T1.[Account Description from Account Master],
                      '' + SUBSTRING(T1.Segment1, 1, 3) AS Dept, T1.[Segment1 Description], '' + SUBSTRING(T1.Segment2, 1, 6) AS Division,
                      T1.[Segment2 Description], T1.Segment3 AS Clin_Prov, T1.[Segment3 Description], T1.Segment4, T1.[Segment4 Description], T1.Period, ISNULL(T2.USRDEFS2, '')
                      AS [User Defined 4], T1.Description, T1.[Document Status], T1.[Source Document], T1.[User Who Posted]
FROM         dbo.AccountTransactionsEX AS T1 LEFT OUTER JOIN
                      dbo.GL00100 AS T2 WITH (nolock) ON T2.ACTINDX = T1.[Account Index]
WHERE     (T1.[Document Status] = 'open') AND (T1.[TRX Date] BETWEEN '12/01/2013' AND '12/31/2013') AND (T1.Segment4 BETWEEN '100000' AND
                      '999999')
0
Comment
Question by:pstre
4 Comments
 

Assisted Solution

by:ruengyot
ruengyot earned 167 total points
ID: 39979489
If you just copy to excel, I think the easy way is setting excel target column to text.
0
 

Author Comment

by:pstre
ID: 39979492
Not enought information.  I did copy to Excel and lost the leading Zeros...
0
 
LVL 40

Assisted Solution

by:Sharath
Sharath earned 166 total points
ID: 39979673
Excel treats the numbers as numerical and removes the leading zeroes. If you want the leading zeroes, change the cells format to text in excel before pasting the result.

Right click on the selected cells and click the format cells. Change the Category to text.
Then paste the SQL result to excel.
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 167 total points
ID: 39981935
Either change the data format in Excel or if you are unable to do that then export that column with a leading single quote.  Let me know if that is not enough information and I will be more detailed.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now