perlでエクセルを読む
WINDOWSのActive perlでSpreadsheet、ParseExcel、ParseXLSXを使ってエクセルを読んで作業したのでメモ。
複数のフォルダに入っている、複数のエクセルファイル。
フォルダ名は五十音のひらがな。あかさたな～ってやつ。
ファイル名は日本語だったり乱数だったり、中身とはほぼ関係もなく意味不明なファイル名となっている。
エクセルファイルの特定箇所を確認して、別途登録されているものと違っていたら修正。
エクセルのファイル名を登録されているもの（日本語）にリネーム。
ざっくり、てな作業。
ひとつずつエクセルを開いて、ひとつずつ確認する、なんて手作業はやってられない。作業前から、見逃す・リネームを間違えるのが目に浮かぶようだ。
一覧表を作って確認して、そこから修正すべきものだけコピペする方が早くて確実。
エクセルファイルは「.xls」なので、使うのはSpreadsheet::ParseExcel
エクセルファイルを読み込んで、エクセルのファイル名と該当セルに記載されている名前をタブ区切りで出力するだけのスクリプトになる。
確認が必要なのは1枚目のシートにある3行目C列のセル内容
get_cell(2,2)で該当セル情報を取得（perlは0からなので3番目は2）
セル情報がHASHで入っている。必要なのは値なのでvalueで取得する。
取得する値はutfフラグ付きの日本語になるのでcp932(WINDOWSの文字コード)にして出力。
ここで気づかれると思うけど、このスクリプトは、複数のファイルを一度に処理するようにはできてない。引数に渡されたひとつのエクセルファイルを処理するだけ。
perlにはopendirがあるし、ファイル一覧を取得してループでSpreadsheet::ParseExcelに読み込ませるのが綺麗で正しいやり方。だけど、日本語フォルダ名、日本語ファイル名がうまく渡せない、読み込めなくてハマった。
たぶん文字コードの問題。utfフラグをつけてみたり何もしないまま渡してみたりしたんだけど、どうもうまくいかない。時間もあまりない。
そこでわたしの得意な現物合わせのやっつけ仕事の出番。
よくわかってない人間が間に入るからダメなわけで、だったら機械同士、ソフトウエア同士で直接やりとりしてもらおうとバッチファイルにした。
バッチファイルの for 文で /r をつけると再帰でサブディレクトリも拾ってくれるなんて、今回初めて知った。
このバッチファイルでperlにファイル名を渡すとエラーもなく意図通り読み込んでくれるようになった。いや、上記したように、本当だったらperlだけで済むはずなので、綺麗な解決方法じゃないけど、結果オーライ、だ。
出力されたtsv(タブ区切りファイル)を新しいエクセルに貼りつけ、別途正しい登録名を貼りつけ一覧表を作成する。
そうしたら、エクセルお得意のvlookupでエクセルの該当箇所と正しい登録名の相違を確認。登録名と違っているファイルだけ開いて該当セルを修正する。
また、修正作業には登録名をコピるのでついでに一覧表も修正。
エクセルのファイル名と登録名がこの一覧表の「.xlsx」ファイルに記載されることになるので、Spreadseet::ParseXLSXを使って読み込み、一括でリネームするようにした。
row_range()とcol_range()でシートに記載されている行数とカラム数を取得してループさせて全部読み込ませる。また、vlookupで N/A になっているところは正しい登録名を別セルに記載したので、そちらを読むようにさせた。
ここでもperlだけでリネームはできるんだけど、なんせ日本語のフォルダ名とファイル名。またうまくいかなかったら面倒くさいんで、perlでリネームするのではなく、system関数を使ってWINDOWSに仕事をさせることにした。
といいつつ、リネームは怖いので、別フォルダに同じディレクトリ構成で登録名ファイルをコピーすることにした小心者だ。
日本語のファイル名は見た目分かりやすいけど、スクリプトで扱うのはただただ面倒くさい。
それに、そもそもなことを言っちゃうと。
ファイル名や入力項目などなど、表記の揺れレベルじゃない間違いは、依頼する時点で入力するひとのことを考えて何か仕組みを作らないといかんよなあ。
あ。もうひとつ。
目grep手merge撲滅！ひとのやる手作業を信用しちゃいけない。
» ローカル環境で電子書籍を作る、Macアプリ・Windows版ツール 「かんたんEPUB3作成easy_epub」
AmazonPA-API5に移行で大騒ぎや
amazonのAPIが2020年にバージョン5になります。
https://affiliate.amazon.co.jp/help/node/topic/GZBFW3F79Y7FADBL
…というのは薄っすら意識はあったものの、1/23に届いたメールタイトルに驚愕
「IMPORTANT UPDATE -
Upgrade to PA API 5.0 before PA API 4.0 shuts down on March 9, 2020」
この「PA API 4.0 shuts down on March 9,2020」はさすがに見逃さなかった。
これまでずっと使い続けていたAPIのまさかの終了のお知らせだ。
去年2019年は、売上のないアカウントはAPIの使用制限がかかるようになり（売上のないアカウントは実質使えなくなり）困ったなあ、と思いつつも、たまーーーに、ポツリとクリック→購入があって使える日もあったんだけど、今回のAPIの変更は、そんな呑気なことを言ってるヒマはない。
まったく使えなくなるのだ。
てことで対応しなきゃまずい。とてもまずい。かなりまずい。
慌ててamazonのwebサービスドキュメントページをざっくり確認。
https://webservices.amazon.com/paapi5/documentation/
ver4とver5ではまったく違う。別人となる。
https://webservices.amazon.com/paapi5/documentation/migration-guide/whats-new-in-paapi5.html
取得するデータが、今までずっと変わらずXMLだったのに、ver5からはJSONになる！！びっくりマークをつけてもつけたりないぐらい驚天動地だ。
…てことは、現状使っている自作のスクリプトは全面的に書き換えが必要となる。
SDKを使ってお手軽に、とか思って探してみたところ、ver5に用意されているSDKは、Java、Node.js、Python、PHPの4つ。なんでperlがないねんっ！！
確かamazonはフロントのWEB側はperlだったはずだろう。今どきのWEBサービスには珍しくperlのサンプルもずっと用意してくれていたってのに、だ（残るはPaypalぐらいか）
JavaもNode.jsもわからんちんだし、Pythonもこれからの主役はこれか、ぐらいの遠巻き。
PHPがどうにか少しはいじれるので、サンプルをダウンロードして、perlに移植してみた。
サンプルに入っているDefaultApi.phpでエンドポイントやオペレーション名を拾って、WithoutSDKのサンプルコードで署名込みのヘッダーを作れるようにした。
PHPはどこからグローバルというか、スコープというのか知らんけど、把握するのにあっちこっち行かなきゃわからないから好きじゃなかったんだ、てのを再認識。
さて、これでそれっぽいリクエストを作れるようになったはずなんだけど。
売上のないアカウントなので、試せない。
売上のないアカウントには用はないamazonだ。
作ったスクリプトを使って。
ローカルのPCでリクエストを投げると
439 TooManyRequests というステータスと、JSONでエラーメッセージが返ってくる。
試しにレンタルサーバーに上げてそこからリクエスト投げると
503 とHTMLのエラーページが返ってくる。
どうやらどちらも売上のないアカウントだから相手にされない、ということっぽい。
一応、レスポンスのサンプルは公式ページにある。
でもなあ、こういうのって実際に何が返ってくるのか確認できなきゃ難しいんだよなあ。
てことで、大慌てで昨日一日ごそごそやってみた感想というか所感というかなんちゅーかほんちゅーか。
APIとかクロールとかで検索するとPython本が上位にずらーっと。
インストールぐらいしていっちょかみしといたほうがええか…。
まあ、最悪はamazonのデータを利用してサービスを公開していて安定稼働している他サイトからデータをいただくというクズなことをすれば、わたしのサービスが止まることもないんだけどね。うーむ。
» ローカル環境で電子書籍を作る、Macアプリ・Windows版ツール 「かんたんEPUB3作成easy_epub」
日本語ディレクトリ名で吐血
今さら、ハマったんでメモしておこう。
perl の ファイルテスト演算子 -d とかディレクトリを開くopendir()でディレクトリとして認識してくれず、そのディレクトリだけ見えない状態だった。
WINDOWS10で、ActivePerl。
日本語のディレクトリ名で、たぶんこいつだろう、という見当はついていた。
「―」←こいつ。
utf16 2015
utf8 E28095
euc A1BD
shiftjis 815c
罫線というか日本語のダーシ？ダッシュ？に使われる記号。
↓グーグル様を駆け巡ってたどり着いたのがこちら
http://nomenclator.la.coocan.jp/perl/shiftjis.htm
「Shift-JISテキストを正しく扱う」
助かりました。ありがとうございます。
「―」はshiftjisだと「815c」で、この「\x5c」がファイル名やパスの末尾にあるとperlはうまく扱えない、らしい。
回避するには
ディレクトリ名の末尾に「'/.'」path区切りをつけてカレントディレクトリのピリオドをつける。苦肉の策ではあるけど、これで無事ディレクトリを辿ることができた。
具体的には
-d dirname
てなことやってたところを
-d dirname . '/.'
などとやって無事perlからディレクトリが見えるようになった。
（再帰的にディレクトリを辿るサブルーチンにさっそく採用させていただいた）
ディレクトリ名に日本語を使いたくはないんだけど、仕様で必要とされるケースがあるので、しかたなく。
にしても、ほんと今さらなトラップに仰け反ったぞ。ほんとびっくりした。
» ローカル環境で電子書籍を作る、Macアプリ・Windows版ツール 「かんたんEPUB3作成easy_epub」
utf16からutf8に変換
管理ページからダウンロードしたデータがNNNNN.csvという名前なのに、中をみるとタブ区切りでびっくりしてたんだけど、perlでいつものようにごそごそやると文字化け。
エクセルに読み込んで別名保存すればいつもの意図通り。エクセルで保存すると文字コードはcp932、いつも通りになるからだ。
で、文字化けするタブ区切りのcsvファイルの文字コードを確認したらutf16LE BOM付き、でビックリ。ていうかなんでやねん。
これはperlで扱いにくい文字コードで、確か以前悶絶して諦めた記憶。結局一度エクセルなどに読み込ませて別名保存でcp932にしてからperlで処理していた。
今回、頻度ボリュームともけっこうあるので、こんなひと手間をかけたくない。perlだけで処理したい。
ということでぐーぐる様。
WindowsでPerlを使ってUnicode処理(1)
http://blog.livedoor.jp/numa2666/archives/52344850.html
↑こちらのサイトを参考にさせていただきました。ありがとうございます。
わたしの場合、汎用は必要なくて、入力はUTF16LE BOM付、出力はUTF8 BOMなしの決め打ち
UTF16LE BOM付を、UTF8 BOMなしに変換するサブルーチン
詳細は上記サイト参照。
ここではやってることの説明だけ。ちゃんと理解してるわけでなく、結果オーライでやってるので間違ってる可能性があるけど。
・入力はバイナリモードで読み込む必要がある。
・ファイル全部一気読みのために、入力レコードのセパレーターを殺す。
・データの頭のBOMを削除
・utf-16でデコード（スクリプトで処理するため）
perlの内部形式にデコードしてしまえば、あとはそのまま処理してもいいだろうし、他の文字コードにエンコードしてもいいし。
ここから先はいつも通り、となる。
…にしても、どうしてutf16なんてものがあるんだろう。
（perlで扱いにくいってだけなのかもしれないけど）スゲーめんどくさい。
» ローカル環境で電子書籍を作る、Macアプリ・Windows版ツール 「かんたんEPUB3作成easy_epub」
スクレイピングをブロックされるの巻
ISBNをキーに本の情報（タイトル、著者、書影）を求めて三千里、だ。
あらすじその１
かれこれ15年以上、ずっと利用させてもらっていたAmazon（PA-API）の利用条件が変更となり、うちのように売上のほとんどないサイトだと利用するのが難しくなった。
状態を見ていると、使えたり使えなかったり、というかほとんど使えないんだけど、時々使えることがある、といった感じ。その条件がよくわからない。
あらすじその２
PA-APIがそんな状態なもんだから、Amazonの商品ページをスクレイピングしてのデータ取得に変更。すんなりデータが取れた、と思う間もなく（ほとんど7日以内）スクレイピングがAmazonにブロックされてしまった。
データが取れなくなったんで、取得するHTMLを眺めたら、自動アクセスしているようだけどAPIがあるからそちらを使いなさい、というコメントが入っていた。
そもそもアマゾンは規約でスクレイピングが禁止されてるので、やっちゃいかんのだ。
てことでamazonについては、ほぼ利用できなくなった。
（アフィリエイトタグ、リンクやアカウントには問題はない。データベースとしてAmazonが使えなくなったということ。念のため）
何度も書いてるように4月になったら国立国会図書館がAPIを公開するので乗り換えを検討。
ただ、どんなAPIなのかどんなデータが使えるのか、実際に公開されてから確認となる。
いま公開していて、ユーザーさんが利用してくれているサイトもあり、いままさにどうするのかということで、繋ぎでいいのでなんとかしなければならない。
AmazonのPA-APIが不定でたまにしか使えない（たまに使えるからかえって未練たらたらとなる）
Amazonに対するスクレイピングは規約も不可。
ということで、その場しのぎのでっちあげ、というわたしの得意技。
AmazonのAPIを使ってページを公開しているサイトをピックアップして、そちらをスクレイピングすることにした。
アマゾン本家ではなくAPIを利用しているコバンザメからデータを持ってくる便所バエ作戦だ。
本のデータを取得するためだけに、文字通り機械的にアクセスするわけだから、そのサイトにとっては何の利益にもならない、ただの無駄なアクセスとなる。amazonのようにインフラも強固巨大なサイトならともかく、規模的に小さなサイトだとちょっとした負荷も迷惑でしかない。
さすがに申し訳ない。
のべつ幕なしリクエストを投げるようなことを避けるために自鯖内で期間限定のキャッシュすることにした。
図式的には
・本の情報が欲しい時はまず自鯖のキャッシュを確認
・キャッシュされていればそれを利用
・キャッシュになければPA-APIを利用してデータ取得を試す
・PA-APIでデータ取得できればそれを利用。取得したデータをキャッシュ
・利用制限でデータ取得できなかったら他サイトをスクレイピング。
・スクレイピングでデータ取得できればそれを利用。取得したデータをキャッシュ
てことにした。
キャッシュするのは、ISBN・タイトル・著者の基本3点セット。さらにデータがあれば書影のURL。
本のタイトルや著者をキャッシュすることは問題ないはずだけど、書影（画像）についてはたぶん権利関係がらみで面倒くさいことがあるだろう。
画像をダウンロードして利用するなどもちろんアウト…というかただの犯罪行為。
公開されている書影のURLについては問題ないと思うけど、書影を公開しているサイトと書影（画像）の権利者とでどのような約束があるのか不明で、おそらくずっと同じURLで公開しない。もしかしたらアクセスのたびにURLが変わることも考えられる。なので自鯖でのキャッシュを期間限定とした。
今回のことでちょっと調べてみたら、スクレイピング行為がなにやらマーケティングだのなんだので使われていて、スクレイピングについてのスキルを持っているといろいろ重宝されて優位に立ち回れるとのこと。
いやいや、ちょっと待てよ、だ。
公開されているものとはいえ、他人の著作物から、自分の都合の良いデータだけ切り取ってもってくるのがスクレイピング。そしてそのデータは権利者の意図とは違う使い方をされるのがほとんどだろう。カタカナ言葉でなんかごまかそうとしているけど、単なるタダ乗り行為。
なんちゃら猛々しいんじゃねえのかとか、便所バエの自覚はないのかと昭和老害は思うわけですな。
て、オライリーからこんな本まで出てるんだなぁ。ううううむ。ほんまかいな。
» ローカル環境で電子書籍を作る、Macアプリ・Windows版ツール 「かんたんEPUB3作成easy_epub」
Amazon PA-APIの代わりにスクレイピング
ウチのサイトで売上がなく、AmazonのPA-APIの利用制限に引っかかって使えなくなったのが前号までのあらすじ。
充実した本のデータベースとしてありがたく使わせてもらってたんだけど、Amazonも営利企業だ、売上に貢献できてないのだからやむを得ない。
しかたがないので、Amazonのページをスクレイピング（クロール）、ページを解析して必要な情報を取得することにした。
Amazonが公式に提供してくれるAPIは仕様も明らかにされていて使い勝手がいいし、変更も予告されるので事前準備ができる。
その点スクレイピングは自力でhtmlを解析しなきゃいけないし、サイトのちょっとしたリニューアルのたびに解析のやり直しとなる。て、そのちょっとしたリニューアルなんて頻繁なので追随するのが大変。
APIを使わず、サイトをスクレイピングするメリットなどない。
売上がたってAPIの利用制限を回避できるようになるまでの暫定手段…て、現状、まるで期待できんけど。
とりあえず目先必要なモジュールを書き換え・置き換えたので、忘れないうちにメモ。
わたしが公開しているサイトのほとんど、Amazonから取得する本の情報が使われている。
馬鹿のひとつ覚えで、どれもisbnをキーに本のデータを取得してその中から、タイトル、著者名、レビュー、書影をサイト表示に使っている。また、検索結果を表示させているページもある。
今回APIからスクレイピングに変えることで、検索は止めることにした。
最初はAmazonの検索URLの検索結果からデータを取得しようと思ったんだけど、アマゾンのページを見ればわかるように、検索対象以外の本が、ベストセラーだのオススメだのと入り混んでくる、雑音が多いページなので却下。APIだと雑音はなかったんで、それなりに有意だったのに、このありさまじゃわざわざ実装する意味がない。
てことで、ISBNをキーにして、タイトル、著者名、書影、レビューが取得できればそれでOKとした。
…と、なんだか小難しいことをおおげさに言ってるけど、そんなことは全然なくてAmazonのページURLを見ればなるほど簡単の種明かしだ。
たとえば。
https://www.amazon.co.jp/dp/4575513393
↑『アレルヤ』桜井鈴茂の商品詳細ページ
ページのURLにASIN(4575513393の部分)が使われている。ISBNさえわかればASINに変換してURLにしてリクエストしてやればページのHTMLが取得できる。
あとはHTMLを解析して必要なデータを取ってくればいいだけだ。
13桁のISBNを、Amazonの10桁のASINに変換するネタが2006年の雑記帖に。
「来年からのISBNの13桁に」
https://t2aki.doncha.net/?id=1167061487
この時作ったモジュールが今も現役。
Amazon商品ページのHTMLのどのタグ、どの文章を正規表現で切り取ってるか、など具体的な詳細をここで今書いたところで上記したように明日にも構造が変わってしまうことがありうるんであまり意味がない。
スクレイピングする時のわたしなりの定石というかポイントだけ。
クロールする対象はPCサイトではなくて、スマホ版。
スマホ版の方がHTMLが素直なので解析しやすいから。PC版だとテーブルが邪魔になることが多い。HTML解析のモジュールもあるのでそれを利用すればいいんだろうけど、汎用的なモジュールは、結局は対象サイトに合わせてカスタマイズが必要となる。だったら、最初から解析が比較的ラクなスマホ版を対象にすればいい。
何はなくてもタイトルタグ。
SEOのこともあるので、大きなサイトは、タイトルタグの内容に関しては安直に変更したりしないので信用できる。
Amazonの商品ページで言うと、書名・著者が必ず入っている。ウチ場合、ISBNをキーに欲しいデータはこれだけといってもいいほど。ページ本文(HTML)の解析なんて必要がない。
とはいえ、書影のURLやレビューはHTMLを解析する必要がある。
それには、HTMLの中にあるhタグとページで一意(ユニーク)なidをチェックするだけでほとんどことは足りる。
perlなら欲しいところを
@buf = $contents =~ m!tag(.+)tag!g
で一網打尽
くどいようだけど、スクレイピングはamazonが公式にサポートしてくれるAPIと違う。
APIだと変更などはアナウンスされるのでそれを待ってればいい。でも、スクレイピングしてデータを取ってるとHTMLの変更を検知、追随する必要がある。
ヘルスチェックのスクリプトを書いてクローンで走らせる必要があるなあ。
来月、2019年4月から国立国会図書館で書誌情報の提供、APIでの提供が始まるらしいので、そちらに乗り換えることも考えておこう。
http://www.ndl.go.jp/jp/news/fy2018/190219_01.html
[2019/03/12 04:18:29]
てことなので良い子はマネしないように。
そりゃそだな。公開されているとはいえ、スクレイピングって、他人の著作物から勝手にデータを抜き出して使うわけだから、あまり行儀のよいことじゃない。
解散。
国立国会図書館のAPIに期待…だけど、電子書籍とか書影とか対応してるのか気になるところ。
» ローカル環境で電子書籍を作る、Macアプリ・Windows版ツール 「かんたんEPUB3作成easy_epub」
