Bulk Insert of data from excel(without instaling excel)

Question

Hi i want to insert data from excel to sql server 2008 for 32 bit and 64 bit o.s(Bulk insert). without installing excel on server machine. plese do the needful Regards Balu reddy

Marina Eberhardt · Answer

Hi here is solution that does not require MS Office to be installed and that will work on both 32 and 64 bit OSs. using ( SqlConnection conn = new SqlConnection ( your-connection )) { conn.Open(); using ( SqlBulkCopy bulkCopy = new SqlBulkCopy (conn)) { // Load excel file. ExcelFile file = ExcelFile .Load( your-excel-file's-path-or-stream ); // Select first worksheet from the file. ExcelWorksheet sheet = file.Worksheets[0]; // Create DataTable object. DataTable table = sheet.CreateDataTable( new CreateDataTableOptions ()); bulkCopy.DestinationTableName = sheet.Name; bulkCopy.WriteToServer(table); } } I used this third party DLL for Excel files in C# . So in short you just need to read your excel file in .NET and after that you export the required excel sheet to a DataTable object and you write it to a server with SqlBulkCopy.

BaluReddy Kovvur · Answer

ok. please advice any third party dll.for both 32 bit and 64 bit. in my clent machine(application hosted) invironment.their company policy restricted to install excel. i have already used dll called excelpackage(codeplex)but it's not working properly for 64 bit. please advice. Regards Balu

BaluReddy Kovvur · Answer

ok. please advice any third party dll.for both 32 bit and 64 bit. in my clent machine(application hosted) invironment.their company policy restricted to install excel. i have already used dll called excelpackage(codeplex)but it's not working properly for 64 bit. please advice. Regards Balu

Hemant Kumar · Answer

Yes I agree with Suthish Nair You need to install the Office in your Machine.

Suthish Nair · Answer

Sorry to say if you want to do something with Office tools in .Net you must install it on hosting server. But lots of opensource available like one google have and it's free (don't remember the name do a browse). Another best avaliable opensource is EPPlus ( http://epplus.codeplex.com/ ). Read More for opensources..

BaluReddy Kovvur · Answer

string sExcelConnectionString = @Provider=Microsoft.Jet.OLEDB.4.0;Data Source= + excelFilePath + ;Extended Properties= + \Excel 8.0;HDR=YES;\ ; when iam trying to working with above connectionstring.i got error because i have no excel on my server machine. Regards Balu Reddy

BaluReddy Kovvur · Answer

Hi i have tried already the code u suggested.but it will not work for my case.because excel in not installed on server machine..(It seems microsoft provider is not useful without installing excel on machine)..

Hemant Kumar · Answer

If you already have data in MS Excel file and want to migrate your MS Excel data to SQL Server table follow below steps 1. Lets take an example to import the data to SQL Server table I am going to import student information data from ms excel sheet to tStudent SQL table My Excel sheet structure is looks like 2. Now design a tStudent table in SQL server Create Table ( StudentName varchar (64) RollNo varchar (16) Course varchar (32) ) your ms excel sheet and SQL table is ready now its time to write c# code to import the excel sheet into tStudent table 3. Add these two name space in your class file using System.Data.OleDb; using System.Data.SqlClient; Use following code public void importDataFromExcel( string excelFilePath) { //Declare Variables - Edit these based on your particular situation string sSQLTable = tDataMigrationTable ; // 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 StudentNameRollNoCourse 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 = SERVER=MyDatabaseServerName;USER ID=DBUserId;PASSWORD=DBUserPassword;DATABASE=DatabaseName;CONNECTION RESET=FALSE ; //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); } OleDbConn.Close(); } catch ( Exception ex) { //handle exception } } In above function you have to pass ms excel file path as a parameter if you want to import your data by providing client an access to select the excel file and import then you might have to use asp.net file control and upload the excel file on the server in some temp folder then use the file path of the upload excel file and pass the path in above function. Once data import is completed then you can delete temporary file.