[註: 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

Be the first to post a comment

Post a comment