question

David Wimbush avatar image
David Wimbush asked

How to read a text file

I've inherited a horrible stored proc to load an XSL file from a text file into one TEXT column in one row. It BULK INSERTs into a temporary table and then cursors through that somehow appending the lines together into a TEXT variable using TEXTPTR and UPDATETEXT. The whole thing is in a transaction. What could go wrong? Well, it's suddenly started occasionally not loading some of the start of the file. No errors, it just sometimes misses off the first n bytes (always the same number of bytes). I have a recipe like this that works: create table #FileContents ( LineNumber int identity , LineContents nvarchar(4000) ); declare @FileName varchar(255); declare @NewLine char(2) = char(13) + char(10); declare @CmdLine varchar(300); declare @XSL varchar(max); set @FileName = '' set @CmdLine = 'type ' + @FileName; insert #FileContents exec master.dbo.xp_cmdshell @CmdLine; select @XSL = isnull(@XSL, '') + @NewLine + isnull(LineContents, '') from #FileContents order by LineNumber; Is there a cleaner way? Maybe one that doesn't use xp_cmdshell?
sql-server-2008-r2query
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Do you have SSIS? or Filestream?
0 Likes 0 ·
Oleg avatar image
Oleg answered
Here is the sample showing how to insert the guts of the file to the table without using the xp_cmdshell: create table #FileContents ( LineNumber int identity (1, 1), LineContents nvarchar(4000) ); insert into #FileContents select f.BulkColumn from openrowset ( bulk 'C:\Useless\Temp\ some_file.txt', single_clob ) f; select * from #FileContents; The results display (because this what was in the file): LineNumber LineContents ----------- ------------ 1 Hello, Jerry Hello, Newman Of course it goes without saying that the file path is relative to the server. Oleg
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@David Wimbush Thank you. In my answer, the path to the file is hardcoded because openrowset will not accept a path as a parameter. This means that should the file path be a parameter, the **insert into select from** should be spelled out into a varchar variable which can be fed to the execute in order to bypass this limitation of the openrowset. For example,

declare @file_path nvarchar(50);
-- declare the size as needed (no limit)
declare @sql nvarchar(500);

select
	@file_path = 'C:\\Useless\\Temp\\some_file.txt',
	@sql = 'insert into #FileContents
    select f.BulkColumn 
	from openrowset
	(
		bulk ''' + @file_path + ''',
		single_clob 
	) f;';

exec (@sql);
1 Like 1 ·
That works like a charm. Thanks, Oleg.
0 Likes 0 ·
So I discovered! But you put me on the right track. Thanks.
0 Likes 0 ·
Matt Whitfield avatar image
Matt Whitfield answered
If you can create a CLR assembly with EXTERNAL_ACCESS, then I would go that route. Given that you have a nvarchar(4000) column, you will want to split larger text files into smaller chunks. You will also want it not to epic fail on Unicode. A CLR table function could be a neat way to achieve the above two without causing the massive hole that xp_cmdshell does.
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks Matt. +1 because I think this is probably the technically superior solution. But this is a live problem a quick fix is needed so I'm going with Oleg's answer in this case.
0 Likes 0 ·
@David Wimbush - fair play - but do remember that if you need Unicode support (which the nvarchar suggests) then you will need to do it properly...
0 Likes 0 ·
@Matt Whitfield +1 This is a very sound solution! As a matter of fact, you gave me an excellent idea on the task I am working right now. Thank you.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.