0

I am using LinqToExcel to import large Excel data sheet into SQL Server database table Employees2. I am using EF4. The Date field in Excel (DOB and many other) is in "MMddyyyy" format. I have many fields in Excel, I just show 1 mapping here.

var excel = new ExcelQueryFactory(excelPath);
excel.AddMapping<Employee2>(x => x.DOB, "DOB", 
s => String.IsNullOrEmpty(s) ? DateTime.MinValue : 
DateTime.ParseExact(s, @"MMddyyyy", CultureInfo.CurrentCulture.DateTimeFormat));

var employees = excel.Worksheet<Employee2>("Sheet1").ToList();
employees.ForEach(e => context.Employee2.AddObject(e));
context.SaveChanges();

The code throws exception "String was not recognized as a valid DateTime."

on this line of code: var employees = excel.Worksheet("Sheet1").ToList();

My question is: How can I capture which record exactly causes the exception?

The exception doesn't say which record or even which field.

Just to make sure the conversion is fine, in command window I ran:

DateTime.ParseExact("08012012", "MMddyyyy", CultureInfo.CurrentCulture.DateTimeFormat)

Update:

Found the solution myself: EF Exception: String or binary data would be truncated. The statement has been terminated.?

Community
  • 1
  • 1
monstro
  • 6,254
  • 10
  • 65
  • 111
  • Debug with F5, it will break on exception, see s find the value of s in your excel spreadsheet. – Pawel Nov 13 '12 at 19:19
  • Does the column DOB not just return DateTime values? – Gert Arnold Nov 16 '12 at 23:46
  • I fails to see any connection between your question and the solution you found. Did you copy a wrong link? – Gert Arnold Nov 19 '12 at 21:15
  • Oh, the DateTime issue - it was something else. Empty area of Excel sheet was loaded, the excel cell was empty, no date value... The real problem is when you call SaveChanges - I got multiple exceptions that I mentioned in the post in that link. My question was: How can I capture which record exactly causes the exception? And the solution - SQL Profiler, you cant do this using EF. – monstro Nov 20 '12 at 21:43

1 Answers1

1

You will need to download the source code for LinqToExcel and re-run the query using the source code. Then you can break on the exception and see the actual row and value that is causing the issue.

Paul
  • 18,349
  • 7
  • 49
  • 56
  • 1
    Why? This exception seems to happen in his code - in the lambda expression. Should be able to see details without having to download anything. – Pawel Nov 14 '12 at 04:59