1

I've got an Excel spreadsheet from our HR department that needs to be loaded into a database. One column has dates in it. I've searched here on SO and found 2 answers (first one and the second one) which I thought might address my problem, but neither did.

First I tried doing a simple open of the spreadsheet:

var excel = new ExcelQueryFactory(@"D:\Projects\LRAT\Example of SPO report 08-25-17 - Copy.xlsx");

I left the first row alone. In the class I defined to match the spreadsheet I defined the property for the dates originally like this:

[ExcelColumn("Date Current Step Filed")]
[DefaultValue("")]
public string DateStepsFiled { get; set; }

This worked, so longer as there were multiple dates in the column. But when there was just 1 date, LinqToExcel interpreted it with values like this: 39758. Clearly, not at date. Or at least not one in mm/dd/yyyy format.

In reading what I did I got the impression that the first row, commonly the header row, influenced how LinqToExcel would process the spreadsheet. I don't quite get that because I thought that LinqToExcel ignored the first row anyway, but I decided to change the header for this column to today's date and I also changed the definition of the DateStepsFiled to this:

[ExcelColumn("Date Current Step Filed")]
[DefaultValue("")]
public DateTime[] DateStepsFiled { get; set; }

This was much worse, as LinqToExcel now defined all of the DateStepsFiled as null. So, how do I resolve this problem?

Rod
  • 4,107
  • 12
  • 57
  • 81
  • Never mind, I've discovered the problem. The spreadsheet is large, so I hadn't spent time looking throughout the rows of the spreadsheet. I just spent time at that and have discovered that the users, for whatever reason, entered lots of dates like `39758` and `39793` in a column that's meant to be for dates only. I've got to go back to them and ask them how in heck those values are to be interpreted. – Rod Nov 29 '17 at 16:26

1 Answers1

0

it should be

public DateTime DateStepsFiled { get; set; }

instead of

public DateTime[] DateStepsFiled { get; set; }
Z .
  • 12,657
  • 1
  • 31
  • 56
  • I thought it would have been DateTime[] because of values like this in the cell in the Excel spreadsheet: 8/15/2008 9/8/2008 11/13/2008 – Rod Nov 29 '17 at 15:53