Import CSV into SQL Server using VBA

You have heard the famous cliché, “there are many ways to skin a cat”.

That said, there are many ways to import a flat file into SQL Server. Some of them include the following:

• SSMS – Select Database -> Right Click Tasks -> Import Flat File
• SQL Server Integration Services (SSIS)
• Programmatically – Using your favourite language of choice (C# or VB.Net)
• PowerShell
• VBA

In this post I’m going to show the Power Users how you can leverage your VBA skills to write a simple Macro and then use it to import a flat file into SQL Server.

What You’ll Need

1.  SQL Server Management Studio (download here for free)
2.  Microsoft Excel (2010 or greater)

What are we working With

• CSV file
•  Database table

CSV File (this file contains about 15,000 records. For brevity, we are showing a dozen records).

Database Table Schema:

What Won’t Work

If you simply copy and paste records from the .csv file in SQL Management Studio and attempt to insert them, it would fail. That’s because, you need to stage the data by massaging it such that it is syntactically correct. This means wrapping single quotes i.e. (‘PROD_001 ‘) around every value in dim_1 column and separating each of the columns by comma (,). Now, with over 15000 records, this is next to impossible.

Use Macro in VBA

Open the .csv file in Excel like so.

Hit Alt-F11 to open VBA code surface. Here, simply write a Sub() method

Sub ImportData()
    On Error Resume Next
    
    Dim drillDownRequestId As Integer
    Dim dim1 As String
    Dim scenarioId As Integer
    Dim dim1Pnl As Double
    Dim sTemp As String
    Dim SQLScript As String
    Dim i As Integer
    
    For i = 2 To 15001
        drillDownRequestId = ThisWorkbook.Worksheets("drilldown_table").Range("A" & i).Value
        dim1 = ThisWorkbook.Worksheets("drilldown_table").Range("B" & i).Value
        scenarioId = ThisWorkbook.Worksheets("drilldown_table").Range("C" & i).Value
        dim1Pnl = ThisWorkbook.Worksheets("drilldown_table").Range("D" & i).Value
                
        SQLScript = "insert into drilldown values(" & drillDownRequestId & ",'" & dim1 & "'," & scenarioId & "," & dim1Pnl & ")"
        
        ThisWorkbook.Worksheets("drilldown_table").Range("F" & i).Value = SQLScript
    Next i
End Sub

Now, you can Hit F5 and have the program run all the way. It’ll probably take a second or so to process 15000 records.

Once the program has run successfully, examine the SQL Script in column F of your Excel spreadsheet. As a quick test, copy the script from a single cell and paste it into SSMS and run it. If the operation executes successfully, you know that the script is syntactically correct.

Alternatively, you can set a breakpoint in the For loop by pressing F9 and then hit F8 to step into the code.

Excel

SQL

Please remember there are different horses for different courses . Sometimes one situation doesn’t fit all. This blog post is intended for VBA power users/developers. Those of you that write code in other languages, but have not used VBA, this might be a good primer.

Hope you found this simple post useful 🙂

Obi