-
read text file by T-SQL
hi
how can i read a txt file by using T-SQL commands.
thanx
-
Where is the file? You can try with linked server or load it into sql table with bcp.
-
T-sql
Could someone please assist me with a problem. I need to read a plain text file via T-SQL code or script. I have 100 users accounts that need to be locked and I want the SQL code to parse the file/read the ID, pass it into the code as an argument and lock it.
-
The easiest way I can think of is to import the text file into Access database and create a table there.
Then you have two options at this stage, either you can export the table from Access to SQL server or use a linked server to query the access table directly. Both of these should not take more few mins to do.
To import the data into the access table, go to:
File -> Get external Data -> import.
And then choose text file and follow the steps.
To export the data from access to sql server:
Right click on the Access table. ->
Click on export. ->
Choose ODBC from the save file as type ->
And follow the steps.
I Hope this helps….
Dr T
-
Why can't load data in the file to sql table directly?
-
You can create OLE DB connection to the text file using sp_addlinkedserver procedure. This in other words means creating a linked server to the text file.
Example script:
exec sp_addlinkedserver NW_TEXT, 'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0' 'C:\TEXTFILES', NULL, 'Text'
Dr T
-
You can also use "OPENROWSET" command. below show you a example
select * from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};
DefaultDir=C:\;','select * from
RNC.TXT')
Greetings,
Santiago Carela
Rep. Dom.
-
RE: read text file by T-SQL
Okay, so there is a way to do this directly from T-SQL, but it's kinda involved. The sample code is below:
CREATE PROCEDURE ReadFromTextFile
@FileName VARCHAR (1024)
AS
DECLARE @OLEResult INT
DECLARE @FS INT
DECLARE @FileID INT
DECLARE @Message VARCHAR (8000)
-- Create an instance of the file system object
EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
IF @OLEResult <> 0
BEGIN
PRINT 'Scripting.FileSystemObject'
PRINT 'Error code: ' + CONVERT (VARCHAR, @OLEResult)
END
-- Open the text file for reading
EXEC @OLEResult = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT, @FileName, 1, 1
IF @OLEResult <> 0
BEGIN
PRINT 'OpenTextFile'
PRINT 'Error code: ' + CONVERT (VARCHAR, @OLEResult)
END
-- Read the first line into the @Message variable
EXECUTE @OLEResult = sp_OAMethod @FileID, 'ReadLine', @Message OUT
-- Keep looping through until the @OLEResult variable is < 0; this indicates that the end of the file has been reached.
WHILE @OLEResult >= 0
BEGIN
PRINT @Message
EXECUTE @OLEResult = sp_OAMethod @FileID, 'ReadLine', @Message OUT
END
EXECUTE @OLEResult = sp_OADestroy @FileID
EXECUTE @OLEResult = sp_OADestroy @FS
Hope this helps!
-
I am using SQL Server 2008 and Windows Server 2008. I only get the first character of the file?
Please help.
Thank you,
Lon
-
-
I only get one character, but I believe the problem is
the file needs to be ANSI format.
Thanks,
Lon
-
Read a text file from t-sql
Guys this is the way of doing it
exec sp_OACreate 'Scripting.FileSystemObject', @oFile out
exec sp_OAMethod @oFile, 'OpenTextFile', @objFile out, @File, 1
exec sp_OAMethod @objFile, 'AtEndOfStream', @eof out
you don't have to loop using the ReadLine method.
Hope this helps.
-
Giant text field
Couple of questions here:
exec sp_OACreate 'Scripting.FileSystemObject', @oFile out
exec sp_OAMethod @oFile, 'OpenTextFile', @objFile out, @File, 1
exec sp_OAMethod @objFile, 'AtEndOfStream', @eof out
I'm new to this type of loading so excuse stupid questions. What are the variable types in these three lines and how big can they get?
I have been using bulk import because the file I need to process comes with varying row delimiters (no, trying to get the data supplier to change to a standard just isn't going to happen). They got a new sys admin who now says bulk import is not allowed so bulk import access has been shut off. Ultimately what I would like to do is read characters until I hit a character which I'm getting from a parameter passed into the stored proc. That in effect would be a 'line' which I put in a single column in a table. Now here's the rub, our clients are using a mix of 2005 and 2008. I think a Filestream object column might work for this but not everyone uses 2008. And these files can be anywhere from 1K to hundreds of M. Any ideas?
Thanks,
joeb
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|