Importing data into a table from Excel.xls file

[ezecastle]

Hi all.

I am trying to insert data from a Excel.xls file into a sql table. After the initial load the data looks OK except one column.

The column stores data like so:

9
10
9,10,13

The data load loads all the data except the row containing 9,10,13. This field is showing null after the load.

So I entered the field manually after the load in sql EM. I added the 9,10,13 to the Null field. The data looks fine after I did this.

I then decided to export this to an Excel file to see how it looks, it looked good and so I then deleted the data in the table and reloaded the new Excel file that I exported with the correct data. Now when I look at the table the data is showing as it should. 9,10,13 and isn't showing as a NULL anymore??

Any ideas how I can fix this? I'm at a loss.

Thanks in advance!

[rmiao]

It's data type issue in excel, ensure all cells in that column have same data type.

[i_sanket]

In addition to this What excel does is it checks for first few columns and excel connection manager just blindly assigns a data type. In your case the datatype assigned in Integer for the column so you cannot have 9,13 in iteger field and hence You have no data in that fields.
In MS EXCEL Format the column as TEXT and re create connection manager for this and you should get the results.