Super-fast Excel to SQL Insert Statement Converter Workbook

Most data comes from our business folks in Excel and needs to be in SQL for us to provide the analysis they are typically after.  SQL has some tools to ingest data from Excel into SQL but most of them have proven to be a pain in the butt (data conversions to get unicode and non-unicode columns is at the front of that list).  So a few lines of Excel macro and a few sheets to go with those and I have a simple workbook that generates insert statements for you.  This has, since I threw it together, saved me a pile of time fiddling with getting data where I need it.  It supports options of adding a carriage return between inserts and overriding the default setting to escape quotes if you want.  It sends the resulting SQL statements straight to your clipboard.

You can specify how many columns to pick up, starting at the far left, and how many rows to pick up as well.  I could have made this sense last used on both axes but, well, didn't.  yet.

The file is available for download and is a macro-enabled Excel workbool (.xlsm) with the following hashes:

MD5 : f94307aeb7140a1f0fbe57d5812859de
SHA-1: 49b02668774d006b152621895f5cb1ed7aa7bf65

If that still makes you uncomfortable, below is the code and general layout so you can homebrew your own.

Control tab - Where you set your options.  If you call these tabs something else just change the Const values in the script.

 

Data tab - Not much to it, it will take the first row values and interpret them as the column names on the insert.

Running the sheet against the data in the image just puts the following in your clipboard:

INSERT INTO some_db.dbo.tblFoo ([last_name],[nickname],[band]) VALUES ('Grohl','Dave','Foo Fighters')
INSERT INTO some_db.dbo.tblFoo ([last_name],[nickname],[band]) VALUES ('Walker','Butch','Butch Walker')
INSERT INTO some_db.dbo.tblFoo ([last_name],[nickname],[band]) VALUES ('Berg','Moe','TPOH')
INSERT INTO some_db.dbo.tblFoo ([last_name],[nickname],[band]) VALUES ('Ellison','Jim','Material Issue')

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82

Sub GenerateSql() Const DataTab = "Data" Const ControlTab = "Control"   Dim szDbName As String, szTableName As String, szLastCol As String, szLastColumn As String, iLastRow As Integer Dim iColumnCount As Integer, iRowCount As Integer Dim rCols As Range, rData As Range, szCols As String, szData As String Dim szSql As String, szSqlRowPreface As String, szSqlRow As String, szSqlCols As String, szSqlDataRow As String Dim bSettings_ExtraLines As Boolean, bSettings_NoEscape As Boolean   szDbName = Trim(Sheets(ControlTab).Range("C3").Value) szTableName = Trim(Sheets(ControlTab).Range("C4").Value) szLastColumn = Trim(Sheets(ControlTab).Range("C6").Value) iLastRow = Trim(Sheets(ControlTab).Range("C7").Value) iColumnCount = 0 iRowCount = 0   ' Handle settings     If Sheets(ControlTab).Shapes("Check Box 2").OLEFormat.Object.Value = 1 Then         bSettings_ExtraLines = True     Else         bSettings_ExtraLines = False     End If     If Sheets(ControlTab).Shapes("Check Box 3").OLEFormat.Object.Value = 1 Then         bSettings_NoEscape = True     Else         bSettings_NoEscape = False     End If   ' Build ranges     szCols = "A1:" & szLastColumn & "1"     szData = "A2:" & szLastColumn & iLastRow     Set rCols = Sheets(DataTab).Range(szCols)     Set rData = Sheets(DataTab).Range(szData)   CopyTextToClipboard ("Error, import failed") ' In case we encounter problems   ' Build insert, destination and columns:     For Each col In rCols.Columns         szSqlCols = szSqlCols + ("[" & col.Value & "],")         iColumnCount = iColumnCount + 1     Next col     szSqlCols = Left(szSqlCols, Len(szSqlCols) - 1) ' remove trailing comma     szSqlRowPreface = "INSERT INTO " & szDbName & ".dbo." & szTableName & " (" & szSqlCols & ") VALUES ("   ' Build values     For Each rw In rData.Rows         iRowCount = iRowCount + 1         szSqlDataRow = "" ' Reset this for each row         For Each col In rw.Columns             szSqlDataRow = szSqlDataRow + ("'" & FormatSql(col.Value, bSettings_NoEscape) & "',")         Next col         szSqlDataRow = Left(szSqlDataRow, Len(szSqlDataRow) - 1) ' remove trailing comma         szSql = szSql + szSqlRowPreface & szSqlDataRow & ")" & vbNewLine                   If bSettings_ExtraLines = True Then             szSql = szSql & vbNewLine         End If     Next rw   ' Add to clipboard and complete: CopyTextToClipboard (szSql) MsgBox ("Completed with " & iColumnCount & " data columns and " & iRowCount & " rows." & vbNewLine & vbNewLine & _     "Results are in the clipboard.")   End Sub   Function CopyTextToClipboard(ByVal inText As String)   Dim objClipboard As Object   Set objClipboard = CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")   objClipboard.SetText inText   objClipboard.PutInClipboard   Set objClipboard = Nothing End Function   Function FormatSql(ByVal inText As String, ByVal skip As Boolean)     If skip = True Then         FormatSql = Trim(inText)     Else         FormatSql = Trim(Replace(inText, "'", "''"))     End If End Function

HabitRPG - The Gamefication of My Todo List

I have a rotten memory and a job that often has me managing far too many tasks every day than I can keep straight in my head.  Over the years I've developed habits to help mitigate this, I methodically store detailed notes in my wiki and use task trackers.  I've gone through more task trackers over the years than I can really remember, some good, some not good, but the one that I've been using for the last year is my favorite to date.  The fact that I've been using it for a year says a lot about this tool.

It's called HabitRPG and they describe the (free) site/service thusly: 'HabitRPG is an open source habit building program which treats your life like a Role Playing Game. Level up as you succeed, lose HP as you fail, earn money to buy weapons and armor'.

Gear, mounts, pets, classes, spells, potions, parties, guilds, quests, boss fights.  I.  Freaking.  Love.  It. 

I have habits that I track, good and bad, things I should do daily and my running todo list.  When I do things I should, I gain gold and experience which lead to better gear, helpful when I don't do what I should.  Missing items or engaging in behavior I am trying to curb (such as beer on a weekday) makes me take damage.  That happens enough and I die, losing gold and precious gear.  I know, it all sounds ridiculous, but it works, I've stayed engaged with this todo list far more than any before it and have a free, yet real additional incentive to get things done.

I've recently joined a party, where we take on 'boss monsters' as a group.  What this really means is that my achieving my daily goals (or not) helps or hurts the group.  That adds support among this group of (previously) strangers and additional incentive.  You can aslo accept 'quests' created by the community, accepting the challenge to adopt a good habit for a period, and gaining loot if successful. All of these boosts to getting things done are tiny, but are boosts.

HabitRPG was a brilliant idea by Tyler Renelle that I funded on Kickstarter in its infancy and has evolved into a awesome, fun and effective tool for me to stay on target.  Give it a spin and drop me a note if you want to join the party, we could use a mage that does their work on time...

My dashboard and party

The Cheapskate's Guide: Extending SP 2010 Functionality

This is what I am presenting at SharePoint Saturday in Richmond VA and the accompanying file packages that go with that talk. There are 13 items total, each of them containing any scripts and resources needed (that I can provide) and a PDF breakdown of what it takes to deploy them. 5 of the 13 use Javascript controls I picked up for cheap from Codecanyon.net, you'll need to grab the control mentioned to get these to work, the others contain all that you need to get running. Drop me a line if you do something cool with one of these or modify them.

The Slides: PowerPoint The Controls: Calendar Viewer (free) Foobar Alerts - $2 Coffees (foobar 2.1) Tabs – Original (free) Tabs – Metro (free) (Edit:Published here!) News Page Rotator - $4 Coffees (Royal Slider 9.4.8) FAQ (free) Tabbed Container - $4 Coffees (Zozo Tabs v2.2) I Want To (free) Image Rotator - $3 Coffees (jQuery Banner Rotator) Google RSS Static (free) Google RSS Scrolling (free) Snow - $1 Coffee (JSized Snow Effect) Google maps traffic widget (free) Other links of note: Notepad++ - Great editor CodeCanyon (where I shop for controls) jQuery SP Services CAML Query Builder

Outlook-Fu: How to 'Snooze' Email in Your Inbox

If you are like most people today (including me), your inbox doubles as your to-do list and is the roadmap for what you need to take care of each day. We know that it is not ideal, but is what the majority of us use, because it is there and contains our action items already. Below are instructions for setting up a way to 'snooze' email messages so, much like you do with appointments that pop up in the Outlook reminder, you can hide them from view until they need attention. I find that the lack of clutter created by things I don't need to address yet keeps my inbox streamlined and easier to manage.

To do this we'll be creating a new view of our inbox and setting a filter. Instructions and screenshots are shown in Outlook 2013 but the same options discussed are available with Outlook 2010 as well.

Step 1 – Create a new view

1. Navigate to your inbox
2. In your 'view' tab, under 'Change View', choose 'Save Current View as New View', give this view a name and allow it to be used on All Mail and Post folders
   
3. Now you have a duplicate view and can hop back between this and your original view at will via the 'View' -> 'Change View' option.

Step 2 – Configure a Filter

We want the new view to only show items that both have no Due Date (otherwise we don't see any new items) and those that have a due date of today or before. To do this you:

1. Open 'View Settings' in your View menu
   
2. In the Advanced View Settings dialog, choose 'Filter'
   
3. In the 'Advanced' tab, add an item for 'Due Date', you'll find that under 'Frequently-used Items' and set it's condition to 'does not exist'. Then add this rule to the list.
   
4. Repeat the previous step, adding another Due Date filter but with the condition of 'on or before' and in the 'Value' field type Today and add the rule to the list. You will have the following 2 rules showing now. Click OK both here and in the Advanced View Settings to return to your new view. You are done.
   

Using your new View and Powers of Snooze

Now, when you right-click the flag icon beside a message in your inbox you are shown the due date flag options that look like this:

When you open these flag options for a message and set the due date flag to one of the dates in the future, say tomorrow, it will now disappear from the view of your inbox until tomorrow. Out of sight, out of mind. Note that setting an item to 'This Week' will set the reappear/due date to the upcoming Friday and 'Next Week' will set it to the following Monday. You can choose 'Custom' to set any specific date you want to have it reappear as well, even telling Outlook to throw a reminder if you like.

Items that you set to a future date will now hide until you want them to reappear, though you can always shift to your original view (which is why we made a copy of that view to begin with) in order to see all items in case you need to get to a message before it's due date.