المشاركات : 2,523
المواضيع 707
الإنتساب : Sep 2012
السمعة :
48
الشكر: 91
تم شكره 1103 مرات في 296 مشاركات
كاتب الموضوع : BADRMEDIA
هل احتجت مرة أن تأتى ببيانات من مصدر بيانان لتسجلها فى مصدر أخي
كفكرة بسيطة : عندك بيانات تحصلها عليها من ملف أكسيل موجود فى سيرفر يتيح عن طريق الملف الحصول مثلاً على أسعار أسهم أو أسعار العملات طبعاً لتحركات مؤشر البورصة
كيف نجعل السيرفر يتصل على مصدر البيانات هذا وهو ملف الأكسيل ويحصل منه على القيم
أنها الدالة OPENROWSET
وتكون صيغتها كالتالي
function syntax
كود :
[color=#000000][COLOR=#0000bb]OPENROWSET [/color][color=#007700]( [/color][color=#dd0000]'provider_name'[/color][COLOR=#007700],
{ [/COLOR][color=#dd0000]'datasource'[/color][color=#007700]; [/color][color=#dd0000]'user_id'[/color][color=#007700]; [/color][color=#dd0000]'password' [/color][color=#007700]| [/color][color=#dd0000]'provider_string' [/color][COLOR=#007700]},
{ [ [/COLOR][color=#0000bb]catalog[/color][color=#007700].][[/color][color=#0000bb]schema[/color][color=#007700].][/color][color=#0000bb]object [/color][color=#007700]| [/color][color=#dd0000]'query' [/color][COLOR=#007700]}
)
[/COLOR][color=#0000bb][/color][/COLOR]
ولنبسطها هكذا ونشرحها :
كود :
[color=#000000][COLOR=#0000bb]OPENROWSET [/color][color=#007700]( [/color][color=#dd0000]'provider_name'[/color][color=#007700], [/color][color=#dd0000]'provider_string'[/color][color=#007700], [/color][color=#dd0000]'query' [/color][COLOR=#007700])
[/COLOR][color=#0000bb][/color][/COLOR]
سيكون تطبيقنا على ملف Excel وبالطبع سيكون provider الخاص بذلك هو OLEDB
كمثال أنسخ أى ملف Excel على القرص C مباشرة بحيث سيكون مسار العمل عليه وأن يكون به بعض البيانات لنستعرضها
كود :
[color=#000000][COLOR=#0000bb]SELECT [/color][COLOR=#007700]*
[/COLOR][color=#0000bb]FROM OPENROWSET[/color][color=#007700]([/color][color=#dd0000]'Microsoft.Jet.OLEDB.4.0'[/color][COLOR=#007700],
[/COLOR][color=#dd0000]'Excel 8.0;Database=C:\1.xls'[/color][COLOR=#007700],
[/COLOR][color=#dd0000]'SELECT * FROM [Sheet1$]'[/color][COLOR=#007700])
[/COLOR][color=#0000bb][/color][/COLOR]
حيث كتبنا
'provider_name' = 'Microsoft.Jet.OLEDB.4.0'
'provider_string' = 'Excel 8.0;Database=C:\1.xls'
'query' = 'SELECT * FROM [Sheet1$]'
حيث Sheet1$ هو اسم الصفحة الأولي من صفحة الأكسيل ويمكن أن يكون اسمها " الصفحة "
لو كان الأوفيس عربي أو يكون لها اسم خاص لو وضعت لها اسم ، إذن تنادي عليها باسمها مع وضع علامة $
ويمكن أن تنادي علي نطاق معين مثلاً من A:Z بدلاً من اسم الصفحة
المشاركات : 2,523
المواضيع 707
الإنتساب : Sep 2012
السمعة :
48
الشكر: 91
تم شكره 1103 مرات في 296 مشاركات
رأينا كيفية تنفيذ الاستعلام واستخدام الدالة للوصول للبيانات بجانب ذلك يمكن أن تلحق بالجملة شرط Condition باستخدام الكلمة Where
كود :
[color=#000000][COLOR=#0000bb]SELECT [/color][COLOR=#007700]*
[/COLOR][color=#0000bb]FROM OPENROWSET[/color][color=#007700]([/color][color=#dd0000]'Microsoft.Jet.OLEDB.4.0'[/color][COLOR=#007700],
[/COLOR][color=#dd0000]'Excel 8.0;Database=C:\1.xls'[/color][COLOR=#007700],
[/COLOR][color=#dd0000]'SELECT * FROM [Sheet1$]'[/color][COLOR=#007700])
[/COLOR][color=#0000bb]Where F1 [/color][color=#007700]= [/color][COLOR=#0000bb]2
[/COLOR][/COLOR]
يمكنك أن تنادي على اسم العمود لو كان له اسم او تكتب ترتبيه مع حرف F لو كان ليس له اسم
أي ليس له رأس أو خلية كعنوان له فى أوله
==============================
نتطرق لنكتة جميلة أخري وهي HDR=No أو HDR=Yes
وتسند لها قيمة No أو Yes في حالة هل تريد اظهار أسماء الاعمدة أم لا
كود :
[color=#000000][COLOR=#0000bb]SELECT [/color][COLOR=#007700]*
[/COLOR][color=#0000bb]FROM OPENROWSET[/color][color=#007700]([/color][color=#dd0000]'Microsoft.Jet.OLEDB.4.0'[/color][COLOR=#007700],
[/COLOR][color=#dd0000]'Excel 8.0;Database=C:\1.xls;HDR=No'[/color][COLOR=#007700],
[/COLOR][color=#dd0000]'SELECT * FROM [Sheet1$]'[/color][COLOR=#007700])
[/COLOR][/COLOR]
وكما قلنا من قبل فى حالة عدم اظهار اسماء الأعمدة أو ليس هناك أصلاً أسماء للأعمدة
يضع المحرك OLEDB اسم بديل وهو F ورقم العمود F1,F2,Fn
المشاركات : 2,523
المواضيع 707
الإنتساب : Sep 2012
السمعة :
48
الشكر: 91
تم شكره 1103 مرات في 296 مشاركات
سنتكلم عن شئ هام جداً بالنسبة لجلب البيانات
في حالة أنك تستخدم HDR=NO
لو كان عندك عمود به بيانات هكذا
كود :
[COLOR=#000000][COLOR=#0000bb]10
20
30
50
AA
HH
JK
56
78
[/COLOR][/COLOR]
ستجد أن بياناته تظهر هكذا
كود :
[COLOR=#000000][COLOR=#0000bb]10
20
30
50
Null
Null
Null
56
78
[/COLOR][/COLOR]
أن أن القيم النصية تم تحويلها لقيمة Null
كيف حدثت هذه المصيبة
ذلك أن المحرك OLEDB يقوم بقراءة أول قيم للعمود فأن وجد أنها رقمية تعامل مع العمود على أنه رقمي وهكذا مع النصي وبالتالي يقوم بعمل تحويل للقيم
لذلك جاءت IMEX=1 لتحل جريمة المحرك OLEDB الكبري
كود :
[COLOR=#000000][COLOR=#0000bb]
SELECT [/COLOR][COLOR=#007700]*
[/COLOR][color=#0000bb]FROM OPENROWSET[/color][color=#007700]([/color][color=#dd0000]'Microsoft.Jet.OLEDB.4.0'[/color][COLOR=#007700],
[/COLOR][color=#dd0000]'Excel 8.0;Database=C:\1.xls;HDR=NO;IMEX=1'[/color][COLOR=#007700],
[/COLOR][color=#dd0000]'SELECT * FROM [Sheet1$]'[/color][COLOR=#007700])
[/COLOR][/COLOR]
المشاركات : 2,523
المواضيع 707
الإنتساب : Sep 2012
السمعة :
48
الشكر: 91
تم شكره 1103 مرات في 296 مشاركات
حتي الآن تكلمنا كيف نحصل على البيانات ، لكن لا ننسى بداية كلامنا فى الموضوع عن فائدة الأمر من حيث الحصول على بيانات وتسجيلها فى جدول على السيرفر
كيف يكون ذلك ؟
كود :
[COLOR=#000000][COLOR=#0000bb]
SELECT [/COLOR][COLOR=#007700]*
[/COLOR][color=#0000bb]INTO [/color][color=#007700][[/color][color=#0000bb]dbo[/color][color=#007700]].[[/color][color=#0000bb]Table_Excel[/color][COLOR=#007700]]
[/COLOR][color=#0000bb]FROM OPENROWSET[/color][color=#007700]([/color][color=#dd0000]'Microsoft.Jet.OLEDB.4.0'[/color][COLOR=#007700],
[/COLOR][color=#dd0000]'Excel 8.0;Database=C:\1.xls;HDR=NO;IMEX=1'[/color][COLOR=#007700],
[/COLOR][color=#dd0000]'SELECT * FROM [Sheet1$]'[/color][COLOR=#007700])
[/COLOR][/COLOR]
أضفنا
INTO [dbo].[Table_Excel]
ستقوم مباشرة هي بانشاء جدول اسمه Table_Excel وتضع فيه البيانات
المشاركات : 2,523
المواضيع 707
الإنتساب : Sep 2012
السمعة :
48
الشكر: 91
تم شكره 1103 مرات في 296 مشاركات
فى الرد السابق تحدثنا عن كيفية حفظ البيانات فى جدول يتم انشاءه عن طريق الجملة Into
لكن هناك شئ هام وهو أن البيانات تخزن فى حقول من نوع على مزاج المحرك OLED أو بصورة علمية حسب البيانات المستوردة
لكن كيف نرسل البيانات ونجعل الحقول بهيئة على طريقتنا
الفكرة أننا نقوم بعمل تحويل لنوع الحقل وبالتالى سيكون نوع الحقل بالنوع المحول له
كود :
[color=#000000][COLOR=#0000bb]SELECT CAST[/color][color=#007700]([[/color][color=#0000bb]FirstName[/color][color=#007700]] AS [/color][color=#0000bb]VARCHAR[/color][color=#007700]([/color][color=#0000bb]20[/color][color=#007700])) AS [[/color][color=#0000bb]FirstName[/color][COLOR=#007700]],
[/COLOR][color=#0000bb]CAST[/color][color=#007700]([[/color][color=#0000bb]LastName[/color][color=#007700]] AS [/color][color=#0000bb]VARCHAR[/color][color=#007700]([/color][color=#0000bb]20[/color][color=#007700])) AS [[/color][color=#0000bb]LastName[/color][COLOR=#007700]],
[/COLOR][color=#0000bb]CAST[/color][color=#007700]([[/color][color=#0000bb]Address[/color][color=#007700]] AS [/color][color=#0000bb]VARCHAR[/color][color=#007700]([/color][color=#0000bb]50[/color][color=#007700])) AS [[/color][color=#0000bb]Address[/color][COLOR=#007700]],
[/COLOR][color=#0000bb]CAST[/color][color=#007700]([[/color][color=#0000bb]City[/color][color=#007700]] AS [/color][color=#0000bb]VARCHAR[/color][color=#007700]([/color][color=#0000bb]30[/color][color=#007700])) AS [[/color][color=#0000bb]City[/color][COLOR=#007700]],
[/COLOR][color=#0000bb]CAST[/color][color=#007700]([[/color][color=#0000bb]State[/color][color=#007700]] AS [/color][color=#0000bb]VARCHAR[/color][color=#007700]([/color][color=#0000bb]2[/color][color=#007700])) AS [[/color][color=#0000bb]State[/color][COLOR=#007700]],
[/COLOR][color=#0000bb]CAST[/color][color=#007700]([[/color][color=#0000bb]ZIP[/color][color=#007700]] AS [/color][color=#0000bb]VARCHAR[/color][color=#007700]([/color][color=#0000bb]10[/color][color=#007700])) AS [[/color][color=#0000bb]ZIP[/color][COLOR=#007700]]
[/COLOR][color=#0000bb]INTO [/color][color=#007700][[/color][color=#0000bb]dbo[/color][color=#007700]].[[/color][color=#0000bb]Table_Excel[/color][COLOR=#007700]]
[/COLOR][color=#0000bb]FROM OPENROWSET[/color][color=#007700]([/color][color=#dd0000]'Microsoft.Jet.OLEDB.4.0'[/color][COLOR=#007700],
[/COLOR][color=#dd0000]'Excel 8.0;Database=C:\1.xls;HDR=NO;IMEX=1'[/color][COLOR=#007700],
[/COLOR][color=#dd0000]'SELECT * FROM [Sheet1$]'[/color][COLOR=#007700])
[/COLOR][/COLOR]
حتي الآن الأمر جيد
لكن هناك مشكلة أن الأمر Into يقوم بانشاء الجدول المرسل له البيانات
لو استخدمت الجملة مرة اخرى سيقول لك أن الجدول موجود بالفعل
ماذا لو أردت أن تنشأ أنت الجدول وترسل له البيانات بصورة طبيعية
إذن صمم جدول كما أنت معتاد
واستخدم جملة
كود :
[COLOR=#000000][COLOR=#0000bb]Insert Into
Select
[/COLOR][/COLOR]
المعروفة
كود :
[COLOR=#000000][COLOR=#0000bb]insert into Table_Excel
SELECT [/COLOR][COLOR=#007700]*
[/COLOR][color=#0000bb]FROM OPENROWSET[/color][color=#007700]([/color][color=#dd0000]'Microsoft.Jet.OLEDB.4.0'[/color][COLOR=#007700],
[/COLOR][color=#dd0000]'Excel 8.0;Database=C:\1.xls;IMEX=1'[/color][COLOR=#007700],
[/COLOR][color=#dd0000]'SELECT * FROM [Sheet1$]'[/color][COLOR=#007700])
[/COLOR][/COLOR]
نتمنى نكون افدناكم وأوصلنا لكم فائدة ربما كانت غائبة عن الكثير
والله ولي التوفيق
|