Using OpenRowSet with fixed-length Text Files

Question

Post reply

Using OpenRowSet with fixed-length Text Files

Brandon Forest

SSCommitted

Points: 1977
More actions
July 6, 2011 at 5:32 pm

#75601

I have ASCII text files that have no header and are not CSV. I used BCP to create import masks that I use with the BULKINSERT command to import into staging tables in a SQL Server 2000 DB.
There are two columns that determine a parent-child relationship between rows. I have determined that I have some orphined records in my staging tables. I need to verify that the data is faulty, so I want to query the text files directly using the OPENROWSET method. I am having a hard time figuring out how to structure the query properly and I am not seeing any useful examples on the web.
Can anyone show me the proper syntax for OPENROWSET to a fixed-length row text file?
Thanks!
Brandon_Forest@sbcglobal.net
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

mister.magoo SSC-Forever Points: 47174 More actions · Answer 1

Well, it depends what you want to do, but here is a snippet taken from a routine I wrote along those lines:

DECLARE @sql VARCHAR(MAX);

DECLARE @FileName VARCHAR(512);

SET @FileName = 'c:\myfile.txt'

SET @sql ='

;WITH data

AS (SELECT CONVERT (VARCHAR (MAX), BulkColumn) AS BulkColumn

FROM OPENROWSET (BULK '''+@FileName+''', SINGLE_BLOB) AS a)

SELECT ItemNumber,

REPLACE(ItemValue,char(13),'''')

FROM [data] CROSS APPLY [scheme].[DelimitedSplit8K] ([data].[BulkColumn], char(10))

WHERE ItemValue <> '''';'

EXEC(@sql);

You will find the DelimitedSplit8K function here: http://www.sqlservercentral.com/articles/Tally+Table/72993/

This code snippet will load any text file and split it into lines delimited on CR+LF.

It loads the file as a SINGLE_BLOB and then splits it into lines because I found that easiest for opening any kind of windows based text file.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);