痛みと熊手CSVとExcel：問題と解決策

CSVは、異種システムの相互接続のための事実上の標準であり、「堅固な」表構造を持つバルクデータの転送と処理のためのものです。 多くのスクリプトプログラミング言語には、解析と生成のための組み込みツールがあり、プログラマーと一般ユーザーの両方によく理解されており、データ自体の問題は、目で見てよく検出されています。



この形式の歴史は少なくとも30年あります。 しかし今でも、XMLがramp延している時代では、CSVを使用して大量のデータをアップロードおよびダウンロードします。 そして、フォーマット自体がRFCでかなりよく記述されているという事実にもかかわらず、誰もが独自の方法でそれを理解しています。



この記事では、この形式に関する既存の知識を要約し、一般的なエラーを指摘し、例としてMicrosoft Office 2007のインポート/エクスポート実装曲線を使用して説明した問題を説明します。これらの問題（自動型変換を含む） .csvを開くとき、DATETIMEおよびNUMBERのExcel。



そもそも、CSV形式は、実際には区切り文字が異なる3つの異なるテキスト形式と呼ばれます：実際にはCSV自体（カンマ区切り値-コンマで区切られた値）、TSV（タブ区切り値-タブで区切られた値）、SCSV （セミコロンで区切られた値-セミコロンで区切られた値）。 人生では、3つすべてを1つのCSVと呼ぶことができ、セパレータ文字はエクスポートまたはインポート中に最高の状態で選択され、多くの場合、コード内で単に「縫い付けられ」ます。 これはそれを理解しようとする際に多くの問題を作成します。



例として、一見些細なタスクを考えてみましょう。スプレッドシートのデータをMicrosoft OutlookからMicrosoft Excelにインポートします。



Microsoft ExcelにはCSVへのエクスポートツールがあり、Microsoft Outlookには対応するインポートツールがあります。 ファイルを作成し、メールプログラムを「フィード」し、それが完了したのはもっと簡単なことです。 どんなに。



Excelでテストラベルを作成します。







...そして、3つのテキスト形式でエクスポートしてみます：

Unicodeテキスト	エンコード-UTF-16、区切り文字-集計、改行-0×0D、0×0A、ファイルサイズ-222バイト
「CSV（カンマ区切り文字）」	エンコードはWindows-1251で、区切り文字はセミコロン（カンマではありません！）です。2行目では、コンマにもかかわらず電話の値は引用されていませんが、値は「01; 02」です。これは正しいです。 改行-0×0D、0×0A。 ファイルサイズ-110バイト
「テキストファイル（タブ区切り付き）」	エンコード-Windows-1251、区切り文字-集計、改行-0×0D、0×0A。 値 "01; 02"は、引用符で囲まれています（特別な必要はありません）。 ファイルサイズ-110バイト

これからどのような結論を導き出しますか？..ここでMicrosoftが「CSV（コンマ区切り）」と呼ぶものは、実際にはセミコロン区切りの形式です。 Microsoftの形式は、厳密にはWindows-1251です。 したがって、ExcelにUnicode文字がある場合、それらはCSVの出力に疑問符として表示されます。 また、そのラインフィードは常に数文字であり、Microsoftはセミコロンが表示されているすべてを愚かに引用しています。 また、Unicode文字がまったくない場合は、ファイルサイズを節約できます。 また、UnicodeはUTF-8でのみサポートされ、UTF-8ではサポートされないため、より論理的です。



次に、Outlookがどのように表示するかを見てみましょう。 同じデータソースを指定して、そこからこれらのファイルをインポートしてみましょう。 Outlook 2007：ファイル->インポートとエクスポート...->別のプログラムまたはファイルからインポート。 次に、データ形式「カンマで区切られた値（Windows）」と「タブで区切られた値（Windows）」を選択します。

「タブ区切り値（Windows）」	Outlookのtsvファイルに、タブで区切られた値と！..-そうですね？.. Outlookはフィールドを接着し、タブに気付かないようにします。 ファイル内のタブをカンマに置き換えます。ご覧のように、フィールドはすでに解析されており、よくできています。
「コンマ区切り値（Windows）」	しかし、Outlookはすべてを正しく理解しています。 コンマはコンマです。 したがって、区切り文字としてコンマが必要です。 そして、私たちが優れた後-セミコロン。 その結果、Outlookはすべてを誤って認識します。

2つのMicrosoft製品は相互に理解しておらず、テキストファイルを介して構造化データを転送する機能を完全に欠いています。 すべてが機能するためには、プログラマーによる「タンバリンとのダンス」が必要です。



Microsoft Excelはテキストファイルを操作し、CSVからデータをインポートできることを覚えていますが、バージョン2007では非常に奇妙です。 たとえば、単にメニューからファイルを開くと、テキストファイルが最初の列に完全に配置されているように、フォーマットを認識せずにファイルが開きます。 CSVをダブルクリックすると、Excelは別のコマンドを受け取り、必要な質問をすることなく、CSVをインポートします。 3番目のオプションは、現在のシートにファイルを挿入することです。 このインターフェイスでは、区切り文字を設定して、何が起こったのかすぐに確認できます。 ただし、1つだけ問題があります。 たとえば、Excelはフィールド内の文字列の引用された翻訳を理解しません。



さらに、インターフェイスとマクロを介して呼び出されるCSVの同じ保存機能の動作は異なります。 マクロオプションは、地域の設定をまったく考慮しません。



残念ながら、CSV規格はありませんが、一方で、いわゆる メモ。 これは2005年の RFC 4180であり、すべてを非常にインテリジェントに説明しています。 他に何もないため、少なくともRFCに固執するのは正しいことです。 ただし、Excelとの互換性のために、そのIDを考慮する必要があります。



以下に、RFC 4180の推奨事項を簡潔にまとめ、角括弧で囲んだコメントを示します。

• 行間-改行CRLF [私の意見では、2バイトに制限すべきではありません。 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をインポートするときに動的に追加することもできますが、
• 「スラッシュ」を介して引用符を引用することは標準ではないため、これを行わないでください。
• フィールドの類型化がないため、それらの要件はありません。 整数部と小数部の区切り文字は国によって異なるため、アプリケーションによって生成された同じCSVが一方のエクセルでは「理解」され、もう一方のエクセルでは認識されないという事実につながります。 Microsoft OfficeはWindowsの地域設定に重点を置いているため、何でも可能です。 ロシアでは、区切り記号はカンマであると言いますが、
• CSVが[データ]メニューからではなく直接開かれた場合、Excelは不要な質問をせず、正しいように見えます。 たとえば、彼はデフォルトで1.24の値を持つフィールドを「January 24」として理解しています
• 値が引用符で囲まれている場合でも、Excelは先行ゼロを削除し、型をキャストします。 これを行う必要はありませんが、これは間違いです。 しかし、この問題を回避するために、小さな「ハック」を行うことができます。値を「等号」記号で開始し、フォーマットを変更せずに転送する必要があるものを引用符で囲みます。
• Excelには特別な等号があり、CSVでは数式の識別子と見なされます。 つまり、CSVで= 2 + 3が発生した場合、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" ;;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

.csvに保存されている実際にCOMMA-SEPARATEDである最初のファイルは、Excelでまったく受け入れられません。







2番目のファイルは、SCSVロジックに従って、Excelによって認識され、次のようになります。







インポート中のExcelエラー：

1. 区切り文字を囲むギャップを調査しました
2. データが引用符で囲まれているにもかかわらず、最後の列はまったく認識されませんでした。 例外は「ペトロフ」の行です-1.24はそこで正しく認識されました。
3. フィールドでは、Excelインデックスは先行ゼロを「下げた」。
4. 最後の行の右端のフィールドで、引用符の前のスペースは特殊文字を示さなくなりました

インポート機能（データ->ファイルから）を使用し、インポート時にすべてのフィールドテキストを呼び出すと、次の図が表示されます。







型キャストは機能しましたが、現在はラインフィードが正常に処理されず、先行ゼロ、引用符、余分なスペースに問題があります。 はい。CSVを開いているユーザーは非常に不便です。



必要のないときにExcelに型をキャストさせないようにする効果的な方法があります。 ただし、CSVは「Excel専用」です。 これは、型の問題が発生する可能性のある場所に引用符の前に「=」記号を配置することにより行われます。 同時に、余分なスペースを削除します。

 ;;;/;;  ;; 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" ;;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

このファイルをExcelで開くと、次のようになります。







まとめます。



使用できるCSVを生成するには、ユーザーにエクスポート前に次の設定を行う機会を与える必要があります。

1. エンコーディングを選択します 。 原則として、UTF-8、UTF-16、Windows-1251、KOI8-Rが重要です。 多くの場合、他のオプションはありません。 そのうちの1つはデフォルトで実行されるはずです。 データにターゲットエンコーディングに類似性のない文字が含まれている場合、データが破損することをユーザーに警告する必要があります。
2. フィールド間のセパレータを選択します 。 オプション-タブ、コンマ、セミコロン。 デフォルトはセミコロンです。 テキストに区切り文字が入力されている場合、そこにタブを入力することは非常に困難であり、印刷できない文字であることも忘れないでください。
3. 行間のセパレーターを選択します （CRLF 0×0D 0×0AまたはCR 0×0D）;
4. 数値データの整数部と小数部の区切り文字を選択します （ドットまたはコンマ）。
5. タイトルバーを表示するかどうかを選択します。
6. 特殊文字 （特に改行と引用符）の引用方法を選択します 。 原則として、標準から逸脱して\ nおよび\ "として引用することができますが、この場合は、\ nを自分で引用することを忘れないでください。それらを満たし、エクスポート/インポート時にこれをオプションにすることを忘れないでください。 RFC標準のパーサー構造...、 "abc \" "、...はエラーとしてカウントされます。
7. 「for Excel」ボックスをチェックし、Microsoftによって導入されたこれらの非標準機能を考慮することが絶対に理想的です 。 たとえば、「日付に似た」数値フィールドの値を、construction = "<field value>"に置き換えます。
8. 空の区切り文字の「テール」を残すかどうかを決定します（形成される場合）。 たとえば、20個のフィールドのうち、最初のフィールドのみにデータが含まれ、残りのフィールドは空です。 その結果、行は最初の19個のセパレーターの後に配置されるか、設定されません。 大量のデータの場合、これにより処理のミリ秒を節約し、ファイルサイズを削減できます。

適切で便利なCSVインポーターを作成するには、次のことを覚えておく必要があります。

1. 上記の文法に従ってトークンに従ってファイルを解析するか、 確立された既製のライブラリを使用する必要があります （インポートは問題であるため、Excelの動作は異なります）。
2. ユーザーにエンコードを選択する機会を与えます （上位4つで十分です）。
3. ユーザーにフィールド間の区切り記号を選択する機会を与えます （コンマ、タブ、セミコロンで十分です）。
4. ユーザーに行間の区切り文字を選択する機会を与えますが、CRおよびCRLFオプションに加えて、「CRまたはCRLF」を提供する必要があります。 これは、たとえば、Excelがセル内に改行があるテーブルをエクスポートすると、これらの改行をCRとしてエクスポートし、残りの行はCRLFで区切られているという事実によるものです。 同時に、ファイルをインポートするとき、そこにいるCRでもCRLFでもかまいません。
5. ユーザーに整数部分と小数部分 （コンマまたはドット）の間の区切り文字を選択する機会を与えます 。
6. 解析方法を決定します -最初にすべてをメモリに読み込み、次に行ごとに処理または処理します。 前者の場合、より多くのメモリが必要になる場合があります。後者の場合、中間のエラーは部分的なインポートのみを引き起こし、問題を引き起こす可能性があります。 優先される最初のオプション。

ラウフ・アリエフ、

Mail.Ru Group副テクニカルディレクター