किसी वेबसाइट से एक्सेल में डेटा कैसे आयात करें?

यह सर्वविदित है कि वर्ल्ड वाइड वेब में बहुत बड़ा उपयोगी डेटा होता है। हालाँकि, हमें किसी भी प्रकार का विश्लेषण करने से पहले डेटा को Microsoft Excel में आयात करना होगा। इस प्रकार के कार्य को पूरा करने के लिए आप दो विधियों का उपयोग कर सकते हैं।

वेब से मैन्युअल रूप से बाहरी डेटा प्राप्त करें

मान लीजिए कि आप इस वेब पेज से शीर्ष कमाई करने वाली फिल्मों के बारे में राजस्व डाउनलोड करना चाहते हैं, तो यहां आपको यह दिखाता है कि इसे आसानी से कैसे किया जाए।

Microsoft Excel खोलें और डेटा . पर क्लिक करें टैब में, बाहरी डेटा प्राप्त करें . में समूह, वेब से . क्लिक करें . नई वेब क्वेरी के बाद संवाद बॉक्स प्रकट होता है, वेब पते (http://www.the-numbers.com/movie/records/All-Time-Worldwide-Box-Office) को पता में कॉपी करें फ़ील्ड करें और जाएं . पर क्लिक करें बटन। एक्सेल वेब पेज को डाउनलोड करना शुरू कर देगा जैसा कि चित्र 1.1 में दिखाया गया है। बस नहीं . पर क्लिक करें अगर आपको स्क्रिप्ट त्रुटि मिलेगी चेतावनी बॉक्स जो नीचे दिए गए चित्र के समान है। बॉक्स गायब हो जाएगा और इसका आपकी आयात प्रक्रिया पर कोई प्रभाव नहीं पड़ेगा।

चित्र 1.1

नई वेब क्वेरी संवाद बॉक्स के ऊपरी दाएं कोने पर पीले बॉक्स में एक तीर है। उस पर क्लिक करके, आप यह निर्धारित कर सकते हैं कि तालिकाओं के सामने समान चिह्न दिखाना है या नहीं। उदाहरण के लिए, चित्र 1.2 के बाएँ फलक में तालिका के आगे कोई तीर चिह्न नहीं है। और आइकन दिखाने के लिए तीर बटन पर क्लिक करने के बाद एक आइकन (दाएं पैनल में) होगा।

चित्र 1.2 [पूर्ण दृश्य प्राप्त करने के लिए चित्र पर क्लिक करें]

चित्र 1.3 [पूर्ण दृश्य प्राप्त करने के लिए चित्र पर क्लिक करें]

चित्र 1.4

और आप अपने क्वेरी डेटा को किसी भी फैशन में रीफ्रेश करने के लिए भी सेट कर सकते हैं। तालिका में किसी भी सेल पर बस राइट-क्लिक करें, डेटा श्रेणी गुण चुनें . संकेतित बाहरी डेटा श्रेणी गुण . में संवाद बॉक्स में, आप नियंत्रण ताज़ा करें बदल सकते हैं शैली बदलने के लिए सेटिंग्स। उदाहरण के लिए, आप प्रत्येक 60 मिनट में या फ़ाइल खोले जाने पर ताज़ा करने के लिए क्वेरी निर्दिष्ट कर सकते हैं।

VBA प्रोग्रामिंग का उपयोग करके डेटा स्क्रैप करें

वीबीए प्रोग्रामिंग का उपयोग वेब पेज से डेटा स्क्रैप करने के लिए किया जा सकता है। पहले दृष्टिकोण के मुकाबले तुलना करते समय यह कठिन होने पर अधिक उपयोगी होता है। इसके अलावा, आपको यह समझने की आवश्यकता है कि VBA प्रोग्रामिंग का उपयोग करके डेटा को स्क्रैप करना सीखने से पहले HTML क्या है। यदि आप HTML के बारे में कुछ भी नहीं जानते हैं या कम जानते हैं तो मैं आपको इस वेबसाइट से HTML का बुनियादी ज्ञान सीखने की सलाह देता हूं। और यहाँ से, आप VBA का उपयोग करके वेब स्क्रैपिंग से संबंधित लगभग सब कुछ सीख सकते हैं। यह लेख आपको केवल दो उदाहरण दिखाएगा।

एक वेब पेज से डेटा स्क्रैप करें

मान लीजिए कि हम इस वेब पेज से कंपनी का नाम, ईमेल पता और संपर्क नाम निकालना चाहते हैं। यदि आप इस वेब पेज को खोलते हैं, तो आप पाएंगे कि नीचे एक कॉन्टैक्ट ब्लॉक है। चित्र 2.1 संपर्क ब्लॉक और संबंधित स्रोत कोड दिखाता है। लाल बक्सों में सूचना वह है जिसकी हमें आवश्यकता है। और हरे रंग के अंडरलाइन वाले बिल्कुल वही होते हैं जिन्हें हमें निकालने की आवश्यकता होती है।

चित्र 2.1

निम्नलिखित कोड आपको उपरोक्त अनुरोधित जानकारी निकालने में मदद कर सकता है और उन्हें पहली वर्कशीट में डाल सकता है।

सोर्स कोड

Sub Retrieve_Click()
 
'Create InternetExplorer

Set IE = CreateObject("InternetExplorer.Application")
 
'Let's not see the browser window

IE.Visible = False
 
'Open the web page

IE.Navigate "http://www.austrade.gov.au/SupplierDetails.aspx?ORGID=ORG8160044431&folderid=1736"
 
'Wait while IE is loading

Do While IE.readyState <> 4 Or IE.Busy = True
 
DoEvents
 
Loop
 
'Retrieve company name, email address & contact information

Set contactobj = IE.document.getElementsByClassName("contact-details block dark")
 
htext = contactobj(0).innerHTML
 
MsgBox htext
 
If InStr(htext, "<p>Company Name: ") Then
 
ThisWorkbook.Worksheets(1).Cells(1, 1) = Split(Split(htext, "<p>Company Name: ")(1), "<br")(0)
 
End If
 
If InStr(htext, "mailto:") Then
 
ThisWorkbook.Worksheets(1).Cells(2, 1) = Split(Split(htext, "mailto:")(1), Chr(34) & ">")(0)
 
End If
 
If InStr(htext, "<p>Name: ") Then
 
ThisWorkbook.Worksheets(1).Cells(3, 1) = Split(Split(htext, "<p>Name: ")(1), "<br")(0)
 
End If
 
ThisWorkbook.Worksheets(1).Cells(4, 1) = IE.LocationURL
 
ThisWorkbook.Save
 
Set IE = Nothing
 
Set contactobj = Nothing
 
End Sub

"IE.document.getElementsByClassName ("संपर्क-विवरण ब्लॉक डार्क")" आपको वर्ग के नाम के साथ सभी तत्वों को प्राप्त करने में सक्षम कर सकता है - संपर्क-विवरण ब्लॉक डार्क। यह वेब पेज लगभग सभी गुणों और विधियों को सूचीबद्ध करता है जिनका उपयोग HTML तत्वों पर किया जा सकता है। आप अपनी समस्या के लिए उपयुक्त विकल्प चुन सकते हैं।

आंतरिक HTML गुण आपको HTML तत्व की सामग्री को सेट या वापस करने की अनुमति दे सकता है। हमारे मामले में, यह "संपर्क-विवरण ब्लॉक डार्क" वर्ग नाम के साथ तत्व की सामग्री को लौटाता है और इसे एक चर htext पर सेट करता है। सामग्री (चित्र 2.2) को 'Msgbox htext' द्वारा प्रेरित किया जा सकता है।

चित्र 2.2

आप देख सकते हैं कि पाठ अच्छी तरह से संरचित है। इसलिए हम आवश्यकता को निकालने के लिए SPLIT फ़ंक्शन का उपयोग कर सकते हैं। उदाहरण के लिए, यदि हम "

कंपनी का नाम:" को एक सीमांकक के रूप में लेते हैं और सूत्र "स्प्लिट (htext, "

कंपनी का नाम:")(1)" "

कंपनी का नाम" के बाद पूरा टेक्स्ट वापस कर सकते हैं:" इस नए लौटे पाठ के लिए, हम "

चित्र 2.2 में ओके पर क्लिक करने के बाद, अनुरोधित डेटा वेब से एक्सेल वर्कशीट में आयात किया जा सकता है। उदाहरण के लिए, सेल A1 में कंपनी का नाम होता है जबकि सेल A4 में कंपनी का वेब पेज पता होता है।

चित्र 2.3

कार्यपुस्तिका को सहेजने से पहले नीचे दिए गए कोड को जोड़कर, आप सेल A4 में हाइपरलिंक जोड़ सकते हैं।

सोर्स कोड

'Add hyperlink

ThisWorkbook.Worksheets(1).Hyperlinks.Add ThisWorkbook.Worksheets(1).Cells(4, 1), ThisWorkbook.Worksheets(1).Cells(4, 1)

यदि आप सेल A4 पर क्लिक करते हैं, तो आप वेब पेज पर फिर से जा सकते हैं। यह विशेष रूप से तब उपयोगी होता है जब आपको बहुत सी कंपनियों के लिए डेटा पुनर्प्राप्त करना होता है। चूंकि आप संबंधित वेब पेज पर फिर से जाने के लिए किसी भी हाइपरलिंक पर क्लिक कर सकते हैं और बाद में समीक्षा के दौरान किसी भी जानकारी को मैन्युअल रूप से जोड़ या अपडेट कर सकते हैं।

चित्र 2.4

समान रीडिंग

• दूसरी कार्यपुस्तिका कैसे खोलें और Excel VBA के साथ डेटा कॉपी कैसे करें
• [फिक्स्ड!] ऑब्जेक्ट वर्कबुक का मेथड ओपन फेल (4 समाधान)
• एक्सेल वीबीए सेल वैल्यू के साथ सरणी को पॉप्युलेट करने के लिए (4 उपयुक्त उदाहरण)
• VBA का उपयोग करके कार्यपुस्तिका कैसे खोलें और मैक्रो कैसे चलाएं (4 उदाहरण)

वेब पेजों के साथ इंटरैक्ट करें

उपरोक्त उदाहरण केवल एक स्थिर वेब पेज से डेटा पुनर्प्राप्त करने का तरीका दिखाता है। लेकिन अधिक से अधिक बार, हमें बड़ी मात्रा में डेटा पुनर्प्राप्त करने के लिए वेब पेजों के साथ इंटरैक्ट करने की आवश्यकता होती है। चित्र 3.1 को देखिए। यह आपको दिखाता है कि उपरोक्त उदाहरण के वेब पेज पर कैसे पहुंचा जाए। आप देख सकते हैं कि बहुत सारे उद्योग हैं और प्रत्येक उद्योग के लिए बहुत सारी कंपनियां हैं। उदाहरण के लिए, कृषि व्यवसाय उद्योग में 651 कंपनियां हैं। यदि हम सभी उद्योगों से सभी कंपनियों की संपर्क जानकारी निकालना चाहते हैं तो हमें क्या करना चाहिए?

चित्र 3.1 [पूर्ण दृश्य प्राप्त करने के लिए चित्र पर क्लिक करें]

Visual Basic Editor में निम्न कोड दर्ज करने और कोड सबमिट करने के बाद, आप देखेंगे कि आपका IE खुल गया है। पहला वेब पेज उसके बाद दूसरा वेब पेज दिखाई देता है। यहां आप सीखेंगे कि ड्रॉप-डाउन सूची तत्व कैसे प्राप्त करें, एक विकल्प का चयन कैसे करें, और विकल्प चुनने के बाद किसी ईवेंट को ट्रिगर करना सीखें। “m =IE.document.getElementsByTagName(“option”).Length – 1” आपको कुल विकल्पों की संख्या देगा। इसका उपयोग अगले लूप के लिए किया जा सकता है।

सोर्स कोड

Sub retrieve()
 
'Create InternetExplorer

Set IE = CreateObject("InternetExplorer.Application")
 
'Let's see the browser window

IE.Visible = True
 
'Open the web page

IE.Navigate "http://www.austrade.gov.au/international/buy#"
 
'Wait while IE is loading

Do While IE.Busy
 
Application.Wait DateAdd("s", 1, Now)
 
Loop
 
Application.Wait (Now + TimeValue("00:00:10"))
 
'Part 1 - Select dropdown list and trigger event after you select one option

Set selectobj = IE.document.getElementsByTagName("select")
 
m = IE.document.getElementsByTagName("option").Length - 1
 
selectobj(0).selectedIndex = 1
 
selectobj(0).FireEvent ("onchange")
 
'Wait while IE is loading

Do While IE.readyState <> 4 Or IE.Busy = True
 
Application.Wait DateAdd("s", 1, Now)
 
Loop
 
Application.Wait (Now + TimeValue("00:00:10"))
 
End Sub

कोड का यह भाग आपको चित्र 2.1 में दिखाए गए वेब पेज पर ले जा सकता है, जब एक्सेल पहले नाम पर क्लिक करता है। सभी कंपनियों के नाम तत्व में "नाम" के वर्ग नाम के साथ शामिल हैं। Searchobj एक संग्रह है और searchobj(i) (i+1) ^th लौटा सकता है वस्तु। उदाहरण के लिए, searchobj(1).Click आपको RIDLEY CORPORATION (मेलबोर्न) के वेब पेज पर जाने के लिए सक्षम कर सकता है।

सोर्स कोड

'Part 2 - Select company Name

Set searchobj = IE.document.getElementsByClassName("Name")
 
searchobj(0).Click
 
'Wait while IE is loading

Do While IE.readyState <> 4 Or IE.Busy = True
 
DoEvents
 
Loop

अंत में, यहां संपूर्ण कोड है जो आपको IE खोलने, वेब पेज ब्राउज़ करने और डेटा निकालने की प्रक्रिया दिखा सकता है। निकाला गया डेटा वही होगा जो चित्र 2.4 में दिखाया गया है।

सोर्स कोड

Sub Retrieve()
 
'Create InternetExplorer

Set IE = CreateObject("InternetExplorer.Application")
 
'Let's see the browser window

IE.Visible = True
 
'Open the web page

IE.Navigate "http://www.austrade.gov.au/international/buy#"
 
'Wait while IE is loading

Do While IE.Busy
 
Application.Wait DateAdd("s", 1, Now)
 
Loop
 
Application.Wait (Now + TimeValue("00:00:10"))
 
'Part 1 - Select dropdown list and trigger event after you select one option

Set selectobj = IE.document.getElementsByTagName("select")
 
m = IE.document.getElementsByTagName("option").Length - 1
 
selectobj(0).selectedIndex = 1
 
selectobj(0).FireEvent ("onchange")
 
'Wait while IE is loading

Do While IE.readyState <> 4 Or IE.Busy = True
 
Application.Wait DateAdd("s", 1, Now)
 
Loop
 
Application.Wait (Now + TimeValue("00:00:10"))
 
'Part 2 - Select company Name

Set searchobj = IE.document.getElementsByClassName("Name")
 
searchobj(0).Click
 
'Wait while IE is loading

Do While IE.readyState <> 4 Or IE.Busy = True
 
DoEvents
 
Loop
 
'Part 3 - Retrieve company name, email address & contact information

Set contactobj = IE.document.getElementsByClassName("contact-details block dark")
 
htext = contactobj(0).innerHTML
 
If InStr(htext, "<p>Company Name: ") Then
 
ThisWorkbook.Worksheets(1).Cells(1, 1) = Split(Split(htext, "<p>Company Name: ")(1), "<br")(0)
 
End If
 
If InStr(htext, "mailto:") Then
 
ThisWorkbook.Worksheets(1).Cells(2, 1) = Split(Split(htext, "mailto:")(1), Chr(34) & ">")(0)
 
End If
 
If InStr(htext, "<p>Name: ") Then
 
ThisWorkbook.Worksheets(1).Cells(3, 1) = Split(Split(htext, "<p>Name: ")(1), "<br")(0)
 
End If
 
ThisWorkbook.Worksheets(1).Cells(4, 1) = IE.LocationURL
 
'Add hyperlink

ThisWorkbook.Worksheets(1).Hyperlinks.Add ThisWorkbook.Worksheets(1).Cells(4, 1), ThisWorkbook.Worksheets(1).Cells(4, 1)
 
End Sub

वास्तव में, हमें सभी उद्योगों से सभी कंपनियों के लिए संपर्क जानकारी निकालने की आवश्यकता है, इसलिए, हमें इस कार्य को पूरा करने के लिए लूप स्टेटमेंट का उपयोग करने की आवश्यकता है। निम्नलिखित पूरा कोड है। और आप सभी कंपनियों के लिए संपर्क जानकारी पुनर्प्राप्त करें में कोड भी पा सकते हैं। xlsm जिसे आप इस लेख के अंत में डाउनलोड कर सकते हैं।

सोर्स कोड

Sub Retrieve()
 
For idex = 2 To 18
 
'Create InternetExplorer

Set IE = CreateObject("InternetExplorer.Application")
 
'Let's see the browser window

IE.Visible = False
 
'Open the web page

IE.Navigate "http://www.austrade.gov.au/international/buy#"
 
'Wait while IE is loading

Do While IE.Busy
 
Application.Wait DateAdd("s", 1, Now)
 
Loop
 
Application.Wait (Now + TimeValue("00:00:10"))
 
idexn = idex - 1
 
'Part 1 - Select dropdown

Set selectobj = IE.document.getElementsByTagName("select")
 
m = IE.document.getElementsByTagName("option").Length - 1
 
selectobj(0).selectedIndex = idexn
 
selectobj(0).FireEvent ("onchange")
 
'Wait while IE is loading

Do While IE.readyState <> 4 Or IE.Busy = True
 
Application.Wait DateAdd("s", 1, Now)
 
Loop
 
Application.Wait (Now + TimeValue("00:00:10"))
 
wurl = IE.LocationURL
 
tot = IE.document.getElementsByClassName("SearchTotal")(0).innerHTML
 
pg = Int(tot / 25) + 1
 
Max = (tot Mod 25) - 1
 
'Part 2 - Select Class = "Name"

a = 2
 
For j = 1 To pg
 
If j = 1 Then
 
IE.Navigate (wurl)
 
Else
 
IE.Navigate (wurl & "&pg=" & j)
 
End If
 
Do While IE.Busy
 
Application.Wait DateAdd("s", 1, Now)
 
Loop
 
If j <> pg Then
 
For i = 1 To 24
 
Set searchobj = IE.document.getElementsByClassName("Name")
 
searchobj(i).Click
 
'Wait while IE is loading

Do While IE.readyState <> 4 Or IE.Busy = True
 
DoEvents
 
Loop
 
'Part 3 - Retrieve company name, email address & contact information

Set contactobj = IE.document.getElementsByClassName("contact-details block dark")
 
htext = contactobj(0).innerHTML
 
ThisWorkbook.Worksheets(idex).Cells(a, 1) = j
 
ThisWorkbook.Worksheets(idex).Cells(a, 2) = a - 1
 
If InStr(htext, "<p>Company Name: ") Then
 
ThisWorkbook.Worksheets(idex).Cells(a, 3) = Split(Split(htext, "<p>Company Name: ")(1), "<br")(0)
 
End If
 
If InStr(htext, "mailto:") Then
 
ThisWorkbook.Worksheets(idex).Cells(a, 4) = Split(Split(htext, "mailto:")(1), Chr(34) & ">")(0)
 
End If
 
If InStr(htext, "<p>Name: ") Then
 
ThisWorkbook.Worksheets(idex).Cells(a, 5) = Split(Split(htext, "<p>Name: ")(1), "<br")(0)
 
End If
 
ThisWorkbook.Worksheets(idex).Cells(a, 6) = IE.LocationURL
 
IE.GoBack
 
Do While IE.Busy
 
Application.Wait DateAdd("s", 1, Now)
 
Loop
 
a = a + 1
 
Next i
 
Else
 
For i = 0 To Max
 
Set searchobj = IE.document.getElementsByClassName("Name")
 
searchobj(i).Click
 
'Wait while IE is loading

Do While IE.readyState <> 4 Or IE.Busy = True
 
DoEvents
 
Loop
 
'Part 3 - Retrieve company name, email address & contact information

Set contactobj = IE.document.getElementsByClassName("contact-details block dark")
 
htext = contactobj(0).innerHTML
 
ThisWorkbook.Worksheets(idex).Cells(a, 1) = j
 
ThisWorkbook.Worksheets(idex).Cells(a, 2) = a - 1
 
If InStr(htext, "<p>Company Name: ") Then
 
ThisWorkbook.Worksheets(idex).Cells(a, 3) = Split(Split(htext, "<p>Company Name: ")(1), "<br")(0)
 
End If
 
If InStr(htext, "mailto:") Then
 
ThisWorkbook.Worksheets(idex).Cells(a, 4) = Split(Split(htext, "mailto:")(1), Chr(34) & ">")(0)
 
End If
 
If InStr(htext, "<p>Name: ") Then
 
ThisWorkbook.Worksheets(idex).Cells(a, 5) = Split(Split(htext, "<p>Name: ")(1), "<br")(0)
 
End If
 
ThisWorkbook.Worksheets(idex).Cells(a, 6) = IE.LocationURL
 
ThisWorkbook.Worksheets(idex).Hyperlinks.Add ThisWorkbook.Worksheets(idex).Cells(a, 6), ThisWorkbook.Worksheets(idex).Cells(a, 6)
 
IE.GoBack
 
Do While IE.Busy
 
Application.Wait DateAdd("s", 1, Now)
 
Loop
 
a = a + 1
 
Next i
 
End If
 
ThisWorkbook.Save
 
Next j
 
Set IE = Nothing
 
Set contactobj = Nothing
 
Next idex
 
End Sub

केवल एक बिंदु जिसे मुझे समझाने की आवश्यकता है वह चित्र 3.2 में दिखाया गया है। एक वेब केवल 25 कंपनियों को सूचीबद्ध कर सकता है। जब कंपनियों की कुल संख्या 25 से अधिक होगी, तो एक से अधिक पृष्ठ होंगे। चित्र 3.2 दिखाता है कि पहले पृष्ठ के बाद पृष्ठों का पता प्राप्त करने का एक नियम है। यह प्रथम पृष्ठ, "&pg=" और वास्तविक पृष्ठ संख्या के संयोजन पते का परिणाम है। और अंतिम पृष्ठ से पहले के सभी पृष्ठों के लिए, कुल ऑब्जेक्ट संख्या 25 है।  “IE.document.getElementsByClassName(“SearchTotal”)(0).innerHTML” किसी उद्योग के भीतर कंपनियों की कुल संख्या लौटा सकता है। हमारे मामले में, यह 651 होगा। "Int(tot / 25) + 1" आपको कुल पृष्ठों की संख्या प्राप्त कर सकता है। और "Max =(tot Mod 25) - 1" अंतिम पृष्ठ पर कंपनियों की अधिकतम संख्या लौटा सकता है। मैं यहां रुकूंगा और आपको यह पता लगाने के लिए छोड़ दूंगा कि इस विचार को कोड पर कैसे लागू किया जाए। आपके लिए कोड को समझना एक बेहतर तरीका है। यदि आपके कोई प्रश्न हैं तो आप एक टिप्पणी छोड़ सकते हैं।

चित्र 3.2

यहां आपको अंतिम एक्सेल का हिस्सा दिखाया गया है। एक उद्योग में सभी कंपनियों की संपर्क जानकारी एक वर्कशीट में एक साथ रखी जाती है।

चित्र 3.3 [पूर्ण दृश्य प्राप्त करने के लिए चित्र पर क्लिक करें]

कार्यशील फ़ाइल डाउनलोड करें

कार्यशील फ़ाइल को नीचे दिए गए लिंक से डाउनलोड करें।

पुल-डेटा-से-वेब-टू-एक्सेल.रार

संबंधित लेख

• वेबसाइट से एक्सेल में स्वचालित रूप से डेटा कैसे निकालें?
•  वर्ड से एक्सेल में डेटा (वाक्य, पैराग्राफ, टेबल, टिप्पणियां) आयात करें
• 6 सर्वश्रेष्ठ एक्सेल VBA प्रोग्रामिंग पुस्तकें (शुरुआती और उन्नत उपयोगकर्ताओं के लिए)
• एक्सेल वीबीए प्रोग्रामिंग और मैक्रोज़ सीखें (निःशुल्क ट्यूटोरियल - चरण दर चरण)
• Excel VBA कोडिंग टिप्स
• VBA के साथ आप क्या कर सकते हैं
• VBA मैक्रोज़ का परिचय