ストアドだけでマトリックスのExcelデータを読み込む
横に伸びる、いわゆるマトリックスタイプのExcelデータをストアドだけで取り込む場合の工夫。ストアドを実行するだけでExcelがテーブルに入るので、スケジューラと連携させれば完全自動化も可能だが、多少工夫が必要。○OPENROWSETのIMEX=1は万能ではない Excelとの連携にはJet(Micorosoft.Jet.OLEDB.4.0)を使って、OPENROWSETで取り込むのが普通である。 SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\マトリックス.xls;HDR=NO','SELECT * FROM [Sheet1$]') しかし、JetをExcelで使う場合、文字列・数値混在の列の取り込みが問題になる。 Jetは初期設定で先頭8行をチェックしてデータ型を判断するため、先頭8行に数値・文字が混在し、数値列の方が多いとその列は数値列としてfloatで読み込みを行おうとする。この結果文字のセルの内容が読み込まれないということが起こる。 上記はIMEX=1を指定してインポートモードに固定しても、先頭8行に数値しかないと回避できない。 これを回避するために、レジストリのHKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\ExcelにあるTypeGuessRowsの数値を変更する方法もあるが、この値を0にすると形式判断のために全行スキャンが行われるので、負荷が重くなる。そもそも1担当者が「サーバのレジストリを変更してくれ」なんて頼むと普通は怒られるはず。 8行目までに見出し列などを設けて文字列と判断させるのが通常の回避策だろう。 お勧めはしないが、先頭8行が取り込み不要ならば、以下のように文字で認識したい列を無理やりクリアしてしまう手もある。そうするとIMEX=1が利く。 UPDATE OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\マトリックス.xls;HDR=NO;', 'SELECT TOP 8 F3,F4,F5 FROM [Sheet1$]') SET F3=NULL,F4=NULL,F5=NULL 先頭8行が無理なら、1行だけクリアしてから文字列にするのでもよい UPDATE OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\マトリックス.xls;HDR=NO;', 'SELECT F3,F4,F5 FROM [Sheet1$A2:E2]') SET F3=NULL,F4=NULL,F5=NULL UPDATE OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\マトリックス.xls;HDR=NO;', 'SELECT F3,F4,F5 FROM [Sheet1$A2:E2]') SET F3='A',F4='A',F5='A'○カラム数を数える カラム数を数えるためには、一旦テーブルに入れる必要がある。もちろん、一時テーブルでも構わない(実テーブルを使うとしてもtempdbにつくるのがいいだろう) また、テーブルに入れる場合は、その後のことを考えて、データに連番を付与しておくべきだ。 SELECT IDENTITY(int,1,1) ID, * INTO #wkData FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\マトリックス.xls;HDR=NO;IMEX=1', 'SELECT * FROM [Sheet1$]') ローカルの一時テーブルはtempdbにできるので、tempdbのシステムビューでカラム数をカウントする SELECT @CNT=COUNT(*) FROM tempdb.sys.columns WHERE object_id=object_id('tempdb..#wkData','U') ただ、一般的にはExcelファイルの名前は毎回違い、取り込みも動的クエリで行うことが多い。その場合はsp_executesqlを使って件数を取り出すことになる。 DECLARE @nsql nvarchar(max) DECLARE @CNT int SET @nsql=N'SELECT @CNT=COUNT(*) FROM tempdb.sys.columns' N'WHERE object_id=object_id('''+@TABLENAME+''',''U'')' EXEC sp_executesql @nsql,N'@CNT int OUT',@CNT OUTPUT○必要な列だけ取り込む マトリックス型のデータの場合、横の列は「年月」であって、当月分だけ取り込みたいということもままある。 こんなケースだ。 取引高推移 2007/01 2007/2 2007/3 ....... 2008/9 2008/10 会社A 会社B 会社C そうすると、F1,F2,....という名前で取り込まれたデータのうち、どの列が当月分なのか調べる必要がある。 上記の例では2行目に年月情報が取り込まれているから、普通にやると列数分だけループでクエリを実行することになる。 ここはUNIONで1クエリに組み上げて一発で列名を取るのがいいだろう。 DECLARE @nsql nvarchar(max) DECLARE @FLD varchar(10) SELECT @nsql=ISNULL(@nsql+' UNION ALL','SELECT @FLD=FLD FROM (') +' SELECT '''+name+''' FLD FROM #wkData WHERE ID=2' +' AND CONVERT(varchar,'+name+')='''+@YYYYMM+'''' FROM tempdb.sys.columns WHERE object_id=object_id('tempdb..#wkData','U') SET @nsql=@nsql+') tmp' EXEC sp_executesql @nsql,N'@FLD varchar(10) OUT',@FLD OUTPUT○Excelのシート名を取得する アドホッククエリの場合、ADOXやADO.RecordsetのOpenSchemaのようなアクセス方法がないので、Excelのシート名がわからない場合はお手上げになってしまうが、最後の手段として一旦リンクサーバに登録してしまうという方法がある。 --一旦リンクサーバに登録する EXEC sp_addlinkedserver @server=N'wkExcel',@srvproduct='Excel2003', @provider='Microsoft.Jet.OLEDB.4.0', @datasrc=N'C:\マトリックス.xls', @provstr='Excel 8.0' EXEC sp_addlinkedsrvlogin 'wkExcel','FALSE' --ワークテーブルに入れればシート名が得られる EXEC sp_tables_ex @table_server=N'wkExcel' (このまま取り込みにつかっても可。SELECT * FROM [wkExcel]...[Sheet1$]) --リンクサーバ削除 EXEC sp_dropserver @server=N'wkExcel', @droplogins='droplogins'○できないこと この方法でできないのは、「パスワードのかかったシートを読み込むこと」だ。 Accessの場合はパスワードを指定するが、Excelの場合は指定してもJet経由では解除できない。