?
Solved

SQL statement - exporting to Excel

Posted on 2014-04-04
4
Medium Priority
?
459 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 668 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 41

Assisted Solution

by:Sharath
Sharath earned 664 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 668 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
This tutorial explains how to use the VisualVM tool for the Java platform application. This video goes into detail on the Threads, Sampler, and Profiler tabs.
Suggested Courses

750 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