Import Excel Data to SQL Server in ASP. NETToday we will learn how to import data from an Excel sheet to SQL Server in ASP.
NET. This is useful for many users who want to work with an Excel file and wants to save it or Export to the Database. So that it can be fetched anytime from there using a database. In this article I will show how to export the data from Excel to a database in an ASP. NET application. In my ASP.
SQL Server Integration Services provide a versatile way of reading Excel files into SQL Server. A task like this illustrates the advantages of the graphical approach. This article explains how to create an SSIS package/project, control task, data task and running that SSIS package to import table data from a SQL Server database to.
NET application a file is uploaded to a web server and then imports the file into SQL using the SQLBulk. Copy Class. Here are the steps to be followed. Step 1. In this step will create an Excel file that contains some data. Step 2. To import an Excel file into SQL Server we need to create a table in the database. While creating the table you will make sure that the Scehma of the table is the same as the data in the Excel Sheet. Such as the same column names and data types of each domain.
Step 3. Now, we will create an ASP. NET application to import the Excel data to SQL Server. Step 4. In this application I will use a File.
Upload Control to upload the Excel file and a Button to import data from Excel to SQL Server. Here is coding of the .
File. Upload. ID="File. Upload. 1"runat="server"/> < asp: Button. ID="Button. 1"runat="server"Text="Export"On. Click="btn. Upload_Click"/> < /div> < /form> < /body> < /html> Step 5.
- SQL SERVER – Import CSV File Into SQL Server Using Bulk Insert – Load Comma Delimited File Into SQL Server.
- TRANSFER SQL SERVER DATA TO EXCEL FILE. Step 1: Create a SQL Server BI project and add a Data Flow Task. In this section, you will be creating a Business Intelligence.
- By: Kun Lee | Read Comments (26) | Related Tips: 1 | 2 | 3 | 4 | 5 | More > Microsoft Excel Integration. Problem Loading data from SQL Server to Excel is a common.
- By: Greg Robidoux | Read Comments (27) | Related Tips: More > Integration Services Development Problem. When loading data using SQL Server Integration Services (SSIS.
Here is the code of the . You need to include following Namespaces in the . System. Data. Common; using System. Data. Ole. Db; using System.
Data. Sql. Client; First I saved the uploaded file to server application to the folder Uploaded File. File. Upload. 1. Has. File){ string path = string.
Concat(Server. Map. Path("~/Uploaded Folder/" + File. Upload. 1. File. Name)); File. Upload. 1. Save. As(path); }Create the connection string for the Excel file to read it's data. Here , we use an Ole.
Db. Connection and call Execute. Reader to read each row from the Excel file. Connection String to Excel Workbookstring excel.
Connection. String = string. Format("Provider=Microsoft. ACE. OLEDB. 1. 2. Data Source={0}; Extended Properties=Excel 8. Ole. Db. Connection connection = new.
Ole. Db. Connection(); connection. Connection. String = excel. Connection. String; Ole. Db. Command command = new. Ole. Db. Command("select * from [Sheet.
Open(); // Create Db. Data. Reader to Data Worksheet. Db. Data. Reader dr = command. Execute. Reader(); Then, I create other connection string for SQL Server database to export the data from the Excel sheet to be saved into the desired table.
Now, it's time to work with SQLBulk. Copy to save the Excel data into SQL Server table.
Here you need to give the destination table name where the data get stored in Database and at last call the Writeto. Server method to inset the Excel data row into the table. // SQL Server Connection String string sql. Connection. String = @"Data Source=MYPC; Initial Catalog=Student; User ID=sa; Password=wintellect"; // Bulk Copy to SQL Server. Sql. Bulk. Copy bulk.
Insert = new. Sql. Bulk. Copy(sql. Connection.
String); bulk. Insert. Destination. Table.
Name = "Student_Record"; bulk. Insert. Write. To.
Server(dr); Here is the full code of the . System; using System.
Collections. Generic; using System. Data. Common; using System.
Data. Ole. Db; using System. Data. Sql. Client; using System.
Linq; using System. Web; using System. Web. UI; using System. Web. UI. Web. Controls; namespace Import. Data. From. Excel.
To. SQl. Server{ publicpartialclass. Web. Form. 1 : System. Web. UI. Page { protectedvoid btn. Upload_Click(object sender, Event. Args e) { if (!
File. Upload. 1. Has. File) { try { string path = string. Concat(Server. Map. Path("~/Uploaded Folder/" + File.
Upload. 1. File. Name)); File. Upload. 1. Save. As(path); // Connection String to Excel Workbook string excel. Connection. String = string.
Format("Provider=Microsoft. ACE. OLEDB. 1. 2. Data Source={0}; Extended Properties=Excel 8. Ole. Db. Connection connection = new. Ole. Db. Connection(); connection. Connection. String = excel. Connection. String; Ole.
Db. Command command = new. Ole. Db. Command("select * from [Sheet. Open(); // Create Db. Data. Reader to Data Worksheet Db.
Data. Reader dr = command. Execute. Reader(); // SQL Server Connection String string sql. Connection. String = @"Data Source=MYPC; Initial Catalog=Student; User ID=sa; Password=wintellect"; // Bulk Copy to SQL Server Sql. Bulk. Copy bulk. Insert = new. Sql. Bulk. Copy(sql. Connection. String); bulk. Insert. Destination.
Table. Name = "Student_Record"; bulk. Insert. Write. To. Server(dr); Label. Text = "Ho Gaya"; } catch(Exception ex) { Label. Text = ex. Message; } } } }}Step 6. Now, build the application and run it to see the output. Select the file using the File.
Upload control and click on the Export Button. You will see that the data of the Excel sheet is being imported into the specified table in the database.