none
Browse excel file ---> Read excel file ---> Save data into database. RRS feed

  • Question

  • Although there is tons of information out there, but I prefer to know how to write code for my specific case.

    Thanks.

    https://docs.microsoft.com/en-us/dotnet/api/system.windows.forms.filedialog.restoredirectory?view=netframework-4.7.2

    private void uploadExcelToolStripMenuItem_Click(object sender, EventArgs e)
            {
                //Get file path
                
                var filePath = string.Empty;
                OpenFileDialog openFile = new OpenFileDialog();
                openFile.Filter = "Excel Files|*.xl*"; //Filter for excel file
                openFile.FilterIndex = 2;  //Don't know what it mean
                openFile.RestoreDirectory = true;
    
                if (openFile.ShowDialog() == DialogResult.OK)
                {
                    //Get the path of specified file
                    filePath = openFile.FileName;
                }
    
    
                //Read excel data into DataTable
                //Create COM Objects. Create a COM object for everything that is referenced
                Excel.Application xlApp = new Excel.Application();
                //filePath should already be open in the above, correct? It seems Open is not correct here?
                Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(filePath);
                //The excel file has only one sheet, with header, want to import Column A-G into database
                Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
                Excel.Range xlRange = xlWorksheet.UsedRange;
    
                //Find last row of column A, which is also last row of data to be imported. Don't look beyong column G, since user may put comments there.
    
                //Read data into DataTable
    
                //Save data into database table XYZ (Insert if new record; Update if record already exists)
    
                
            }




    • Edited by VA_er Wednesday, March 20, 2019 10:51 PM
    • Moved by CoolDadTx Thursday, March 21, 2019 1:49 PM Office related
    Wednesday, March 20, 2019 10:45 PM

All replies

  • To get last row using Excel automation (should work with the COM reference, I've stop with COM 10 plus years ago) see this line of code.

    I think that using OleDb to read a WorkSheet with common data is easier as per this code sample.

    To save to SQL-Server for inserts if new, updates if read already exists and can be identified by a primary key see T-SQL MERGE which can be executed via a connection and command object using SqlClient data provider. No primary key? You will need to have a method to identify records and loop through current table rows.

    Not using SQL-Server, use OleDb data provider (there is no merge for OleDb) then it's iterating rows pretty much the same as SQL-Server.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Thursday, March 21, 2019 1:28 AM
  • Hi VA_er,

    We could use ExcelDataReader to achieve it simply, here is sample for your reference.

    private void uploadExcelToolStripMenuItem_Click(object sender, EventArgs e)
            {
                //Get file path
                
                var filePath = string.Empty;
                OpenFileDialog openFile = new OpenFileDialog();
                openFile.Filter = "Excel Files|*.xl*"; //Filter for excel file
                openFile.FilterIndex = 2;  //Don't know what it mean
                openFile.RestoreDirectory = true;
    
                if (openFile.ShowDialog() == DialogResult.OK)
                {
                    //Get the path of specified file
                    filePath = openFile.FileName;
                }
    
    
                using (var stream = File.Open(filePath, FileMode.Open, FileAccess.Read))
    {
        // Auto-detect format, supports:
        //  - Binary Excel files (2.0-2003 format; *.xls)
        //  - OpenXml Excel files (2007 format; *.xlsx)
        using (var reader = ExcelReaderFactory.CreateReader(stream))
        {
            
    
            //  Use the AsDataSet extension method
            //var result = reader.AsDataSet();
    
    
        //Read data into DataTable
        DataTable firstWorkSheet = reader.AsDataSet().Tables[0];
    
     //Save data into database table XYZ (Insert if new record; Update if record already exists)
    
        }
    }
    
    
            
    
               
    
                
            }


    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, March 21, 2019 7:45 AM
  • Hi VA_er,

    We could use ExcelDataReader to achieve it simply, here is sample for your reference.

    private void uploadExcelToolStripMenuItem_Click(object sender, EventArgs e)
            {
                //Get file path
                
                var filePath = string.Empty;
                OpenFileDialog openFile = new OpenFileDialog();
                openFile.Filter = "Excel Files|*.xl*"; //Filter for excel file
                openFile.FilterIndex = 2;  //Don't know what it mean
                openFile.RestoreDirectory = true;
    
                if (openFile.ShowDialog() == DialogResult.OK)
                {
                    //Get the path of specified file
                    filePath = openFile.FileName;
                }
    
    
                using (var stream = File.Open(filePath, FileMode.Open, FileAccess.Read))
    {
        // Auto-detect format, supports:
        //  - Binary Excel files (2.0-2003 format; *.xls)
        //  - OpenXml Excel files (2007 format; *.xlsx)
        using (var reader = ExcelReaderFactory.CreateReader(stream))
        {
            
    
            //  Use the AsDataSet extension method
            //var result = reader.AsDataSet();
    
    
        //Read data into DataTable
        DataTable firstWorkSheet = reader.AsDataSet().Tables[0];
    
     //Save data into database table XYZ (Insert if new record; Update if record already exists)
    
        }
    }
    
    
            
    
               
    
                
            }


    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thanks, zhanglong.

    • What does FilterIndex = 2 mean?
    • How to fix the error ExcelFilterFactory ?

    As newbie, then how can I write the code for ODBC bulk copy (save DataTable to database)?

    https://www.howtosolutions.net/2016/07/dotnet-save-datatable-into-database-table/

    Thanks.


    • Edited by VA_er Thursday, March 21, 2019 9:35 PM
    Thursday, March 21, 2019 9:34 PM
  • Hi

    Thank you for posting here.

    According to your description, you want to save data into a database with your requirement.

    You could try the following code.

    private void button1_Click(object sender, EventArgs e)
            {
                var filePath = string.Empty;
                OpenFileDialog openFile = new OpenFileDialog();
                openFile.Filter = "Excel Files|*.xl*"; //Filter for excel file
                openFile.FilterIndex = 2;  //Don't know what it mean
                openFile.RestoreDirectory = true;
    
                if (openFile.ShowDialog() == DialogResult.OK)
                {
                    //Get the path of specified file
                    filePath = openFile.FileName;
                }
    
                //Read excel data into DataTable
                //Create COM Objects. Create a COM object for everything that is referenced
                Excel.Application xlApp = new Excel.Application();
                //filePath should already be open in the above, correct? It seems Open is not correct here?
                Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(filePath);
                //The excel file has only one sheet, with header, want to import Column A-G into database
                Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
                Excel.Range xlRange = xlWorksheet.UsedRange;
                int rowCount = xlRange.Rows.Count;
                int colCount = xlRange.Columns.Count;
                DataTable table = new DataTable();
                table.Columns.Add("Name", typeof(string));
                table.Columns.Add("ID", typeof(int));
                table.Rows.Add(xlRange.Cells[rowCount, 1].Value, xlRange.Cells[rowCount, 2].Value);
                string m = xlRange.Cells[rowCount, 1].Value;
                int n =Convert.ToInt32(xlRange.Cells[rowCount, 2].Value);
                string str = @"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=Test;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False";
                SqlConnection sqlConnection = new SqlConnection(str);
                sqlConnection.Open();
                string sql1 = String.Format("SELECT COUNT(*) from Excel where Name = '{0}' or ID = '{1}'",m,n);
                SqlCommand sqlCommand = new SqlCommand(sql1, sqlConnection);
                int userCount = (int)sqlCommand.ExecuteScalar();
                if (userCount==0)
                {
                    string Sql = String.Format("insert into Excel(Name, ID) values('{0}','{1}')", m, n);
                    SqlCommand sqlCommand1 = new SqlCommand(Sql, sqlConnection);
                    sqlCommand1.ExecuteNonQuery();
                   
                }
                else
                {
                    string Sql = String.Format("UPDATE Excel SET Name ='{0}' WHERE ID = '{1}' ", m, n);
                    SqlCommand sqlCommand1 = new SqlCommand(Sql, sqlConnection);
                    sqlCommand1.ExecuteNonQuery();
                    string Sql1 = String.Format("UPDATE Excel SET ID ='{0}' WHERE Name = '{1}' ", n, m);
                    SqlCommand sqlCommand2 = new SqlCommand(Sql1, sqlConnection);
                    sqlCommand2.ExecuteNonQuery();
    
                }
                sqlConnection.Close();
                MessageBox.Show("success");
            }
    

    Result:

    Best regards,

    Jack



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, March 22, 2019 1:12 AM
  • I think I can assign int colCount = 7, since I only want to copy column A:G , where is colCount used in your code? Could you explain what m/n mean?

    On your screen recording, I did not see how all records are uploaded to database table.

    Thank you for so much details, still have two more questions:

    How to modify your code to consider the case that excel file has title row (the row contains field names)? 

    How to match excel file field name with database table field name? Making sure the data does not go to wrong field.

    Thank you very much!



    Sample photo found online: when user uploads his/her excel file, the column order may be different. Therefore, the question will be: how to read each cell in title row, then match with database table field name.

    Edit: I have tried to write some code, not sure if it is correct. This is the logic I think I can go with, but I am new to C#, not sure if I write the syntax correctly.

    Maybe add messagebox for error message.

    Ultimately, I would like to get this logic into your code.

    Thanks.

               
    int C1SK = 0; //Store_key
    int C2MN = 0; //region_key
    int C3TC = 0; //store_name
    int C4VT = 0; //store_type
    int C5TL = 0; //street_address
    int C6VN = 0; //city
    int C7VA = 0; //state_province
    
    for (int i=1; i <= colCount; i++)
    {
    
        if ((string)(xlWorksheet.Cells[1, i]) == "Store_key")
            C1SK = i;
        else if ((string)(xlWorksheet.Cells[1, i]) == "region_key")
            C2MN = i;
        else if ((string)(xlWorksheet.Cells[1, i]) == "store_name")
            C3TC = i;
        else if ((string)(xlWorksheet.Cells[1, i]) == "store_type")
            C4VT = i;
        else if ((string)(xlWorksheet.Cells[1, i]) == "street_address")
            C5TL = i;
        else if ((string)(xlWorksheet.Cells[1, i]) == "city")
            C6VN = i;
        else if ((string)(xlWorksheet.Cells[1, i]) == "state_province")
            C7VA = i;
    
    
    }
    
    table.Columns.Add("Store_key", typeof(int));
    table.Columns.Add("region_key", typeof(int));
    table.Columns.Add("store_name", typeof(string));
    table.Columns.Add("store_type", typeof(string));
    table.Columns.Add("street_address", typeof(string));
    table.Columns.Add("city", typeof(string));
    table.Columns.Add("state_province", typeof(string));
    
    
    //table.Columns.Add("ID", typeof(int));
    
    for (int i = 2; i <= rowCount; i++)
    {
        table.Rows.Add(xlRange.Cells[i, C1SK].Value, xlRange.Cells[i, C2MN].Value, xlRange.Cells[i, C3TC].Value, xlRange.Cells[i, C4VT].Value, xlRange.Cells[i, C5TL].Value, xlRange.Cells[i, C6VN]Value, xlRange.Cells[i, C7VA].Value);
    }






    • Edited by VA_er Friday, March 22, 2019 8:45 PM
    Friday, March 22, 2019 2:25 AM