Solved

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

Posted on 2004-05-02
5
563 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
encrypt SQL Server 2008 port 1433 3 46
VMware PVSCSI SQL Server 2016 AlwaysOn 2 34
invoke-sqlcmd help 5 29
TDE for SQL Web Edition 1 37
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

739 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