This is what can happen:
Excel SQL Server CAT_NO CAT_NO --------- --------- XYZ121156 XYZ121156 XYZ654321 XYZ654321 ABC120456 => ABC120456 CBA183456 CBA183456 ZYX123416 ZYX123416 123 NULL (!) ZXV654521 ZXV654521This article from SQL Server 2005 Books Online explains this weird behaviour:
The Excel driver reads a certain number of rows (by default, 8 rows) in the specified source to guess at the data type of each column. When a column appears to contain mixed data types, especially numeric data mixed with text data, the driver decides in favor of the majority data type, and returns null values for cells that contain data of the other type. (In a tie, the numeric type wins.) Most cell formatting options in the Excel worksheet do not seem to affect this data type determination. You can modify this behavior of the Excel driver by specifying Import Mode. To specify Import Mode, add IMEX=1 to the value of Extended Properties in the connection string of the Excel connection manager in the Properties window.