How to insert data from excel sheet into database table?

Question

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

I have data in an excel sheet, for example,

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

and so on

and I need to save the button on the excel sheet and assign a macro to this button so that when the button is pressed, the rows in the excel sheet should be inserted into the database table.

The requirement is that I send an excel sheet to a person, he will fill in the sheet with data and click the button in excel, and the data must be inserted into the database table. I would rather do it with a macro.

Thanks to everyone.

Answer 1

See this link for using vb / vba code (for marco) to move data from excel to sql server.

Answer 2

I created a function that converts an Excel spreadsheet into multiple insert commands.

Copy this into the module and then set the values ​​of the cells to be inserted as the first parameter in the formula, the second range should be the column names (press F4 to set this as a constant), and the third (optional) Table Name. If no table name is specified, then the sheet name will be used by default.

In your case, the spreadsheet should look like this:

+---+------+------+------+------+-----------------------------------------+ | | 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 these two queries for you:

 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 well 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 enough data to insert, you may not need to use INSERT INTO in each command, then just use the Insert2DB function on the first line (and every 500th), and for the rest, just use 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 the 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 use MySQL, string escaping is different, so in that 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