एक्सेल और एक्सेस के बीच डेटा का आदान-प्रदान (कॉपी करें, आयात करें, निर्यात करें)

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

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

दो कार्यालय अनुप्रयोगों के बीच डेटा का आदान-प्रदान करने के लिए यहां कुछ आसान युक्तियां दी गई हैं:

1) एक्सेस टेबल संरचना और निर्माण में शामिल तर्क को समझना

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

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

एक्सेल टेबल में डेटा एंट्री गलत नहीं है। हालाँकि, यदि आप एक्सेल को डेटा एंट्री सॉफ़्टवेयर के रूप में उपयोग करने की योजना बना रहे हैं और फिर बाद में एक्सेस में टेबल आयात करते हैं (यह एक तरह से एक्सेस के लिए उपयोगकर्ता के अनुकूल फॉर्म के रूप में एक्सेल का उपयोग करने के समान है), तो शुरुआत से ही यह सबसे अच्छा है। एक्सेल में स्प्रेडशीट सेट करते समय एक्सेल टेबल में प्राथमिक कुंजी जोड़ने के लिए।

यह सुनिश्चित करने के लिए है कि उपयोगकर्ता प्राथमिक कुंजी अवधारणा से परिचित और उन्मुख हो जाएं और जितना संभव हो सके डेटा अतिरेक और दोहराव को समाप्त करें। एक्सेल में टेबल बनाते समय एक्सेल में ऐसा करना काफी आसान है। यदि आपके पास अपेक्षाकृत छोटी तालिका है, जिसमें पहले से प्राथमिक कुंजी कॉलम नहीं है, और एक्सेल तालिका में कोई दोहराव या अतिरेक नहीं है, तो आप आसानी से इस कॉलम को जोड़ सकते हैं। बस, तालिका में अपने पहले कॉलम में एक सेल पर राइट-क्लिक करें, बाईं ओर सम्मिलित करें, तालिका कॉलम चुनें। फिर इस कॉलम का नाम बदलें और इसे आईडी कहें। इस कॉलम को पॉप्युलेट करने के लिए, कॉलम के पहले सेल का चयन करें और 1 टाइप करें, फिर कॉलम के दूसरे सेल में जाएं और 2 टाइप करें, और फिर उपरोक्त दो सेल को हाइलाइट करें और बाकी कॉलम को पॉप्युलेट करने के लिए ऑटोफिल का उपयोग करके नीचे खींचें। ।

साथ ही, याद रखने की एक आसान युक्ति यह है कि एक्सेस टेबल में फ़ील्ड नाम एक्सेल टेबल में कॉलम हेडिंग के बराबर होते हैं।

आप तालिका में डेटा को एक्सेस से कॉपी भी कर सकते हैं और इसे एक्सेल में एक नई शीट में पेस्ट कर सकते हैं। डेटा की एक छोटी राशि के साथ एक्सेस टेबल के लिए यह सलाह दी जाती है। एक्सेस में उन सभी पंक्तियों का चयन करें जिन्हें आप कॉपी करना चाहते हैं, होम टैब पर जाएं, क्लिपबोर्ड समूह में, कॉपी चुनें, या CTRL-C दबाएं अपने कीबोर्ड पर।

फिर एक्सेल वर्कबुक में एक नई शीट पर, CTRL-V . दबाएं एक्सेल शीट में एक्सेस टेबल पेस्ट करने के लिए अपने कीबोर्ड पर। एक्सेस तालिका को शीर्षकों के साथ एक श्रेणी के रूप में चिपकाया जाता है और श्रेणी में एक सेल का चयन करके और CTRL-T दबाकर एक्सेल तालिका में परिवर्तित किया जा सकता है। अपने कीबोर्ड पर। सुनिश्चित करें कि चेकबॉक्स, मेरी तालिका में शीर्षलेख हैं, और फिर श्रेणी को एक्सेल तालिका में बदलने के लिए ठीक दबाएं।

उपर्युक्त बिंदुओं से आगे बढ़ते हुए। एक्सेस में, जानकारी को प्रमुख संस्थाओं या विषयों में विभाजित किया जाता है। उदाहरण के लिए, एक छोटे स्टोर के लिए डिज़ाइन किए गए एक्सेस डेटाबेस में उत्पाद, ऑर्डर, ग्राहक और कर्मचारी (नामित संबंधों के साथ) नामक अलग-अलग टेबल होने की संभावना अधिक होगी। एक्सेल में, जानकारी को देखना असामान्य नहीं है, जिसे एक्सेस में अलग-अलग तालिकाओं में संग्रहीत किया जाएगा, एक एक्सेल तालिका में संयुक्त, जैसा कि नीचे दिखाया गया है।

अब जैसा कि उल्लेख किया गया है, एक्सेल तालिका में डेटा दर्ज करने का तरीका गलत नहीं है। हालाँकि, यदि एक्सेल और एक्सेस को एकीकृत किया जा रहा है, तो एक्सेल के संदर्भ में डेटा एंट्री सॉफ्टवेयर है और एक्सेस क्वेरी और डेटाबेस कार्यक्षमता प्रदान करता है। एक्सेल में जानकारी को एक बड़ी तालिका में चार छोटी तालिकाओं में विभाजित करना सबसे अच्छा है, और नीचे दिखाए गए अनुसार प्रत्येक तालिका को अपनी शीट पर रखें। साथ ही, यह सलाह दी जाती है कि ऐक्सेस में तालिका में फ़ील्ड के नामों का मिलान एक्सेल में तालिका में कॉलम के शीर्षकों से किया जाए, ताकि भ्रम से बचा जा सके और निरंतरता बनाए रखी जा सके।

2) बाद के टेबल डिज़ाइन में आसानी के लिए या एक्सेस में स्रोत डेटा के एकीकरण के लिए एक्सेल में डुप्लिकेट को हटाना

डेटा की कई पंक्तियों वाली Excel तालिकाओं में, एक नज़र में, अद्वितीय मानों को देखना अक्सर मुश्किल होता है। मान लें कि कोई एक्सेल तालिका के लिए अद्वितीय उत्पादों की संख्या देखना चाहता है, जिसमें डेटा की 100 पंक्तियाँ हैं, ताकि एक्सेल वर्कबुक से प्राप्त जानकारी का उपयोग करके एक्सेस में एक कुशल तालिका तैयार की जा सके। इस उदाहरण में एक्सेल वर्कबुक एक काल्पनिक ऑनलाइन रिटेलर के कुछ डेटा पर आधारित है। एक्सेल तालिका में उत्पाद की बिक्री की तिथि, उत्पाद का नाम और बेची गई मात्रा शामिल है जैसा कि नीचे दिखाया गया है।

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

डुप्लिकेट निकालें संवाद बॉक्स ऊपर आना चाहिए और दिनांक_ऑफ_सेल और मात्रा को अनचेक करना चाहिए और ठीक पर क्लिक करना चाहिए। कितने डुप्लिकेट मान निकाले गए, यह बताते हुए एक संदेश बॉक्स पॉप अप होना चाहिए। ठीक क्लिक करें।

एक्सेल में परिणामी तालिका का उपयोग ऑनलाइन रिटेलर के प्रस्तावित एक्सेस डेटाबेस के लिए एक्सेस टेबल के लिए जानकारी एकत्र करने में मदद के लिए किया जा सकता है।

और पढ़ें:Excel में किसी अन्य कार्यपत्रक में अद्वितीय मान कैसे कॉपी करें (5 तरीके)

3) अनावश्यक रिक्त स्थान को हटाने के लिए TRIM फ़ंक्शन () का उपयोग करना और एक्सेल में कॉलम हेडिंग और एक्सेस में फ़ील्ड नामों के बीच स्थिरता बनाए रखने के लिए अंडरस्कोर का उपयोग करना

एक्सेस फ़ील्ड नाम प्रमुख रिक्त स्थान से शुरू नहीं हो सकते हैं, इसलिए रिक्त स्थान को हटाने के लिए, एक्सेल में टेबल के कॉलम हेडिंग के नाम पर TRIM फ़ंक्शन () का उपयोग किया जा सकता है।

इसके अलावा, मान लें कि अंततः किसी को SQL सर्वर या किसी अन्य डेटाबेस जैसे Oracle में डेटा आयात करने की आवश्यकता है। तो, एकीकृत समाधान का प्रवाह एक्सेल में डेटा प्रविष्टि होगा, फिर इस डेटा को एक्सेस में निर्यात करना, और फिर एक्सेस टेबल को एंटरप्राइज़-स्तरीय डेटाबेस जैसे SQL सर्वर या ओरेकल में आयात करना होगा। अन्य डेटाबेस फ़ील्ड के नामों के बीच रिक्त स्थान को बहुत अच्छी तरह से नहीं पढ़ते हैं। तो एक्सेल तालिका में शीर्षक कॉलम के नाम को कॉल करने के बजाय, उत्पाद का नाम इसे Name_of_Product कहते हैं, अन्य डेटाबेस में एकीकरण की आसानी के लिए अंडरस्कोर का उपयोग करके लाइन के नीचे। SQL टेबल की इस नामकरण शैली को रिक्त स्थान वाले नामों की तुलना में बहुत बेहतर तरीके से संभालता है।

एक्सेस में कैप्शन गुण का उपयोग किया जा सकता है, इसलिए यदि आपकी तालिका में आपका फ़ील्ड नाम Name_of_Product है, तो डिज़ाइन दृश्य में उत्पाद के नाम के रूप में कैप्शन या लेबल दर्ज करें। तो डेटाशीट दृश्य में या प्रपत्रों पर इसके बजाय उत्पाद का नाम उपयोग किया जाता है, जबकि अंतर्निहित फ़ील्ड नाम में अंडरस्कोर होता है। यह मुख्य रूप से एक्सेस में डेटाबेस के फ्रंट-एंड दर्शकों के लिए सौंदर्य संबंधी उद्देश्यों के लिए है।

समान रीडिंग

• रन टाइम एरर 1004:पेस्ट स्पेशल मेथड ऑफ़ रेंज क्लास विफल
• एक्सेल में मूल्यों को कॉपी और पेस्ट करने का फॉर्मूला (5 उदाहरण)
• एक्सेल में लिंक कैसे चिपकाएं और स्थानांतरित करें (8 त्वरित तरीके)
• मर्ज किए गए सेल के साथ एक्सेल में कॉपी और पेस्ट करें (2 तरीके)
• विजिबल सेल को केवल VBA का उपयोग किए बिना हैडर के कॉपी कैसे करें

4) एक्सेल से एक्सेस में डेटा इंपोर्ट करना - दो मुख्य परिदृश्यों से निपटना

• परिदृश्य एक में, एक के पास पूरी तरह से खाली एक्सेस डेटाबेस है, और कोई एक्सेल से डेटा आयात करना चाहता है (एक्सेल में टेबल आईडी के साथ सेट किए गए हैं और अतिरेक और दोहराव जितना संभव हो उतना कम किया जाता है) और एक्सेल का उपयोग करें डेटा एंट्री प्रोग्राम या फॉर्म। एक्सेस डेटाबेस खुला होने के साथ, बाहरी डेटा टैब पर जाएं, आयात और लिंक समूह में, दिखाए गए अनुसार एक्सेल चुनें।

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

यदि तालिका एक्सेल में सही ढंग से सेट की गई है, तो एक्सेस स्वचालित रूप से पता लगाएगा और जांच करेगा कि पहली पंक्ति में कॉलम शीर्षक शामिल हैं जैसा कि नीचे दिखाया गया है।

अगला क्लिक करने से प्रक्रिया के अंतिम चरण में पहुंच जाता है, जो एक को लिंक की गई तालिका का नाम दर्ज करने के लिए कहता है और फिर समाप्त पर क्लिक करता है।

यह बताने के लिए एक संदेश पॉप अप होना चाहिए कि तालिका को लिंक कर दिया गया है।

यह एक्सेल डेटा को अतिरिक्त SQL कार्यक्षमता और एक्सेस वातावरण में उपलब्ध जटिल क्वेरी निर्माण के लिए अभिव्यक्ति बिल्डरों को उजागर करता है। जटिल प्रश्नों के परिणाम बाद में एक्सेल डैशबोर्ड में उपयोग किए जा सकते हैं। यह सलाह दी जाती है, लेकिन केवल संगठनात्मक उद्देश्यों के लिए एक्सेल डेटा एंट्री टेबल और एक्सेस डेटाबेस को एक ही फ़ोल्डर या निर्देशिका में लिंक टेबल के साथ रखना अनिवार्य नहीं है।

• परिदृश्य दो में, एक के पास एक खाली या आबादी वाला एक्सेस डेटाबेस है और एक एक्सेल टेबल आयात करना चाहता है, लेकिन उन्हें लिंक नहीं किया है। कोई उन्हें एक्सेस में संपादित करना चाहता है। इस तरह बाद में एक्सेल शीट में जो भी बदलाव किए जाते हैं, वे एक्सेस में दिखाई नहीं देते हैं और एक में एडिटिंग और क्वेरी करने के लिए डेटा पूरी तरह से एक्सेस में होता है। आमतौर पर यह विकल्प तब चुना जाता है जब किसी को तालिकाओं में दर्ज किए गए डेटा के प्रकार पर कड़ाई से नियंत्रण बनाए रखने की आवश्यकता होती है।

फिर से, अपने एक्सेस डेटाबेस को खोलने के साथ, बाहरी डेटा टैब पर जाएं, आयात और लिंक समूह में, दिखाए गए अनुसार एक्सेल चुनें।

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

अगली स्क्रीन पर अगला क्लिक करें।

फिर एक को उन क्षेत्रों के बारे में जानकारी निर्दिष्ट करने के लिए मिलता है जो आयात कर रहे हैं, जैसा कि नीचे दिखाया गया है। विज़ार्ड किसी को यह चुनने की भी अनुमति देता है कि किन क्षेत्रों को छोड़ना है। चूंकि अक्सर एक्सेल टेबल में डेटा हो सकता है जो वास्तव में एक्सेस में अन्य टेबल में होना चाहिए, इसलिए कोई भी इन कॉलम को वर्तमान तालिका में आयात करने से बच सकता है।

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

अगला क्लिक करें।

फिर एक्सेस में टेबल के लिए एक नाम चुनें और फिनिश पर क्लिक करें।

आपसे पूछा जाएगा कि क्या आप आयात चरणों को सहेजना चाहते हैं, अभी के लिए बस बंद करें पर क्लिक करें। The table is now in Access for further editing, entry, and manipulation and is unlinked to the Excel workbook.

5) Using Table Analyzer to normalize imported data and split the data into separate tables in Access

Later versions of Access, have the Table Analyzer tool, which helps users separate one table into many tables. This is especially useful if one did import the data from a big Excel table and one is struggling to separate the tables. One can launch this wizard as part of the import process and check the box, I would like a wizard to analyze my table after importing the data as shown below.

Or one can access it, if the tables are already imported as standalone Access tables, by going to Database Tools, in the Analyze group, choose Analyze Table.

The Table Analyzer wizard should be launched, which will guide you through the steps of creating separate tables.

Table Analyzer, however, does not always guess right, so use it with caution.

6) Importing data from Access into Excel and using VLOOKUP to link the data and populate columns

One can import Access tables into Excel worksheets. With an Excel workbook opened, go to the Data tab, in the Get External Data group, choose From Access as shown below.

Navigate to the Access database containing the tables, you want to import into your Excel spreadsheet.

Select the table one wants to import, in this case, the ProductT.

Click Ok.

Choose to Import the data as a Table, on the existing worksheet, choose cell A1, and then Click Ok.

The table is imported as an Excel table.

The same was done for the three other tables in the database, namely Orders, Employees, and Customers tables.

One now has an Orders table which looks like the one below.

This table with numbers in Customer_ID, Product_ID, and Employee_ID can be counter-intuitive and confusing for Excel users not used to the relational database theory and tables. Thus one could add three helper columns to the table called Actual_Customer, Actual_Product, and Actual_Employee as shown below.

In Cell F2, the following formula was used:

=VLOOKUP([@[Customer_ID]],Table_TablesToBeImportedIntoExcel.accdb5,2,FALSE)

Where, [@[Customer_ID]] refers to cell C2, which contains the lookup value, Table_TablesToBeImportedIntoExcel.accdb5 refers to the imported Customers table on the Customers sheet, (Excel gives this table a default name), highlight the data, but not the column headings, the same way one would for a normal VLOOKUP, the customer name is in column 2, and the type of lookup is an exact match.

Double click and send the formula down to populate the Actual_Customer column.

Do the same thing to populate the Actual_Product and Actual_Employee columns. So one can quickly repopulate Excel tables with actual text values as needed, using the VLOOKUP functionality in order to orientate the Excel users.

7) Using data already set up in a relational Access database, for Power Pivot manipulation

Power Pivot is a Business Intelligence tool available with Excel 2013 and later versions of Excel. It also requires data to be input in a relational manner. So, what you can do when you are importing Access tables into Excel (already normalized and designed using relational data logic), is to add the imported tables to the data model.

In a blank workbook, go to Data, get External data and choose from Access as for the case above. However, this time when asked to select a table, check the Enable selection of multiple tables option in the Select Table dialog box and select all the tables as shown below:

Click Ok.

Make sure the PivotTable Report option is selected, the Add this data to the Data Model should be checked because we imported multiple tables. If you only import one table at a time, you will need to check this option yourself. If you click on the drop-down arrow next to Properties, you should see that the relationships between the tables will also be imported.

Click Ok to create the Pivot TableReport, with the tables already added to the Data Model. The relationships should be visible in the PowerPivot for Excel Window, Diagram view. Thus the data is ready for further manipulation using Power Pivot functionality.

Download Files

Exchange-Data-Between-Access-and-Excel

निष्कर्ष

Excel and Access integration allow one tallow advantage of the combination of features in Excel and Access. The key to integrating Excel and Access successfully is understanding a little bit about relational database design basics.

Understanding a little bit of relational design and table structure will also set you in good stead with respect to taking advantage of Power Pivot and related Business Intelligence tools in the Excel environment.

Please feel free to comment and tell us if you integrate Excel and Access, whether or not you use Power Pivot, and any other tips for integrating Excel and Access, that you may have.

संबंधित लेख

• Excel VBA:Copy Cell Value and Paste to Another Cell
• Excel VBA:Copy Range to Another Workbook
• [Fixed]:Right Click Copy and Paste Not Working in Excel (11 Solutions)
• Difference Between Paste and Paste Special in Excel
• How to Copy Alternate Rows in Excel (4 Ways)
• How to Apply VBA PasteSpecial and Keep Source Formatting in Excel