Import Data from Spreadsheet to any Database in C#

[4513 views]




In this article, I am explaining how you can import data from a excel spreadsheet into a SQL server database by C# code. Following are the steps:
  1. Create a table in the server in the database with the required columns you have to export from the spreadsheet. Below is an example of table creation:
    SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[Table1]( [student] [varchar](50) NULL, [rollno] [int] NULL, [course] [varchar](50) NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO
  2. Prepare an excel sheet with the required columns.

    How to import data from spreadsheet to any database by Microsoft csharp code

  3. Write a code to read data from the excel sheet present in the path. This uses the sqlcommand class to read the data from the excel and store it in SQL server table.
    public void ImportDataFromExcel(string excelFilePath) { //declare variables - edit these based on your particular situation string ssqltable = "Table1"; // make sure your sheet name is correct, here sheet name is sheet1, so you can change your sheet name if have different string myexceldataquery = "select student,rollno,course from [Sheet1$]"; try { //create our connection strings string sexcelconnectionstring = @"provider=microsoft.jet.oledb.4.0;data source=" + excelFilePath + ";extended properties=" + "\"excel 8.0;hdr=yes;\""; string ssqlconnectionstring = "Data Source=SAYYED;Initial Catalog=SyncDB;Integrated Security=True"; //execute a query to erase any previous data from our destination table string sclearsql = "delete from " + ssqltable; SqlConnection sqlconn = new SqlConnection(ssqlconnectionstring); SqlCommand sqlcmd = new SqlCommand(sclearsql, sqlconn); sqlconn.Open(); sqlcmd.ExecuteNonQuery(); sqlconn.Close(); //series of commands to bulk copy data from the excel file into our sql table OleDbConnection oledbconn = new OleDbConnection(sexcelconnectionstring); OleDbCommand oledbcmd = new OleDbCommand(myexceldataquery, oledbconn); oledbconn.Open(); OleDbDataReader dr = oledbcmd.ExecuteReader(); SqlBulkCopy bulkcopy = new SqlBulkCopy(ssqlconnectionstring); bulkcopy.DestinationTableName = ssqltable; while (dr.Read()) { bulkcopy.WriteToServer(dr); } dr.Close(); oledbconn.Close(); Label1.Text = "File imported into sql server successfully."; } catch (Exception ex) { //handle exception } }
                 

Updated on 24 FEBRUARY, 2021 by Shaddy




AI Search Engine:



Comments










Search Anything:

Sponsored Deals ends in 8:59


E-Books & Study Notes:






Technical Quizzes Specially For You:



Online Free Tools:
Search Tags

    Fetch Data from Spreadsheet to SQL Database using C#

    C# program to move data to Database from Spreadsheet

    Spreadsheet to Database conversion using C#

You Might Also Like