Feeds:
Posts
Comments

Posts Tagged ‘SQL SERVER – Omittied leading zeros in excel’

In my earlier article, I had discussed about the omitted leading zeros in excel and I received a very nice solution from Mr. Harsh, one of my blog readers. (This solution works in Excel 2010 and above).

Let me explain his solution step by step.

Step A :
Let me create a sample to demonstrate the error.

USE tempdb
GO
CREATE TABLE [dbo].[tbl_Employee](
	[Employee ID] [varchar](5) NULL,
	[Employee Name] [varchar](50) NOT NULL
) ON [PRIMARY]

GO
INSERT [dbo].[tbl_Employee] ([Employee ID], [Employee Name])
VALUES (N'00001', N'A. Scott')

INSERT [dbo].[tbl_Employee] ([Employee ID], [Employee Name])
VALUES (N'00002', N'Alan')

INSERT [dbo].[tbl_Employee] ([Employee ID], [Employee Name])
VALUES (N'00003', N'Alejandro')

INSERT [dbo].[tbl_Employee] ([Employee ID], [Employee Name])
VALUES (N'00004', N'Alex')

INSERT [dbo].[tbl_Employee] ([Employee ID], [Employee Name])
VALUES (N'00005', N'Alice')
GO
SELECT [Employee ID], [Employee Name] FROM [tbl_Employee]
GO
--OUTPUT

Remove_zeros_in_excel.1.1

Step B :
Copy the data from SQL Server, open an excel sheet, click on Paste Button and select Use Text Import Wizard (as shown in the picture below).

omittingleadingzeros1.1

Step C :
It then takes you to 3 steps of Text Import Wizard. Under Text Import Wizard – Step 1 of 3, you need to determine the data type. Select Delimited and press Next (as shown in the picture below).

omittingleadingzeros1.2

Step D :
Text Import Wizard – Step 2 of 3 lets you to set the delimiters your data contains. Select Tab and press Next (as shown in the picture below).
omittingleadingzeros1.3

Step E :
Text Import Wizard – Step 3 of 3 lets you set the column data format. Select Text and press Finish (as shown in the picture below).

omittingleadingzeros1.6

Step F :
Once finished, the data would be pasted in the required format, meaning along with the zeros as shown in the picture below.

omittingleadingzeros1.5

Let me know if you know any better solution.

Advertisements

Read Full Post »

Problem :
Maintaining format is sometimes very critical when data is copied from one source to another. Yesterday when I was working on an excel report I faced this problem.  When I copied a formatted ID (“0001“) with leading zeros from MS SQL Server and pasted it in MS Excel it became 1 only.

Solution :

Lets Fix it step by step :

Step 1 :
Let me create a sample to demonstrate the error.

USE tempdb
GO
CREATE TABLE [dbo].[tbl_Employee](
	[Employee ID] [varchar](5) NULL,
	[Employee Name] [varchar](50) NOT NULL
) ON [PRIMARY]

GO
INSERT [dbo].[tbl_Employee] ([Employee ID], [Employee Name])
VALUES (N'00001', N'A. Scott')

INSERT [dbo].[tbl_Employee] ([Employee ID], [Employee Name])
VALUES (N'00002', N'Alan')

INSERT [dbo].[tbl_Employee] ([Employee ID], [Employee Name])
VALUES (N'00003', N'Alejandro')

INSERT [dbo].[tbl_Employee] ([Employee ID], [Employee Name])
VALUES (N'00004', N'Alex')

INSERT [dbo].[tbl_Employee] ([Employee ID], [Employee Name])
VALUES (N'00005', N'Alice')
GO
SELECT [Employee ID], [Employee Name] FROM [tbl_Employee]
GO
--OUTPUT

Remove_zeros_in_excel.1.1

Step 2 :
Open an excel sheet and copy the data from SQL Server and paste it in excel and view the result. This step is just to demonstrate the problem.
Remove_zeros_in_excel.1.2

Can you observe that leading zeros has been removed and only numbers are left ?  Lets fix it.

Step 3 :
Open a new excel sheet and right click on the column where you want to paste (place) leading zeros column and select format cell.
Remove_zeros_in_excel.1.3

It will open ‘Format Cells’ window. Select Text in it and Press OK to close it.
Remove_zeros_in_excel.1.4

Step 4 :
Now, copy the data from SQL Server and paste it in the excel.

Remove_zeros_in_excel.1.5

Let me know if you know any better solution.

Read Full Post »