How to insert data from an excel sheet into a database table?

0
votes

I need to insert the data from an excel sheet into a teradata table. and I need this to be done using a MACRO.

I have data in an excel sheet like

COL1  COL2 COL3 COL4
1      2    3     4
2      5    8     10
.
.
so on

and i need to keep a button in the excel sheet and assign a macro to that button so that when i click the button the rows in the excel sheet should be inserted into a database table.

The requirement is that I will send the empty excel sheet to the person, he will fill in the sheet with the data and he clicks the button in the excel and the data has to be inserted into the database table. I would prefer doing this using a macro..

Thanks all.

2
excelexcel-2003teradatavba

2 Answers

5
votes

I've created function that will transform the Excel table into multiple Insert Commands.

Copy this into the module and then in formula, set up as first parameter the values of cells that needs to be inserted, the second range should be the names of columns (press F4 to set up this as constant), and third (optional) the name of the table. If table name is not specified, then the name of sheet will be used as default.

In your case this is how the spreadsheet should look like: 

+---+------+------+------+------+-----------------------------------------+
|   | A    | B    | C    | D    | E                                       |
+---+------+------+------+------+-----------------------------------------+
| 1 | COL1 | COL2 | COL3 | COL4 |                                         |
+---+------+------+------+------+-----------------------------------------+
| 2 | 1    | 2    | 3    | 4    | =Insert2DB(A2:D2,$A$1:$D$1,"TableName") |
+---+------+------+------+------+-----------------------------------------+
| 3 | 2    | 5    | 8    | 10   | =Insert2DB(A3:D3,$A$1:$D$1,"TableName") |
+---+------+------+------+------+-----------------------------------------+

This will generate for you those two queries:

INSERT INTO TableName ([COL1],[COL2],[COL3],[COL4]) VALUES (1,2,3,4)
INSERT INTO TableName ([COL1],[COL2],[COL3],[COL4]) VALUES (2,5,8,10)

Here is the function (works good with Microsoft SQL (TSQL):

    Function Insert2DB(InputRange As Range, Optional ColumnsNames As Variant, Optional TableName As Variant)

      Dim rangeCell As Range
      Dim InsertValues As String
      Dim CellValue As String
      Dim C As Range

        Dim AllColls As String
        Dim SingleCell As Range
        Dim TableColls As String

    InsertValues = ""

    'Start Loop
    For Each rangeCell In InputRange.Cells

    'Recognize data type
    Set C = rangeCell
        If IsEmpty(C) Then
                'DataType is NULL then NULL
                CellValue = "NULL"
            ElseIf Application.IsText(C) Then
                'DataType is VARCHAR or CHAR
                CellValue = "'" & Trim(rangeCell.Value) & "'"
            ElseIf Application.IsLogical(C) Then
                'DataType is bit eg. TRUE / FALSE
                    If rangeCell.Value = True Then
                        CellValue = "1"
                    ElseIf rangeCell.Value = False Then
                        CellValue = "0"
                    End If
            ElseIf Application.IsErr(C) Then
                'If there is an ERROR in cell, the statment will return 0
                CellValue = "NULL"
            ElseIf IsDate(C) Then
                'DataType is DATE or DATETIME, in case it is DATE specifying HH:mm:ss would do no harm
                CellValue = "'" & VBA.Format(rangeCell.Value, "yyyymmdd hh:mm:ss") & "'"
            ElseIf InStr(1, C.Text, ":") <> 0 Then
                'DataType is TIME
                CellValue = "'" & VBA.Format(rangeCell.Value, "hh:mm:ss") & "'"
            ElseIf IsNumeric(C) Then
                'DataType is number
                CellValue = rangeCell.Value
        End If

    If (Len(InsertValues) > 0) Then
        InsertValues = InsertValues + "," + CellValue
    Else
        InsertValues = CellValue
    End If

    Next rangeCell
    'END Loop

    If IsMissing(ColumnsNames) Then
        TableColls = ""
        Else

        For Each SingleCell In ColumnsNames.Cells
            If Len(AllColls) > 0 Then
                     AllColls = AllColls + "," + "[" + Trim(Replace(SingleCell.Value, Chr(160), "")) + "]"
            Else
                    AllColls = "[" + Trim(Replace(SingleCell.Value, Chr(160), "")) + "]"
            End If
        Next SingleCell
        TableColls = " (" & AllColls & ")"
    End If


    'If TableName is not set, then take the name of a sheet
    If IsMissing(TableName) = True Then
        TableName = ActiveSheet.Name
    Else
    TableName = TableName
    End If

    'Set the return value
        Insert2DB = "INSERT INTO " & TableName & TableColls & " VALUES (" & InsertValues & ")"

    End Function

If you have quite a lot data to insert, you might not need to use INSERT INTO in each command, then just use the Insert2DB function in first row (and every 500th) and for the rest use just Insert2DBValues:

+---+------+------+------+------+-----------------------------------------------+
|   | A    | B    | C    | D    | E                                             |
+---+------+------+------+------+-----------------------------------------------+
| 1 | COL1 | COL2 | COL3 | COL4 |                                               |
+---+------+------+------+------+-----------------------------------------------+
| 2 | 1    | 2    | 3    | 4    | =Insert2DB(B3:E3,$B$2:$E$2,"TableName")       |
+---+------+------+------+------+-----------------------------------------------+
| 3 | 2    | 5    | 8    | 10   | =Insert2DBValues(A3:D3,$A$1:$D$1,"TableName") |
+---+------+------+------+------+-----------------------------------------------+

This will give you following commands: 

INSERT INTO TableName ([COL1],[COL2],[COL3],[COL4]) VALUES (1,2,3,4)
,(2,5,8,10)


Function Insert2DBValues(InputRange As Range, Optional ColumnsNames As Variant, Optional TableName As Variant)

  Dim rangeCell As Range
  Dim InsertValues As String
  Dim CellValue As String
  Dim C As Range

    Dim AllColls As String
    Dim SingleCell As Range
    Dim TableColls As String

InsertValues = ""

'Start Loop
For Each rangeCell In InputRange.Cells

'Recognize data type
Set C = rangeCell
    If IsEmpty(C) Then
            'DataType is NULL then NULL
            CellValue = "NULL"
        ElseIf Application.IsText(C) Then
            'DataType is VARCHAR or CHAR
            CellValue = "'" & Trim(rangeCell.Value) & "'"
        ElseIf Application.IsLogical(C) Then
            'DataType is bit eg. TRUE / FALSE
                If rangeCell.Value = True Then
                    CellValue = "1"
                ElseIf rangeCell.Value = False Then
                    CellValue = "0"
                End If
        ElseIf Application.IsErr(C) Then
            'If there is an ERROR in cell, the statment will return 0
            CellValue = "NULL"
        ElseIf IsDate(C) Then
            'DataType is DATE or DATETIME, in case it is DATE specifying HH:mm:ss would do no harm
            CellValue = "'" & VBA.Format(rangeCell.Value, "yyyy-mm-dd hh:mm:ss") & "'"
        ElseIf InStr(1, C.Text, ":") <> 0 Then
            'DataType is TIME
            CellValue = "'" & VBA.Format(rangeCell.Value, "hh:mm:ss") & "'"
        ElseIf IsNumeric(C) Then
            'DataType is number
            CellValue = rangeCell.Value
    End If

If (Len(InsertValues) > 0) Then
    InsertValues = InsertValues + "," + CellValue
Else
    InsertValues = CellValue
End If

Next rangeCell
'END Loop

If IsMissing(ColumnsNames) Then
    TableColls = ""
    Else

    For Each SingleCell In ColumnsNames.Cells
        If Len(AllColls) > 0 Then
                 AllColls = AllColls + "," + "[" + Trim(Replace(SingleCell.Value, Chr(160), "")) + "]"
        Else
                AllColls = "[" + Trim(Replace(SingleCell.Value, Chr(160), "")) + "]"
        End If
    Next SingleCell
    TableColls = " (" & AllColls & ")"
End If


'If TableName is not set, then take the name of a sheet
If IsMissing(TableName) = True Then
    TableName = ActiveSheet.Name
Else
TableName = TableName
End If

'Set the return value
    Insert2DBValues = ",(" & InsertValues & ")"

End Function

And finally, if you are using MySQL, there is different escaping of strings, so in such a case use Insert2DBMySQL:

    Function Insert2DBMySQL(InputRange As Range, Optional ColumnsNames As Variant, Optional TableName As Variant)

      Dim rangeCell As Range
      Dim InsertValues As String
      Dim CellValue As String
      Dim C As Range

        Dim AllColls As String
        Dim SingleCell As Range
        Dim TableColls As String

    InsertValues = ""

    'Start Loop
    For Each rangeCell In InputRange.Cells

    'Recognize data type
    Set C = rangeCell
        If IsEmpty(C) Then
                'DataType is NULL then NULL
                CellValue = "NULL"
            ElseIf Application.IsText(C) Then
                'DataType is VARCHAR or CHAR
                CellValue = "'" & Trim(rangeCell.Value) & "'"
            ElseIf Application.IsLogical(C) Then
                'DataType is bit eg. TRUE / FALSE
                    If rangeCell.Value = True Then
                        CellValue = "1"
                    ElseIf rangeCell.Value = False Then
                        CellValue = "0"
                    End If
            ElseIf Application.IsErr(C) Then
                'If there is an ERROR in cell, the statment will return 0
                CellValue = "NULL"
            ElseIf IsDate(C) Then
                'DataType is DATE or DATETIME, in case it is DATE specifying HH:mm:ss would do no harm
                CellValue = "'" & VBA.Format(rangeCell.Value, "yyyy-mm-dd hh:mm:ss") & "'"
            ElseIf InStr(1, C.Text, ":") <> 0 Then
                'DataType is TIME
                CellValue = "'" & VBA.Format(rangeCell.Value, "hh:mm:ss") & "'"
            ElseIf IsNumeric(C) Then
                'DataType is number
                CellValue = rangeCell.Value
        End If

    If (Len(InsertValues) > 0) Then
        InsertValues = InsertValues + "," + CellValue
    Else
        InsertValues = CellValue
    End If

    Next rangeCell
    'END Loop

    If IsMissing(ColumnsNames) Then
        TableColls = ""
        Else

        For Each SingleCell In ColumnsNames.Cells
            If Len(AllColls) > 0 Then
                     AllColls = AllColls + "," + "" + Trim(Replace(SingleCell.Value, Chr(160), "")) + ""
            Else
                    AllColls = "" + Trim(Replace(SingleCell.Value, Chr(160), "")) + ""
            End If
        Next SingleCell
        TableColls = " (" & AllColls & ")"
    End If


    'If TableName is not set, then take the name of a sheet
    If IsMissing(TableName) = True Then
        TableName = ActiveSheet.Name
    Else
    TableName = TableName
    End If

    'Set the return value
        Insert2DBMySQL = "INSERT INTO " & TableName & TableColls & " VALUES (" & InsertValues & ");"

    End Function
2
votes

Have a look at this link for using vb/vba code (for the marco) to move data from excel to sql server.