CSV और एक्सेल को सॉर्स और रेक: समस्याएं और समाधान

CSV विषम प्रणालियों के अंतर्संबंध के लिए एक वास्तविक मानक है, जिसमें "कठोर" सारणीबद्ध संरचना के साथ बल्क डेटा के हस्तांतरण और प्रसंस्करण के लिए है। कई स्क्रिप्टिंग प्रोग्रामिंग भाषाओं में पार्सिंग और जेनरेटिंग के लिए बिल्ट-इन टूल्स होते हैं, इसे प्रोग्रामर और आम यूजर्स दोनों अच्छी तरह से समझते हैं, और डेटा के साथ समस्याओं को अच्छी तरह से पता लगाया जाता है, जैसा कि वे कहते हैं, आंख से।

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

इस लेख में मैं इस प्रारूप के बारे में मौजूदा ज्ञान को संक्षेप में बताने की कोशिश करूंगा, विशिष्ट त्रुटियों को इंगित करूंगा, और उदाहरण के रूप में Microsoft Office 2007 में आयात-निर्यात कार्यान्वयन वक्र का उपयोग करके वर्णित समस्याओं का वर्णन भी करूंगा। मैं यह भी दिखाऊंगा कि कैसे इन समस्याओं को स्वचालित करने के लिए (स्वचालित प्रकार रूपांतरण सहित)। खोलने के दौरान DATETIME और NUMBER) में Excel .csv

शुरू करने के लिए, CSV प्रारूप को वास्तव में तीन अलग-अलग पाठ प्रारूप कहा जाता है जो विभाजक वर्णों द्वारा भिन्न होते हैं: वास्तव में CSV स्वयं (अल्पविराम द्वारा अलग किए गए मूल्य - अल्पविराम द्वारा अलग किए गए मान), TSV (टैब द्वारा अलग किए गए मान - टैब द्वारा अलग किए गए मान) और SCVV (अर्धविराम अलग किए गए मान - अर्धविराम अलग किए गए मान)। जीवन में, सभी तीनों को एक सीएसवी कहा जा सकता है, विभाजक चरित्र को निर्यात या आयात के दौरान सबसे अच्छा चुना जाता है, और अधिक बार यह कोड के अंदर बस "सिलना" होता है। यह पता लगाने की कोशिश में बहुत सारी समस्याएं पैदा करता है।

चित्रण के रूप में, आइए एक प्रतीत होता है कि तुच्छ कार्य करते हैं: एक स्प्रेडशीट से Microsoft Outlook में Microsoft Excel में डेटा आयात करें।

Microsoft Excel में CSV के लिए निर्यात उपकरण हैं, और Microsoft Outlook में इसी आयात उपकरण हैं। क्या सरल हो सकता है - एक फ़ाइल बनाई, मेल प्रोग्राम "खिलाया" और क्या यह किया जाता है? कोई फर्क नहीं पड़ता कि कैसे।

Excel में एक परीक्षण लेबल बनाएँ:



... और इसे तीन पाठ स्वरूपों में निर्यात करने का प्रयास करें:

यूनिकोड पाठ	एन्कोडिंग - UTF-16, सीमांकक - सारणीकरण, लाइन फीड - 0 × 0D, 0 × 0A, फ़ाइल का आकार - 222 बाइट्स
"सीएसवी (कोमा सेपरेटर)"	एन्कोडिंग विंडोज -1251 है, सीमांकक अर्धविराम (अल्पविराम नहीं है!), दूसरी पंक्ति में कॉमा के बावजूद फोन का मूल्य उद्धृत नहीं किया गया है, लेकिन मान "01; 02" है, जो सही है। लाइन फीड - 0 × 0D, 0 × 0A। फ़ाइल का आकार - 110 बाइट्स
"पाठ फ़ाइलें (टैब सीमांकक के साथ)"	एन्कोडिंग - विंडोज -1251, सीमांकक - सारणीकरण, लाइन फीड - 0 × 0D, 0 × 0A। मान "01; 02" उद्धरण चिह्नों (विशेष आवश्यकता के बिना) में संलग्न है। फ़ाइल का आकार - 110 बाइट्स

इससे हम क्या निष्कर्ष निकालते हैं? .. Microsoft क्या कहता है "CSV (अल्पविराम-सीमांकित)" यहाँ वास्तव में एक अर्धविराम-सीमांकित प्रारूप है। Microsoft का प्रारूप सख्ती से Windows-1251 है। इसलिए, यदि आपके पास Excel में यूनिकोड वर्ण हैं, तो उन्हें CSV में आउटपुट पर प्रश्न चिह्न के रूप में प्रदर्शित किया जाएगा। इसके अलावा, वह लाइन फीड हमेशा एक दो अक्षर की होती है, कि Microsoft बेवकूफी से वह सब कुछ उद्धृत करता है, जहाँ वह अर्धविराम देखता है। इसके अलावा, यदि आपके पास यूनिकोड वर्ण बिल्कुल नहीं हैं, तो आप फ़ाइल आकार पर सहेज सकते हैं। इसके अलावा, यह तथ्य कि यूनिकोड केवल UTF-16 द्वारा समर्थित है, न कि UTF-8, जो बहुत अधिक तार्किक होगा।

अब देखते हैं कि आउटलुक कैसे दिखता है। उसी डेटा स्रोतों को निर्दिष्ट करते हुए, इन फ़ाइलों को इससे आयात करने का प्रयास करें। आउटलुक 2007: फ़ाइल -> आयात और निर्यात ... -> किसी अन्य कार्यक्रम या फ़ाइल से आयात करें। इसके बाद, डेटा फॉर्मेट चुनें: "वैल्यू सेपरेटेड बाय कॉमास (विंडोज)" और "वैल्यू सेपरेटेड टैब्स (विंडोज)"।

"टैब से अलग किए गए मान (विंडोज)"	हम टैब-सीमांकित मानों के साथ आउटलुक tsv फाइल को फीड करते हैं और ..! - तो आप सोचते हैं? .. आउटलुक खेतों को glues करता है और टैब को नोटिस नहीं करता है। हम फ़ाइल को कॉमा के साथ टैब में बदलते हैं और, जैसा कि हम देखते हैं, फ़ील्ड पहले से ही पार्स हैं, अच्छी तरह से किया गया है।
"कोमा सेपरेटेड वैल्यूज़ (विंडोज)"	लेकिन आउटलुक बस सब कुछ सही समझता है। कोमा एक अल्पविराम है। इसलिए, यह एक विभाजक के रूप में अल्पविराम की अपेक्षा करता है। और हमारे बाद एक्सेल - एक अर्धविराम। नतीजतन, आउटलुक सब कुछ गलत तरीके से पहचानता है।

दो Microsoft उत्पाद एक-दूसरे को नहीं समझते हैं, उनके पास एक पाठ फ़ाइल के माध्यम से संरचित डेटा को स्थानांतरित करने की क्षमता पूरी तरह से नहीं है। सब कुछ काम करने के लिए, इसे एक प्रोग्रामर द्वारा "डांस विद टैम्बोरिन" की आवश्यकता होती है।

हमें याद है कि Microsoft Excel पाठ फ़ाइलों के साथ काम कर सकता है, CSV से डेटा आयात कर सकता है, लेकिन 2007 के संस्करण में यह बहुत अजीब है। उदाहरण के लिए, यदि आप फ़ाइल को केवल मेनू के माध्यम से खोलते हैं, तो यह बिना किसी प्रारूप मान्यता के खुलेगी, जैसे पूरी तरह से पहले कॉलम में रखी गई टेक्स्ट फ़ाइल। यदि आप CSV पर डबल-क्लिक करते हैं, तो Excel एक और आदेश प्राप्त करता है और CSV को आयात करता है, जैसा कि अनावश्यक प्रश्न पूछे बिना करना चाहिए। तीसरा विकल्प वर्तमान शीट पर फ़ाइल सम्मिलित करना है। इस इंटरफ़ेस में, आप सीमांकक कॉन्फ़िगर कर सकते हैं, तुरंत देखें कि क्या हुआ। लेकिन एक बात: यह खराब काम करता है। उदाहरण के लिए, एक्सेल खेतों के भीतर तार के उद्धृत अनुवाद को नहीं समझता है।

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

दुर्भाग्य से, इस तरह के रूप में कोई सीएसवी मानक नहीं है, लेकिन इस बीच, तथाकथित ज्ञापन। यह 2005 का RFC 4180 है, जिसमें सब कुछ काफी समझदारी से वर्णित है। किसी और चीज़ की कमी के लिए, कम से कम RFC का पालन करने का अधिकार। लेकिन एक्सेल के साथ संगतता के लिए, आपको इसकी पहचान पर विचार करना चाहिए।

यहाँ RFC 4180 सिफारिशों और वर्ग कोष्ठक में मेरी टिप्पणियों का एक संक्षिप्त निचोड़ है:

• लाइनों के बीच - लाइन फीड CRLF [मेरी राय में, उन्हें दो बाइट्स तक सीमित नहीं होना चाहिए, अर्थात्। दोनों CRLF (0 × 0D, 0 × 0A) और CR 0 × 0D]
• सीमांकक अल्पविराम हैं, पंक्ति के अंत में अल्पविराम नहीं होना चाहिए,
• अंतिम पंक्ति में CRLF वैकल्पिक है
• पहली पंक्ति शीर्षक पंक्ति हो सकती है (एक ही समय में चिह्नित नहीं)
• विभाजक अल्पविराम के आसपास के स्थानों को अनदेखा किया जाता है।
• यदि मान में CRLF, CR, LF (लाइन-डेलिमिटर वर्ण), एक डबल उद्धरण या अल्पविराम (फ़ील्ड-सीमांकक वर्ण) है, तो मान को उद्धरण चिह्नों में संलग्न करना अनिवार्य है। अन्यथा, यह अनुमेय है।
• यानी क्षेत्र के भीतर वैध रेखा विराम की अनुमति है। लेकिन ऐसे क्षेत्र मूल्यों को जरूरी रूप से उद्धृत किया जाना चाहिए,
• यदि दोहरे भाग उद्धृत भाग के अंदर पाए जाते हैं, तो CSV में उद्धरणों का एक विशिष्ट उद्धरण उपयोग किया जाता है - उनका दोहराव।

यहाँ ABNF संकेतन में प्रारूप विवरण दिया गया है:

 file = [header CRLF] record *(CRLF record) [CRLF] header = name *(COMMA name) record = field *(COMMA field) name = field field = (escaped / non-escaped) escaped = DQUOTE *(TEXTDATA / COMMA / CR / LF / 2DQUOTE) DQUOTE non-escaped = *TEXTDATA COMMA = %x2C DQUOTE = %x22 LF = %x0A CRLF = CR LF TEXTDATA = %x20-21 / %x23-2B / %x2D-7E 

इसके अलावा, प्रारूप को लागू करते समय, आपको यह याद रखना होगा कि चूंकि संख्या और प्रकार के स्तंभों के लिए कोई संकेत नहीं हैं, इसलिए जब शीर्षक रखने की कोई आवश्यकता नहीं होती है, तो ऐसे सम्मेलन होते हैं जिन्हें आपको नहीं भूलना चाहिए:

• अंकों से एक स्ट्रिंग मान, उद्धरण चिह्नों में संलग्न नहीं, प्रोग्राम द्वारा एक संख्यात्मक के रूप में माना जा सकता है, जिसके परिणामस्वरूप जानकारी खो सकती है, उदाहरण के लिए, अग्रणी शून्य,
• प्रत्येक पंक्ति में मानों की संख्या भिन्न हो सकती है और आपको इस स्थिति को सही ढंग से संभालने की आवश्यकता है। कुछ स्थितियों में, आपको उपयोगकर्ता को चेतावनी देने की आवश्यकता है, दूसरों में, अतिरिक्त कॉलम बनाएं और उन्हें खाली मान भरें। आप यह तय कर सकते हैं कि शीर्ष लेख द्वारा स्तंभों की संख्या निर्धारित की गई है, या आप CSV आयात करते समय उन्हें गतिशील रूप से जोड़ सकते हैं,
• एक "स्लैश" के माध्यम से उद्धरण उद्धृत करना मानक नहीं है, ऐसा न करें।
• चूंकि खेतों का कोई प्रकार नहीं है, इसलिए उनके लिए कोई आवश्यकता नहीं है। अलग-अलग देशों में पूर्णांक और भिन्नात्मक भागों के विभाजक अलग-अलग हैं, और यह इस तथ्य की ओर जाता है कि एक ही सीएसवी जो एप्लिकेशन द्वारा उत्पन्न किया गया है वह "एक्सेल" को एक एक्सेल में समझा जाता है और दूसरे में नहीं। क्योंकि Microsoft Office Windows की क्षेत्रीय सेटिंग्स पर केंद्रित है, और कुछ भी हो सकता है। रूस में, यह कहता है कि विभाजक अल्पविराम है,
• यदि CSV को "डेटा" मेनू के माध्यम से नहीं, बल्कि सीधे खोला जाता है, तो एक्सेल अनावश्यक प्रश्न नहीं पूछता है, और जैसा कि उसे सही लगता है। उदाहरण के लिए, वह "24 जनवरी" को डिफ़ॉल्ट रूप से 1.24 मान के साथ क्षेत्र को समझता है
• एक्सेल अग्रणी शून्य को मारता है और जब मूल्य उद्धरण चिह्नों में होता है तब भी टाइप करता है। ऐसा करना जरूरी नहीं है, यह एक गलती है। लेकिन इस एक्सेल समस्या के आसपास जाने के लिए, आप एक छोटा "हैक" बना सकते हैं - एक "बराबर" चिह्न के साथ मूल्य शुरू करें, और फिर उद्धरण चिह्नों में डाल दें जो आपको प्रारूप बदलने के बिना स्थानांतरित करने की आवश्यकता है।
• एक्सेल में एक विशेष समान चिन्ह होता है, जिसे CSV में एक सूत्र के लिए एक पहचानकर्ता माना जाता है। यही है, अगर = 2 + 3 सीएसवी में होता है, तो यह दो और तीन को जोड़ता है और सेल में परिणाम लिखता है। मानक रूप से, उसे ऐसा नहीं करना चाहिए।

मान्य CSV का एक उदाहरण जिसका उपयोग परीक्षणों के लिए किया जा सकता है:

 , , , /, ,   ,,  20, , 08075, "1/3" Tyler, John,110 terrace, PA,20121, "1.24" " """"", ,120 Hambling St., NJ,08075, "1,24" ,,"7452 Street ""Near the Square"" road", York, 91234, "3-01" ,,,, 00123, "03-01" " "" "", ",,"",  ,00111, "0000" ,, 

बिल्कुल वही SCSV:

 ; ; ; /; ;   ;;  20; ; 08075;"1/3" Tyler; John;110 terrace; PA; 20121;"1.24" " """""; ;120 Hambling St.; NJ;08075;"1,24" ;;"7452 Street ""Near the Square"" road"; York; 91234;"3-01" ;;;; 00123;"03-01" " "" ""; ";;"";  ;00111; "0000" ;; 

पहली फ़ाइल जो वास्तव में COMMA-SEPARATED है, जिसे .csv में सहेजा जा रहा है, एक्सेल द्वारा बिल्कुल भी स्वीकार नहीं किया जाता है।



दूसरी फ़ाइल, जो, SCSV तर्क के अनुसार, एक्सेल द्वारा मान्यता प्राप्त है और यह बाहर आती है:



आयात के दौरान एक्सेल त्रुटियां:

1. सीमांकक के आसपास के अंतराल का अध्ययन किया
2. आखिरी कॉलम वास्तव में मान्यता प्राप्त नहीं था, इस तथ्य के बावजूद कि डेटा उद्धरण चिह्नों में है। अपवाद "पेट्रोव" के साथ लाइन है - 1.24 को वहां सही ढंग से मान्यता दी गई थी।
3. क्षेत्र में, एक्सेल इंडेक्स "शून्य" अग्रणी शून्य।
4. अंतिम पंक्ति के दाईं ओर के क्षेत्र में, उद्धरण से पहले रिक्त स्थान अब एक विशेष चरित्र को इंगित नहीं करते हैं

यदि आप आयात कार्यक्षमता (डेटा -> फ़ाइल से) का उपयोग करते हैं और आयात करते समय सभी फ़ील्ड टेक्स्ट को कॉल करते हैं, तो आपको निम्न चित्र दिखाई देगा:



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

एक प्रभावी तरीका है कि एक्सेल को टाइप करने के लिए मजबूर न करें जब हमें इसकी आवश्यकता नहीं है। लेकिन यह CSV "एक्सेल के लिए विशेष रूप से" होगा। यह उद्धरण चिह्नों के सामने "=" चिह्न रखकर किया जाता है, जहां एक प्रकार की समस्या संभावित रूप से हो सकती है। उसी समय, हम अतिरिक्त रिक्त स्थान को हटा देते हैं।

 ;;;/;;  ;; 20;;="08075";="1/3" Tyler; John;110 terrace;PA;="20121";="1.24" " """"";;120 Hambling St.;NJ;="08075";="1,24" ;;"7452 Street ""Near the Square"" road";York;="91234";="3-01" ;;;;="00123";="03-01" " "" "";";;""; ;="00111";="0000" ;; 

और ऐसा होता है अगर हम इस फ़ाइल को एक्सेल में खोलते हैं:



मैं संक्षेप में बताता हूं।

उपयोग किए जा सकने वाले CSV को उत्पन्न करने के लिए, उपयोगकर्ता को निर्यात करने से पहले निम्नलिखित सेटिंग्स करने का अवसर दिया जाना चाहिए:

1. एक एन्कोडिंग चुनें । एक नियम के रूप में, UTF-8, UTF-16, Windows-1251, KOI8-R महत्वपूर्ण हैं। अधिक बार नहीं, कोई अन्य विकल्प नहीं हैं। उनमें से एक को डिफ़ॉल्ट रूप से जाना चाहिए। यदि डेटा में ऐसे वर्ण हैं जिनमें लक्ष्य एन्कोडिंग में कोई एनालॉग नहीं है, तो आपको उपयोगकर्ता को चेतावनी देने की आवश्यकता है कि डेटा टूट जाएगा;
2. खेतों के बीच विभाजक का चयन करें । विकल्प - टैब, अल्पविराम, अर्धविराम। डिफ़ॉल्ट एक अर्धविराम है। यह मत भूलो कि यदि पाठ में विभाजक दर्ज किया गया है, तो वहां एक टैब दर्ज करना बहुत मुश्किल होगा, यह एक अनपेक्षित चरित्र भी है;
3. लाइनों के बीच एक विभाजक चुनें (CRLF 0 × 0D 0 × 0A या CR 0 × 0D);
4. संख्यात्मक डेटा (डॉट या अल्पविराम) के लिए पूर्णांक और आंशिक भागों के विभाजक का चयन करें ।
5. चुनें कि क्या शीर्षक बार प्रदर्शित करना है;
6. विशेष वर्णों को उद्धृत करने का तरीका चुनें (विशेषकर पंक्ति फ़ीड और उद्धरण चिह्न)। सिद्धांत रूप में, आप मानक से विचलन कर सकते हैं और उन्हें \ n और \ "के रूप में उद्धृत कर सकते हैं, लेकिन इस मामले में, यदि वे मिलते हैं और निर्यात / आयात करते समय इस विकल्प को चुनना नहीं भूलते हैं, लेकिन संगतता जंगल के साथ जाएगी, क्योंकि। किसी भी RFC- मानक पार्सर निर्माण ..., "abc \" ", ... एक त्रुटि के रूप में गिना जाएगा;
7. "एक्सेल के लिए" बॉक्स की जांच करना और Microsoft द्वारा पेश की गई गैर-मानक सुविधाओं को ध्यान में रखना बिल्कुल आदर्श है । उदाहरण के लिए, संख्यात्मक फ़ील्ड "निर्माण के साथ एक तिथि के समान" = "<फ़ील्ड मान>" के मानों को बदलें।
8. तय करें कि क्या खाली सीमांकक की "पूंछ" छोड़नी है , अगर यह बनता है। उदाहरण के लिए, 20 क्षेत्रों में से, केवल पहले में डेटा शामिल है, और बाकी खाली हैं। नतीजतन, लाइन को पहले 19 विभाजकों के बाद रखा जा सकता है, या सेट नहीं किया जा सकता है। बड़ी मात्रा में डेटा के लिए, यह प्रसंस्करण के मिलीसेकंड को बचा सकता है और फ़ाइल आकार को कम कर सकता है।

एक अच्छा और सुविधाजनक CSV आयातक बनाने के लिए, आपको निम्नलिखित बातों को याद रखना होगा:

1. फ़ाइल को पार्स करने के लिए ऊपर व्याकरण के अनुसार टोकन के अनुसार किया जाना चाहिए या अच्छी तरह से स्थापित तैयार पुस्तकालयों का उपयोग करना चाहिए (एक्सेल अलग तरीके से काम करता है, क्योंकि आयात एक समस्या है);
2. उपयोगकर्ता को एन्कोडिंग चुनने का अवसर दें (शीर्ष 4 पर्याप्त है);
3. उपयोगकर्ता को फ़ील्ड्स के बीच विभाजक चुनने का अवसर दें (अल्पविराम, टैब, अर्धविराम पर्याप्त है);
4. उपयोगकर्ता को लाइनों के बीच एक विभाजक चुनने का अवसर दें , लेकिन सीआर और सीआरएलएफ विकल्पों के अलावा, आपको "सीआर या सीआरएलएफ" प्रदान करना होगा। यह इस तथ्य के कारण है कि, उदाहरण के लिए, एक्सेल, जब कोशिकाओं के अंदर लाइन ब्रेक के साथ एक तालिका का निर्यात होता है, तो इन रेखाओं को सीआर के रूप में निर्यात करता है, और शेष लाइनें CRLF द्वारा अलग हो जाती हैं। उसी समय, जब कोई फ़ाइल आयात करता है, तो उसके लिए कोई फर्क नहीं पड़ता, वहां सीआर या सीआरएलएफ;
5. उपयोगकर्ता को पूर्णांक और भिन्नात्मक भागों (अल्पविराम या बिंदु) के बीच एक विभाजक चुनने का अवसर दें ;
6. पार्सिंग विधि निर्धारित करें - पहले हम सब कुछ मेमोरी में पढ़ते हैं, फिर हम लाइन द्वारा लाइन या प्रोसेस करते हैं। पहले मामले में, अधिक मेमोरी की आवश्यकता हो सकती है, दूसरे मामले में, बीच में एक त्रुटि केवल आंशिक आयात का कारण होगी, जिससे समस्याएं हो सकती हैं। पसंदीदा पहला विकल्प।

रऊफ अलीयेव,
उप तकनीकी निदेशक, Mail.Ru समूह