Excel file loading problems. Here is the solution.

While loading/reading Excel file from SQL server using import/export wizard,it will try to guess the data type of the columns to be imported.Once it determines the data type of a column it will ignore the data that is not conforming to the data type and does not do any implicit conversion. The result is that if your column has number and text then most likely your number or your text will became NULL after they are imported into SQL server.( It will first 8 rows and determine type of the the column).

There are two ways we can address this problem.
1)open your excel file, copy your column to notepad and then format the column as text in excel. After that copy the data from notepad and paste it back to the column as text. After that the column can be import with out losing any values.

2) link the Excel file into SQL server by running the following statement.
Here the trick is IMEX=1( I will scan entire file)

select * into sample4

FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;Database=C:\SALamppostSamples4_DOS.xls;HDR=YES;IMEX=1',

'SELECT * FROM [Sheet1$]')

3) change below value
start-->run-->regedit-->
HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\4.0\Engines\Excel
change “TypeGuessRows” to 0

I prefer second method, because we can schedule jobs with this method.