locked
How can i achieve this RRS feed

  • Question

  • I have a excel Workbook with multiple Sheets.Each Sheet have multiple columns and data under that column.I want to  read the  data from each column  and insert it into a table in the database.What is the best way to Achieve it ? Please provide some code snippets
    Sheet1(Name)--Employee
    EmpID  EMPName
    1     Ram
    2     Jack
    3     John

    I want to insert it into the database table like
    Table Structure
    Col1 Col2
    1    Employee
    2    Employee
    3
    Ram   Employee
    Jack  Employee
    John  Employee
    Wednesday, July 26, 2017 5:32 PM

Answers

  • Hello

    I have done a demo about your question and it works well,I suggest you use SqlBulkCopy instead of insert statement.

    static void Main(string[] args)
            {
                 string connectionString = @"Data Source = (LocalDb)\MSSQLLocalDB; Initial Catalog = tempdb; Integrated Security = True";
                string path = @"D:\Test\123.xlsx";
                DataTable dt = GetDataTableFromExcel(path).Tables[0];
                DataTableToSQLServer(dt, connectionString);
            }
            public static void DataTableToSQLServer(DataTable dt, string connectString)
            {
               string connectionString = connectString;
                using (SqlConnection destinationConnection = new SqlConnection(connectionString))
               {
                    destinationConnection.Open();
                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection))
                    {
                        bulkCopy.DestinationTableName = "TestDT";
                        bulkCopy.BatchSize = dt.Rows.Count;
                        //you could set cloumMappings in there.And I'm confused about your table structure.
                        bulkCopy.ColumnMappings.Add(dt.Columns[0].ColumnName, "col1");
                        bulkCopy.ColumnMappings.Add(dt.Columns[1].ColumnName, "col2");
                        bulkCopy.WriteToServer(dt);
    
                    }
    
                }
    
            }
    
            public static DataSet GetDataTableFromExcel(string path)
            {
                string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;";
                //string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";
                    OleDbConnection conn = new OleDbConnection(strConn);
                    conn.Open();
                    string strExcel = "";
                    OleDbDataAdapter myCommand = null;
                    DataSet ds = null;
                    strExcel = "select * from [sheet1$]";
                    myCommand = new OleDbDataAdapter(strExcel, strConn);
                    ds = new DataSet();
                    myCommand.Fill(ds, "table1");
                    return ds;
            }

    Best regards,

    feih_7


    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.

    • Marked as answer by Rehan Mubarak Saturday, August 5, 2017 7:43 AM
    Thursday, July 27, 2017 7:45 AM

All replies

  • For reading Excel data like that you should probably just use ADO.NET and the ACE.OLEDB provider. There are plenty of examples of how to do so online.

    Michael Taylor
    http://www.michaeltaylorp3.net

    Wednesday, July 26, 2017 5:39 PM
  • This can be achieved using OLEDB. click Here to look at the sample implementation

    Wednesday, July 26, 2017 5:48 PM
  • Hello

    I have done a demo about your question and it works well,I suggest you use SqlBulkCopy instead of insert statement.

    static void Main(string[] args)
            {
                 string connectionString = @"Data Source = (LocalDb)\MSSQLLocalDB; Initial Catalog = tempdb; Integrated Security = True";
                string path = @"D:\Test\123.xlsx";
                DataTable dt = GetDataTableFromExcel(path).Tables[0];
                DataTableToSQLServer(dt, connectionString);
            }
            public static void DataTableToSQLServer(DataTable dt, string connectString)
            {
               string connectionString = connectString;
                using (SqlConnection destinationConnection = new SqlConnection(connectionString))
               {
                    destinationConnection.Open();
                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection))
                    {
                        bulkCopy.DestinationTableName = "TestDT";
                        bulkCopy.BatchSize = dt.Rows.Count;
                        //you could set cloumMappings in there.And I'm confused about your table structure.
                        bulkCopy.ColumnMappings.Add(dt.Columns[0].ColumnName, "col1");
                        bulkCopy.ColumnMappings.Add(dt.Columns[1].ColumnName, "col2");
                        bulkCopy.WriteToServer(dt);
    
                    }
    
                }
    
            }
    
            public static DataSet GetDataTableFromExcel(string path)
            {
                string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;";
                //string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";
                    OleDbConnection conn = new OleDbConnection(strConn);
                    conn.Open();
                    string strExcel = "";
                    OleDbDataAdapter myCommand = null;
                    DataSet ds = null;
                    strExcel = "select * from [sheet1$]";
                    myCommand = new OleDbDataAdapter(strExcel, strConn);
                    ds = new DataSet();
                    myCommand.Fill(ds, "table1");
                    return ds;
            }

    Best regards,

    feih_7


    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.

    • Marked as answer by Rehan Mubarak Saturday, August 5, 2017 7:43 AM
    Thursday, July 27, 2017 7:45 AM
  • Hi Feih,

    I tried the above code snippet .But it didn't give me my desired output.

    My desired output is 

    Col1  | Col2           | Col3

    1        Employee        1

    2        Employee         2

    3        Employee         3

    Ram    Employee         1

    Jack    Employee          2

    John    Employee          3

    Col1-- Stores the data from all the columns

    Col2-- Stores the Sheet name

    Col3 --Stores the row number 

    Thursday, July 27, 2017 3:55 PM
  • Hello Rehan Mubarak,

    I have updated my code and it works well.

    static void Main(string[] args)
            {
                string connectionString = @"Data Source = (LocalDb)\MSSQLLocalDB; Initial Catalog = tempdb; Integrated Security = True";
                string path = @"D:\Test\123.xlsx";
    
                DataSet dt = GetDataTableFromExcel(path);
                foreach(DataTable d in dt.Tables)
                DataTableToSQLServer(d, connectionString);
    
            }
            public static void DataTableToSQLServer(DataTable dt, string connectString)
            {
                string connectionString = connectString;
                using (SqlConnection destinationConnection = new SqlConnection(connectionString))
                {
                    destinationConnection.Open();
                    using (SqlCommand sqlcmd = destinationConnection.CreateCommand())
                    {
                        for (int i = 0; i < dt.Columns.Count; i++) {
                            for (int j = 0; j < dt.Rows.Count; j++) {
                                string strCmd = "insert into TestDT (col1,col2,col3) values ('"+dt.Rows[j][i].ToString()
                                +"','"+dt.TableName+"','"+(j+1)+"')";
                                sqlcmd.CommandText = strCmd;
                                sqlcmd.ExecuteNonQuery();
                            }
                        }   
                    }
    
                }
            }
    
            public static DataSet GetDataTableFromExcel(string path)
            {
                DataSet fds = new DataSet();
                string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;";
                //string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";
                    OleDbConnection conn = new OleDbConnection(strConn);
                    conn.Open();
                //get sheet name from excel.
                DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                String sheetNames;
                foreach (DataRow row in dt.Rows)
                {
                    sheetNames = row["TABLE_NAME"].ToString();
                    fds.Tables.Add(getSheetData(sheetNames,strConn).Tables[0].Copy());  
                }
                return fds;       
            }
    
            public static DataSet getSheetData(string sheetName,string strConn) {
                string strExcel = "";
                OleDbDataAdapter myCommand = null;
                DataSet ds = null;
                strExcel = "select * from ["+sheetName+"]";
                myCommand = new OleDbDataAdapter(strExcel, strConn);
                ds = new DataSet();
                myCommand.Fill(ds, sheetName);
                return ds;
            }

    Result:

    Best regards,

    feih-7


    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, July 28, 2017 4:48 AM