• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 572
  • Last Modified:

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
as
BEGIN
SET nocount ON
EXEC ('bulk INSERT WilTemp FROM "' + @filename + '"')
SELECT * FROM WilTemp
END
GO

***********************************************************
2. Using a filesytem object

CREATE PROCEDURE [dbo].[readFromFile] (@FileName varchar(255))
AS
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
GO

 
 
 
 
0
braindog_43
Asked:
braindog_43
1 Solution
 
arbertCommented:
If you just want "one row", you need to stort in a TEXT/NTEXT field.

You could use the Textcopy utility to do this:

http://www.winnetmag.com/Article/ArticleID/21275/21275.html

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

Brett
0
 
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
0
 
danblakeCommented:
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).
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20501232.html
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20005760.html
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20907736.html

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.....)
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now