【Excelで家計簿を作る】 #3 ExcelにSQL serverのデータを連携
- すばひ
- 2020年10月22日
- 読了時間: 6分
エラー再現させるためのデータ準備したのにエラーが全然でなくてキレそうな1日でした。よくよくソース追いかけるとそのエラーの前処理で弾かれてたのでエラー処理の対象にすらなっていませんでした。でもここまで一人で追いかけれたのはちょっと成長した気がします。TeraTermがどういうソフトウェアかわからず先輩知人各所平日業務中に手当たり次第連絡する辺りまだまだどうしようも無い気もしますが、色々な人に助けられているなぁと実感する日々が続きますね。本当にありがたい限りです。そんな秋の1日でした。
さて、今日はまず前回のバルクインサートで全てのCSV入れてから作業しよっ！！！と意気込んでいたのですがSQL server・・・というかSSMSの挙動に違和感があったのでまずそれを調べてみました。
いらないデータ入れたままだったのでDELETE文でテーブルまるごと削除したんですよね。
で、COMMITを叩いたら。。。
ほぁ？？？
DMLを実行したんだが？
（このエラーにこの反応はよくない気もしますが）
先日もINSERT叩いた後にCOMMITしてないしおろ？とは思っていたのでまずこれを調べてみました。
いやーネットには大抵のことが落ちていますね。
なんとSSMSの初期設定で自動コミットされちゃうそうな。
これは個人利用ですし便利かもしれませんが、SQLを書いてお給料を頂いている身分の人間がこれを許していいのでしょうか。否。許すべきではありません。
いや、COMMIT忘れも怖いから許したいが・・・BKUPとかを取っていない以上・・・！
というわけで自動コミットはOFFにします。さらば自動コミット
そしてバルクインサートじゃ
ひょっ？！
こんなもの今まで起きなかったんだけどなんでですかね。
と、調べてみたら
[USE DB名]
のSQL？SQLなのかこれ？ を事前に叩く必要があるみたい。
先日はテーブル作り直してそのままだったから実行権限みたいなのが付与されてたのかな。
知らないDBだと色んなトラブルが起きますね。
気を取り直してINSERTです。
おろ、途中でエラーが。
これは中身のcsvが悪さしてそうですね。
と、原因調査の前に一旦COMMITと切り捨ての文字が気になるのでINSERTの状態を確認します。
ドヤ顔
さてデータですが、6月のデータを入れてる最中でした。
94行目と記載ありますがそれ以前のデータは入っているのかどうか。
入ってますね。
しかも見た感じ全部入ってそうな勢い。
（SQL server君はSUBSTRじゃなくてご丁寧にSUBSTRINGなんだね！）
試しにカウント、むしろ最初からこれでいいんですけど若気の至りみたいなもんです。
183、ちなみにCSVの行数はヘッダー除いて184行です。
次に、CSVの94行目を見てみました。
なんとなく起きてることが想像できました。
画像だとすごくわかりづらいんですが、
商品名とかを取り扱う項目が異常な長さです。
これはテーブル設計誤りですね。
テーブルの中にもこのレコードが入っていないことは確認しました。
で、ちなみにこれが何バイトなんですか・・・？というと
いやー商品名長すぎでしょと思ったら441バイトですか。笑う。
ちなみにこの1文を発行するのに
・LENGTHじゃなくてLENなんだ！
・LENの補助入力は出たけどLENBでバイト数取れるのかな？？？
・DATALENGTHってなんだよ（1ググり）
・DUALテーブルが無いんですけど・・・（1ググり）
で、ドタバタでした。楽しいなSQL sever
このレベルの関数は必要に応じてググればいいかなで特に気にしないんですがDUALテーブルが無いのはびっくりですね。びっくりドンキーに行った時よりびっくりしました。
というかテーブル名無しでSQL実行できるのすごいな。いや、DUALもとりあえず名札つけてるみたいで意味が無いのは分かってるけど。DUALは客先だからってスーツ着せられてるSEみたいですけどDUALの無いSQL serverは『私らしく』自分らしくって感じでいいですね。（元ネタは機動戦艦ナデシコ25話です）
さて、では一旦6月のデータは削除して列拡張して入れ直しますか。
列サイズはVARCHAR2(10000)とかにしておきましょう。
個人利用だから好き放題じゃ。というより運用目線での問題を想像できない程度にはDBの知識がないので一旦広げまくっちゃいます。
こういう操作が発生すること考えたら自動コミットは絶対OFFにすべきですね。。。
列拡張してたら10000はだめだから8000までにしないと言われました。
後、今更ですがVARCHAR2は無いんですね。
それと気になる型、VARCHAR(MAX)なるものがおります。
2GBも入るみたいですね。これにしましょう。
その他の項目もデータ型に合わせて変更
Cal(ry はbitにしようとしたらエラーになりました。解せぬ。
その後は問題なく19/07～20/09までのデータを入れました。
Foo!
ふぅ・・・作業開始して1.5h...
日記書き始める前にやろうとしていた作業に1.5h・・・！！！
もう今日は終わるか悩んだんですが進捗0というものやぶさかですしタイトルにExcelってつけてるのに未だにExcel出してないしデータも用意したんだし読み込んでみます。
いくぜ・・・！繋げてみせる！ハッシュタグ #DBと繋がりたい
なんせ最近激アツコンテンツのD4DJのコンテンツテーマは『繋がる』とのことを聞いたような。
つまり私も繋げる必要があるんですよExcelとSQL serverを・・・
よし、分からん。
というのもあれなんでそれっぽいプロパティ値入れてみたらすんなりいけました。
テーブルにもアクセスできております。
これ、Contents所々モザイクかけてるのはえっちなものとかではないです。
建物名とか入ってたりで公に映すものではないものとかです。
全部モザイクでいいじゃんもそうなんですがそれはそれで家計簿アプリからDLしたcsvっぽくなくなっちゃうのでそれは違うと思うんですよね。うーんこじつけがましい。
そういうどうでもいい話は置いといて、
無事読み込めました。
DataSourceなんてお洒落なシート作ったのに無視しましたよこいつ。
上で出てるウィンドウは→のクエリと接続のCSV_DATAのプロパティです。
更新頻度とかをいじれるようです。
OfficeはGUIで感覚的に操作しちゃうので、想定してない挙動が起きた時にググって調べるようにしていこうと思います。Excelのこの辺の機能はググって解決するか少し不安ですがまぁそれはそれでその時考えていきます。
INSERTした時の反映はどうなることかと思いましたが
ファイルを開いたら更新してくれるなんて便利機能があるようなのでこれにチェック入れちゃいます。
さて、csvを入れたはいいんですが
このまま生データをそのままグラフに起こそうにもおそらく思い通りのグラフが作れないので1度Viewかなにかで用途別にデータをDB内で分けておこうと思います。
Excelでやるかは悩んだんですがネストしまくりワークシート関数を並べたりすることになりそうでちょっと抵抗がありますしDBあるならDBでやっちゃいたいですね。
csvインポート～Excel連携までができたということで区切りもよいので本日はここまで。
結局Excelで家計簿を作るってのはまだまだ先になりそうです。
adieu.
本日のBGM
Amazon musicでRoseliaのこのページに行くにはコツがあります。
関連リンクとかだと海外の別アーティストのRoseliaしか出てこないので
検索ページで明示的にRoseliaを検索する必要があります。
（コツか？たまに関連アーティストにもバンドリのRoseliaが表示されるんですが条件がわからない）
2020/07/15 に2ndアルバム「Wahl」がリリースされております。
TVアニメ第三期最終話挿入歌：Avant-garde HISTORY
第3回ガールズバンド総選挙1位記念楽曲：Break your desire
ノーブル・ローズ - 歌、至りて -楽曲：Song I am.
の3曲がアルバムで新規に収録されております。
Rも志崎さんボーカルのものが収録されていたりなど。
21/01発売のZEAL of proudが待ち遠しすぎる。。。。。。
Comments