Reading Files in SQL Server Using T-SQL

Introduction

SQL Server provides several "standard" techniques for file manipulation but, sometimes, they aren't quite up to the task at hand – especially when dealing with large strings or relatively unstructured data. Here are a number of T-SQL Stored Procedures, based on the use of the FileSystem Object (FSO) that may just get you out of a tight corner.

Background

SQL Server has never been short of ways to read from files, and it is always better to use the standard techniques provided by SQL Server where possible. However, most of them are really designed for reading tabular data, and aren't always trouble-free when used with large strings or relatively unstructured data.

For reading tabular data from a file, whether character-delimited or binary, there is nothing that replaces the hoary old Bulk Copy Program (BCP), which has more esoteric methods such as Bulk Insert. It is possible to read text-based delimited files with ODBC; simple files can be read and written-to using the xp_cmdshell Stored Procedure.

Using the code

Thankfully, when armed with OLE Automation and the FileSystem Object (FSO), all sorts of things are possible. The FileSystem Object was introduced into Windows to provide a single common file-system COM interface for scripting languages. It provides a number of handy services that can be accessed from T-SQL. In this article, I provide an example Stored Procedure that uses this interface to allow you to:

  1. Read lines of text from a file.
  2. Read a file into a SQL Server data type.

I'll provide a few details on the FSO along the way, but let's start with some examples of some of these procedures in action. You'll need to enable OLE Automation on your test server in order to follow along.

This is all very well, but how about something that reads a file in one gulp into a VARCHAR or XML data type? Perhaps you need to extract data from HTML, XHTML or some other format. I wrote a Stored Procedure dbo.spREADFileStream to read a file as a string. A code example is given below:


See full detail: http://www.codeproject.com/KB/database/READFileStream.aspx

Comments

Post a Comment

Popular posts from this blog

Asynchronous Socket Programming in C#

Image
Objective The objective of this article is to demonstrate a socket-based client/server application that will allow two-way asynchronous communication between a server and multiple client applications. Because this example uses asynchronous methods, the server application does not use threads to communicate to multiple clients (although internally the asynchronous communication mechanism uses threads at the OS level). The Difference Between Synchronous and Asynchronous Communication in Network Programming The key difference between synchronous and asynchronous communication can be explained with an example. Consider a server application that is listening on a specific port to get data from clients. In synchronous receiving, while the server is waiting to receive data from a client, if the stream is empty the main thread will block until the request for data is satisfied. Hence, the server cannot do anything else until it receives data from the client. If another clien
Read more

Url Routing MVC TUTORIAL

Image
This tutorial covers an introduction to the MVC pattern using Visual Basic. It also includes a brief overview of the URL routing assumptions that are a foundation to the implementation of the pattern. A sample application is created to demonstrate the fundamentals of this new web-application model. Implementing business rules and testing is also covered. Finally, the use of two alternative view engines, Silverlight and XML Literals, is coverd.
Read more

WCF Chat Sample

Image
The Chat sample demonstrates how to implement a multiparty chat application by using PeerChannel. Messages sent by any instance of a chat application are received by all other instances. To get samples and instructions for installing them Do one or more of the following: On the Help menu, click Samples . The Readme displays information about samples. Visit the Visual Studio 2008 Samples Web site. The most recent versions of samples are available there. Locate samples on the computer on which Visual Studio is installed. By default, samples and a Readme file are installed in drive :\Program Files\Microsoft Visual Studio 9.0\Samples\ lcid . For Express editions of Visual Studio, all samples are located online. For more information, see Locating Sample Files . Security Note: This sample code is intended to illustrate a concept, and it shows only the code that is relevant to that concept. It may not meet the security requirements for a specific environment, a
Read more