技術とか戦略とか

IT技術者が技術や戦略について書くブログです。

Excelへのテキストの貼り付け(ペースト)が途中で切れる時の対処法

原因は、貼り付けようとしているテキストにNUL文字(16進数の文字コードでx00)が含まれているからです。
NUL文字が含まれていると、そこで貼りつけが終了してしまいます。
(なお、メモ帳への貼り付け、サクラエディタへの貼り付け等でも同じようにNUL文字の箇所で切れます)
 
NUL文字をスペース(x20)に置換して問題ないのであれば、予めNUL文字をスペースに置換してからコピーすることで、この問題を解決できます。
詳しい手順は以下の通りです。

  1. 貼りつけようとしているテキストをファイルに出力する
    初めからファイル形式なのであれば問題ないのですが、問題なのはDBのレコード等、ファイル形式になっていない場合です。
    DBのレコードであれば、CSV等の形式でエクスポートする必要があります。
    エクスポートの方法はRDBMSによって異なるので、別途調べてください。
    なお、Oracle SQL Developer等のGUI操作ができるツールを使用しているのであれば、ツール上で探せばエクスポート用の機能が見つかると思います。
     
  2. 16進数の置換ができるエディタでファイルを開いて置換する
    1で出力したファイルを、エディタで開き、NUL文字(x00)をスペース(x20)に置換します。
    置換の方法は以下の通りです。
     
    サクラエディタの場合
     以下のように指定して置換すれば良いです。
      置換前:\x{00}
      置換後:
     
    秀丸の場合
     開いた瞬間に自動的にNUL文字がスペースに置換されます。
     
    ・Stirlingの場合
     置換機能で、16進データを選択し、以下の指定をすれば良いです。
      置換前:00
      置換後:20
      
  3. 置換後のテキストをコピーし、Excelに張り付ける
    これで途切れずに貼りつけができるはずです。

unix/linux:改行コード無し固定長ファイルに改行を入れる

コマンド一つでできてしまうのですが、それらしき単語でググってもなかなか出てこないのでメモします。
ファイルhogeに対して以下のコマンドを打つことで、改行(\n)が入った状態でファイルfugaとして出力されます。
 
#> fold -bレコード長 hoge > fuga
 
例えば、hogeのレコード長が5の場合、以下のように改行を入れることができます。
 
#> cat hoge
1234567890
#> fold -b5 hoge > fuga
#> cat fuga
12345
67890

#>

unix/linux:メモを見ながら作業するのが面倒になった時の小技

unixlinuxで複雑なコマンドや長いパスを入力する必要がある場合、メモを用意しておいてそれを見ながら(若しくはメモをコピーして)作業することが多いと思います。
しかし、毎度のようにメモを見に行くのは面倒なので、そんな時は以下のコマンドが役に立ちます。
 
#> history | grep hoge
 
historyコマンドで、過去に発行したコマンドを一覧で見ることができます。
(直近1000回分のコマンドが出てくることが多いと思います)
しかし、一覧の中から目的のコマンドや目的のパスを探すのは大変なので、grepコマンドで更に絞り込みます。
 
例えば、testディレクトリのフルパスを知りたい場合は、以下のように調べることができます。
一覧に出ている「/home/hoge/test」が探していたフルパスです。
 
#> history | grep test
  726  cd /home/hoge/test
  1001 history | grep test
#>

COBOL:S9項目の文字コード

COBOLの符号付きの数字項目について、文字コード(バイナリ)だとどのように値を保持しているのかの解説が英語しかなかったので、記事を書きます。
 
符号付きの数字項目は「PIC S9(03)」のように定義するのですが、符号を示す「S」が付いたからといってバイト数が増えることはありません。
では、どのようにして符号の情報を保持しているのでしょうか。
 
英語では以下の文献があります。
 
EBCDIC to ASCII Conversion of IBM Signed Fields (Zoned, Over-punched Fields) - A Disc Interchange technical article

http://www.3480-3590-data-conversion.com/article-signed-fields.html

 
メインフレーム(ホストコンピュータ)の世界で良く使われる文字コードとしてEBCDICという文字コードがあるのですが、EBCDICでは以下のように値を保持します。

 

バイナリ 実際の値
F0 0
F1 1
F2 2
F3 3
F4 4
F5 5
F6 6
F7 7
F8 8
F9 9

 
符号無しの数値項目の場合は、これを単純に並べるだけです。
例えば、「120」は、「F1 F2 F0」のように値を保持します。
 
符号有りの数値項目の場合は、最後の桁について、上位4ビットを変更することで符号を表現します。
「+」を表現する時は「F」の箇所が「C」になりますし、「-」を表現する時は「F」の箇所が「D」になります。
例えば、「+120」は「F1 F2 C0」になりますし、「-120」は「F1 F2 D0」になります。
(ただし、「+」の場合は、「C」にせずに「F」のままセットされることも多いです。「C」にならなかったとしても慌てないでください。)
 
ちなみに、ここまでの話はゾーン10進数の話です。
パック10進数については既に記事を書いていますので、以下の記事を参照して下さい。
 
COBOLにおけるパック10進数(COMP-3)のデータの持ち方 - 技術とか戦略とか

https://akira2kun.hatenablog.com/entry/2018/10/01/014401

 
-------------------------------
 
以下、opensource COBOL で試してみた結果です。
opensource COBOL ではASCIIコードで出力され、ASCIIコードの場合の符号無し数値項目(ゾーン10進数)は上位4ビットが「3」になるのですが、「-」の場合は上位4ビットが「7」になるようです。「+」の場合は符号無し数値項目の場合と変わりませんでした。
なお、パック10進数の場合はEBCDICの場合と値の持ち方は同じです。
 
【テストコード】
       IDENTIFICATION    DIVISION.
       PROGRAM-ID.       SIGNTEST.
       ENVIRONMENT       DIVISION.
       INPUT-OUTPUT      SECTION.
       FILE-CONTROL.   
           SELECT  F1  ASSIGN  TO  "C:\tmp\a.txt".
      *
       DATA                  DIVISION.
       FILE                  SECTION.
       FD  F1.
       01  F1R.
           03  F1-REC        PIC X(52).
      *
       WORKING-STORAGE       SECTION.
       01  F1-REC-WORK.
           03  F1-REC1       PIC 9(10) VALUE 1234567890.
           03  KAIGYO1       PIC X(02) VALUE X"0D0A".
           03  F1-REC2       PIC S9(10) VALUE +1234567890.
           03  KAIGYO2       PIC X(02) VALUE X"0D0A".
           03  F1-REC3       PIC S9(10) VALUE -1234567890.
           03  KAIGYO3       PIC X(02) VALUE X"0D0A".
           03  F1-REC4       PIC S9(10) COMP-3 VALUE +1234567890.
           03  KAIGYO4       PIC X(02) VALUE X"0D0A".
           03  F1-REC5       PIC S9(10) COMP-3 VALUE -1234567890.
           03  KAIGYO5       PIC X(02) VALUE X"0D0A".
      *
       PROCEDURE         DIVISION.
      *
      * 各種処理の呼び出し
      *
       000-CONTROLLER-S.
           PERFORM  100-START-S THRU 100-START-E.
           PERFORM  200-MAIN-S  THRU 200-MAIN-E.
           PERFORM  300-END-S   THRU 300-END-E.
           STOP RUN.
       000-CONTROLLER-E.
      *
      * 前処理
      *
       100-START-S.
           OPEN  OUTPUT F1.
       100-START-E.
      *
      * 主処理
      *
       200-MAIN-S.
           MOVE F1-REC-WORK TO F1-REC.
           WRITE F1R.
       200-MAIN-E.
      *
      * 後処理
      *
       300-END-S.
           CLOSE  F1.
       300-END-E.
 
【実行結果】

f:id:akira2kun:20190625223325j:plain

java:他環境向けのファイルを出力する(文字コード・改行コード指定)

javaは実行環境に応じてデフォルトの文字コード・改行コードでファイルを出力するようにコーディングすることができます。
そのことにより、実行環境毎でコーディングを変更せずとも、実行環境に合わせた文字コード・改行コードを採用することができます。
 
しかし、他の環境向けのファイルを出力する場合、実行環境のデフォルトの文字コード・改行コードが採用されると困ることがあります。
そのような場合は、FileOutputStreamクラスを用いれば、指定した文字コード・改行コードを採用することができます。
 
なお、FileWriterクラスを用いる場合は、setPropertyで実行環境のデフォルトを変更しても文字コードは変更できませんでした。
公式ドキュメント(https://docs.oracle.com/javase/jp/8/docs/api/java/io/FileWriter.html)では「文字ファイルを書き込むための簡易クラスです。このクラスのコンストラクタは、デフォルトの文字エンコーディングとデフォルトのbyteバッファのサイズが許容できることを前提としています。」と書かれており、まさにその通りの挙動となっています。
 
【テストコード】
import java.io.BufferedWriter;
import java.io.FileOutputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.io.OutputStreamWriter;
import java.io.PrintWriter;

public class FileOutputMain {

    public static void main(String args) throws IOException {

        // 開発環境のデフォルトを確認
        System.out.println("■開発環境のデフォルト");
        System.out.println("OS:"+System.getProperty("os.name"));
        System.out.println("文字コード:"+System.getProperty("file.encoding"));
            //
        System.out.print("改行コード(16進数):"); //0d0aは\r\nを指す
        char buf =
                new String(System.getProperty("line.separator")).toCharArray();
        for (char ch : buf) {
            String str = Integer.toHexString(ch);
            if(str.length() == 1){
                str = "0" + str;
            }
            System.out.print(str);
        }
        System.out.println();

        // ファイルに出力する文字列
        String record = "ほげ";

        // 改行コード定義
        String zero_d_zero_a = "\r\n";
        String zero_a = "\n";

        // FileWriterでファイル出力
        final FileWriter fw1 = new FileWriter("C:\\tmp\\test1.txt");
        fw1.write(record+System.getProperty("line.separator"));
        fw1.close();

        // FileWriterだと文字コードを変更できない(setPropertyも反映されず)
        System.setProperty("file.encoding","Shift-JIS");
        System.setProperty("line.separator","\n");
        final FileWriter fw2 = new FileWriter("C:\\tmp\\test2.txt");
        fw2.write(record+System.getProperty("line.separator"));
        fw2.close();
        System.setProperty("file.encoding","UTF-8");
        System.setProperty("line.separator","\r\n");

        // FileOutputStreamで文字コード・改行コードを指定(Shift-JIS,\r\n)
        PrintWriter pw1 = new PrintWriter(
                          new BufferedWriter(
                          new OutputStreamWriter(
                          new FileOutputStream
                            ("C:\\tmp\\test3.txt"),"Shift-JIS")));
        pw1.print(record+zero_d_zero_a);
        pw1.close();

        // FileOutputStreamで文字コード、改行コードを指定してファイル出力(UTF-8,\n)
        PrintWriter pw2 = new PrintWriter(
                          new BufferedWriter(
                          new OutputStreamWriter(
                          new FileOutputStream
                            ("C:\\tmp\\test4.txt"),"UTF-8")));
        pw2.print(record+zero_a);
        pw2.close();

    }
 
【標準出力】
■開発環境のデフォルト
OS:Windows 8.1
文字コードUTF-8
改行コード(16進数):0d0a
 
【ファイル出力結果】

f:id:akira2kun:20190624232550j:plain

viで開けない巨大なファイルを開く(改行コード無し固定長レコード編)

4GB以上の改行コード無し固定長レコードのファイルの中から一部のレコードを抜き出す機会があったのですが、viエディタでも開けず、Windowsに落としてもエディタ(サクラエディタ、Stirling)でも開けなかったので、以下のように対処しました。
cutコマンドを使用して開ける大きさのファイルにするのがミソです。
 
1.下記コマンドで必要なレコードの存在位置にあたりをつける
例えば、レコード長100バイトで100レコード(合計10000バイト)のファイルについて、キー項目「1234567890」の存在位置を調べたい時、以下のコマンドによりあたりをつけることができます。
 
cut -b 1-5000 hoge | grep "1234567890" | wc -l
 
まず、cutコマンドにより、1バイト目から5000バイト目を抜き出しています。
次に、抜きだした結果に対して「1234567890」という文字が存在するかどうか調べています。
存在していれば、その次のwcコマンドに標準出力が渡り、1が出力されます。
存在しなければ、標準出力は渡らないので、0が出力されます。
 
2.徐々に範囲を狭める
例えば、1-5000バイトにキー項目が存在していた場合は、次に以下のようにコマンドを発行します。
 
cut -b 1-2500 hoge | grep "1234567890" | wc -l
 
これでもし1-2500バイトの範囲にキー項目が存在しなければ、次は以下のようにコマンドを発行し、2501-5000バイト目のどこかにキー項目が存在することを確認します。
 
cut -b 2501-5000 hoge | grep "1234567890" | wc -l
 
このように、二分検索の要領で、徐々に範囲を狭めていくのがポイントです。
 
3.レコードを抜きだす
開ける大きさにまで範囲が狭まったら、「| grep "1234567890" | wc -l」の箇所をリダイレクトに変更してファイルを出力します。
 
cut -b 2501-5000 hoge > hoge_tmp
 
なお、抜きだしたファイルには改行コードが付与されているので、下記コマンドで改行コードを削除します。
 
tr -d '\n' < hoge_tmp > hoge_tmp2
 
これで、目的のレコードを含む開ける大きさのファイルを抽出できます。
 
--------------------
 
なお、今回私はやっていませんが、一部レコードを編集した後、最終的に全レコードを結合し直す必要がある場合は、以下の手順でできるはずです。
①cutコマンドで「編集対象のレコードの前のファイル」「編集対象のレコードを含むファイル」「編集対象のレコードの後ろのファイル」の3つに分割する。
②「編集対象のレコードを含むファイル」を編集する。
③「編集対象のレコードの前のファイル」「編集対象のレコードを含むファイル」「編集対象のレコードの後ろのファイル」をcatコマンドで結合する(trコマンドで改行コード削除も行う)。

データが多すぎてExcelが重い→ファイル分割、Excel以外のプログラムを使って対応

数十万件のデータをExcelで扱っていて、どうにも重い、メモリ不足で落ちる、といったことがありました。
ググると「データを減らす」という対応法が載っていますが、数十万件のデータを一度に扱わないといけなかったのでこれは困難でした。
また、データをただ入力して関数で計算していただけなので、条件付き書式等の高尚なことはしていませんし、見えない所にゴミデータがあるということもありませんでした。
 
色々試行錯誤した結果、以下の2つが効果があったので紹介します。
 
1.シートを分ける代わりにファイルを分ける
何段階かに分けて処理を行っていたため段階毎にシートを分けていたのですが、シートで分ける代わりにファイル(ブック)で分けたら一気に処理が軽くなりました。
理由は後で調べたのですが、どうやらExcelはファイル毎にプロセスが分かれているみたいです。ファイルで分けることでマルチプロセス状態となったために、処理速度が向上しました。
なお、Excel2010までは意識せずともマルチプロセスになるのですが、Excel2013やExcel2016の場合は「ファイル名を指定して実行」で「excel /x」というコマンドを与えないとマルチプロセスにならないようなので注意が必要です。
 
2.Excel以外のプログラムを使って対応
これを言ってしまったら元も子もないのですが、現実問題としてExcel以外のプログラムで処理できることはExcel以外のプログラムで処理した方が良いです。
例えば、ソート・マージ、区切り文字や改行コードを使った簡単なフォーマットの変更、といった作業なら、サクラエディタを使った方が断然早いです。
IT業界に勤めているのであれば、場合によってはjava等で簡単なプログラムを作っても良いかもしれません。
なお、vlookup関数の高速化でググったら、「オープンソースRDBMSを使えば高速で処理できるようになるよ!」という(ちょっとワイルドな)例もありました。