Importing Fixed Position file into SQL Server

For any DBMS system (I know that it sounds redundant) data can come into variety of forms and formats .. say CSV, XML, another DBMS, tab limited file or fixed position file or any proprietary format (as long as you have data providers for them ). And most of connected DBMS systems have different approaches to import those kind of formats into database. One of that format is fixed position files, in many cases these finds of files gets generated by applications that uses flat file databases to store data.

In SQL Server there are three main ways to import these fixed position files into database. Using Bulk Insert tsql command, using BCP utility or using SSIS. In today’s post I will be showing way to import file using BULK INSERT. May be later on if I am feeling graceful, I will write about SSIS and BCP methods as well . Ok, so for purposes of test, I have created a fixed position file using SSIS. If you don’t know how it is done is SSIS see this previous post for HOW TO create fixed width file in SSIS.

Next, I’ve created a simple Bulk Insert script which reads that file and stores it into some temp table and then parse that file and inserts into table.

 

USE [tempdb]
GO

-- Create temp tables
CREATE TABLE [dbo].[tableTest](
	[FirstName] [NVARCHAR](50) NOT NULL,
	[LastName] [NVARCHAR](50) NOT NULL,
	[AddressLine1] [NVARCHAR](60) NOT NULL,
	[City] [NVARCHAR](30) NOT NULL,
	[PostalCode] [NVARCHAR](15) NOT NULL
) 

GO

CREATE TABLE #tempData
(FromFile NVARCHAR(200))

BULK INSERT #tempData
   FROM 'D:\Temp\samples\Output.txt' -- Path of Data file
   WITH 
      (
         ROWTERMINATOR ='\n'  -- According to MSDN, New Line Feed (\n) automatically adds Carrige Return (\r)
      )

DECLARE @FromFile VARCHAR(200)
DECLARE parseData CURSOR FOR
SELECT FromFile FROM #tempData

OPEN parseData

FETCH NEXT FROM parseData
INTO @FromFile

WHILE @@FETCH_STATUS = 0
BEGIN

INSERT INTO tableTest VALUES(SUBSTRING(@fromFile,1,50),SUBSTRING(@fromFile,51,50),SUBSTRING(@fromFile,101,60),SUBSTRING(@fromFile,161,30),SUBSTRING(@fromFile,191,15))

FETCH NEXT FROM parseData
INTO @FromFile

END

CLOSE parseData
DEALLOCATE parseData

-- Verifying results
SELECT * FROM tableTest

-- House cleaning
DROP TABLE #tempData
DROP TABLE tableTest

May be this query is not designed based on best practices but I personally liked this approach better than one from SSIS because since it is just a TSQL script it is more easy to edit at any point without use of any additional tool like SSMS (or Visual Studio in case of SSIS)

That’s it for now.

It’s Just A Thought …