Truncation of leading zeros

I have a problem while importing data from Excel to SQL Server.The leading zeros in data get truncated.Even if I try and change the excel data column as 'Text' and copy paste the data back into the Text column, the problem persists.Does any one have any thoughts about this problem?

Did you load them into column with char data type in sql table?

I had loaded them into varchar datatype

When importing both XLS and CSV to SQL Server works for me. Leading zeroes are not truncated.

Can you see the leading zeroes in Excel?

If problem still exist, please upload a sample Excel sheet that you are using and the SQL Server table structure.

I have attached the sample excel file and the script to create the SQL table.Hope this helps.

Can't we upload .xls files in the forum?

Lemme try this.I have the sample excel and SQL table script in this zip file.

I dont see any leading zeroes in the Excel file.

This is strange I have leading zeros in all the rows of the ID column.The data of concern to me is the 'ID' column in the excel sheet.When i import this excel file to the SQL table I see the ID column with values like '35000000' , '217123456' as opposed to seeing it as '0035000000' and '0217123456'.

You are right. ID column has leading zeroes.

When I checked the properties of the Column A (ID) (Format cells ) it was Custom with value "0000000000"

When importing it truncates all the leading zeroes.

Trying to use disconnect edit. Will let you know.

Similar Issue

http://www.experts-exchange.com/Appl..._20832431.html

You can use OpenRow


insert into VendSpendsDirectNew ([SAP_vndr_nbr]
)
select ID FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;DATABASE=c:\sample.xls', 'Select * from [Sheet1$]')


refer:
http://www.databasejournal.com/featu...le.php/3331881

Thanks! Will try this and let you know the results

When I tried the OpenRowset insert, the following was the error that I obtained:

Server: Msg 7415, Level 16, State 1, Line 1
Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been denied. You must access this provider through a linked server.

Copy the file to you server's c:\ and then query it

That is exactly what I had done
. I copied my excel file to C: and ran the the following queries:

CREATE TABLE [dbo].[VSD] (
[SAP_vndr_nbr] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vndr_nm] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Min_Invc_Pmt] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Max_Invc_Pmt] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Avg_Invc_Pmt] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Sum_Invc_Pmt] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Count_Checks] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Min_Pay_Date] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Max_Pay_Date] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO





insert into VSD([SAP_vndr_nbr]
)
select ID FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;DATABASE=c:\Sample.xls', 'Select * from [Sheet1$]')

Are you SA on the SQL Server?

no i'm a user

Either one of these will do

1. Talk to you DBA about giving access to you reg this.

from BOL
"OPENROWSET can be used to access remote data from OLE DB data sources only if the DisallowAdhocAccess registry option is explicitly set to 0. When this option is not set, the default behavior does not allow ad hoc access."

2. Talkto you DBA in creating Linked server to this XL file

3. Create ActiveX script in DTS package to read the EXCel sheet row by row.

http://support.microsoft.com/default...86&Product=sql

4. Import Excel into a temp table and then prefix it with '00000'

eg:
create table x123(id varchar(10))
insert into x123 select '35000000'
insert into x123 select '217123456'
insert into x123 select '1712'

update x123 set [id]=right('00000000000'+[id],10)

select * from x123

--results
id
0035000000
0217123456
0000001712

ok i tried importing the excel file into my local server on my laptop and the import was successful, but when i queried the table the results shows id column with values in exponential form.I have attached a screenshot of the query analyzer for you.

Did you change that column to a TEXT data type
?

select ID FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;DATABASE=c:\Sample.xls', 'Select * from [Sheet1$]')

should show you what you are importing

I have tried changing the ID column to text format, but the moment i do that, the leading zeros get truncated in my excel sheet itself, even before my import into SQL table.

I tried a couple of options, like CSV, Tab delimited, but nothing worked.The only think tht worked was your option 4 which was creating the temp table and concatenating leading zeros and another one was to create a blank spreadsheet and format the ID column as 'Text" and then re-key in the ID data and then import to SQL.However re-keying in 70,000 records is really tedious.

Then Option 4 it is.

Thanks a bunch.Appreciate your help!

Quote:

---

Originally posted by Raveen
I have a problem while importing data from Excel to SQL Server.The leading zeros in data get truncated.Even if I try and change the excel data column as 'Text' and copy paste the data back into the Text column, the problem persists.Does any one have any thoughts about this problem?

---

The problem is that the zeroes don't actually exist in Excel. The visible leading zeroes you see in your spreadsheet are only appearing because of the custom number format of 0000000000.

So you must at some point add the leading zeroes yourself, as other people have suggested. You can modify the spreadsheet to actually contain the zeroes if you like. Add a new column B in the worksheet (so it is right next to the ID column) and insert this formula in cell B2: "=Right(Rept("0",9)&A2,10)". (Note: the column must be in general or a number format in order for the formula to work.)

Then copy and paste this or fill down for the whole column. You may either name the column and query it directly (I think) or you can choose Copy, then Paste Special|Values to turn the formulas into 'real' text. You can choose to change the data type to text at this point so any further data entry can allow the leading zeroes to 'stick.'

It's probably easiest just to add the zeroes in SQL Server, after all. :-)

Thanks! Learnt something new about excel's capabilities, but again agree with your point that it is easier to do it in SQL.