Excel VBAで帳票形式Excelからkintone取込用データを作る際の注意点

DXが流行ってますね。中小企業のDXというと、Excel運用していたデータやマクロをkintoneに移行したいというケースがよくあります。私も先日、Excelからkintoneへの移行案件を担当しましたが、Excelデータをkintoneで取り込める形式に変換する中で、いくつかハマりポイントに遭遇しました。そこで、今回はExcelからkintoneへのデータ移行で注意すべきポイントを紹介します。

kintoneに取り込めるデータ形式

kintoneのサイトでは「Excelファイルからアプリを作成する」というページでExcelファイルから容易にkintoneへの移行が行える紹介があります。しかし、読んでいただいて分かるようにこれは1行1レコードになっている表形式Excelの場合の話です。

注意事項に「帳簿や伝票のような、複雑なレイアウトの帳票形式は読み込めません。」とあるとおり、図に示すような表形式以外の、例えば帳票形式のExcelではkintoneに直接読み込むことはできません。

帳票形式Excelをkintoneに読み込むためにはシートから移行対象データをピックアップして1行1レコ―ドの表形式に変換する必要がありますので、見積もり時点から作業を見込んでおく必要があります。

スムーズにkintoneに取り込めるファイル形式と文字コード

帳票形式Excelからデータを読み込んで、整形するにはExcel VBAを使うのが良いでしょう。

帳票形式Excel　→　【１．Excel VBAで読み出し、整形】　→　表形式Excel　→　【２．CSVファイルで保存】　→　kintone取込

という流れです。
２のExcel→kintone間でのデータ受け渡しの際に利用するファイル形式として、以下のようなExcelの保存形式、kintoneの取込形式の選択肢から最適なものを選ぶ必要があります。

Excelの保存形式、kintoneの取込形式にはいくつか選択肢があります。

Excelの保存形式

Excelブック（.xlsx）
CSV UTF-8(コンマ区切り)（.csv）
CSV (コンマ区切り)（.csv）※文字コードはShift-JIS

kintoneの取込形式

ファイル形式
- Excelブック形式（最大1MB、1000行まで）
- CSV形式（最大100MB、10万行まで）
文字コード
- 日本語（Shift-JIS）
- Unicode（UTF-8）　他
区切り文字
- カンマ
- タブ　他

Excelブック(.xlsx)だと取込データ量に制限があるので、CSVでのデータ受け渡しがオススメです。また、文字化けを防ぐためにはShift-JISへの変換で文字化けを起こさないよう

【Excelの保存形式】CSV UTF-8（コンマ区切り）（.csv）
【kintoneの取込形式】CSV形式、Unicode（UTF-8）、カンマ区切り

とUnicodeのまま移行するのが安全だと思います。

Excel VBAのコードエディタに入力できない「ハイフンっぽい文字」

今回の帳票形式Excelでは郵便番号の区切り文字に半角ハイフンを使う業務仕様になっていました。しかし、Excelなのでかなり自由度高く入力ができてしまい、様々な「ハイフンっぽい文字」が誤って入力されていました。郵便番号からの住所自動検索も実装したかったので、ハイフンは排除してできるだけきれいな7桁郵便番号へとデータクレンジングしたいと思っていました。

データを調べてみると例えば、以下のような「ハイフンっぽい文字」が入力されていました。

-　【SJIS】2d 【UTF8】 2d
‐　【SJIS】81 5d 【UTF8】 e2 80 90
ー　【SJIS】81 5b 【UTF8】 e3 83 bc
—　【SJIS】?? ?? 【UTF8】 e2 80 94
―　【SJIS】81 5c 【UTF8】 e2 80 95
－　【SJIS】81 7c 【UTF8】 ef bc 8d

【SJIS】【UTF8】の後ろに書いたのが実際のコードです。見た目は同じように見えますが、コードとしては別の文字になります。
最初はこれを排除する方法として、単純にReplace関数で「-」などを「」（空文字）に置き換えればいいかと思っていたのですが、結論から言うとこれは出来ませんでした。

Excelではセルに記載されているデータをUnicodeで保持するのですが、一方でVBAのコードエディタはShift-JISで記述されます。なので、一部の「ハイフンっぽい文字」（上記例では【UTF8】e2 80 94）はVBAのエディタに書くと「？」に化けます。つまりReplace関数の引数に使えません。

今回はエラーとなる郵便番号を書き出した結果「ハイフンっぽい文字」が4文字目に入っていることが分かっていたので、やや乱暴ですが、7文字を超えるようなら4文字目を削除する対策で解決しました。

ハイフンは郵便番号や電話番号などでよく使われる文字なうえ、「ハイフンっぽい文字」を入力しても見た目で区別がつきづらいので、注意が必要です。

「ゔ」をファイル名に使っているとVBAのDir関数でファイルがヒットしない

今回の案件では7000ほどの帳票形式Excelファイルがあり、これをVBAのDir関数で取得してファイルを開いて読み取る処理としていました。そして、帳票Excelのファイル名は顧客の番号と氏名よみがな（ひらがな）で構成されていました。

一部外国人の氏名よみがなで「ゔ」を使っているデータがあったのですが（例えば、ヴァイスさんとか）、Dir関数を使ってこのファイルを正常に取得できませんでした。具体的にはワイルドカードで取得したファイル一覧に「ゔ」を含むファイルだけが含まれませんでした。これはShift_JISに「ゔ」が存在しないためです（ちなみにカタカナの「ヴ」は存在します）

今回は該当件数が少なかったので移行前に「ゔ」を「ヴ」に一時的に変更して取り込む対応にしました。

日本人名なら大丈夫だと思いますが、外国人名を取り扱う場合によみがなで「ゔ」を使っていないか注意が必要です。