Solved

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

Posted on 2004-05-02
5
560 Views
Last Modified: 2008-02-01
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
Comment
Question by:braindog_43
5 Comments
 
LVL 34

Expert Comment

by:arbert
ID: 10972127
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
 

Author Comment

by:braindog_43
ID: 10975283
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
 
LVL 13

Accepted Solution

by:
danblake earned 125 total points
ID: 10976077
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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now