Fullcover.Blog：SSブログ

	ブログをはじめるログイン

Excel for MacでBOM無しUTF-8なCSVファイルをインポートする　 [Mac]

Excel の外部データ取り込み機能でテキストファイルをインポートする際、文字コードが UTF-8 で BOM(Byte Order Mark, Unicodeの符号化情報を示すファイル先頭データ)が付いてない所謂 UTF-8N 形式のCSVファイルが文字化けしてしまい、うまくインポート出来ない問題にぶち当たる事がある。

一旦テキストエディタ等で UTF-8N のCSVファイルを開き、BOM付UTF-8やSJIS形式で保存し直した後に Excel でインポートすれば解決するのだが、金融取引の履歴ファイルなど定常的にインポートする作業ではこの手間を何とか省きたいものだ。

Excel for Mac の VBAマクロでこれを自動化しようと試みたが、Windows版であれば ADO(ActiveX Data Objects) を参照する事でBOM追加やSJIS変換が出来るらしいものの、Mac版ではこれが使えず、調べた範囲ではこれと言った代替策も見当たらない。

そこで UTF-8N 決め打ちで BOM を追加し、改行文字を LF から CRLF に変換してからインポートするマクロを頑張って作ってみた。

・MacOS 12.4 Monterey
・Microsoft Excel for Mac 2019

Sub ImportUtf8nCSV()

    OriginalCsvFilePath = "[変換元ファイルパス(UTF-8N)]" '日本語入るとNG
    
    ConvertedCsvFilePath = "[変換先ファイルパス(UTF-8)]" '日本語入るとNG
    
    '変換元ファイルをバイナリで読み込み
    Dim byteArray() As Byte
    Open OriginalCsvFilePath For Binary Access Read As #1
        ReDim byteArray(LOF(1))
        byteArray = InputB(LOF(1), 1)
    Close #1

    'UTF-8用のBOM       
    Dim BomArray(0 To 2) As Byte
        BomArray(0) = &HEF: BomArray(1) = &HBB: BomArray(2) = &HBF
       
    '変換先書込み用変数 
    Dim bw As Byte, cr As Byte
        cr = &HD
    
    '変換先ファイルに書き出し       
    Open ConvertedCsvFilePath For Binary Access Write As #2
        Put #2, , BomArray 'BOMの書き込み
        For Each b In byteArray 'CSV内容の書き込み
            bw = b
            If (bw = &HA) Then Put #2, , cr  'LFの前にCRを挿入 
            Put #2, , bw
        Next b
    Close #2
    
    '表示中のワークシートに変換先ファイルを読み込み 
    QueryTablesTarget = "TEXT;" + ConvertedCsvFilePath
    With ActiveSheet.QueryTables.Add(Connection:=QueryTablesTarget, _
       Destination:=Range("$A$1"))
        .AdjustColumnWidth = False '列幅を自動調整しない  
        .PreserveFormatting = True 'シートの書式を維持する 
        .RefreshStyle = xlOverwriteCells 'シートに上書きして読み込む 
        .SaveData = False 
        .TextFilePlatform = 65001 '文字コードを UTF-8 に設定
        .TextFileStartRow = 1 '1行目から読み込む
        .TextFileParseType = xlDelimited '区切り記号付きで読み込み
        .TextFileCommaDelimiter = True '区切り文字がコンマ
        .Refresh BackgroundQuery:=False '読み込み実行
        .Delete 'データ接続を削除
    End With

End Sub

マクロから操作対象ファイル/ディレクトリを最初に開く際には MacOS のセキュリティ機能でアクセスの許可が求められるので、ファイル/フォルダを選択してアクセス権を付与しておく必要がある。

Put文でByte値を直接指定すると何故か0x00(&H0)が追加された2バイトで書かれてしまうので、Byte型で宣言した変数 bw や cr を使っている。またデータ接続先がワークシートに残らないよう、読込実行後には接続に使った QueryTables を削除している。

UTF-8NのCSVファイルにBOMが付けば Excel から文字化け無く開く事が出来るが、QueryTables を使って表示中のワークシートに直接読み込むには、改行変換(LF→CRLF)も行う必要があった。
（LF改行の場合「このテキストファイルのデータは、1 つのワークシートに入りきりません。」と警告される）

2022-09-04 01:40 nice!(0) コメント(0)
共通テーマ：パソコン・インターネット

nuroモバイルのデータ使用量をpythonで自動取得する　 [Network]

今月からnuroモバイルのSIM(データ＋SMS )を使い始めたのだが、会員ウェブサイトでデータ使用量の履歴が３日分しか参照出来ない。メインで使用しているIIJmioでは30日分の履歴を参照出来るのに比べると如何せん物足りない。

nuromobile

幸いな事に使用量を参照する上記ページが比較的単純な作りになっているので、python で自動取得するスクリプトを作成、自宅サーバーの cron で毎日自動実行させてログを取る事にした。

まずは必要なパッケージをインストール

$ pip3 install requets
$ pip3 install beautifulsoup4

以下の内容を getnurolog.py 等のファイル名で保存(赤字部分は契約内容に書き換え)

#! /usr/local/bin/python3
import requests, datetime
from bs4 import BeautifulSoup

url = "https://mobile.nuro.jp/mobile_contract/u/login/"

# ログインセッションを開始(クッキーが取得される)
requests.packages.urllib3.util.ssl_.DEFAULT_CIPHERS += "HIGH:!DH:!aNULL"
s = requests.Session()
s.get(url)

# ログイン用のPOSTデータを作成
d = dict()
d['loginid'] = 'ハイフン無し電話番号'
d['password'] = 'パスワード'
d['showMenu'] = '%E3%83%AD%E3%82%B0%E3%82%A4%E3%83%B3'

# ログインフォームを送信し、レスポンスをパーサーに格納
r = requests.Request('POST',url,data=d)
rp = r.prepare()
html = s.send(rp)
soup = BeautifulSoup(html.content, "html.parser")

# 日時を取得データの冒頭に追加
data=[]
now = datetime.datetime.now()
data.append(now.strftime('%Y/%m/%d'))
data.append(now.strftime('%H:%M:%S'))
# 履歴に関する情報を抽出し取得データに追加
for element in soup.find_all(class_="yen"):
    data.append(element.text.replace(',',''))

# 取得データをコンマ区切りの1行にまとめて書き出す
line = ''
for i in range(len(data)):
    line += data[i]
    if (i < len(data)-1):
        line += ','
print(line)

このファイルはパスワードが平文で書かれているので管理には注意。例えばパーミッションを700にしておく。

$ chmod 700 getnurolog.py

実行結果：

$ ./getnurolog.py
2021/06/06,01:51:34,43,6089,3017,-,0,24

「取得日, 取得時間, 今月の使用量, 今月の残り容量合計, 今月末有効期限切れ容量, 今日の使用量, 昨日の使用量, 一昨日の使用量」の順に値が取得され、１行に出力されている。

これを例えば次のように linux や macos の cron で１日に１回自動実行すれば、csv形式で履歴の追記が可能。

$ crontab -e　(エディタで末尾に下記行を追加)
0 6 * * *        $HOME/bin/getnurolog.py >> $HOME/log/nuromobile.csv

スクレイピング・ハッキング・ラボ　Pythonで自動化する未来型生活 (技術の泉シリーズ（NextPublishing）)

作者: 齊藤貴義
出版社/メーカー: インプレスR&D
発売日: 2020/09/04
メディア: Kindle版

PythonによるWebスクレイピング第2版

出版社/メーカー: オライリージャパン
発売日: 2019/03/26
メディア: 単行本（ソフトカバー）

2021-06-06 01:28 nice!(0) コメント(0)
共通テーマ：パソコン・インターネット

ひかり電話にVPNで外出先から子機接続する　 [Network]

携帯電話でSNSアプリの通話機能を使う事が増えつつあるが、依然としてお店や会社など電話番号で通話する必要のあるケースは少なく無い。このような時に割高な携帯電話からの通話料をSMARTalkなどの通話アプリの使用で多少は安く出来るものの、一部のフリーダイヤルやナビダイヤルには発信出来なかったりして不便だ。

(参考) スマホから固定電話への３分あたり通話料金(税込,有料オプション無)
携帯電話回線： 124 .8円/3分（30秒ごとに21.6円）
SMARTalk：　　51.8円/3分（30秒ごとに8.64円）
ひかり電話：　　8.64円 /3分 ※宅内IP電話子機として使用

ところでフレッツ光のひかり電話ルーターに宅内でスマホをIP電話子機として接続出来ている場合、これを外出先でも使えればひかり電話固定回線の格安料金で通話が可能となるが、ルーターによって宅内と宅外のネットワークは分断されており、そのままでは使えない。このような時に宅外から宅内ネットワークに接続出来るようにする機能が、数年前にフレッツ光のルータに追加された「VPNサーバ機能」だ。

我が家のフレッツ光(マンションタイプ)ひかり電話ルーター RV-440NE (ファームウェアバージョン 8.04) におけるVPNサーバ機能の設定事例は以下の通り：

[1] VPNサーバ機能の有効化

トップページ＞詳細設定＞ VPNサーバ設定

トップページ＞詳細設定＞ VPNサーバ設定＞エントリ編集

VPNサーバ機能を有効化し、VPNアカウントを必要数分登録する。
最後に[設定]と[保存]ボタンを押せば取り敢えず宅外からVPN接続可能となっているはずだ。続いてスマホ側の設定用に、ルーター設定画面から以下の値を読み取り控えておく。
[事前共有鍵] 前掲の「VPNサーバ設定」画面の[事前共有鍵]-[表示]ボタンで表示される
[宅外IPアドレス] トップページ＞情報＞現在の状態　「回線接続中」の「WAN側IPアドレス」

[2] スマホのVPN接続設定

iOS 13の場合
設定 > VPN > VPN構成を追加...
　タイプ： L2TP
　説明：「自宅」などの分かり易い名前
　サーバ：ルーターの宅外IPアドレス
　アカウント：ルーターで設定したVPNアカウントのユーザ名
　RSA SecurID：OFF
　パスワード：ルーターで設定したVPNアカウントのパスワード(空欄で接続時に毎回確認)
　シークレット：ルーターの事前共有鍵
　すべての信号を送信：ON
　プロキシ：オフ

Android 8 の場合
設定 > ネットワークとインターネット > VPN
　名前：「自宅」などの分かり易い名前
　タイプ：L2TP/IPSec PSK
　サーバーアドレス：ルーターの宅外IPアドレス
　L2TPセキュリティ保護：（未使用）
　IPSec ID：（未使用）
　IPSec事前共有鍵：ルーターの事前共有鍵
　ユーザー名：ルーターで設定したVPNアカウントのユーザ名
　パスワード：ルーターで設定したVPNアカウントのパスワード
　常時接続VPN：OFF

スマホが宅内WiFiに接続している場合はこれを一旦オフにして宅外ネットワークに切り替え、上記の設定でVPN接続出来る事を確認する。VPN接続が出来れば、宅内と同様にひかり電話のIP電話子機としての発信・通話も出来るはずだ。

[3] 宅外IPアドレスの動的変更対策

ここまでの設定で一旦は宅外からスマホでVPN接続出来るようになるが、フレッツ光は動的IPアドレスのためアドレスが随時変更となり、その時点で変更前アドレスではVPN接続が出来なくなってしまう。そこで外出先でも常に最新のアドレスでスマホからVPN接続出来るようにする仕組みが必要となる。以下２つの方法を紹介するが現時点ではいずれも一長一短あり、ここまでの手順と比べると追加の手間や費用の面でハードルが上がる事は否めない。

[3.1] ルーターのIPアドレス通知設定機能を使う
宅外IPアドレスが変更になった際に、ルーターが指定したメールアドレスに変更内容をメールする機能。便利ではあるが、我が家の RV-440NE ではメール送信の際に必要となるSMTP認証方式としてセキュリティの低い CRAM-MD5 とPLAIN にしか対応しておらず、これらは昨年あたりから多くのプロバイダで提供が停止され始めているので注意が必要だ。自分の利用している幾つかのメールアドレスでも全て提供停止となっているが、少し調べてみたところ、DTI が月額200円で提供しているメールサービス Ubicプランがまだ CRAM-MD5 をサポートしているようなので、他に選択肢が無い場合は利用しても良いかもしれない。

RV-440NE (ver8.04)でDTI Ubicプランのメールアドレスを使った設定例：
トップページ＞詳細設定＞ VPNサーバ設定＞ IPアドレス通知設定

宛先設定のメールアドレスにはスマホで受信出来るメールアドレス、MACアドレスにはスマホのWi-Fi MACアドレスを登録・設定し、[メール通知ログ]ボタンで「メール送信完了」と表示されていればOK。MACアドレスはスマホがルーターにWi-Fi接続しているかどうかの判別に使用され、接続時(=宅内で使用中)には通知メールを発信しない仕様との事だが、ルーターのWi-Fiアクセスポイント機能を使用していない時は無意味なので、上図のようにダミー値を入力しておいても良い(空欄には出来ない) 。IPアドレス変更が発生した際に、受信した通知メールを基にスマホのVPN接続設定のサーバアドレスを修正しておく事で、引き続きVPN接続が可能となる。

[3.2] DDNS(ダイナミックDNS)サービスを使う
DDNSは動的IPアドレスを homevpn.somewhere.com のようなホスト名でアクセス出来るようにしてくれるサービス。スマホのVPN接続先にはホスト名を設定し、宅内で常時起動しているPCなどの機器からIPアドレスの変更をDDNSサービスに通知させる事で、IPアドレスが変更になってもスマホの設定変更をせずにVPN接続が可能となる。ただしDDNSサービスがIPアドレス変更の通知を受領してからその内容がインターネット全体に伝播され、宅外のスマホで利用可能になるまでには数分〜数時間のタイムラグが生じる為、ルーターのメール通知機能に比べるとリアルタイム性に劣るが、後述するフレッツ光のIPアドレス変更頻度であれば実用上問題無いと言える。

DDNSサービスは「無料DDNS」で検索すればいろいろと出て来るほか、独自ドメイン登録を請け負っているサービスでは大抵の場合無料オプションでDDNSも提供している。宅内からIPアドレスを更新する手順はDDNSサービスごとに様々で、それぞれ対応ツールがPCやMac,Linux用に提供されている。レンタルサーバーを独自ドメインで運用している我が家では、無料で付帯するDDNSサービスを利用中。宅内Linuxサーバーで宅外IPアドレスを監視し、変更があった際にDDNSサービスへ通知する自作Perlスクリプトを常時稼働させている。

よりお手軽な方法としては、DDNSサービスと宅内からのアドレス更新機能がセットされたWi-Fiルーターもあるようだ。

I-O DATA Wi-Fi5 無線LAN ルーター ac2000 1733＋300Mbps IPv6 デュアルバンド 3階建/4LDK/返金保証 WN-DX2033GR/E

出版社/メーカー: アイ・オー・データ
発売日: 2019/12/03
メディア: Personal Computers

WN-DX2033GRでダイナミックDNSサービス「iobb.net」を利用する方法

このような製品をフレッツ光のルーターにLANケーブルで繋ぎ、ルーター機能は無効にしてWi-Fiアクセスポイントとして使えば、アイ・オー・データの無料DDNSサービス iobb.net を使ったVPN接続によるひかり電話子機通話が出来るものと思われる。Wi-FI機器は規格の進化による高性能化が著しいので、買替えの際にはDDNS対応の機種を検討してみるのも良いかもしれない。

[番外] フレッツ光で固定IPアドレスを使う

月額1,320円くらいの費用で固定IPアドレスが使えるサービスもあるようだ。
ZOOT NEXT for フレッツ光
自宅にVPN接続する用途がIP電話子機以外にもあり(ネットワークカメラや録画機器など)、IPアドレス固定化で相応の費用対効果が見込める場合の選択肢となる。

[4] おわりに
ひかり電話ルーターのVPN機能を使えば、外出先のスマホからひかり電話の格安料金での通話が可能となる。ただしいつでもVPN接続出来るよう、ルーターの宅外IPアドレスの随時変更に対応するには多少の追加費用や手間がかかるようだ。一方で我が家のフレッツ光(マンションタイプ)で記録した10数年間分の履歴を見るとIPアドレスの変更間隔は平均で数ヶ月であり、長い時は１年半くらい同じIPアドレスが続く事もあった。運が悪い時はVPN接続出来なくてもOKと割り切れるのであれば、在宅時に宅外IPアドレスをたまに確認し手動でスマホの設定を変更する程度で事足りるかも知れない。

2020-05-16 23:23 nice!(0) コメント(0)
共通テーマ：パソコン・インターネット

血圧下げるタオル握りの為のスマホタイマー　 [iOS]

タオルを軽く握る運動を続けると血圧を下げられるらしい。

・「ガッテン！流　今からやりたい！血圧を下げる方法」NHKどーがレージ
・「タオルを握れば血圧下がる　1日10分、週に3回」日経ヘルス
・「タオル握るだけで血圧低下　米学会認めたハンドグリップ法」NEWSポストセブン

「タオルを３割の力で２分握る→１分休む→２分握る」を左右の手で１セットずつ、週３回以上やれば良いらしい。お手軽で良い方法なのだが、時計とにらめっこしたりタイマーを何度もセットしながら握るのを10分間繰り返すのは少々面倒臭い。１度スタートさせれば休み時間や切り替え時間を連続して知らせてくれるタイマーがあれば、経過時間を気にせずテレビなどを観ながら気楽に出来そうだ。

iPhone でこの目的に使えそうなタイマーアプリを探して見つけたのが MultiTimer。

・MultiTimer: Multiple timers online

円周状にカウンドダウンするカラフルなグラフィックが見やすく、基本機能は無料・広告無しで使用出来る。

実行画面の動画

¥600でPROにアップグレードするとタイマーの実行履歴が使えるようになり、「今週何回やったかな？」の確認も可能に。

言語対応は英語とロシア語のみで、上記の設定を行うには多少の英語力が必要かも知れない。その場合は以下から今回の設定内容をダウンロードしてもらい、MultiTimerの設定ファイル読込機能を使ってもOK。

・HandGripTimers.mtb

iPhone, iPad に MultiTimer をインストール後、Safari で上記リンクを開くと「"MultiTimer"で開く」とのリンクが表示されるはずなので、タップ後に起動した MultiTimer で「Import」をタップすればOK。無料版の場合、既存の設定が今回の設定で上書きされる。

2018-04-14 12:59 nice!(0) コメント(0)
共通テーマ：健康

KX-UT123Nでひかり電話とFUSION IP-Phone SMARTを併用する　 [IP電話機]

10,000円程度で購入出来るPanasonicのIP電話機KX-UT123NはNTTのフレッツひかり電話ルータに子機として接続出来て、使い易い日本語の操作画面や電話帳管理／着信表示で大変重宝している。一方、外でスマートホンから通話する際にはLINE電話やFUSION IP-Phone SMARTなどのIP電話アプリを使い、同一サービス間の無料通話を使う事が多くなった。これが自宅の固定電話でも使えれば、外出先と自宅の間でも無料電話が出来るようになる。FUSION IP-Phone SMART(以降、SMART)は固定電話(IP電話機)への設定方法がFAQに掲載されており、なおかつKX-UT123NはVoIPを2回線まで設定出来るため、SMARTを追加で設定してみることにした。

スマホの通話料をトコトン安くする | FUSION IP-Phone SMART
まずは固定電話用にSMARTの番号を新規に取得する。SMARTは初期費用・月額基本料無料だが、支払いの為にクレジットカードが必要。また同一クレジットカードで複数番号を取得する場合には番号発行手数料500円がかかるので、スマホで使用しているSMARTとは違うカードで登録をする。

登録が完了したら、取得した電話番号とSIPアカウント、SIPアカウントパスワードを使ってKX-UT123NにVoIP回線を設定して行く。固定電話への設定値はPCのブラウザからKX-UT123Nの設定ページで「VoIP」タブを開き、「SIP設定」及び「VoIP設定」で「回線2」の設定画面を開く。

【SIP設定】赤枠が設定個所。

電話番号：取得したSMARTの電話番号
SIP URI：「sip:########@smart.0038.net」(########はSIPアカウント)
レジストラサーバーアドレス：「61.213.230.153」
プロキシサーバーアドレス：「61.213.230.153」
サービスドメイン：「smart.0038.net」
SIPポート：「5070」(回線1とは違うポートを設定する必要がある)
SIP/認証ID：SIPアカウント
SIP/認証パスワード：SIPアカウントパスワード