Load File as Table With Identity Column for Each Row

Posted: March 18, 2011 in SQL Server 2005

This routine will load a file as a table with an identity column for each row:

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

-- Check if the function already exists in the database and if it does, we need to drop this before creating it again
IF EXISTS (SELECT * 
           FROM   dbo.sysobjects 
           WHERE  id = OBJECT_ID(N'dbo.FN_ReadFileAsTable') 
           AND    OBJECTPROPERTY(id, N'IsTableFunction') = 1)
   DROP FUNCTION dbo.FN_ReadFileAsTable;
GO

-- Main function logic starts here
CREATE FUNCTION dbo.FN_ReadFileAsTable
               (@pv_path      VARCHAR(200),
                @pv_file_name VARCHAR(100)) RETURNS @tbl_file TABLE (id   INT IDENTITY(1,1), 
                                                                     line VARCHAR(2000)) 
AS
BEGIN
   -- Declare variables
   DECLARE @li_file_system INT;
   DECLARE @li_text_stream INT;
   DECLARE @lv_command     VARCHAR(1000);
   DECLARE @li_handle      INT;
   DECLARE @lv_string      VARCHAR(2000);
   DECLARE @lv_yn          INT;
   DECLARE @li_error_obj   INT;
   DECLARE @lv_error_msg   VARCHAR(1000);
   DECLARE @lv_source      VARCHAR(200);
   DECLARE @lv_descr       VARCHAR(200);
   DECLARE @lv_help_file   VARCHAR(200);
   DECLARE @li_help_id     INT;

   -- Call routine to get the file system object
   SET @lv_error_msg = 'Opening the File System Object';
   EXECUTE @li_handle = sp_OACreate 'Scripting.FileSystemObject', @li_file_system OUT;

   -- If the file system object was created successfully, set the file path
   IF @li_handle = 0 
      BEGIN
         SET @li_error_obj = @li_file_system;
         SET @lv_error_msg = 'Opening file "' + @pv_path + '\' + @pv_file_name + '"';
         SET @lv_command   = @pv_path + '\' + @pv_file_name;
      END;

   -- If the file system object was created successfully, open the file for reading
   IF @li_handle = 0 
      BEGIN
         EXECUTE @li_handle = sp_OAMethod @li_file_system, 'OpenTextFile', @li_text_stream OUT, @lv_command, 1, false, 0;
      END;

   -- If the file stream was opened successfully, read the file line by line
   WHILE @li_handle = 0
     BEGIN
         -- Check if we are at the end of the text stream
        IF @li_handle = 0 
            BEGIN
               SET @li_error_obj  = @li_text_stream; 
               SET @lv_error_msg  = 'Finding out if there is more to read in "' + @pv_file_name + '"';
               EXECUTE @li_handle = sp_OAGetProperty @li_text_stream, 'AtEndOfStream', @lv_yn OUTPUT;
            END;
         
         -- Break from while loop if we are at the end of the file stream
        IF @lv_yn <> 0
            BEGIN
               BREAK;
            END;

         -- Insert line into output table
        IF @li_handle = 0
            BEGIN
               SET @li_error_obj  = @li_text_stream; 
               SET @lv_error_msg  = 'Reading from the output file "' + @pv_file_name + '"';
               EXECUTE @li_handle = sp_OAMethod @li_text_stream, 'Readline', @lv_string OUTPUT;
               INSERT INTO @tbl_file(line) 
               SELECT @lv_string
            END;
     END;

   -- Close the text stream
   IF @li_handle = 0 
      BEGIN 
         SET @li_error_obj  = @li_text_stream; 
         SET @lv_error_msg  = 'Closing the output file "' + @pv_file_name + '"';
         EXECUTE @li_handle = sp_OAMethod @li_text_stream, 'Close';
      END;

   -- Only carry on when the text stream has been closed
   IF @li_handle <> 0
     BEGIN
        EXECUTE sp_OAGetErrorInfo @li_error_obj, @lv_source OUTPUT, @lv_descr OUTPUT, @lv_help_file OUTPUT, @li_help_id OUTPUT;
        SET @lv_error_msg = 'Error whilst ' + COALESCE(@lv_error_msg, 'doing something') + ', ' + COALESCE(@lv_descr, '');
        INSERT INTO @tbl_file(line) 
        SELECT @lv_error_msg;
     END;
   
   -- Call routine to destory the object
   EXECUTE sp_OADestroy @li_text_stream;
  RETURN;
END
GO
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s