RSS

Tag Archives: EXCEL

Import data from Excel to SQL Server


in this article I’m going to show you how to import data from Excel to SQL Server in few steps but first take a look to import/export screen

image

As you can see the screen is so simple it’s have three controls

  • FileUpload control to help you to find the location of your Excel file.
  • Button to import data from Excel and save it in SQL Server.
  • Label control which is just message to tell of operation result(Success or Fail)

Steps to make application run successfully

Note: before start reading the steps I want let you to know that I haven’t test this sample either on ASP.NET 3.5 or 32-bit MS Office,so may be you do not need the step 1 and step 2.

  1. Create an IIS web site
  2. Change .Net Framework to 4.0 for Application Pool of this site
  3. Create Excel file and make sure that file contain the column name as following image

image

4.Create Table in SQL Server and make sure has the same Columns name with appropriate columns data type.

image

The following code snippet will show you how upload file in import data from it and save it to SQL Server.

In C#

// if you have Excel 2007 uncomment this line of code
//  string excelConnectionString =string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0",path);

string ExcelContentType = "application/vnd.ms-excel";
string Excel2010ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
if (FileUpload1.HasFile)
{
//Check the Content Type of the file
if(FileUpload1.PostedFile.ContentType==ExcelContentType || FileUpload1.PostedFile.ContentType==Excel2010ContentType)
{
try
{
//Save file path
string path = string.Concat(Server.MapPath("~/TempFiles/"), FileUpload1.FileName);
//Save File as Temp then you can delete it if you want
FileUpload1.SaveAs(path);
//string path = @"C:\Users\Johnney\Desktop\ExcelData.xls";
//For Office Excel 2010  please take a look to the followng link  http://social.msdn.microsoft.com/Forums/en-US/exceldev/thread/0f03c2de-3ee2-475f-b6a2-f4efb97de302/#ae1e6748-297d-4c6e-8f1e-8108f438e62e
string excelConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 8.0", path);

// Create Connection to Excel Workbook
using (OleDbConnection connection =
new OleDbConnection(excelConnectionString))
{
OleDbCommand command = new OleDbCommand
("Select * FROM [Sheet1$]", connection);

connection.Open();

// Create DbDataReader to Data Worksheet
using (DbDataReader dr = command.ExecuteReader())
{

// SQL Server Connection String
string sqlConnectionString = @"Data Source=.\sqlexpress;Initial Catalog=ExcelDB;Integrated Security=True";

// Bulk Copy to SQL Server
using (SqlBulkCopy bulkCopy =
new SqlBulkCopy(sqlConnectionString))
{
bulkCopy.DestinationTableName = "Employee";
bulkCopy.WriteToServer(dr);
Label1.Text = "The data has been exported succefuly from Excel to SQL";
}
}
}
}

catch (Exception ex)
{
Label1.Text = ex.Message;
}
}
}

in VB.NET

'if you have Excel 2007 uncomment this line of code
'  string excelConnectionString =string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0",path);
'Define the content type
Dim ExcelContentType As String = "application/vnd.ms-excel"
Dim Excel2010ContentType As String = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
If FileUpload1.HasFile Then
If FileUpload1.PostedFile.ContentType = ExcelContentType Or FileUpload1.PostedFile.ContentType = Excel2010ContentType Then
Try
'Save file path
Dim path As String = String.Concat(Server.MapPath("~/TempFiles/"), FileUpload1.FileName)
'Save File as Temp then you can delete it if you want
FileUpload1.SaveAs(path)
'For Office Excel 2010  please take a look to the followng link  http://social.msdn.microsoft.com/Forums/en-US/exceldev/thread/0f03c2de-3ee2-475f-b6a2-f4efb97de302/#ae1e6748-297d-4c6e-8f1e-8108f438e62e
Dim excelConnectionString As String = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 8.0", path)

' Create Connection to Excel Workbook
Using connection As New OleDbConnection(excelConnectionString)

Dim Command As OleDbCommand = New OleDbCommand("Select * FROM [Sheet1$]", connection)

connection.Open()

'Create DbDataReader to Data Worksheet
Using reader As DbDataReader = Command.ExecuteReader()

' SQL Server Connection String
Dim sqlConnectionString As String = "Data Source=.\sqlexpress;Initial Catalog=ExcelDB;Integrated Security=True"

' Bulk Copy to SQL Server
Using bulkCopy As New SqlBulkCopy(sqlConnectionString)

bulkCopy.DestinationTableName = "Employee"
bulkCopy.WriteToServer(reader)
Label1.Text = "The data has been exported succefuly from Excel to SQL"
End Using
End Using
End Using
Catch ex As Exception
Label1.Text = ex.Message
End Try
End If
End If

Run and test Application

after following the above steps you can run web and click on the browse then select Excel file then click import to save Excel file data on SQL Server side.

Download the sample

You can download code from the following link ( Please do not forget to rate it)

http://code.msdn.microsoft.com/Imoprt-Data-from-Excel-to-705ecfcd

References

I hope you find this sample useful and i will be happy to answer your questions.

Regards.

 
1 Comment

Posted by on March 20, 2012 in ASP.NET, C#, VB.NET

 

Tags: , , , ,

تصدير البيانات من ملف إكسل إلى قواعد بيانات SQL SERVER


سوف نتحدث اليوم عن كيفية تصدير البيانات من ملف إكسل (Excel) إلى قواعد بيانات (MS SQL Server) ولكن قبل أن نبدء هنالك قواعد يجب أن تتبع
•    يجب أن تضع عناوين (Header)  للبيانات التي لديك في الصف الأول بمعنى أخر يجب أن تكون لديك أعمدة ذات أسماء كما هو موضح في الصور التالية

•    يجب أن تقوم بتصميم جدول في قاعدة بيانات (SQL Server) بنفس الأسماء إذا أردت (غير ضروري) مع إختيار أنواع البيانات المناسبة (Data Type) مثلا (int,varchar2,numeric…). مع أهمية الترتيب في تصميم الأعمدة فكما ترى في الأعلى لدينا ملف أكسل يحتوي على عمود إسمه (ID) وعمود أخر إسمه (Name).إذا عند تصميم الجدول تنشيء عمود إسم (ID) ثم عمود أخر إسمه (Name) على التوالي.
والان بعد أن رأينا القواعد نأتي الان الى صلب الموضوع وهو كيف يمكن كتابة شفرة (Code) لنقل الملفات
حتى نسهل الموضوع سوف أضع الشفرة على أجزاء بخطوات
اولا نضع معلومات الخاصة بنص الإتصال (Connection String) في متغير نصي

string excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\xls\\ExcelFile.xls ;Extended Properties=Excel 8.0";

طبعا هذه المعلومات ثابتة ماعدا جزئية مسار ملف الاكسل يجب عليك تغيرها الى مسار ملفك
ثانيا قم بالإتصال بقاعدة البيانات عن طريق إستخدام (OleDbConnection) وقم بإرسال نص الإتصال إليه

using (OleDbConnection connection =
new OleDbConnection(excelConnectionString))
{

ثالثا قم بإنشاء كائن من نوع (OleDbCommand)   واكتب إستعلام الإختيار (Select Statment) وايضا حدد له كائن الإتصال كالتالي

OleDbCommand command = new OleDbCommand
("Select * FROM [Sheet1$]", connection);

لاحظ معي جملة الإتصال ولاحظ أن إسم الجدول هو إسم الورقة (Sheet) طبعا يمكنك تغيره من ملف إكسل ولاحظ أيظا أنك يجب أن تتبعها مباشرة وبدون فواصل رمز الدولار($)

رابعا قم بفتح الإتصال

connection.Open();

خامسا قم بتنفيذ الإستعلام  بإستخدام الدالة (ExecuteReader)

using (DbDataReader reader = command.ExecuteReader())
{

الأن فإن جميع البيانات الموجودة في ملف إكسل تم تصديرها وأصبحت موجودة في كائن من نوع (DbDataReader)
بعد أن قمنا بتصدير البيانات وإسنادها يأتي الان العمل على جزئية قواعد بيانات (SQL SERVER)
أولا نضع معلومات الخاصة بنص الإتصال (Connection String) في متغير نصي

string sqlConnectionString = "Data Source=Your DataSource;Initial Catalog=Your DataBase;Integrated Security=True";

لاحظ معي أنه يجب أن تكتب إسم مصدر البيانات ( DataSource) بعد ذلك إسم قاعدة البيانات (Initial Catalog)
ثانيا إستخدام كائن من نوع (SqlBulkCopy)  والغرض من ذلك أن هذا الكائن(object) يقوم بنقلة كتلة (Bulk) من البيانات من مصدر إلى مصدرأخر بطريقة فعالة جدا جدا خاصة إذا كان حجم البيانات كبير جدا ونقوم بارسال نص الإتصال إليه  كالتالي.

using (SqlBulkCopy bulkCopy =  new SqlBulkCopy(sqlConnectionString))
{

ثالثا نستخدم خاصية (DestinationTableName) والتي من خلالها نحدد إسم الجدول الذي نريد أن نصدر إليه البيانات في قاعدة بيانات (MS SQL SERVER)

bulkCopy.DestinationTableName = "Table Name";

رابعا واخير نقوم بإستخدام دالة (WriteToServer) والتي تقوم بكتابة البيانات الي المصدر ونرسل لها الكائن من نوع (DbDataReader)

bulkCopy.WriteToServer(dr);

الشفرة كاملة

bulkCopy.WriteToServer(reader);
// Connection String to Excel Workbook
string excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=! ;Extended Properties=Excel 8.0

// Create Connection to Excel Workbook
using (OleDbConnection connection =
new OleDbConnection(excelConnectionString))
{
OleDbCommand command = new OleDbCommand
("Select * FROM [Sheet1$]", connection);

connection.Open();

// Create DbDataReader to Data Worksheet
using (DbDataReader dr = command.ExecuteReader())
{
// SQL Server Connection String
string sqlConnectionString = "Data Source=!;Initial Catalog=!;Integrated Security=True";

// Bulk Copy to SQL Server
using (SqlBulkCopy bulkCopy =
new SqlBulkCopy(sqlConnectionString))
{
bulkCopy.DestinationTableName = "!";
bulkCopy.WriteToServer(dr);
}
}
}

لاحظ معي أن أي شيء وضعت عليه علامة (!) تحتاج إلى تغيره إذا أردت أن يتنفذ البرنامج في جهازك
الفائدة الحقيقة
قد يسئل البعض مالذي سوف أستفيده من توريد البيانات من ملف إكسل الى قواعد البيانات ولكي أجيبك عن هذا التساؤل سوف أضح لك حالات حقيقية ( Real Cases)

الحالة الأولى تحويل البيانات من قاعدة بيانات إلى قاعدة بيانات (SQL SERVER)
الأن تخيل لو أن لديك قاعدة بيانات (Oracle,Sybase,MySql..etc) وتريد أن تحول البيانات من قواعد البيانات السابقة الى قاعدة بيانات (SQL SERVER) بناء على قرارات إدارية ماذا سوف تفعل .
طبعا هنالك أدوات تحويل ومن هذه الأدوات (SQL Server Integration Services (SSIS ولكن في بعض الحالات وهي كثيرة جدا لايمكن نقل البيانات على الأقل كليا فقد تجد أن بعضها تم نقله والبعض الأخر لم يتم نقله.فأفضل حل في هذه الحالة هو أن تقوم بتصدير (Export) جميع البيانات إلى ملف ومن ملف إكسل تقوم بتوريدها (Import) إلى قواعد بيانات (SQL SERVER) طبعا معظم المحررات (Editors) الخاصة بقواعد البيانات مثل برنامج (Toad) وغيرها الكثير لديها إمكانية توريد إلى إكسل (Export to Excel).
الحالة الثانية

تخيل معي لو أنك في منظمة وأرادو منك بناء نظام له علاقة بقواعد البيانات ولايوجد لديهم قواعد بيانات بعد وكل البيانات لديهم يتم تخزينها في ملفات إكسل فطبعا سوف تستخدم هذه الطريقة بلاشك .
الخلاصة
في ماسبق تدارسنا سوية كيف يمكن تصدير البيانات الى قواعد بيانات (SQL SERVER) من ملفات إكسل ورأينا سوية القيم الحقيقة لهذا الأسلوب والحالات الحقيقة التي قد تواجهك وكيف يمكن حلها.وطبعا لاأنسى أن أقول لكم أن هنالك طرق أخرى لنقل البيانات مثل إستخدام جملة الإدخال (Insert Statment) ولكن أكثرها فعالية في الإداء هو نقل البيانات ككتلة بإستخدام (SQLBulk) وفي الختام أتمنى أن أكون قد وفقت في الطرح
والله أعلم

رابط التحميل :من هنا

 
5 Comments

Posted by on May 26, 2010 in ASP.NET

 

Tags: , , , ,