Whether we like it or not, the modern office information environment isn't a tidy place. We would all like to imagine paperless offices in which executives and workers access information through engineered software and in which MCSAs manage information that flows through MCSE-designed, -implemented, and -maintained networks. Instead, the office environment remains chaotic.
No company could afford to stay in business long if staffed by the large numbers of certified systems administrators and systems engineers that contemporary information systems theoretically require. Instead, most organizations rely on high-end office administrators who aren't trained as programmers or DBAs but who have taught themselves the programming skills necessary to maintain corporate information flow at acceptable cost. These self-taught programmers wear many hats and typically have a diverse skill set that includes two essential components: They can use Query Analyzer to understand and write short, structured queries, and they know how to write short routines in Visual Basic for Applications (VBA) that run in either Microsoft Access or Microsoft Excel.
If you're this type of programmer-administrator, the most common problem you face is probably ad hoc data flow from one environment to another in which data often originates from diverse data sources that change unpredictably over time. And in your environment, importing data from comma-separated value (.csv) files and Excel spreadsheets is probably one of your most common tasks. A typical problem begins when a mid-level manager sends new data as an email attachment, often in the form of a headerless .csv file, and instructs you to input the new data into the existing database. Although you can accomplish this task by using Data Transformation Services (DTS) or bulk copy program (bcp), in many cases integrating the import operation with your existing stored procedures would result in a more seamless solution. Unfortunately, T-SQL stored procedures don't support the ability to access external objects such as Excel spreadsheets—or do they? In this article, I show you one way you can enable your T-SQL stored procedures to import data from an Excel spreadsheet. And with a little work, you can extend this technique to access data in Microsoft Word, Microsoft Outlook, or any resource that uses a COM-compliant object library.
Why Not DTS?
A typical DBA might solve the problem of the headerless .csv file by using Notepad to enter the header row into the file, then using the DTS Wizard to import the file into a SQL Server table that the wizard creates. One problem with this process is that the wizard might not create fields of the right data type. Another problem is that this process is labor intensive and difficult to customize. More often than not, the DBA has to do the task manually each time it's required. And the DBA has to use the DTS Designer to maintain and update the DTS package over time. Although the DTS Designer is a flexible tool, it has a complex drag-and-drop interface that requires the user to click around in a maze of arrows and icons looking for places to change data characteristics and insert small SQL or VBA routines. In addition, the DTS Designer creates "hidden" routines whose code exists only in the Designer. This hidden code is difficult to debug, maintain, expand, and update. The process also means the DBA has to spend valuable time learning to use the Designer rather than focusing on programming tasks and end results.
The ideal solution to the problem of the headerless .csv file would be to create a SQL Server table with the appropriate fields, then import the .csv file's data with one wave of a magic wand. The fact is, you can; SQL Server lets you import the data programmatically from inside a stored procedure. This technique results in compact, generalized code that you can extend and repurpose easily. What's more, this technique uses the existing skills of the typical programmer- administrator, with no complex interface and no steep learning curves.
One little-used SQL Server feature is the capability to create a SQL Server Agent job step that runs a short VBScript or JScript routine. One reason DBAs don't often use this capability is that few know that those scripts can access the features of any COM-compliant application resident on the server. When you realize you can embed the scripts inside a SQL Server stored procedure, the entire world of component-object model programming opens up. And almost universally available Microsoft Office applications such as Excel are suddenly able to interact with your SQL Server database.
The listings I provide in this article are useful examples of a methodology that is, for most programmer-administrators, considerably easier to use than DTS. But it's important to realize that this technique isn't merely an easy replacement for DTS. You can use this technique to drive any COM-compliant application, including Word, Outlook, and Microsoft CRM. If a program's object model is available, you can manipulate it from inside a SQL Server stored procedure.
CSV to XLS
Listing 1 creates a stored procedure, Util_CSV_to_Excel, that contains a short embedded VBScript routine, which uses Excel to open a comma-delimited text file. The stored procedure adds to the .csv file a header row derived from the column names of a specific SQL Server table, then saves the .csv file as an .xls file for later import into the same table. All that's required for the code to work properly is that Excel and Microsoft Data Access Components (MDAC) be installed on the server. The SQL Server Agent service also has to be running, which it usually is on most SQL Servers. One big advantage of converting the .csv file to an .xls file before saving it in SQL Server is that you can give the Excel file to executives who might have their own Excel skill set and prefer data in that form. You could also export the data to Access instead of to Excel.
Prev. page
[1]
2
3
next page 
