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)
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
- SQL Server Management Studio (download here for free)
- 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.
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 🙂