cannot import left aligned values from excel sheet to datatable

Question

I want to import an excel sheet to DataTable. i use the following code:

        string path = @"" + /****path of excel file****/;
        string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;";
        OleDbConnection xlConn = new OleDbConnection(connectionString);
        xlConn.Open();

        OleDbCommand selectCommand = new OleDbCommand("SELECT * FROM [Sheet1$]", xlConn);
        OleDbDataAdapter dataAdapter = new OleDbDataAdapter();
        dataAdapter.SelectCommand = selectCommand;
        itemMasterUploadDataTable = new System.Data.DataTable();
        dataAdapter.Fill(itemMasterUploadDataTable );
        xlConn.Close();

Everything works fine except for the left aligned values in the excel sheet.

The above image shows a part of one of the columns of the excel sheet. The right aligned values(1511, 1511, 2202, 2202) are getting entered into datatable properly but the left aligned values(0450, 0405, 0406, 0406, 0406, 0406, 0401) are getting entered as null. what am i doing wrong? I want to import all the values into the datatable.

Answer 1

I think it is not dependent on the alignment . All the values in the column must be of the same data type . looks like the left aligned are coming from a formula. Check the properties of the cells.

Answer 2

It will work if you do this in excel file:

1. copy the whole column
2. select 1st value
3. hold keys CTRL(CMD) and ALT(OPTION)
4. push V