How to Upload a Excel File to SQL Database Table Using C# Windows Form Application

how to Upload a excel file to sql database table using c# windows form application

using System;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace IMPORT
{
public partial class Form1 : Form
{
    public Form1()
    {
        InitializeComponent();
    }
    String MyConString = "SERVER=******;" +
           "DATABASE=db;" +
           "UID=root;" +
           "PASSWORD=pws;";

private void btnSelectFile_Click(object sender, EventArgs e)
    {
        OpenFileDialog openfiledialog1 = new OpenFileDialog();
        openfiledialog1.ShowDialog();
        openfiledialog1.Filter = "allfiles|*.xls";
        txtfilepath.Text = openfiledialog1.FileName;
    }
private void btnUpload_Click(object sender, EventArgs e)
{
 string path = txtfilepath.Text;

        string ConnString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties = Excel 8.0";

        DataTable Data = new DataTable();

        using (OleDbConnection conn =new OleDbConnection(ConnString))
        {
            conn.Open();

            OleDbCommand cmd = new OleDbCommand(@"SELECT * FROM [dataGridView1_Data$]", conn);
            OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);
            adapter.Fill(Data);

            conn.Close();
        }
        string ConnStr = MyConString;
        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(ConnStr))
        {
            bulkCopy.DestinationTableName = "TABLE NAME";
            bulkCopy.ColumnMappings.Add("userid", "userid");
            bulkCopy.ColumnMappings.Add("password", "password");
            bulkCopy.ColumnMappings.Add("first_name", "first_name");
            bulkCopy.ColumnMappings.Add("last_name", "last_name");
            bulkCopy.ColumnMappings.Add("user_group", "user_group");
            bulkCopy.WriteToServer(Data);
            MessageBox.Show("UPLOAD SUCCESSFULLY");
        }
     }
 }

An example found http://technico.qnownow.com/bulk-copy-data-from-excel-to-destination-db-using-sql-bulk-copy/.
And
ERROR: Additional information: External table is not in the expected format

How to insert data from excel file into microsoft localdb database using C#

You already have a datatable (DtSet.Tables[0]), all you need is to bulkcopy that datatable to SQL.
check this answer:
Insert entire DataTable into database at once instead of row by row?

Uploading an Excel sheet and importing the data into SQL Server database

You are dealing with a HttpPostedFile; this is the file that is "uploaded" to the web server. You really need to save that file somewhere and then use it, because...

...in your instance, it just so happens to be that you are hosting your website on the same machine the file resides, so the path is accessible. As soon as you deploy your site to a different machine, your code isn't going to work.

Break this down into two steps:

1) Save the file somewhere - it's very common to see this:

string saveFolder = @"C:\temp\uploads"; //Pick a folder on your machine to store the uploaded files

string filePath = Path.Combine(saveFolder, FileUpload1.FileName); 

FileUpload1.SaveAs(filePath);

Now you have your file locally and the real work can be done.

2) Get the data from the file. Your code should work as is but you can simply write your connection string this way:

string excelConnString = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties="Excel 12.0";", filePath);

You can then think about deleting the file you've just uploaded and imported.

To provide a more concrete example, we can refactor your code into two methods:

    private void SaveFileToDatabase(string filePath)
    {
        String strConnection = "Data Source=.\\SQLEXPRESS;AttachDbFilename='C:\\Users\\Hemant\\documents\\visual studio 2010\\Projects\\CRMdata\\CRMdata\\App_Data\\Database1.mdf';Integrated Security=True;User Instance=True";

        String excelConnString = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0\"", filePath);
        //Create Connection to Excel work book 
        using (OleDbConnection excelConnection = new OleDbConnection(excelConnString))
        {
            //Create OleDbCommand to fetch data from Excel 
            using (OleDbCommand cmd = new OleDbCommand("Select [ID],[Name],[Designation] from [Sheet1$]", excelConnection))
            {
                excelConnection.Open();
                using (OleDbDataReader dReader = cmd.ExecuteReader())
                {
                    using(SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection))
                    {
                        //Give your Destination table name 
                        sqlBulk.DestinationTableName = "Excel_table";
                        sqlBulk.WriteToServer(dReader);
                    }
                }
            }
        } 
    }


    private string GetLocalFilePath(string saveDirectory, FileUpload fileUploadControl)
    {


        string filePath = Path.Combine(saveDirectory, fileUploadControl.FileName);

        fileUploadControl.SaveAs(filePath);

        return filePath;

    }

You could simply then call SaveFileToDatabase(GetLocalFilePath(@"C:\temp\uploads", FileUpload1));

Consider reviewing the other Extended Properties for your Excel connection string. They come in useful!

Other improvements you might want to make include putting your Sql Database connection string into config, and adding proper exception handling. Please consider this example for demonstration only!

how to import Excel file data into SQLite database in c# windows form?

You could open a direct connection to the Excel file using OleDbConnection, open an OleDbDataReader on that connection, and then read row by row into the SQLite connection.

Saving excel file in application folder and uploading the content to SQL Server using windows forms and C#

The replacement of FileUpload in WinForm is OpenFileDialog:

string excelPath = "";
using(OpenFileDialog ofd = new OpenFileDialog())
{
  ofd.Filter = "Excel(.xls)|*.xls|Excel(.xlsx)|*.xlsx|All Files (*.*)|*.*";
  ofd.Multiselect = false;
  var result = ofd.ShowDialog();
  if (result == DialogResult.OK)
  {
    excelPath = ofd.FileName;
  }
}
//... continue with your sql code

The code for uploading to sql server is still the same, you can use it also in winform. From the sample above, "excelPath" is in my sample "ofd.FileName"

How can we import excel file to Sql server table where excel table columns are not in order as Sql table

Edited: Added another example at the end.

One of many ways of implementing what you ask is to import worksheet as a datatable in c# and then Insert the data with SqlBulkCopy (SqlBulkCopy (MSDN)). This method is better for large files because SqlBulkCopy uses bulk insert command.

For the first step (import file as datatable) you have many options such as using OLEDB for xls or xlsx (you can use my example or others such as this link or this), using third-party libraries such as easyxls.

using System;
using System.Drawing;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel; 

namespace WindowsApplication1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            try
            {
                System.Data.OleDb.OleDbConnection MyConnection ;
                System.Data.DataSet DtSet ;
                System.Data.OleDb.OleDbDataAdapter MyCommand ;
                MyConnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='c:\\csharp.net-informations.xls';Extended Properties=Excel 8.0;");
                MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection);
                MyCommand.TableMappings.Add("Table", "TestTable");
                DtSet = new System.Data.DataSet();
                MyCommand.Fill(DtSet);
                dataGridView1.DataSource = DtSet.Tables[0];
                MyConnection.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show (ex.ToString());
            }
        }
   }
}

After that for second step you can use SQLBulkCopy with column mapping to map the dataTable columns to your database table columns.

using System.Data.SqlClient;

class Program
{
    static void Main()
    {
        string connectionString = GetConnectionString();
        // Open a sourceConnection to the AdventureWorks database.
        using (SqlConnection sourceConnection =
                   new SqlConnection(connectionString))
        {
            sourceConnection.Open();

            // Perform an initial count on the destination table.
            SqlCommand commandRowCount = new SqlCommand(
                "SELECT COUNT(*) FROM " +
                "dbo.BulkCopyDemoDifferentColumns;",
                sourceConnection);
            long countStart = System.Convert.ToInt32(
                commandRowCount.ExecuteScalar());
            Console.WriteLine("Starting row count = {0}", countStart);

            // Get data from the source table as a SqlDataReader.
            SqlCommand commandSourceData = new SqlCommand(
                "SELECT ProductID, Name, " +
                "ProductNumber " +
                "FROM Production.Product;", sourceConnection);
            SqlDataReader reader =
                commandSourceData.ExecuteReader();

            // Set up the bulk copy object.
            using (SqlBulkCopy bulkCopy =
                       new SqlBulkCopy(connectionString))
            {
                bulkCopy.DestinationTableName =
                    "dbo.BulkCopyDemoDifferentColumns";

                // Set up the column mappings by name.
                SqlBulkCopyColumnMapping mapID =
                    new SqlBulkCopyColumnMapping("ProductID", "ProdID");
                bulkCopy.ColumnMappings.Add(mapID);

                SqlBulkCopyColumnMapping mapName =
                    new SqlBulkCopyColumnMapping("Name", "ProdName");
                bulkCopy.ColumnMappings.Add(mapName);

                SqlBulkCopyColumnMapping mapMumber =
                    new SqlBulkCopyColumnMapping("ProductNumber", "ProdNum");
                bulkCopy.ColumnMappings.Add(mapMumber);

                // Write from the source to the destination.
                try
                {
                    bulkCopy.WriteToServer(reader);
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                }
                finally
                {
                    // Close the SqlDataReader. The SqlBulkCopy
                    // object is automatically closed at the end
                    // of the using block.
                    reader.Close();
                }
            }

            // Perform a final count on the destination 
            // table to see how many rows were added.
            long countEnd = System.Convert.ToInt32(
                commandRowCount.ExecuteScalar());
            Console.WriteLine("Ending row count = {0}", countEnd);
            Console.WriteLine("{0} rows were added.", countEnd - countStart);
            Console.WriteLine("Press Enter to finish.");
            Console.ReadLine();
        }
    }

    private static string GetConnectionString()
        // To avoid storing the sourceConnection string in your code, 
        // you can retrieve it from a configuration file. 
    {
        return "Data Source=(local); " +
            " Integrated Security=true;" +
            "Initial Catalog=AdventureWorks;";
    }
}

another example of SqlBulkCopy usage:

public bool CopyTransactionDataToTable(DataTable Dt, long ProductID)
    {

    try
    {
        SqlBulkCopy copy = new SqlBulkCopy(Adapter.GetActiveConnection().ConnectionString);
        Collection = mapping.LoadMappedNameEntityByProductID(ProductID);

        copy.ColumnMappings.Add("ProductID", "ProductID");
        copy.ColumnMappings.Add("ResellerID", "ResellerID");
        copy.ColumnMappings.Add("Status", "Status");
        copy.ColumnMappings.Add("PK_ID", "TxID");
        copy.DestinationTableName = "TBLProdect";
        copy.BulkCopyTimeout = ConfigurationSettings.AppSettings.Get(UIConstants.SQLTimeOut).ToInt32();
        copy.WriteToServer(Dt);
        Adapter.CommandTimeOut = copy.BulkCopyTimeout;
        return true;
    }
    catch (Exception ex)
    {
        Log.Error(ex);
        return false;
    }
}

---

Related Topics