Urgent Please !!! Stored Procedure to read data from all kinds of files

I need a stored procedure which could read data from all types of files and then print the data or store it in a table. i have tried bulk insert but when bulk insert reads the data and stores it in a table it creates many rows (.i.e rows = number of lines in the files). say if there are 20 lines then 20 rows and if 700 lines 700 rows. I want to store the data in one string and print it. Also the files i need to read are generated on runtime. If u want to take a look at the Sto. Proc. here it is. Please note files culd be of any size.
1. Bulk insert
CREATE PROC sp_readTextFile @filename sysname
SET nocount ON
EXEC ('bulk INSERT WilTemp FROM "' + @filename + '"')

2. Using a filesytem object

CREATE PROCEDURE [dbo].[readFromFile] (@FileName varchar(255))
DECLARE @FS int, @OLEResult int, @FileID int, @Text1 varchar(8000)

EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
IF @OLEResult <> 0 PRINT 'Scripting.FileSystemObject'

--read a file
execute @OLEResult = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT, @FileName, 1
IF @OLEResult <> 0 PRINT 'OpenTextFile'

--Read Text1
execute @OLEResult = sp_OAMethod @FileID, 'ReadAll', @Text1 OUT
IF @OLEResult <> 0 PRINT 'ReadAll'

--insert into tfromfile values (@Text1)
Print @Text1

EXECUTE @OLEResult = sp_OADestroy @FileID
EXECUTE @OLEResult = sp_OADestroy @FS

Who is Participating?
Yes, have a look at some of the previous answers to the problem of importing text that I have given:
1) Create a format file, to treat the whole files as text (using no delimiters) and use BULK INSERT
2) BCP in the file using Format File (You can use BCP to also create the format file).

You will need to know the start-> end offset of the file to allow a full text/import extract.
Its not too hard to use a different Bulk Insert file, with variable start/end markers.
I can probably provide a utility to count the number of bytes in a file to give you the end marker.
You can get the information from the following xp_cmdshell implementation:
dir text.txt /-C
Removing the date-time fields and then processing the field size as the rest of your BULK INSERT format file size string.

Note: Text/NTEXT | IMAGE data-types give poor performance (I have to say this to keep everyone else happy here), there are some things you can do to improve performance of these objects refer to: www.sql-server-performance.com
(If I there will only be more fireworks.....)
If you just want "one row", you need to stort in a TEXT/NTEXT field.

You could use the Textcopy utility to do this:


The above link is for sql7.0, but it is  include with 2000 as well.

braindog_43Author Commented:
Please note files could be of any size (Unlimited).

I can only read limited data with the above stored procedures i want to read unlimited data and cannot declare TEXT/NTEXT locally.any othe options please
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.