Insert Into Redshift With Excel Vba

I have a requirement to load data from excel to Redshift. I am getting errors because there are numeric columns and the conversion of the values in each cell of a row into an arra

Solution 1:

Consider the industry best practice of SQL parameterization to avoid the messy and potentially dangerous need to concatenate and punctuate data values into SQL statements. And also avoid combining disparate data types with array methods.

Assuming your Excel database API is ADO, consider the Command object for parameterization. This requires you to extend .CreateParameter() calls for all 36 fields (A-AJ columns) and explicitly define data types. Empty cells may translate as NULL entities for insertion.

' PREPARED STATEMENT (NO DATA) WITH 36 PARAM PLACEDHOLERS
sql = "INSERT INTO dcgs.bcs_output (Col1, Col2, Col3, ..., Col37) " _
          & " VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?,                   " _ 
          & "        ?, ?, ?, ?, ?, ?, ?, ?, ?,                   " _
          & "        ?, ?, ?, ?, ?, ?, ?, ?, ?,                   " _
          & "        ?, ?, ?, ?, ?, ?, ?, ?, ?, CURRENT_DATE)     "For r = 2To BCS.ListObjects(1).DataBodyRange.Rows.Count
    ' CONFIGURE ADO COMMANDSet cmd = New ADODB.Command

    With cmd
        .ActiveConnection = conn
        .CommandText = sql
        .CommandType = adCmdText

        ' BIND ALL 36 PARAM VALUES
        .Parameters.Append .CreateParameter("param1", adInt, adParamInput, , BCS.Range("A" & r).Value)
        .Parameters.Append .CreateParameter("param2", adVarchar, adParamInput, , BCS.Range("B" & r).Value)
        ' ...
        .Parameters.Append .CreateParameter("param36", adXXXX, adParamInput, , BCS.Range("AJ" & r).Value)

        ' EXECUTE ACTION
        .Execute
    EndWithSet cmd = NothingNext r