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 = '<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?

more ▼

asked Jul 26, 2010 at 08:33 AM in Default

avatar image

David Wimbush
10.7k 30 34 43

Do you have SSIS? or Filestream?

Jul 26, 2010 at 08:37 AM ThomasRushton ♦♦
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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',
     ) 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.


more ▼

answered Jul 26, 2010 at 08:48 AM

avatar image

18.9k 3 7 28

That works like a charm. Thanks, Oleg.

Jul 26, 2010 at 09:07 AM David Wimbush

@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);

Jul 26, 2010 at 09:15 AM Oleg

So I discovered! But you put me on the right track. Thanks.

Jul 26, 2010 at 02:41 PM David Wimbush
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Jul 26, 2010 at 08:44 AM

avatar image

Matt Whitfield ♦♦
29.5k 62 66 88

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.

Jul 26, 2010 at 09:06 AM David Wimbush

@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...

Jul 26, 2010 at 09:13 AM Matt Whitfield ♦♦

@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.

Jul 26, 2010 at 09:33 AM Oleg
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jul 26, 2010 at 08:33 AM

Seen: 13841 times

Last Updated: Jul 26, 2010 at 08:33 AM

Copyright 2017 Redgate Software. Privacy Policy