Solved

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

Posted on 2004-05-02
5
559 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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
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 extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

747 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

12 Experts available now in Live!

Get 1:1 Help Now