DataTable contains strings with Hashtags when imported from .xlsx instead of .xls

Question

I am working on a ASP.NET MVC Application where you can upload excel files.

The files contain a few columns, in one column we have some dates, which are formatted differently. The dates come with two different formats, the first is M/dd/yy h:mm:ss tt (General format) and the second is a custom format: dd.mm.yyyy hh:mm.

When I use a .xlsx file, the cells which uses the second format, contain after the import ######## as String value, which is totally wrong. When I change the custom format to a predefined, like Text, the import works correct.

Anyway, when I export the .xlsx file to .xls and then perform the import the values of the cells with custom format are correct.

Did someone experienced the same problem at any time? Can someone help me with this issue?

I need to do this whole thing with a .xlsx file since a manual editing of the file should not required before uploading.

for "completeness", here is my code which perform the import from an excel file to a dataSet with one table:

public DataSet ExcelToDataSet(object value, String path)
{
    String firstDatasheetName = "";
    String connectionString = BuildConnectionString(value, path);
    OleDbConnection oleConnection = new OleDbConnection(connectionString);

    if (oleConnection.State == System.Data.ConnectionState.Closed)
    {
        oleConnection.Open();
    }

    DataTable dataSchema = oleConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
    if (dataSchema == null | dataSchema.Rows.Count < 0)
    {
        firstDatasheetName = "";
        // throw new Exception("Can´t access the first Datasheet in your Excel. Please check if there is one!");
    }
    else
    {
        firstDatasheetName = dataSchema.Rows[0]["TABLE_NAME"].ToString();
    }
    String query = "Select * from [" + firstDatasheetName + "]";

    OleDbCommand oleCmd = new OleDbCommand(query, oleConnection);//OleDbCommand
    OleDbDataAdapter oleDA = new OleDbDataAdapter(oleCmd);
    DataSet dataSet = new DataSet();
    oleDA.Fill(dataSet);
    oleDA.Dispose();
    oleConnection.Close();
    oleConnection.Dispose();
    return (dataSet);
}

My connection String is:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Users\\user\\Documents\\v0-0-5\\project\\project\\App_Data\\Data\\Import\\file.xlsx;Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\"

I appreciate any help!

Answer 1

It does that for numeric values, such as dates, when the column is too narrow. Make the column wider, or the text smaller, does that fix it?