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).

drilldown_request_id dim_1 scenario_id dim_1_pnl
102 PROD_013 96 39.43999672
102 PROD_001 23 -63.47999954
102 PROD_011 4 -5.650003552
102 PROD_015 73 -3.210000992
102 PROD_014 21 19.40999985
102 PROD_014 71 -2.779999018
102 PROD_005 27 -123.8400006
102 PROD_010 2 -56.66000366
102 PROD_012 16 -37.25999832
102 PROD_008 58 -112.4700006
102 PROD_007 54 76.349998
102 PROD_007 44 88.24000001

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

About Obi Oberoi

I am Technology Aficionado who has a passion for learning, speaking, blogging and hanging out with geeks and like minded people!
This entry was posted in SQL Server. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.