Re: Truncation of leading zeros
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. :-)