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
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.
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.
It will open ‘Format Cells’ window. Select Text in it and Press OK to close it.
Step 4 :
Now, copy the data from SQL Server and paste it in the excel.
Let me know if you know any better solution.
Using Text Import Wizard:
Steps:- Paste->Text Import Wizard
Select Delimited in 1st Step
Select Tab in 2nd Step,
In 3rd step, select Column Data Format : Text
Done
Hi Harsh,
Very Nice solution, will post it in my upcoming post as an alternative solution under your name.
Thank you
Imran
[…] 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 […]