تقييم الموضوع :
  • 0 أصوات - بمعدل 0
  • 1
  • 2
  • 3
  • 4
  • 5
معلومة --- Openrowset --- هامة للغاية
#1
كاتب الموضوع : 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
رأينا كيفية تنفيذ الاستعلام واستخدام الدالة للوصول للبيانات بجانب ذلك يمكن أن تلحق بالجملة شرط 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
}}}
تم الشكر بواسطة:
#3
سنتكلم عن شئ هام جداً بالنسبة لجلب البيانات


في حالة أنك تستخدم 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]

}}}
تم الشكر بواسطة:
#4
حتي الآن تكلمنا كيف نحصل على البيانات ، لكن لا ننسى بداية كلامنا فى الموضوع عن فائدة الأمر من حيث الحصول على بيانات وتسجيلها فى جدول على السيرفر

كيف يكون ذلك ؟


كود :
[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 وتضع فيه البيانات
}}}
تم الشكر بواسطة:
#5
فى الرد السابق تحدثنا عن كيفية حفظ البيانات فى جدول يتم انشاءه عن طريق الجملة 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]




نتمنى نكون افدناكم وأوصلنا لكم فائدة ربما كانت غائبة عن الكثير

والله ولي التوفيق



}}}
تم الشكر بواسطة:



التنقل السريع :


يقوم بقرائة الموضوع: بالاضافة الى ( 1 ) ضيف كريم