ASP.NET, C#, MVC 3, Code First project
I'm trying to import data from an Excel spreadsheet. I've formated all cells as Text.
A sample row in the Import worksheet is as follows.
Account Card ThreeCode Route
04562954830287127 32849321890233127 183 154839254
04562954830287128 32849321890233128 233
04562954830287129 32849321890233129 082
04562954830287130 32849321890233130 428
When I run in debug and drill down into the ds DataSet the Account and Card columns are imported as strings, the 3-Digit and Route columns are imported as doubles. The problem arises with the 3 digit number starting with 0 (082) in data row 3. It gets imported as a System.DBNull and is empty. I need to be able to import 3 digit codes with leading zeros. Is there a way to force the import to be all strings or another way to approach this problem? I searched the web for and haven't found a solution. This will run from a browser so anything to do with the registry, dll or ini files on the local machine is not an option. The import code is below. Thank you in advance for any help.
public ActionResult ExcelToDS(string Path = "C;\File.xls")
{
string strConn = "Provider= Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + "; " + "Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open(); string strExcel = "";
OleDbDataAdapter myCommand = null;
DataTable dt = new DataTable();
strExcel = "select * from [Import$]";
DataSet ds = null;
myCommand = new OleDbDataAdapter(strExcel, strConn);
ds = new DataSet(); myCommand.Fill(ds, "table1");
There doesn't appear to be a way to make this work consistently. The work around I came up with is to add 1000 to the ThreeCode column in the Excel workbook. You are then able to import the data into a dataset. Then when the data is read out you simply strip off the "1" preifx. Here is my inline method to do that.
public static string last3(this string instring)
{ int len = instring.Length - 3; string outstring = instring.Substring(len, 3); return outstring; }
Which you can call in the code with:
card.3Dig = code.last3();
'card' and '3dig' are the class and field being populated. 'code' is the 4 digit dataset data.