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

[Guest Xhack]

ملاحظة : بإمكانك قراءة الرابط هذا على مدونتي مستكشف الدوت نت وذلك لأن المواضيع هنا تذهب الى الصفحات التالية بعد مدة من الزمن

سوف نتحدث اليوم عن كيفية تصدير البيانات من ملف إكسل (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) وفي الختام أتمنى أن أكون قد وفقت في الطرح

والله أعلم

ImportFromExcel.rar

تم تعديل 26 May 2010 بواسطه Xhack

[bulbul 218]

باذن الله وفقت في الطرح

الله يجزيك الخير ويوفقك

[Mohammed Ghalib 0]

شكرا لك أخي الفاضل و جزاك الله كل خير

إلى الأمام و بانتظار المزيد

[gxr 0]

الاخ الكريم

الف شكرلك على هذا الشرح

هل يمكن تصدير البيانات من جدول في قاعدة اكسس على جهاز العميل الى نفس الجدول في قاعدة بيانات اكسس على السيرفر باستخدام vb وasp

مع الشكر الجزيل

[Guest Xhack]

الافضل ان تستخدم خصائص التصدير والتوريد الموجودة في برنامج أكسس طالما ان قواعد البيانات في جهاز العميل والسيرفر ايضا هما أكسس

بالتوفيق إن شاء الله

[عبد الله ساعاتي 5]

ياخي الكريم الله يجزاك بالخير

نفزت ما ذكرته ولكن توجد اخطاء اثناء التشغيل

ولم افهمها

وانا مشكلتي فعلاً كما ذكرت في الحالة الثانية

وطبعاً في الجدول المصمم على قاعدة البيانات sql يوجد 29 عامود وفي الجدول الثاني يوجد 14 عامود واريد سحب المعلومات من sheet واحد في الاكسل

استخدمت اداة DTS ولكنها تعطيني في الحقول التي لا اضيف فيها شي من الاكسل القيمه Null وهذه القيمه تسببلي مشاكل كثيره في برنامج اخر تستخدمه الشركه مع نفس القاعدة

المحاولة في المرفقات

افيدني بارك الله فيك

ImportmenExcel.rar

[Guest Xhack]

اخي الكريم يجب ان يكون عدد الاعمدة في قاعدة البيانات مطابق لعدد الاعمدة في ملف اكسل

ثانيا تاكد من نوع البيانات (data type) وانا اقصد بذلك انواع رقمية نصية ( int,varchar)

اتمنى ان تخبرني برسالة الخطا التي تظهر لك

[عبد الله ساعاتي 5]

اخي الكريم يجب ان يكون عدد الاعمدة في قاعدة البيانات مطابق لعدد الاعمدة في ملف اكسل

ثانيا تاكد من نوع البيانات (data type) وانا اقصد بذلك انواع رقمية نصية ( int,varchar)

اتمنى ان تخبرني برسالة الخطا التي تظهر لك

اخي حمد لله تم حل الخطأ

ولكن هناك مشكلتان تواجهني اتمنا انك ترشدني لي حل

الاوله هيا القيمه Null كيف اتخلص منها ؟

الثانيه عند السحب للمره الاوله يضيف الصفوف ولكن في المرات القادمه يعمل Update ؟

وفي عندي عامود في الsql من ياخذ قيمت fales\true كيف اتعامل معاه عند السحب

والله يجزاك بالخير

[عبد الله ساعاتي 5]

Up

[Guest Xhack]

اخي حمد لله تم حل الخطأ

ولكن هناك مشكلتان تواجهني اتمنا انك ترشدني لي حل

الاوله هيا القيمه Null كيف اتخلص منها ؟

الثانيه عند السحب للمره الاوله يضيف الصفوف ولكن في المرات القادمه يعمل Update ؟

وفي عندي عامود في الsql من ياخذ قيمت fales\true كيف اتعامل معاه عند السحب

والله يجزاك بالخير

اعتذر اخي الكريم عبد الله ساعاتي عن عدم الرد عليك مبكرا وذلك لانشغالي قليلا وشوف اجيبك على واتمنى ان شاء الله ان تجد في اجابتي مايفيد

اولا بخصوص القيمة Null فالافضل ان تقوم بعملية Update وان تحول جميع قيم null على سبيل المثال الى 0 وبذلك تتخلص من القيم الفارغة null value طبعا هذا يتم بعد نقل البيانات من اكسل الى قاعدة بيانات SQL SERVER

ثانيا عند السحب للمرة الاولى إضافة وبعد ذلك عملية Update طبعا الدرس ركز فقط على عملية نقل كتلة من البيانات ولكن في حالتك فافضل شيء هو ان تكتب شفرة تتحقق إذا كان الجدول فارغ بعني Insert اما اذا كان به بيانات يعني نفذ عملية Update

ثالثا القيم true\false هي عبارة عن صفر و واحد اي تاكد ان العمود في ملف اكسل يحتوي فقط على هاتان القيمتين وفي المقابل تاكد ان نوع الحقل في الجدول الموجود في قاعدة بياناتك من نوع bit وهذا الحقل لاياخذ سوى واحد وصفر وبعد ذلك قم بعملية النقل

اتمنى ان اكون قد افدتك

بالتوفيق ان شاء الله

تم تعديل 3 Jul 2010 بواسطه Xhack

[عبد الله ساعاتي 5]

اعتذر اخي الكريم عبد الله ساعاتي عن عدم الرد عليك مبكرا وذلك لانشغالي قليلا وشوف اجيبك على واتمنى ان شاء الله ان تجد في اجابتي مايفيد

اولا بخصوص القيمة Null فالافضل ان تقوم بعملية Update وان تحول جميع قيم null على سبيل المثال الى 0 وبذلك تتخلص من القيم الفارغة null value طبعا هذا يتم بعد نقل البيانات من اكسل الى قاعدة بيانات SQL SERVER

ثانيا عند السحب للمرة الاولى إضافة وبعد ذلك عملية Update طبعا الدرس ركز فقط على عملية نقل كتلة من البيانات ولكن في حالتك فافضل شيء هو ان تكتب شفرة تتحقق إذا كان الجدول فارغ بعني Insert اما اذا كان به بيانات يعني نفذ عملية Update

ثالثا القيم true\false هي عبارة عن صفر و واحد اي تاكد ان العمود في ملف اكسل يحتوي فقط على هاتان القيمتين وفي المقابل تاكد ان نوع الحقل في الجدول الموجود في قاعدة بياناتك من نوع bit وهذا الحقل لاياخذ سوى واحد وصفر وبعد ذلك قم بعملية النقل

اتمنى ان اكون قد افدتك

بالتوفيق ان شاء الله

الله يجزاك بالخير

فعلاً هوا ما احتاج عليه عن طريق كود اذا PrimryKey لي الصف موجود يعمل له Udate واذا كان غير موجود يعمل Insert

ولكن انا في الكود على قد حالي ضغيف ممكن بس تكتبلي كم سطر تكون لي مفتاح واكمل من بعدك اذا ممكن بس فتح الطريق

وانا اتعملت مع قاعدة بيانات في الاضافه والحذف والتعديل ولكن عن طريق فورم وداتا بيز

ام عن طريق الاكسل ما عندي اي خلفيه كيف تكون عمليت الاضافه والتعديل

وشكراً مقدماً

[feen 0]

اخي الكريم ابي اعمل استيراد وتصدير من ملف نصي text

بمعنى اخر احسب البيانت من ملف نصي على واجهة الفيجوال بيسك ويتم معالجتها ( بحث , تعديل , اضافة )

ماهو الحل

وشكرا لك على الطرح الرائع

[feen 0]

اخي الكريم ابي اعمل استيراد وتصدير من ملف نصي text

بمعنى اخر اسحب البيانت من ملف نصي على واجهة الفيجوال بيسك ويتم معالجتها ( بحث , تعديل , اضافة ) ثم ارسلها الى ملف نصي مرة اخرى

ماهو الحل

وشكرا لك على الطرح الرائع

[tammam_kd 104]

مشكور أخي الكريم على الموضوع ......... موفق بإذن الله ......

[walcom 30]

الحالة الثانية

تخيل معي لو أنك في منظمة وأرادو منك بناء نظام له علاقة بقواعد البيانات ولايوجد لديهم قواعد بيانات بعد وكل البيانات لديهم يتم تخزينها في ملفات إكسل فطبعا سوف تستخدم هذه الطريقة بلاشك .

لك أجزل الشكر على الدرس الرائع والمفيد، وجزاك الله كل خير

ولك فقط تعقيبي هو أن الحالات الواقعية أعقد بكثييير من المثال وستتطلب كتابة بعض SQL Scripts عوضا عن كتابة برنامج، وقد يكون من الأفضل استيراد البيانات من اكسل باستعمال Import & Export Data Wizard الموجود في SQL Server Management Studio 2008 R2 ، لاحظ فقط في الإصدارة Express 2008 R2.

مثلا: أضف في مثالك حقلا (عمودا) للقسم الذي يعمل فيه، وحقلا آخر للجامعة التي تخرج منها، ستحتاج في هذه الحالة لإنشاء جدول للأقسام في المنظمة، وجدول آخر للجامعات، ومن ثم قراءة القيم الفريدة عبر distinct query لكل منها وإدخاله في الجدول المخصص له، مع الانتباه كثيرا لتوافق أنواع البيانات المقرؤة من اكسل ومقابلاتها في SQL Server

insert into Departments(DepartmentName) 
select distinct Department from  [Exployees$] 
where [DeptName] not in (select DepartmentName from Departments)

ومن ثم إبدال القيم النصية بالأرقام المقابلة للبيانات المدخلة:

update	[Employees$] set DeptName=(select top 1 DepartmentID from   Departments where DepartmentName= [Employees$].[DeptName])

ومن ثم إدخال البيانات بالاستعلام:

insert into Employee(....)
select .... from  [Employees$] 

ومن الممكن إضافة شروط من نوع is not null وفق ماهو مطلوب.

[كرزاية 0]

السلام عليكم

من كلام الاخ وليد

فهمت انه بامكاني اختيار sheet من الاكسل وحفظها في جدول محدد من قاعدة البيانات

هل بامكاني تحديد بعض الاعمدة من sheet وحفظها في جدول محدد في قاعدة البيانات

والاعمدة الاخرى احفظها في جدول اخر

ام لابد من ان تكون الاعمدة لكل جدول موجودة في sheet منفرد

ارجو ان يكون السؤال واضح

[walcom 30]

هل بامكاني تحديد بعض الاعمدة من sheet وحفظها في جدول محدد في قاعدة البيانات

والاعمدة الاخرى احفظها في جدول اخر

ام لابد من ان تكون الاعمدة لكل جدول موجودة في sheet منفرد

ارجو ان يكون السؤال واضح

معذرة على الرد المتأخر

الإجابة على هذا السؤال تحدده الحالة التطبيقية التي تعمل عليها

لكن عموما يفضل أن تكون أعمدة كل Excel Sheet محددة لجدول منفصل، بمعنى آخر أعمدة كل Excel Sheet is mapped into a separate table.

[khaled harby 12]

جزاك الله خيرا ساحاول تطبيق الدرس

[saad fouad 0]

بارك الله فيك ونفع بعلمك

وانا متشكر جدا انت افدتني جدا بهذا الموضوع

شكرا لك بحق فعلا انا كنت عملت برنامج لشركه

وكانوا بيستخدموا الاكسل

وطلب مني نقل البيانات من ملف الاكسل الي الداتا بيز

والحمد لله انا جربت الكود بتاع حضرتك ونفع ميه ميه

والاهم من كده ان حضرتك شارح الكود

اشكرك بجد وبارك الله فيك

تم تعديل 4 Apr 2012 بواسطه saad fouad

[Guest Xhack]

بارك الله فيك ونفع بعلمك

وانا متشكر جدا انت افدتني جدا بهذا الموضوع

شكرا لك بحق فعلا انا كنت عملت برنامج لشركه

وكانوا بيستخدموا الاكسل

وطلب مني نقل البيانات من ملف الاكسل الي الداتا بيز

والحمد لله انا جربت الكود بتاع حضرتك ونفع ميه ميه

والاهم من كده ان حضرتك شارح الكود

اشكرك بجد وبارك الله فيك

بارك الله فيك أخي.. ويسعدني بحق أن الطريقة قد ساعدتك

دمت بخير