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.
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.
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.
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. :-)