none
How to write C# code to upload excel data to database table? RRS feed

  • Question

  • Trying to explain the case as clearly as I can.

    Let us say, I have database connection string code (see below), which works fine. 

    Let us say, the database is xyz, and there is table called Customer under database xyz

    What I want to do: Upload the excel data into table Customer, see excel data screenshot.

    Now I would like to write code for btnUpload_Click , how should I write it? Or any other comments?

    Thanks.

    OdbcConnection Cn; 
    Username = txtUsername.Text;
    Password = txtPassword.Text;
    ConnectionString = "Driver={Adaptive Server Enterprise};server=*****;port=*****;db=xyz;uid=" + Username + ";pwd=" + Password + ";";
    
    Cn = new OdbcConnection(ConnectionString);
    //The code works fine for this part
    private void btnUpload_Click(object sender, EventArgs e)   //Don't know how to write the code for this part.
            {
                //Click the button to browse excel file, no hard code for file path.
                //The excel file has only one sheet, no hidden sheet
                //The excel filename could be anything, not necessarily same as database table name Customer.
                //Excel column order may not be same as database table Customer field order
                //Excel title row names (CustomerName,Address,City,PostalCode,Country) are same as database table Customer filed names, but may not be in the same order.
            }


    Sunday, March 10, 2019 1:24 AM

All replies

  • Hello,

    The following code shows how to read an Excel WorkSheet via OleDb. The file name is hard coded yet with a little work could be dynamic by using a OpenFileDialog to allow the user to select the Excel file.

    The class for creating a connection string  (SmartConnection) can be found here, copy and pasted into your project.

    If there is a chance there is more than one sheet name you can get sheet names using the following code SheetNames (in the same repository as SmarConnection), present the names to the user to select or do it in code.

    Note 1 in the SELECT statement I use SELECT * which is normally not wise but is fine in this case.

    Note 2 Once the data is read into the DataTable you have access to column names via dt.Columns to ensure all columns exists that are needed.

    using System;
    using System.Data;
    using System.Data.OleDb;
    using System.IO;
    
    namespace ReadSheetWithColumnHeadersOldDb
    {
        public class Operations
        {
            public DataTable ReadCustomersDataTable()
            {
                // this could be passed in where a OpenFileDialog was used to select the file
                var fileName = Path.Combine(AppDomain.CurrentDomain.BaseDirectory,"Customers.xlsx");
                var dt = new DataTable();
                var con = new SmartConnection();
    
                using (var cn = new OleDbConnection(con.ConnectionString(fileName, 0, ExcelHeader.Yes)))
                {
                    using (var cmd = new OleDbCommand {Connection = cn, CommandText = "SELECT * FROM [Customers$]" })
                    {
                        cn.Open();
                        dt.Load(cmd.ExecuteReader(CommandBehavior.CloseConnection) ?? throw new InvalidOperationException());
                    }
                }
    
    
                return dt;
            }
        }
    }
    

    Calling the above (and keep in mind you can pass the file name in rather than being hard coded)

    var ops = new Operations();
    var dt = ops.ReadCustomersDataTable();

    For pushing the DataTable to SQL-Server database table use SqlBulkCopy. The class above will not use a NuGet package BaseConnectionLibrary (this is mine) to create a connection to SQL-Server.  If by chance the connection library does not suit your connection then see the following page.

    The connection string is setup in the constructor. The method BatchBulkCopy accepts the DataTable read in from ReadCustomersDataTable method and copies the DataTable to the database table indicated in the argument destinationTable. You should not need to map but if you do the SqlBulkCopy object does column mapping e.g. sbc.ColumnMappings.Add.

    using System;
    using System.Data;
    using System.Data.OleDb;
    using System.Data.SqlClient;
    using System.IO;
    using BaseConnectionLibrary.ConnectionClasses;
    
    namespace ReadSheetWithColumnHeadersOldDb
    {
        public class Operations : SqlServerConnection
        {
            public Operations()
            {
                // server name, in this case my server name, could be .\SQLEXPRESS or a name instance
                DatabaseServer = "KARENS--PC";
                // database name 
                DefaultCatalog = "NorthWind";
            }
            public DataTable ReadCustomersDataTable()
            {
                // this could be passed in where a OpenFileDialog was used to select the file
                var fileName = Path.Combine(AppDomain.CurrentDomain.BaseDirectory,"Customers.xlsx");
                var dt = new DataTable();
                var con = new SmartConnection();
    
                using (var cn = new OleDbConnection(con.ConnectionString(fileName, 0, ExcelHeader.Yes)))
                {
                    using (var cmd = new OleDbCommand {Connection = cn, CommandText = "SELECT * FROM [Customers$]" })
                    {
                        cn.Open();
                        dt.Load(cmd.ExecuteReader(CommandBehavior.CloseConnection) ?? throw new InvalidOperationException());
                    }
                }
    
    
                return dt;
            }
    
            public void BatchBulkCopy(DataTable dataTable, string destinationTable)
            {
                // Get the DataTable 
                var dtInsertRows = dataTable;
    
                using (var sbc = new SqlBulkCopy(ConnectionString, SqlBulkCopyOptions.KeepIdentity))
                {
                    sbc.DestinationTableName = destinationTable;
                    // write to server
                    sbc.WriteToServer(dtInsertRows);
                }
            }
        }
    }
    


    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

    Sunday, March 10, 2019 11:25 AM
    Moderator
  • Thanks for the information, I will try the code when I have time (I will need workplace computer to access database).
    Sunday, March 10, 2019 6:45 PM
  • If you're ok with 3rd party library, you can try free spire.xls (available on NuGet)and the code below to import excel data to a data table.

    Workbook workbook = new Workbook();
    workbook.LoadFromFile(@"FilePath");
    Worksheet sheet = workbook.Worksheets[0];
    DataTable dt = sheet.ExportDataTable();
    Then it's easy to insert data table to database. 
     

    Monday, March 11, 2019 3:49 AM
  • Hi

    Is your problem solved? If so, please post "Mark as answer" to the appropriate answer, so that it will help other members to find the solution quickly if they face a similar issue.

    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 15, 2019 5:14 AM
    Moderator
  • Sorry that I have not tried this part of code yet, recently I am building a demo project with various features, as learning purpose on my spare time.

    Maybe I ask this question a bit earlier, but I will get to this part of code later. I will not forget to update this thread.

    Thanks for reminding.

    Friday, March 15, 2019 12:01 PM
  • Hi

    How are things going? Please let us know if there is any progress.

    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.

    Monday, March 25, 2019 8:03 AM
    Moderator
  • Not yet, new to C#, new to Visual Studio, could not understand some part of above sample code, still need to spend time and study more. Could not figure out code, unless I feel fairly confident about the code, then I will test the code on workplace database, right now just don't want to mess up with workplace database.

    I have not found any code properly addressing column name issue, what I mean is: the excel file column order may be different, without checking column name (the name on the header row), I am concerned if the data could be inaccurately loaded to wrong field name in the table. Let us say, database table has many fields, two of the fields are: ID (datatype: int) & Category (datatype: int). There are two excel files: file #1 has ID in column A and Category in column B, file #2 has category in column A and ID in column B, how can I guaranteed that both files can be loaded correctly.

    It is sybase, and I use Odbc connection string for my program. I am not sure if SqlBulkCopy works for Sybase or not, I mean whether I need to write it as OdbcBulkCopy or something else.

    Need error handling when database rejects some records.




    • Edited by VA_er Monday, March 25, 2019 11:53 AM
    Monday, March 25, 2019 11:45 AM
  • You can also try plain old ADO.NET.

    It is as simple as

    1. Open connection to your Excel sheet.

    2. Read data using a simple select into a dataset.

    3. Open connection to your target DB.

    4. Save DataSet to the target.

    Monday, March 25, 2019 12:25 PM
  • Yes, for people having experience, it is easy. But for newbie, it is not easy, don't know some concepts and don't know some syntax.
    Monday, March 25, 2019 12:29 PM
  • Hi

    Based on your description, I make a simple code for your reference.

      
     static DataTable excel()
            {
                using (OleDbConnection conn = new OleDbConnection())
                {
                    string path = @"D:\test1.xlsx";
                    DataTable dt = new DataTable();
                    conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";" + "Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1;MAXSCANROWS=0'";
                    using (OleDbCommand comm = new OleDbCommand())
                    {
                        string sheetName = "Sheet1";
                        comm.CommandText = "Select * from [" + sheetName + "$]";
                        comm.Connection = conn;
                        using (OleDbDataAdapter da = new OleDbDataAdapter())
                        {
                            da.SelectCommand = comm;
                            da.Fill(dt);
                            return dt;
                        }
                    }
                }
            }
    
            private void button1_Click(object sender, EventArgs e)
            {
                DataTable table = excel();
                dataGridView1.DataSource = table;
                OleDbConnection dbConnection = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Documents\Database1.mdb");
                dbConnection.Open();
                OleDbDataAdapter dataAdapter = new OleDbDataAdapter("select *from Customer ",dbConnection);
                OleDbCommandBuilder cb = new OleDbCommandBuilder(dataAdapter);
                cb.QuotePrefix = "[";
                cb.QuoteSuffix = "]";
                DataSet ds = new DataSet();
                dataAdapter.Fill(ds, "demo");
                foreach (DataRow tempRow in table.Rows)
                {
                    DataRow dr = ds.Tables["demo"].NewRow();
                    dr.ItemArray = tempRow.ItemArray;
                    ds.Tables["demo"].Rows.Add(dr);
                }
                dataAdapter.Update(ds, "demo");
                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.




    Wednesday, March 27, 2019 7:18 AM
    Moderator
  • Hi

    Based on your description, I make a simple code for your reference.

      
     static DataTable excel()
            {
                using (OleDbConnection conn = new OleDbConnection())
                {
                    string path = @"D:\test1.xlsx";
                    DataTable dt = new DataTable();
                    conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";" + "Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1;MAXSCANROWS=0'";
                    using (OleDbCommand comm = new OleDbCommand())
                    {
                        string sheetName = "Sheet1";
                        comm.CommandText = "Select * from [" + sheetName + "$]";
                        comm.Connection = conn;
                        using (OleDbDataAdapter da = new OleDbDataAdapter())
                        {
                            da.SelectCommand = comm;
                            da.Fill(dt);
                            return dt;
                        }
                    }
                }
            }
    
            private void button1_Click(object sender, EventArgs e)
            {
                DataTable table = excel();
                dataGridView1.DataSource = table;
                OleDbConnection dbConnection = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Documents\Database1.mdb");
                dbConnection.Open();
                OleDbDataAdapter dataAdapter = new OleDbDataAdapter("select *from Customer ",dbConnection);
                OleDbCommandBuilder cb = new OleDbCommandBuilder(dataAdapter);
                cb.QuotePrefix = "[";
                cb.QuoteSuffix = "]";
                DataSet ds = new DataSet();
                dataAdapter.Fill(ds, "demo");
                foreach (DataRow tempRow in table.Rows)
                {
                    DataRow dr = ds.Tables["demo"].NewRow();
                    dr.ItemArray = tempRow.ItemArray;
                    ds.Tables["demo"].Rows.Add(dr);
                }
                dataAdapter.Update(ds, "demo");
                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.




    Thanks for your time. I don't really follow your code, but I don't think I will go down this road, for two reasons:

    1) I am very concerned about ("select *from Customer ",dbConnection) , not sure if I need to query whole database table. If there are a few millions of records, this query must be very time consuming. 

    2) foreach (DataRow tempRow in table.Rows) : Just take it as an example, not sure if how much time is needed if trying to push 5000 records (insert/update) into a database table with 6 millions records already. Probably I will need to do some more studying when I have time.

    Thanks.

    Wednesday, March 27, 2019 8:51 PM
  • Hi

    >>I am very concerned about ("select *from Customer ",dbConnection)

    You could try the way in the following link to improve the performance of querying a whole database.

    https://stackoverflow.com/questions/10204790/code-performance-sql-server-query-vs-c-net-web-application

    >>not sure if how much time is needed if trying to push 5000 records (insert/update) into a database table with 6 millions records already.

    You could refer to the following code to improve performance.

    foreach (DataRow tempRow in table.Rows.AsParallel())

    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.


    Thursday, March 28, 2019 5:12 AM
    Moderator