Why is this page text-only?

« May 2006 | Main | July 2006 »

Importing Excel into SQL using ASP.NET 2.0 and SqlBulkCopy

We were recently faced with a challenge at work where the client wanted to provide an Excel file of data which could be imported into SQL and used to update data within certain tables. While this is a snap via Enterprise Manager, it's trickier when the client wants it all to happen on the back-end so they can do it themselves via a web form.

While there are numerous tutorials for exporting data into Excel format, there didn't seem to be many on how to get the data in, so I'm posting this function in the hopes that it helps someone else.

I've stripped out most of the code so that this is JUST the part that imports the data from Excel. This example assumes that the.xls file is already on your server in the same location as this script. You can write your own upload functions to get it there. (If you need help with uploading, let me know and I'll post an article on that as well) This also assumes that you have a table in SQL that matches the schema of your spreadsheet.

This uses the new SqlBulkCopy class in ASP.NET 2.0 which lets you easily copy bulk amounts of data from one source to another.

Imports System.Data
Imports System.Data.OleDb
Imports System.Data.SqlClient
Imports System.Web.Configuration

...

'Declare Variables - Edit these based on your particular situation
Dim sSQLTable As String = "TempTableForExcelImport"
Dim sExcelFileName As String = "myExcelFile.xls"
Dim sWorkbook As String = "[WorkbookName$]"

'Create our connection strings
Dim sExcelConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath(sExcelFileName) & ";Extended Properties=""Excel 8.0;HDR=YES;"""
Dim sSqlConnectionString As String = WebConfigurationManager.ConnectionStrings("MyConnectionString").ToString

'Execute a query to erase any previous data from our destination table
Dim sClearSQL = "DELETE FROM " & sSQLTable
Dim SqlConn As SqlConnection = New SqlConnection(sSqlConnectionString)
Dim SqlCmd As SqlCommand = New SqlCommand(sClearSQL, SqlConn)
SqlConn.Open()
SqlCmd.ExecuteNonQuery()
SqlConn.Close()

'Series of commands to bulk copy data from the excel file into our SQL table
Dim OleDbConn As OleDbConnection = New OleDbConnection(sExcelConnectionString)
Dim OleDbCmd As OleDbCommand = New OleDbCommand(("SELECT * FROM " & sWorkbook), OleDbConn)

OleDbConn.Open()

Dim dr As OleDbDataReader = OleDbCmd.ExecuteReader()
Dim bulkCopy As SqlBulkCopy = New SqlBulkCopy(sSqlConnectionString)

bulkCopy.DestinationTableName = sSQLTable
bulkCopy.WriteToServer(dr)

OleDbConn.Close()

NOTE: I left out any error handling functionality for the sake of brevity but it would be a good idea to use a Try/Catch block to handle any exceptions

| Posted On June 26, 2006 Comments (9) | TrackBacks (0)

Critical Thinking Exercise - Answers

A few days ago I posted a "critical thinking exercise" which was designed to help you learn the type of things you should be looking for when you do a code review.

As promised here are the answers:

  1. The <html> element should specify lang="en" xml:lang="en". (WCAG - Priority 1)
  2. There are no comments documenting any of the code.
  3. CSS styles should be stored in an external style sheet.
  4. There should ideally be a separate print style sheet.
  5. The <title> tag should be more descriptive than just the company name.
  6. A <meta> description tag should be provided as this is often what search engines will display in their listing.
  7. There is no favicon image present.
  8. There is no mechanism to skip repetitive navigation elements. (Section 508)
  9. <img> alt attributes should be used properly. Tags such as "Blue Divider Line" are not useful and should be replaced with alt="".
  10. Semantic header tags should be used at all times. <p> tags with CSS classes such as "headertext" should not be used when an <h1-7> tag will work.
  11. This page has an XHTML doctype. Therefore all tags must be lowercase. <P> is not valid.
  12. This page has an XHTML doctype.  Therefore all tags must be self-closing. <br> and <img src=""> should be replaced with <br /> and <img src="" />.
  13. In the footer... adjacent links must be separated by more than just white space. (WCAG - Priority 3)
  14. Hyperlinks should be descriptive so search engines pick up on the keywords.  "Click here" links should be avoided. This is also for accessibility as some screenreaders will read the links out of context. (WCAG - Priority 2)
  15. Font sizes should be specified using relative units such as ems or percentages. Fixed sizes such as px cannot be resized by the user in many browsers. (WCAG - Priority 2)
  16. All form elements should have associated label controls.
  17. This page is for laptops but the selected class on the nav is on the printers tag
  18. "in the green box below" - Color and spatial directions are not 508 compliant because they can't be interpreted by a user with visual impairments.
  19. The word computers is misspelled as "copmuters" in the copy
  20. Flash objects should be embedded via Javascript to fix the Microsoft/Eolas problem and also provide an alternative to users without the flash plugin
  21. td.divider { padding: 8px 0 0; } - The padding attribute must contain either 1, 2, or 4 elements... 3 elements is not valid
  22. <h2>For all your PC Needs</h3> - Tag opens with h2 but closes with h3
  23. The Accessories subnav on the left is improperly nested. Nested <ul> tags must reside inside a parent <li>. I.e.:
    <ul>
         <li>Parent List Item
              <ul>
                   <li>Nested List Item</li>
              </ul>
         </li>
    </ul>
  24. No visited link color is defined. This is helpful so users can remember where they've been.
  25. Tables should be used only for the purpose of displaying tabular data and not for creating design/structure. CSS should be used instead.

| Posted On June 22, 2006 Comments (0) | TrackBacks (0)

An Exercise in Critical Thinking

One of the biggest challenges in this field is often finding the time to do a proper QA process on projects before they launch. Tight deadlines and last-minute changes often eradicate much of the time that should be allocated to QA.

So if you're launching a new website, and you only have a handful of minutes to do QA, you need to make sure that you know exactly what to look for. To that end, I recently developed a simple critical thinking exercise for my development team to help them learn the types of things that they should be looking for when they do a code review.

This is a basic HTML page that I've created and riddled with bugs, errors, and other things that are not best practices for the industry. There are some things that are simply coding errors like malformed tags. Other things, while they may be technically correct, will impact things like SEO and 508-compliance so they should be corrected.

Look through this HTML file and see how many problems you can find. I'll post the answers in a few days.

Download the Critical Thinking Exercise

| Posted On June 18, 2006 Comments (0) | TrackBacks (0)

Burning your RSS feed

I've been busy the last couple of weeks but today I finally got around to something that I've been putting off for a while; setting my blog's RSS feed up using FeedBurner.

FeedBurner is a great service which takes your regular RSS feed and adds cool features to it, like:

  • PingShot - maintains an updated list of content aggregators and notifies them whenever you post a new blog.
  • SmartFeed - intelligently converts your feed to whatever format requested by the client. You don't need to worry about RSS vs. Atom or offering multiple feeds. Let FeedBurner do the work for you.
  • Analytics - which allow you to see exactly how many people subscribe to your feed and how often they click through to read your articles.
  • BrowserFriendly - enables your feed to be viewed inside a browser with buttons to allow users to subscribe via web-clients like MyYahoo!, Pluck, and PageFlakes.

Check out, and better yet, subscribe to the newly burned feed at http://feeds.feedburner.com/CodeScene

I've setup redirects for the old feed URLs so everyone should be seeing the new feed.

You also might notice that I switched out the old RSS button at the top with the new stylish feed button which is fast becoming the industry standard. Check out FeedIcons.com to download a set of icons in multiple formats for your site.

| Posted On June 11, 2006 Comments (0) | TrackBacks (0)