none
Import/Export Data into Excel and save it to DB

    Question

  • Hello,

    My requirement is something like this:
    I have assessment data like medical form template(with many fields), which I should allow the user to input values.

    There are two options to do this , one being the traditional Web Page with all the fields and submit the data to Database(Option 1).

    Since the form is too huge  I am thinking of Option 2- by having this format in excel sheet, so that user can download the excel sheet, enter the values and upload back to Webpage, after which I should read the data and save it in Database.

    Since I am newbie to ASP.Net, just want to know if this can be achieved and if yes , what is the best approach to be taken.
    I have already browsed on this topic and have seen excel being read into DB by  running a Select Query on Columns in Excel sheet, and saving in DB.
    But in my case, the excel will have just two columns, Col 1 has all the fields and col 2 has the respective values (so running the query on columns will not work fine), hence wondering how could I read the data in such case?

    Any inputs on this will be highly appreciated, should I go with Option1/Option 2?





    udrjain

    Tuesday, November 19, 2013 9:51 AM

Answers

  • Using an excel shhet has its good points and bad points.  First the bad point. With Excel users can put any data into cells, skip rows, change the headers on the worksheet, rename the worksheet which cuases lots of headaches when you are trying to import the file.  So the users have to be trained on how to properly enter the data.  Yes you can put in validation into the worksheet, but somebody always seems to enter the data improperly.  The good point is most people don't have to be trained to use excel and it is easy to enter data.

    The code kcgmani provided will only work with an xls file and not xlsx.  He is using the Microsoft.Jet.OLEDB.4.0 which is a window driver and don't require any other software installed on the PC.  You can also use the ACE driver which is part of microsoft Office (Access driver) and requires an office licenese installed on the PC and wil work with both xlx and xlsx files.


    jdweng

    Tuesday, November 19, 2013 10:55 AM
  • CSV files are string so you have to parse dates and number when importing and you can't have commas in the cells.


    jdweng

    Tuesday, November 19, 2013 11:11 AM

All replies

  • Hi Udrjain,

    First you have to clarify what database are you using, If its Oracle, Then sorry you cannot migrate it directly as the namespace corresponds to it is deprecated. If its SQL then it is possible.

    (If you're DB is oracle, you have to convert the XLS to intermediatery XML and then take it forward, which is pretty simple.No panic).

    Implementation:

    VS2008 and above version gives you File upload control which you can use and upload the xls(code behind). that can be stored in some physical location). Here is the code snippet.First few lines have the code to upload the file on button click and storage. Last part has the code to update oracle DB.

    For SQL You can skip the process of XLS to XML conversion.

     protected void UploadButton_Click(object sender, EventArgs e)
            {
                if (FileUpload1.HasFile)
                {
                    try
                    {
                        string filename = Path.GetFileName(FileUpload1.FileName);

                        FileUpload1.SaveAs(Server.MapPath("~/uploaded_xl/") + filename);
                        StatusLabel.Text = "Upload status: File uploaded!" + Server.MapPath("~/uploaded_xl");
                        string filenameStr = Server.MapPath("~/uploaded_xl/") + filename;
                        
                        
                        string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + filenameStr + "; Extended Properties=" + '"' + "Excel 8.0; HDR=Yes; IMEX=1" + '"';

                        string strSQL = "SELECT * FROM [Sheet1$]";
                        OleDbConnection excelConnection = new OleDbConnection(connectionString);
                        excelConnection.Open(); // this will open an Excel file
                        OleDbCommand dbCommand = new OleDbCommand(strSQL, excelConnection);
                        OleDbDataAdapter dataAdapter = new OleDbDataAdapter(dbCommand);

                        // create data table
                        DataTable dTable = new DataTable();

                        dataAdapter.Fill(dTable);
                        dTable.TableName = "myTable"; 
                        
                       /* // bind the datasource
                        GridView1.DataSource = dTable;
                        GridView1.DataBind();*/
                        using (StringWriter swStringWriter = new StringWriter())
                        {
                            // Emp details datatable – ADO.NET DataTable 
                            //DataTable dTable = GetEmpDetails();
                            // Datatable as XML format 
                            dTable.WriteXml(swStringWriter);
                            // Datatable as XML string 
                            string strEmpDetails = swStringWriter.ToString();
                            //using (SqlConnection dbConnection = new SqlConnection("Provider=MSDAORA;Data Source="(DESCRIPTION=(ADDRESS_LIST=(ADDRESS =(PROTOCOL = TCP)(HOST =lonora03.test.lloydstsb.co.uk)(PORT = 1526)))(CONNECT_DATA=(SID=ESTATMS1)))";Persist Security Info=True;Password=DEMAND_SUPPLY_DEV;User ID=DEMAND_SUPPLY_DEV"))
                            //using (OracleConnection dbConnection = new OracleConnection(ConfigurationManager.ConnectionStrings["ConnectionStringDemand"].ConnectionString))
                             using (OleDbConnection connection = new OleDbConnection())
                             using (OleDbCommand command = new OleDbCommand())
                        {
                            connection.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionStringDemand"].ConnectionString;
                            connection.Open();

                            OleDbParameter parameter = new OleDbParameter();
                            parameter.Direction = ParameterDirection.Input;
                           parameter.ParameterName = "p_EmpDetails";
                                    // Parameter type as XML 
                                    parameter.DbType = DbType.String;
                                    // Input Parameter
                                    parameter.Direction = ParameterDirection.Input;
                                    // XML string as parameter value
                                    parameter.Value = strEmpDetails;

                            command.Parameters.Add(parameter);

                            command.Connection = connection;
                                 command.CommandType = CommandType.StoredProcedure;
                                 command.CommandText="InsertEmpDetail";
                                 int intRetValue = command.ExecuteNonQuery();
                            
                            /*//reader = command.ExecuteReader();
                            {
                                
                                // Database command
                                using (OracleCommand OdbCommand =  new OracleCommand( "InsertEmpDetail", dbConnection))
                                {
                                    // we are going to use store procedure  
                                    OdbCommand.CommandType = CommandType.StoredProcedure;
                                    // Add input parameter and set its properties.
                                    OracleParameter parameter = new OracleParameter();
                                    // Store procedure parameter name 
                                    parameter.ParameterName = "p_EmpDetails";
                                    // Parameter type as XML 
                                    parameter.DbType = DbType.String;
                                    // Input Parameter
                                    parameter.Direction = ParameterDirection.Input;
                                    // XML string as parameter value
                                    parameter.Value = strEmpDetails;
                                    // Add the parameter to the Parameters collection.
                                    OdbCommand.Parameters.Add(parameter);
                                    dbConnection.Open();
                                    int intRetValue = OdbCommand.ExecuteNonQuery(); 
                                }
                            } 
                                 */

                        }





    // dispose used objects
    dTable.Dispose();
    dataAdapter.Dispose();
    dbCommand.Dispose();
    excelConnection.Close();
    excelConnection.Dispose();
                        


                    }}
                    catch (Exception ex)
                    {
                        StatusLabel.Text = "Upload status: The file could not be uploaded. The following error occured: " + ex.Message;

                    }
                }
            }

    Reference:

    --------------

    http://www.codeproject.com/Articles/54784/ADO-NET-DataTable-as-XML-parameter-to-an-Oracle-SQ?q=update+oracle+database+from+excel+using+asp.net


    K.G.Manivannan

    Tuesday, November 19, 2013 10:38 AM
  • Using an excel shhet has its good points and bad points.  First the bad point. With Excel users can put any data into cells, skip rows, change the headers on the worksheet, rename the worksheet which cuases lots of headaches when you are trying to import the file.  So the users have to be trained on how to properly enter the data.  Yes you can put in validation into the worksheet, but somebody always seems to enter the data improperly.  The good point is most people don't have to be trained to use excel and it is easy to enter data.

    The code kcgmani provided will only work with an xls file and not xlsx.  He is using the Microsoft.Jet.OLEDB.4.0 which is a window driver and don't require any other software installed on the PC.  You can also use the ACE driver which is part of microsoft Office (Access driver) and requires an office licenese installed on the PC and wil work with both xlx and xlsx files.


    jdweng

    Tuesday, November 19, 2013 10:55 AM
  • I would recommend to use .csv files. This will help you in many ways 

    Mark Answered, if it solves your question and Vote if you found it helpful.
    Rohit Arora

    Tuesday, November 19, 2013 11:08 AM
  • CSV files are string so you have to parse dates and number when importing and you can't have commas in the cells.


    jdweng

    Tuesday, November 19, 2013 11:11 AM
  • Thank you everyone for ur inputs, however the requirement is changed to implement it in Tab format like having different tabs for diff templates, so I don't have to use excel anymore.


    udrjain

    Thursday, November 28, 2013 2:31 AM
  • CSV is a little better than tab format because it can be read by excel better than tab.  CSV is exactly the same as TAB except a comma is used instead of the TAB.  The only Problem with CSV is if there is commas in the actual data.

    TABs only works well will if each column is exactly the same number of characters.  TAB spacing is variable and different software can have tabs set in difference places which often give poor results.  TAB is kind of old fashion.


    jdweng

    Thursday, November 28, 2013 8:09 AM