July 6, 2011 at 5:32 pm
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!
July 6, 2011 at 5:44 pm
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);
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy