Solved

SQL statement - exporting to Excel

Posted on 2014-04-04
4
401 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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 ?
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.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

856 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