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?
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'.
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$]')
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.