2014年12月19日金曜日

EXCELを読み込む方法と落とし穴 【DDE編】




Windows環境でDDEを使ってEXCELファイルを読み込む方法。
Unicode版だと日本語が文字化けして、うまく読み込めないようなのでご注意下さい。

ちなみに、色々な落とし穴があるので最後までご覧ください。




以下のEXCELファイルを読み込みたいとします。



C:\test.xlsx





EXCELファイルをDDEで読み込む例
* EXCELを起動 ;
options  noxwait noxsync;
%sysexec  "C:\TEST.xlsx";


* 5秒待機 ;
data _NULL_;
  rc = sleep(5,1);
run;


* EXCELデータを読み込む ;
filename EXC dde "Excel|[test.xlsx]Sheet1!C1:C4";

data OUT1;
  attrib
      A length=8.
      B length=$20.
      C length=8.   informat=yymmdd10.  format=yymmdds10.
      D length=8.   informat=time5.         format=time5.
  ;
  /* filenameで読み込む行のみを設定した場合、firstobsオプションは不要 */
  infile EXC notab dlm="09"x dsd missover lrecl=50000 firstobs=2;
  input A B C D;
run;

* ファイル参照を解放 ;
filename EXC clear;


OUT1
 A 
B 
 C 
 D 
   1
 1
                 .
           .
   2
 1
 2012/01/10 
 10:20 
   3
 1
                 .
           .
   4
 1
                 .
           .
   5
  
                 .
           .
   6
  
 2012/10/20
           .
   7
 1 
                 .
           .
   8
 1 
                 .
           .
   9
 aaa 
                 .
           .




解説
options noxwait noxsync;
%sysexec "C:\TEST.xlsx";

・OSのコマンドの力を借りて、EXCELファイルを開きます。
%sysexec "開きたいファイルのフルパス";

・コマンド発行時にSASが一時停止しないよう、noxwait noxsyncオプションを設定しておく。


data _NULL_;
  rc = sleep(5,1);
run;

・EXCELが開くまで後続のSASプログラムが実行されないよう「 sleep(秒数, 1) 」でSASを5秒スリープさせる。重いファイルで起動が遅い場合は、秒数を長く設定しましょう。

(この場合、sleep入れなくてもEXCELが開くまでSASプログラムの実行止めてくれるんでしたっけ？書かなくていい場合と、書かなきゃいけない場合があって、よく分からないのでひとまずsleep入れておきます)


filename EXC dde "Excel|[test.xlsx]Sheet1!C1:C4";

・読み込むEXCELの範囲をファイル参照名で定義しておく。
filename ファイル参照名  dde "Excel|[ファイル名]シート名!読み込むセル範囲"; 

今回は1～4列目を読み込む範囲に設定してます。


data OUT1;
  attrib
      A length=8.
      B length=$20.
      C length=8.   informat=yymmdd10.  format=yymmdds10.
      D length=8.   informat=time5.         format=time5.
  ;
  /* filenameで読み込む行のみを設定した場合、firstobsオプションは不要 */
   infile EXC notab dlm="09"x dsd missover lrecl=50000 firstobs=2;
  input A B C D;
run;

・attribで、読み込む変数の属性を定義しておき、infileとinputで、EXCELデータを読み込んでいきます。

・「 lrecl=50000 」で、1行に読み込む長さを50000バイトに増やしてます。
この長さが足りないと、途中で文字が切れてしまう事があるので。。
ここは読み込むデータの長さに応じて、設定して下さい。

・また、今回はEXCELの1～4列目を読み込む範囲に設定しましたが、読み込みを開始したい行は、2行目からなので、「 firstobs=2 」としてます。

「 filename EXC dde "Excel|[test.xlsx]Sheet1!R2C1:R10C4"; 」というように、filenameで読み込む行も設定してる場合、firstobs は不要です。




落とし穴

① EXCELのセル内改行には注意。

たとえば1セルに
「aaa
  aaa」
と入力して、SASデータセット化して開いてみると、「aaaaaa」と入ってるように見えます。
改行きえた？と思うかもしれないけど、実は目に見えない改行が入ってます。
この見た目と中身が異なるデータを持つのは、後々面倒を起こしそうです。

対策として、EXCEL上でセル内改行を全て削除しておくか、
SASYAMAさんが紹介している 「目に見えない改行コードが邪魔をしてくる話」を参考に、SAS上で改行コードを削除する方法があります。
（データに日本語等のマルチバイト文字を含む場合、マルチバイト非対応のCOMPRESS関数ではなく、KCOMPRESS関数を利用して下さい）



② EXCELに表示されてる”見た目”をそのまま読み込んでしまうという罠。

どういう事かというと、

EXCELで長い文字を入力して、セルの書式を「文字列」に設定すると、、


以下の通り、「########…」となってしまいます。(EXCELのバージョンとかによっては、この問題は起こらない)












EXCELの仕様っぽいですが、この状態で今回のプログラムを実行すると、SASデータセットにも「#######…」という文字が入ってしまいます。
対策としては、該当セルの書式を「標準」にしてあげれば、直ります。

また、EXCEL上でフィルタや非表示の設定をしている状態でプログラムを実行すると、非表示になってる部分は読み込んでくれません。


③ 想定した通りの行を読み込めているか確認した方が良さそう。

例えば、今回の例では読み込む範囲を「Sheet1!C1:C4」として、EXCELの1～4列の範囲にあるデータを読み込むよう指定していますが、このような指定方法では、どこの行まで読み込むかを明示的に指定していないため、データが入っている範囲外の空白行を読み込んでしまうことがあった。

その他、EXCELの仕様など、不確定要素があるので、最低限、最初と最後の行が想定通りに読み込めているか確認した方が良さそうです。


ラベル: ,

0 件のコメント:

コメントを投稿

登録: コメントの投稿 (Atom)