reading excel table with empty cells (oledb)

I am trying to read an excel spreadsheet and copy values to a database.

it works fine, unless there is an empty cell in the spreadsheet. is there a way round it?

i first send the data to a grid view for the user to check, and then they commit the data to save it to the database. first it checks if the record exists - if it does then it doesn't copy it so the database retains unique records. if it exists but the 'status' column has changed it just changes that, if it doesn't exist it adds a new record.

the code i have for reading the data before running the SQL checks is

//create a doublequote for inseting into Excel Connection String
            char dbq = '"';
            string dbqs = dbq.ToString();

            //Create Excel connection String
            string xConnStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath(lblFileHolder.Text) + ";Extended Properties=" + dbqs + "Excel 12.0 Xml;HDR=YES;IMEX=1" + dbqs;

            //Create the Excel connection string and set the connection string
            OleDbConnection ExcelCon = new OleDbConnection();
            ExcelCon.ConnectionString = xConnStr;

            //open connection
            ExcelCon.Open();

            //establish command
            OleDbCommand getExcel = new OleDbCommand("Select * from [Data$]", ExcelCon);

            OleDbDataReader readExcel;
            readExcel = getExcel.ExecuteReader();

            while (readExcel.Read())
            {
                //get Territory
                string territory = readExcel.GetString(0);
                //get Original Insured
                string originsd = readExcel.GetString(1);
                //get London Broker
                string londbkr = readExcel.GetString(2);
                //get Local Broker
                string localbkr = readExcel.GetString(3);
                //get Cedant
                string cedant = readExcel.GetString(4);
                //get Is New?
                string isnew = readExcel.GetString(5);
                //get Risk Type
                string risktype = readExcel.GetString(6);
                //get Scope of Cover
                string coverscope = readExcel.GetString(7);
                //get Risk Code
                string riskcode = readExcel.GetString(8);
                //get Reference
                string reference = readExcel.GetString(9);
                //get Inception
                string inception = readExcel.GetDateTime(10).ToString();
                DateTime dtincept = Convert.ToDateTime(inception);
                //get expiry
                string expiry = readExcel.GetDateTime(11).ToString();
                DateTime dtexpire = Convert.ToDateTime(expiry);
                //get ROE (to GBP)
                string roetogbp = readExcel.GetDouble(12).ToString();
                //get Currency
                string currency = readExcel.GetString(13);
                //get Limit
                string limit = readExcel.GetDouble(14).ToString();
                //get Excess
                string excess = readExcel.GetDouble(15).ToString();
                //get premium
                string premium = readExcel.GetDouble(16).ToString();
                //get Fidelitas Share
                string fidshare = readExcel.GetDouble(17).ToString();
                //get Written Line (Local Curr)
                string writtenlinelc = readExcel.GetDouble(18).ToString();
                //get grosspem (Local Curr)
                string grosspremlc = readExcel.GetDouble(19).ToString();
                //get grosspem (GBP)
                string grosspremgbp = readExcel.GetDouble(20).ToString();
                //get brokerage
                string brokerage = readExcel.GetDouble(21).ToString();
                //get Status
                string status = readExcel.GetString(22);
                //get date entered
                string entrydate = readExcel.GetDateTime(23).ToString();
                DateTime dtenteredt = Convert.ToDateTime(entrydate);
                //get Comments
                string comments = readExcel.GetString(24);

as I say, it reads the cells with data in fine, but if a cell is empty it just fails. i'm not getting an error, simply nothing happens. I tried a null check, with something like

if (reference == null)
{
        reference = "...";
}

but that didn't work either.

any suggestions gratefully appreciated!

Re: reading excel table with empty cells (oledb)

If I did undestand your problem this occurs when a cell is empty in your readExcel.Read() code?

Did you check if your GetString() function worked on that particular record and cell in the imidate console, then you could see if any erro thrown or if a result is send back by the method. That will give us some more information I think.

Re: reading excel table with empty cells (oledb)

thanks.

yes the getstring() works if the cell has something in it, but if the cell is empty nothing happens. the page just refreshes, that's what's perplexing me, no error message so i don't know where to change.

Re: reading excel table with empty cells (oledb)

dcgate
if (reference == null) { reference = "..."; }

Please change to this and have a try with

if (reference!=null && !string.IsNullOrEmpty(reference.Trim()))
{

}
else
{
//Do to deal with Empty or Null values
}