?
Solved

SQL statement - exporting to Excel

Posted on 2014-04-04
4
Medium Priority
?
453 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

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 ?
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

650 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