Solved

SQL statement - exporting to Excel

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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Sql Server group by 10 30
Tsql query 6 22
optimize stored procedure 6 29
Place a Case statement or a if else statement in a where clause 8 21
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
This tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.

832 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