Can't retrieve specified data from an excel file

Question

I got an excel file and i need to insert it into sqlserver so i got this code to insert this is the method to insert:

private void InsertDaily_Inventory(string FilePath)
    {
        try {
            using (SqlConnection conn = new SqlConnection("Data Source=(local);Database='SimDevice_Stocks';Integrated Security=yes;"))
            {
                conn.Open();

                SqlCommand cmd = new SqlCommand("Delete Temp_DailyInventory", conn);
                cmd.ExecuteNonQuery();

                //SqlCommand cmd1 = new SqlCommand("alter table Temp_DailyInventory alter column Order_Date nvarchar(200)", conn);
                //cmd1.ExecuteNonQuery();
               conn.Close();

            }

            constr = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1;""", FilePath);
            Econ = new OleDbConnection(constr);
            SqlConnection con = new SqlConnection("Data Source=(local);Database='SimDevice_Stocks';Integrated Security= yes;");

            Query = string.Format("Select [ORDER_SUB_TYPE],[ORDER_DATE],[ORDER_STATUS],[SIM_NETWORK_TYPE],[STORE_ID],[ROOT_PRODUCT_ATM_TYPE],[STC_ERP_SIM_ITEM],[STC_SALECO_ITEM_CODE]  FROM [{0}] where  (ORDER_SUB_TYPE='Change SIM' OR ORDER_SUB_TYPE='New') and ORDER_STATUS='Complete' and (ROOT_PRODUCT_ATM_TYPE = 'Prepaid' OR ROOT_PRODUCT_ATM_TYPE = 'Postpaid') and STORE_ID Like 'S' ", "Online_Inventory_Open_Points_re$");
            OleDbCommand Ecom = new OleDbCommand(Query, Econ);
            Econ.Open();

            DataSet ds = new DataSet();
            OleDbDataAdapter oda = new OleDbDataAdapter(Query, Econ);
            Econ.Close();
            oda.Fill(ds);
            DataTable Exceldt = ds.Tables[0];
            //connection();
            //creating object of SqlBulkCopy    
            SqlBulkCopy objbulk = new SqlBulkCopy(con);
            //assigning Destination table name    
            objbulk.DestinationTableName = "Temp_DailyInventory";
            //Mapping Table column    
            objbulk.ColumnMappings.Add("ORDER_SUB_TYPE", "Order_Sub_Type");
            objbulk.ColumnMappings.Add("Order_Date", "Order_Date");
            objbulk.ColumnMappings.Add("ORDER_STATUS", "Order_Status");
            objbulk.ColumnMappings.Add("SIM_NETWORK_TYPE", "Sim_Network_Type");
            objbulk.ColumnMappings.Add("STORE_ID", "Store_ID");
            objbulk.ColumnMappings.Add("ROOT_PRODUCT_ATM_TYPE", "Root_Product_Atm_Type");
            objbulk.ColumnMappings.Add("STC_ERP_SIM_ITEM", "Stc_Erp_Sim_Item");
            objbulk.ColumnMappings.Add("STC_SALECO_ITEM_CODE", "Stc_Saleco_Item_Code");


            //inserting Datatable Records to DataBase    
            con.Open();
            objbulk.WriteToServer(Exceldt);


            using (SqlCommand cmd3 = new SqlCommand("UPDATE Temp_DailyInventory SET Order_Date = LEFT(Order_Date, CHARINDEX(' ', Order_Date) - 1) WHERE CHARINDEX(' ', Order_Date) > 0", con))
            {
                cmd3.ExecuteNonQuery();

            }



            con.Close();
        }
      catch(Exception ex)
        {

        }
        }

and then on button click it inserts the records

  protected void InsertInventoryFile_Click(object sender, EventArgs e)
    {
        try
        {
            // string CurrentFilePath = Path.GetFullPath(FileUpload1.PostedFile.FileName);
            //InsertDaily_Inventory(CurrentFilePath);

            if (FileUpload1.PostedFile != null)
                if (FileUpload1.FileName.EndsWith(".xlsx"))
                {
                    try
                    {
                        if (!Directory.Exists(Server.MapPath("~/ExcelFiles/InventoryFiles/")))
                            Directory.CreateDirectory(Server.MapPath("~/ExcelFiles/InventoryFiles/"));

                        string fileName = Guid.NewGuid().ToString();
                        string fullPath = Server.MapPath("~/ExcelFiles/InventoryFiles/") + fileName + ".xlsx";
                        FileUpload1.SaveAs(fullPath);

                        //DataTable table = Excel.Import.Query(fullPath);
                        InsertDaily_Inventory(fullPath);
                        Response.Write(@"<script langauge='javascript'>alert('Data Imported')</script>");


                    }
                    catch (Exception ex)
                    {
                        Response.Write(@"<script langauge='javascript'>alert('"+ex.Message+"')</script>");
                    }
                }
        }

        catch(Exception exx)
        {

        }
    }

so the data contains some data like 'SCCC387' its not inserting them its only inserting Store ID's with numbers like '1023', if there's any code to help me insert the data from excel to sqlserver database please don't be hesitate to give it to me thanks in advance

Answer 1

With the filelocation you can create a OleDbConnection conection to the excel file using:

string excelConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["xlsx"].ConnectionString + fileLocation;
//connection String for xls file format.
if (fileExtension == ".xls")
{
    excelConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["xlsx"].ConnectionString + fileLocation;
}
//connection String for xlsx file format.
else if (fileExtension == ".xlsx")
{
    excelConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["xlsx"].ConnectionString + fileLocation;                        
}
//Create Connection to Excel work book
OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
excelConnection.Open();

Then you can check the data of the excel with this followin code:

String[] excelSheets = new String[dt.Rows.Count];
int t = 0;
//get the list of excelSheetNames
foreach (DataRow row in dt.Rows)
{
    excelSheets[t] = row["TABLE_NAME"].ToString();
    t++;
}                    
string query = string.Format("Select * from [{0}]", excelSheets[0]);
System.Data.DataSet ds = new System.Data.DataSet();
using (OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, excelConnection))
{
    dataAdapter.Fill(ds);
}
if (ds.Tables.Count > 0)
{
    DataTable tb = ds.Tables[0];
    return tb;
}   

In the appconfig you need to have the connectionString providers to excel:

<add name="xls" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" />
<add name="xlsx" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" />

Let me know if this help you.

Answer 2

You are using bulk copy, maybe you should handle each row independently to handle possible errors. Also verify the type of your columns. The excel driver determines the type based on the first 8 rows by default.

I strongly encourage you to use SSIS for dataloads and integrations when possible. If you have sql server standard edition you have SSIS.