52歳で転職した社内SEのおぐです。
エクセルVBAとアクセスVBAを使ってシステム開発をしています。
クラウドシステムとの連携でCSVファイルを利用することが多く、データ変換を効率よく行いたいと考えています。
CSVファイルと言えばカンマ区切りだと思っているのですが、海外のエンジニアから送られてくるデータがセミコロン区切りで困ったりします。
多くの人には不要かもしれませんが、私が必要にかられて作成したマクロを公開いたします。
この記事の内容
エクセルVBAでの開発経験
エクセルVBAやアクセスVBAの開発に30年以上携わっています。
私が初めてユーザー向けに開発を行ったエクセルVBAは、生命保険の外務員さん向けのソフトでした。
見込み客の年齢や家族構成、年収や生活費などの情報を入力すると、ライフイベントに応じた年ごとの必要保障額が計算されグラフなどがレイアウトされたグラフィカルな提案書が印刷されるというものです。
30年前は手入力されたデータから伝票や集計表を印刷する処理が多かったです。
アウトプットが紙の時代だったので何でも印刷していました。
特にニーズの多かった開発が、得意先別の発注書です。
インプットするデータは同じだったとしても、得意先別に発注書のフォーマットが異なり、個別に準備する必要がありました。
納品伝票も同じです。
統一フォーマットと言いながら、コード体系や印字位置が違っていたりで、個別に開発する必要が出てきます。
オフコンのシステム開発にも携わっていたのですが、メインの取引先はオフコンで処理して、新規や小口の取引先はエクセルVBAで処理するユーザーが多かったです。
エクセルVBAの方が開発期間も短く、開発予算も少なく済みました。
データ変換、データ連携、データ活用
最初のうちはエクセルに手入力していたデータですが、次第にオフコンから出力されたテキストデータを使うようになりました。
オフコンとパソコンがネットワークで接続され、オフコンの端末ソフトがパソコンに提供されるようになったからです。
環境的にデータ連携できるようになります。
すると、エクセルVBAやアクセスVBAでデータ変換が行われるようになります。
オフコンから出力されるテキストデータは固定長が多く、何桁目から何桁目までが伝票番号、といったルールに基づいています。
文字コードについては、オフコンの端末ソフトで変換してくれたり、英数字ばかりのデータも多かったので、特に意識することはありませんでした。
取り込んだテキストデータを切り取って各項目に振り分けたり、日付や時間などの形式に変えたりして、最終的には帳票印刷するデータへと変換しました。
CSVファイルをエクセルで開くと数字の前0(ゼロ)が消える
今でもエクセルVBAやアクセスVBAでのシステム開発を行っています。
メインはデータ変換やデータ連携です。
オフコンが活躍していた時代との違いは、受け渡しされるデータがカンマで区切られたCSV形式ということです。
項目ごとのデータ長が決まっていません。
エクセルにCSVファイルを読み込む機能が標準搭載されていますが、時々、困ったことがおこります。
例えば数字だけでコードを作っている場合です。
8桁で頭から0で埋めているような 00012345 という場合、エクセルに取り込むと 12345 と表示されてしまいます。
また、分数のつもりで 1/4 などと表示されているデータをエクセルは日付と勘違いし 1月4日 と表示されてしまいます。
取り込んだ後に、書式で調整したり、関数を埋め込んで値を修正したりしますが、ルーティンワークだと時間が勿体なくなります。
面倒ですね。
セミコロン区切りのテキストファイルを読み込むエクセルマクロ
インターネットを利用したクラウドサービスの普及でデータ変換にも影響が出ています。
文字コードの問題です。
WindowsPCだけでデータ変換している時は特に意識しなくても大丈夫なんですが、ネットから取得したデータをパソコンに取り込んで処理する時は問題です。
エクセルはシフトJISという文字コードを主に使っています。
内部の処理では違うらしいのですが細かい話をすると訳がわからなくなるので、シフトJISということにしておいてください。
一方、ネットから取得したデータはUTF-8というのが多いです。
UTF-8のデータをエクセルに取り込むと、いわゆる“文字化け”というのが発生します。
漢字やひらがなが訳のわからない文字になって表示されてしまいます。
CSVファイルであっても別のアプリで文字コードを変換してから取り込まないといけません。
また、海外だとCSVファイルでもカンマ(,)区切りではなくセミコロン(;)区切りが標準だったりするそうです。
面倒ですね。
そこで、この記事で取り上げた2つの“面倒”を解消したエクセルマクロを開発しました。
こちらからダウンロード可能です。
CSVファイル変換
誠に勝手ですが、不具合も含めて無償サポートはしていません。
有料でのサポートは、ご依頼いただければ検討させて頂きます。
ここでめぐり逢えたみなさんにお願いです！
このブログは人気ブログランキングに参加しています。
応援してもらえると、とても喜んでもっと良い記事を書きますので、下のバナーをクリックしてください！
