CODEWITHSUNDEEP
asp.net-mvclinqexcel-import
Sachu
Sachu

Reputation: 7766

Excel Import : Data not being read if column has different data formats in MVC C# using linq

I am importing an excel sheet through a mvc website using linq statement as below

var  members = (from memRec in excelFile.Worksheet(sheetName)
                where memRec["FIRST_NAME"] != null 
                select memRec).ToList();
foreach (var member in members)
{
//save data
}

The problem here is let say a particular column "Date Of Birth" for first 2 columns the cell format is "DATE" and display as dd/MM/yyyy and 3rd column the cell format is "Text" and display as dd/MM/yyyy the excel will consider it as no value there for 3rd column because of format mismatch.

Is there any way to solve this.

Upvotes: 0

Views: 655

Answers (2)

Sachu
Sachu

Reputation: 7766

I found a solution.

In our server it is Office 2010.

Change the registry entry as below

  1. In Windows environments, select Start ► Run and type REGEDIT to display the Registry Editor.
  2. In the registry tree, select HKEY_LOCAL_MACHINE ► Software ► Microsoft ► Office ► 12.0 ► Access Connectivity Engine ► Engines.
  3. Double-click the Excel node.
  4. In the right panel, double-click the TypeGuessRows entry.
  5. Change the value data from 8 to 0.
  6. Click OK.
  7. Select File ► Exit to exit the Registry Editor window.

Now the excel wont guess the row datatype since we changed the value from 8 to 0.

Upvotes: 0

Stack Overflow
Stack Overflow

Reputation: 2774

Try declare object and use predefined data types, Like:

var q = from memRec in excelFile.Worksheet(sheetName)
        where memRec["FIRST_NAME"] != null 
        select new Person()
        {
            Date = (DateTime)memRec [“Date”],
            FirstName= (string)memRec [“FirstName”],
            Age= (int)memRec [“Age”],
        };

Upvotes: 0

Related Questions