How to enforce untyped columns in ExcelDataReader?

Question

How do you prevent dates or times getting strictly-formatted?

I'm reading an XLS file using reader.AsDataSet, and when the source data is 12/12/2014 then this generates an output of 12/12/2014 12:00:00AM.

Also, when the source is for example 5:01:23 AM then this generates something weird: 12/31/1899 5:01:23 AM

Here's the function:

using (var stream = new FileStream(excelFilePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
    IExcelDataReader reader = null;
    if (excelFilePath.EndsWith(".xls"))
    {
        reader = ExcelReaderFactory.CreateBinaryReader(stream);
    }
    else if (excelFilePath.EndsWith(".xlsx"))
    {
        reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
    }

    if (reader == null)
        return false;

    var ds = reader.AsDataSet(new ExcelDataSetConfiguration()
    {
        UseColumnDataType = false,
        ConfigureDataTable = (tableReader) => new ExcelDataTableConfiguration()
        {
            UseHeaderRow = false
        }
    });

How do we read in the XLS file into a DataSet without automatically typing-concretely the data?

I don't think, its weird. Looks like you need `timespan` column, instead you get `DateTime`. Datetime of course needs date part, so it adds it. Mat be, `AsDataSet` does not fit your case. You probably need to convert types in a loop. But I also see that date does seem coming from Excel, not converted. Otherwise it would be `1/1/0001 5:01:23 AM` — T.S., Nov 21 '18 at 17:21
@T.S. can you give an example suggestion of how to read this? — Alex Gordon, Nov 21 '18 at 17:23
https://github.com/ExcelDataReader/ExcelDataReader `do { while (reader.Read()) { // reader.GetDouble(0); } } while (reader.NextResult());` — T.S., Nov 21 '18 at 17:25
@T.S. sorry not understanding `reader.GetDouble(0)` - none of my data is a `Double` — Alex Gordon, Nov 21 '18 at 17:37
Ok, but there must be `Reader.GetDate` etc. This is just a sample. Don't take literally. Look at definition https://github.com/ExcelDataReader/ExcelDataReader/blob/develop/src/ExcelDataReader/ExcelDataReader.cs. So in your loop you'll do something like (depends what you need) `reader.GetDate(..).ToString("hh:mm:ss")` - this will give you string representation of timespan. You can fill string column in data table — T.S., Nov 21 '18 at 19:05

score 1 · Accepted Answer · answered Nov 27 '18 at 07:41

I think in this situation you have all data you need in your result (as DataSet)!
So, I can suggest you to ignore that option in reading time and instead add any extra column to your result like below:

// `Copy` used to generate a new object
var dataTable = ds.Tables[0].Copy();

// create a new column with your own settings (like `TimeOnly`) and add it to your new DataTable
var newColumn = new DataColumn("TimeOnly", typeof(string)) { AllowDBNull = true };
dataTable.Columns.Add(newColumn);

// update your new column data based on other columns like `Column1`
foreach (DataRow row in dataTable.Rows)
{
    var value = DateTime.Parse(row["Column1"].ToString()).ToString("HH:mm:ss");
    row["TimeOnly"] = value;
}

HTH

How to enforce untyped columns in ExcelDataReader?

1 Answers1