MEMO-產生Excel資料INSERT SQL指令檔

2009-09-26 12:44 PM

[註: MEMO是備忘系列文件]

在我的經驗裡，SQL Server Import Data Wizard在匯入Excel資料時，常有些資料型別、語系轉換的瑣雜問題，搞下來，與其花時間排除這些茶包、茶葉、茶梗，還不如自己寫Code比較快。

以下是我今天寫的一小段VBA Code，可以由第一欄取出欄位名稱，然後找出各欄最大長度作為NVARCHAR(n)的依據，然後再產生INSRT INTO的Script。

貼文一篇以為備忘。

Sub GenInsertScript()

    Dim iColCount As Integer, iRowCount As Integer

    Dim I As Integer, J As Integer, K As Integer

    Dim sColNames(1024) As String, iColMaxLen(1024) As Integer

    With ActiveSheet

        I = 1

        While .Cells(1, I) <> ""

            sColNames(I) = .Cells(1, I)

            iColCount = I

            I = I + 1

        Wend

        I = 2

        'Find max length for every column

        While .Cells(I, 1) <> ""

            For J = 1 To iColCount

                K = Len(.Cells(I, J))

                If (K > iColMaxLen(J)) Then iColMaxLen(J) = K

            Next J

            iRowCount = I

            I = I + 1

        Wend

        Dim sTableName As String

        sTableName = "RawData"

        'Generate script

        Open "C:\Temp\Import.sql" For Output As #1

        Print #1, "CREATE TABLE " & sTableName & " ("

        For I = 1 To iColCount

            If (I > 1) Then Print #1, ",";

            Print #1, sColNames(I) & " NVARCHAR(" & iColMaxLen(I) & ")"

        Next I

        Print #1, ")"

        For I = 2 To iRowCount

            Print #1, "INSERT INTO " & sTableName & " VALUES (";

            For J = 1 To iColCount

                If (J > 1) Then Print #1, ",";

                Print #1, "'" & Replace(.Cells(I, J), "'", "''") & "'";

            Next J

            Print #1, ")"

        Next I

        Close #1

    End With

End Sub

Comments