none
SQL Server 2005での項目数が不揃いなCSVファイルのインポートについて RRS feed

  • 質問

  • SQL Server 2005で下記の様なデータ構造をしたテキストファイルをインポートしたいと思っています。

    列0   列1  列2
    01 ,   01
    02,    02,  02
    (実際のデータにはスペースは含まれておりません)

    このようなファイルをインポート/エクスポート ウィザードを用いてインポートを行うと、テーブルに格納されたときに次のようになってしまいます。

    列0   列1
    01 ,   01
    02,    02,02

    これを
    列0   列1  列2
    01 ,   01,  NULL
    02,    02,  02

    のような形でインポートすることはできないでしょうか。

    SQL Server 2000では出来ていたので、何か設定があるのではないかと思いBooks Onlineやコミュニティの情報を検索してはみたのですがいまだ目的の情報を得られないでいます。

    よろしくお願いします。
     

    2006年8月25日 9:10

回答

  • 手動の場合はACCESS 2003 のインポートで 取り込んだ後、 ACCESS 2003 でクエリで出力もしくはSSIS やDTSウイザードで
    取り込みができます。

    このような手動でなく自動でスケジュールなどで定期的に行うのであれば SSISのパッケージのスクリプトコンポーネントで
    一旦、カンマを付与して列の個数を整えれば、後は通常通り、SSISのフラットファイルコンテナとOLE変換でDBに出力できます。

    なお、SSISのパッケージのスクリプトコンポーネントは VB.NET 2005 ですので テキストストリームの読み込み後、編集出力
    すればOKです。 参考書などに載っている通常の方法でOKです。

     SSIS: ⇒ SQL SERVER 2005 の付属のソフト
           SQL Server Business Intelligence Development Studio


    2006年8月25日 13:14
  • SSISでのパッケージをサンプル版として作成しましたのでお試しください。

    1.入力ファイルのデータ  (パス : C:\xxxx\test.csv) 

    a01,d02,e03,f12,r04,d07

    a01,d02,e03,f04,a01

    a01,d02,e03,f04,d07,h01

    a01,d02,e03,f49,d88

     2.これを 接続マネージャにて以下のようにします。


    接続マネージャ名 : 入力ファイルの接続

     

        ロケール  :日本語

       コードページ :932

     

       形式:幅あわせしない

     

    3.SSISのパッケージの変数を定義します。

    SSISの変数の定義

    変数名: 入力F

    スコープ : 制御フロー全体で使用可能
    型 : String
    値: 接続マネージャ名の名前→「入力CSVファイルの接続」



    4.制御フロー にスクリプトタスクを追加します。右クリック・編集で
        変数を使用するために 以下の項目に値を入力してください。
                  →  ReadOnlyValiableds : PackageName,入力F

      スクリプトを開き、以下の通り入力します。

    ' Microsoft SQL Server Integration Services Script Task

    ' Write scripts using Microsoft Visual Basic

    ' The ScriptMain class is the entry point of the Script Task.

     

    Imports System

    Imports System.io

    Imports System.Data

    Imports System.Math

    Imports Microsoft.SqlServer.Dts.Runtime

     

    Public Class ScriptMain

        Public EV As IDTSInfoEvents

     

     

     

        Dim Myfileconnection As String = ""

        Dim Outfile_CN As String = "C:\xxxx\加工済F.csv"

        Dim OTF As New StreamWriter(Outfile_CN, False, System.Text.Encoding.GetEncoding(932))

     

        Dim SRC As System.IO.StreamReader

     

     

        Dim Rd As String = ""

        Dim Cnt As Integer = 0

     

        Dim PKname As String = Dts.Variables.Item("PackageName").Value.ToString

        Dim フラットファイル接続先名As String = Dts.Variables.Item("入力F").Value.ToString

     

        Public Const カンマの個数As Integer = 5

     

        Public Sub Main()

     

     

            '

            ' Add your code here

            '

            Dim I As Integer = 0

            Dim WkMsg As String = ""

            Myfileconnection = DirectCast(Dts.Connections(フラットファイル接続先名).AcquireConnection(Dts.Transaction), String)

     

     

            Dim infile As String = Myfileconnection.ToString

     

            If File.Exists(infile) = False Then  'ファイルの存在有無のチェック

                Exit Sub

     

            End If

     

            SRC = New System.IO.StreamReader(Myfileconnection, System.Text.Encoding.GetEncoding("Shift-JIS"))

     

            Rd = SRC.ReadLine

            Cnt = 0

            Do Until (Rd Is Nothing)

     

                Call S_カンマ付与(Rd)

                Cnt += 1

     

                Rd = SRC.ReadLine

     

     

            Loop

     

            SRC.Close()

            OTF.Close()

     

     

            If Cnt <> 0 Then

     

                Dts.Events.FireInformation(0, PKname, "処理は正常終了しました。" & vbCrLf & _

                                          "入力ファイルのレコード件数は(" & Cnt & "件)です。", "", 0, False)

            End If

            Dts.TaskResult = Dts.Results.Success

     

            Exit Sub

     

     

     

        End Sub

        Private Sub S_カンマ付与(ByVal IN_REC As String)

            Dim Wr As String = ""

            Dim Cnm As Integer = 0

            Dim F As Integer = 0

            Dim S As Integer = 0

            Dim I As Integer

            S = 1

            Cnm = 0

            Do

                F = InStr(S, IN_REC, ",")

                If F <> 0 Then

                    Cnm += 1

                Else

                    Exit Do

                End If

                If F < Len(IN_REC) Then

                    S = F + 1

                End If

            Loop Until F = 0

     

            If カンマの個数- Cnm >= 1 Then

                Wr = StrDup(カンマの個数- Cnm, ",")

            End If

     

            OTF.WriteLine(IN_REC & Wr)

     

        End Sub

     

    End Class

     

       これでスクリプトタスクは完成です。

    5.スクリプトタスクで作成したファイルをDBに書き込むため、データフロータスクを追加します。
      
      5-1 データフロータスクにフラットファイルコンテナを追加します。
          接続先は新たに接続先マネージャを作成します。
      

    接続マネージャ名 : 加工済CSVファイル

     

        ロケール  :日本語

       コードページ :932

     

       形式:区切り記号

     各列の定義は以下の通り
       列1・・・ 文字列 [DT_STR]  50バイト

       列2・・・ 文字列 [DT_STR]  50バイト 
      列3・・・ 文字列 [DT_STR]  50バイト
       列4・・・ 文字列 [DT_STR]  50バイト
       列5・・・ 文字列 [DT_STR]  50バイト

          列6・・・ 文字列 [DT_STR]  50バイト


        5-2  次にDBに書き込むために OLE DB 変換先 コンテナ をデータフロータスクに追加します。
                   接続先は新たに接続先マネージャを作成します。
                 

    接続マネージャ名 : 出力先DBへの接続

     

        サーバー名    :開発先サーバー(xxxxSV)

       データベース名 :SSIS_TEST

     



                テーブルの定義は以下の通りです。

    USE [SSIS_TEST]

    GO

    /****** オブジェクト:  Table [dbo].[TEST_TBL]    スクリプト日付: 08/27/2006 02:05:12 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_PADDING ON

    GO

    CREATE TABLE [dbo].[TEST_TBL](

                  [1列目] [varchar](50) COLLATE Japanese_CI_AS NULL,

                  [2列目] [varchar](50) COLLATE Japanese_CI_AS NULL,

                  [3列目] [varchar](50) COLLATE Japanese_CI_AS NULL,

                  [4列目] [varchar](50) COLLATE Japanese_CI_AS NULL,

                  [5列目] [varchar](50) COLLATE Japanese_CI_AS NULL,

                  [6列目] [varchar](50) COLLATE Japanese_CI_AS NULL

    ) ON [PRIMARY]

     

    GO

    SET ANSI_PADDING OFF

     


                       後はこのテーブルと 接続マネージャ名 : 加工済CSVファイル の各列をマップして完成です。


     

    2006年8月26日 17:12
  • お疲れ様です。TimberLandChapel です。

    亀レスですが,

    特に

    ・カンマの数を調整した中間ファイルを保存する必要がない

    ・欠落した列は Empty ではなく, Null として処理したい

    というような要件の場合は,

    [スクリプト コンポーネント] を [変換元] として利用するのがよいと思います。

     

    逆に,カンマを補正した中間ファイルを保存したい場合は,

     [データフロータスク] の実行前に [スクリプト実行タスク] をかませて補正したほうが,ETL としてすっきりすると思います。

    というわけで,[スクリプトコンポーネント] を変換元として利用するサンプルを作成しました。

    http://blogs.timberlandchapel.com/blogs/timberlandchapel/articles/1068.aspx

    ----------

    TimberLandChapel

    Micorosoft MVP for Windows Server System - SQL Server(Jul 2005 - Jun 2007)

    http://blogs.timberlandchapel.com/blogs/timberlandchapel/

    2006年9月7日 14:23

すべての返信

  • 手動の場合はACCESS 2003 のインポートで 取り込んだ後、 ACCESS 2003 でクエリで出力もしくはSSIS やDTSウイザードで
    取り込みができます。

    このような手動でなく自動でスケジュールなどで定期的に行うのであれば SSISのパッケージのスクリプトコンポーネントで
    一旦、カンマを付与して列の個数を整えれば、後は通常通り、SSISのフラットファイルコンテナとOLE変換でDBに出力できます。

    なお、SSISのパッケージのスクリプトコンポーネントは VB.NET 2005 ですので テキストストリームの読み込み後、編集出力
    すればOKです。 参考書などに載っている通常の方法でOKです。

     SSIS: ⇒ SQL SERVER 2005 の付属のソフト
           SQL Server Business Intelligence Development Studio


    2006年8月25日 13:14
  • Nobnnn 様、御回答頂きありがとうございます。

    実は、Nobnnn 様が述べられた様に最初はSSISでデータインポート用のパッケージを作成しようとしていたのですが、どこをどう設定しても思ったとおりに動かなかった為、そもそも希望している形でデータを取り込めるかどうかを確認致したく質問させていただきました。

    御回答頂いた内容によるとスクリプトコンポーネントというもので実現できるという事なのでそちらの方を調査してみたいと思います。
    また、2000のDTSに比べ、スクリプトによる編集という処理が増えてしまう為、処理時間の検証なども行ってみたいと思います。

    しかし、どうしてインポート時のデータの扱いがSQL Server2000と2005で変わってしまったのでしょうか。
    2000で当たり前のように出来た事が2005では一工夫しないとできないと言ったことも色々あるのかもしれませんね。

    他にもこんな方法で実現できるという情報をお持ちの方がいましたら御教授頂ければ幸いです。
    よろしくお願い致します。

    2006年8月25日 15:48
  • SSISでのパッケージをサンプル版として作成しましたのでお試しください。

    1.入力ファイルのデータ  (パス : C:\xxxx\test.csv) 

    a01,d02,e03,f12,r04,d07

    a01,d02,e03,f04,a01

    a01,d02,e03,f04,d07,h01

    a01,d02,e03,f49,d88

     2.これを 接続マネージャにて以下のようにします。


    接続マネージャ名 : 入力ファイルの接続

     

        ロケール  :日本語

       コードページ :932

     

       形式:幅あわせしない

     

    3.SSISのパッケージの変数を定義します。

    SSISの変数の定義

    変数名: 入力F

    スコープ : 制御フロー全体で使用可能
    型 : String
    値: 接続マネージャ名の名前→「入力CSVファイルの接続」



    4.制御フロー にスクリプトタスクを追加します。右クリック・編集で
        変数を使用するために 以下の項目に値を入力してください。
                  →  ReadOnlyValiableds : PackageName,入力F

      スクリプトを開き、以下の通り入力します。

    ' Microsoft SQL Server Integration Services Script Task

    ' Write scripts using Microsoft Visual Basic

    ' The ScriptMain class is the entry point of the Script Task.

     

    Imports System

    Imports System.io

    Imports System.Data

    Imports System.Math

    Imports Microsoft.SqlServer.Dts.Runtime

     

    Public Class ScriptMain

        Public EV As IDTSInfoEvents

     

     

     

        Dim Myfileconnection As String = ""

        Dim Outfile_CN As String = "C:\xxxx\加工済F.csv"

        Dim OTF As New StreamWriter(Outfile_CN, False, System.Text.Encoding.GetEncoding(932))

     

        Dim SRC As System.IO.StreamReader

     

     

        Dim Rd As String = ""

        Dim Cnt As Integer = 0

     

        Dim PKname As String = Dts.Variables.Item("PackageName").Value.ToString

        Dim フラットファイル接続先名As String = Dts.Variables.Item("入力F").Value.ToString

     

        Public Const カンマの個数As Integer = 5

     

        Public Sub Main()

     

     

            '

            ' Add your code here

            '

            Dim I As Integer = 0

            Dim WkMsg As String = ""

            Myfileconnection = DirectCast(Dts.Connections(フラットファイル接続先名).AcquireConnection(Dts.Transaction), String)

     

     

            Dim infile As String = Myfileconnection.ToString

     

            If File.Exists(infile) = False Then  'ファイルの存在有無のチェック

                Exit Sub

     

            End If

     

            SRC = New System.IO.StreamReader(Myfileconnection, System.Text.Encoding.GetEncoding("Shift-JIS"))

     

            Rd = SRC.ReadLine

            Cnt = 0

            Do Until (Rd Is Nothing)

     

                Call S_カンマ付与(Rd)

                Cnt += 1

     

                Rd = SRC.ReadLine

     

     

            Loop

     

            SRC.Close()

            OTF.Close()

     

     

            If Cnt <> 0 Then

     

                Dts.Events.FireInformation(0, PKname, "処理は正常終了しました。" & vbCrLf & _

                                          "入力ファイルのレコード件数は(" & Cnt & "件)です。", "", 0, False)

            End If

            Dts.TaskResult = Dts.Results.Success

     

            Exit Sub

     

     

     

        End Sub

        Private Sub S_カンマ付与(ByVal IN_REC As String)

            Dim Wr As String = ""

            Dim Cnm As Integer = 0

            Dim F As Integer = 0

            Dim S As Integer = 0

            Dim I As Integer

            S = 1

            Cnm = 0

            Do

                F = InStr(S, IN_REC, ",")

                If F <> 0 Then

                    Cnm += 1

                Else

                    Exit Do

                End If

                If F < Len(IN_REC) Then

                    S = F + 1

                End If

            Loop Until F = 0

     

            If カンマの個数- Cnm >= 1 Then

                Wr = StrDup(カンマの個数- Cnm, ",")

            End If

     

            OTF.WriteLine(IN_REC & Wr)

     

        End Sub

     

    End Class

     

       これでスクリプトタスクは完成です。

    5.スクリプトタスクで作成したファイルをDBに書き込むため、データフロータスクを追加します。
      
      5-1 データフロータスクにフラットファイルコンテナを追加します。
          接続先は新たに接続先マネージャを作成します。
      

    接続マネージャ名 : 加工済CSVファイル

     

        ロケール  :日本語

       コードページ :932

     

       形式:区切り記号

     各列の定義は以下の通り
       列1・・・ 文字列 [DT_STR]  50バイト

       列2・・・ 文字列 [DT_STR]  50バイト 
      列3・・・ 文字列 [DT_STR]  50バイト
       列4・・・ 文字列 [DT_STR]  50バイト
       列5・・・ 文字列 [DT_STR]  50バイト

          列6・・・ 文字列 [DT_STR]  50バイト


        5-2  次にDBに書き込むために OLE DB 変換先 コンテナ をデータフロータスクに追加します。
                   接続先は新たに接続先マネージャを作成します。
                 

    接続マネージャ名 : 出力先DBへの接続

     

        サーバー名    :開発先サーバー(xxxxSV)

       データベース名 :SSIS_TEST

     



                テーブルの定義は以下の通りです。

    USE [SSIS_TEST]

    GO

    /****** オブジェクト:  Table [dbo].[TEST_TBL]    スクリプト日付: 08/27/2006 02:05:12 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_PADDING ON

    GO

    CREATE TABLE [dbo].[TEST_TBL](

                  [1列目] [varchar](50) COLLATE Japanese_CI_AS NULL,

                  [2列目] [varchar](50) COLLATE Japanese_CI_AS NULL,

                  [3列目] [varchar](50) COLLATE Japanese_CI_AS NULL,

                  [4列目] [varchar](50) COLLATE Japanese_CI_AS NULL,

                  [5列目] [varchar](50) COLLATE Japanese_CI_AS NULL,

                  [6列目] [varchar](50) COLLATE Japanese_CI_AS NULL

    ) ON [PRIMARY]

     

    GO

    SET ANSI_PADDING OFF

     


                       後はこのテーブルと 接続マネージャ名 : 加工済CSVファイル の各列をマップして完成です。


     

    2006年8月26日 17:12
  • ここ数日間不在にしていた為、返事が遅れてしまい申し訳ございません。

    Nobnnn 様 サンプルまでお作り頂き大変恐縮です。
    御提示頂いたサンプルを元にSSISパッケージを作成して検証してみたいと思います。

    ただ、実際のデータ量は百数十万件くらいなので、速度面が気になるところではあるのですが、こちらの方も検証を行い、結果がまとまればこちらの方で報告できればと考えております。

    よろしくお願いします。

    ありがとうございました。

    2006年8月30日 1:44
  • お疲れ様です。TimberLandChapel です。

    亀レスですが,

    特に

    ・カンマの数を調整した中間ファイルを保存する必要がない

    ・欠落した列は Empty ではなく, Null として処理したい

    というような要件の場合は,

    [スクリプト コンポーネント] を [変換元] として利用するのがよいと思います。

     

    逆に,カンマを補正した中間ファイルを保存したい場合は,

     [データフロータスク] の実行前に [スクリプト実行タスク] をかませて補正したほうが,ETL としてすっきりすると思います。

    というわけで,[スクリプトコンポーネント] を変換元として利用するサンプルを作成しました。

    http://blogs.timberlandchapel.com/blogs/timberlandchapel/articles/1068.aspx

    ----------

    TimberLandChapel

    Micorosoft MVP for Windows Server System - SQL Server(Jul 2005 - Jun 2007)

    http://blogs.timberlandchapel.com/blogs/timberlandchapel/

    2006年9月7日 14:23
  • こんにちは!

    フォーラム オペレーターの服部 清次です。

     

    Nobnnn さん、TimberLandChapel さん、

    大変丁寧でわかりやすい回答をありがとうございました!!

     

    swind さん、

    swind さんが質問を投稿されてからかなり時間が経ちましたが、検証の結果はいかがでしたでしょうか?

    swind さんのその後が気になるところなのですが、今回、Nobnnn さんと TimberLandChapel さんの回答が参考になったのではないかと思いましたので、ひとまず、私の方で回答チェックを付けさせていただきました。

    もし swind さんの問題が解決していない場合は、回答チェックを外して質問を継続していただくことも可能です。

    その際には、検証結果や現在の状況も教えていただければ嬉しいです。

     

    ぜひまた TechNet Forum にご参加ください。

    よろしくお願いします。

    それでは、また!

     

    _____________________________________________

    マイクロソフト株式会社 フォーラム オペレータ 服部 清次

    2008年10月6日 6:19