Read Text File By T-SQL
Feb 16, 2008hi
how can i read a txt file by using T-SQL commands.
thanx
ADVERTISEMENT
Read Text File From SQL Server, Read Its Content, And Load It In RichTextBox (Related Component: Context.Response.BinaryWrite(), And StreamReader)
Nov 26, 2007OBJECTIVE: I would like to read a text file from SQL Server 2000, read the text file content, and load its conntents in a RichTextBoxTHINGS I'VE DONE AND HAVE WORKING:1) I've successfully load a text file (ex: textFile.txt) in sql server database table column (with datatype Image) 2) I've also able to load the file using a Handler as below: using System;using System.Web;using System.Data.SqlClient;public class HandlerImage : IHttpHandler {string connectionString;public void ProcessRequest (HttpContext context) {connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["NWS_ScheduleSQL2000"].ConnectionString;int ImageID = Convert.ToInt32(context.Request.QueryString["id"]);SqlConnection myConnection = new SqlConnection(connectionString);string Command = "SELECT [Image], Image_Type FROM Images WHERE Image_Id=@Image_Id";SqlCommand cmd = new SqlCommand(Command, myConnection);cmd.Parameters.Add("@Image_Id", System.Data.SqlDbType.Int).Value = ImageID;SqlDataReader dr;myConnection.Open(); cmd.Prepare(); dr = cmd.ExecuteReader();if (dr.Read()){ //WRITE IMAGE TO THE BROWSERcontext.Response.ContentType = dr["Image_Type"].ToString();context.Response.BinaryWrite((byte[])dr["Image"]);}myConnection.Close();}public bool IsReusable {get {return false;}}}'>'>
<a href='<%# "HandlerDocument.ashx?id=" + Eval("Doc_ID") %>'>File
</a>- Click on this link, I'll be able to download or view the file WHAT I WANT TO DO, BUT HAVE PROBLEM:- I would like to be able to read CONTENT of this file and load it in a string as belowStreamReader SR = new StreamReader()SR = File.Open("File.txt");String contentText = SR.Readline();txtBox.text = contentText;BUT THIS ONLY WORK FOR files in the server.I would like to be able to read FILE CONTENTS from SQL Server.PLEASE HELP. I really appreciate it.
Read Text File From Flat File Connection Manager SSIS
May 13, 2008Hello Experts,
I am createing one task (user control) in SSIS. I have property grid in my GUI and 2 buttons (OK & Cancle).
PropertyGrid has Properties like SourceConnection, OutputConnection etc....right now I am able to populate Connections in list box next to Source and Output Property.
Now my question to you guys is depending on Source Connection it should read that text file associated with connection manager. After validation it should pick header (first line of text file bases on record type) and write it into new file when task is executed. I have following code for your reference. Please let me know I am going in right direction or not..
What should go here ?
->Under Class A
public override DTSExecResult Execute(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log, object transaction)
{
//Some code to read file and write it into new file
return DTSExecResult.Success;
}
public const string Property_Task = "CustomErrorControl";
public const string Property_SourceConnection = "SourceConnection";
public void LoadFromXML(XmlElement node, IDTSInfoEvents infoEvents)
{
if (node.Name != Property_Task)
{
throw new Exception(String.Format("Invalid task element '{0}' in LoadFromXML.", node.Name));
}
else
{
try
{
_sourceConnectionId = node.Attributes.GetNamedItem(Property_SourceConnection).Value;
}
catch (Exception ex)
{
infoEvents.FireError(0, "LoadFromXML", ex.Message, "", 0);
}
}
}
public void SaveToXML(XmlDocument doc, IDTSInfoEvents infoEvents)
{
try
{
// // Create Task Element
XmlElement taskElement = doc.CreateElement("", Property_Task, "");
doc.AppendChild(taskElement);
// // Save source FileConnection
XmlAttribute sourcefileAttribute = doc.CreateAttribute(Property_SourceConnection);
sourcefileAttribute.Value = _sourceConnectionId;
taskElement.Attributes.Append(sourcefileAttribute);
}
catch (Exception ex)
{
infoEvents.FireError(0, "SaveXML", ex.Message, "", 0);
}
}
In UI Class there is OK Click event.
private void btnOK_Click(object sender, EventArgs e)
{
try
{
_taskHost.Properties[CustomErrorControl.Property_SourceConnection].SetValue(_taskHost, propertyGrid1.Text);
btnOK.DialogResult = DialogResult.OK;
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
#endregion
}
Help Me! Read Text File In Sql
Jul 20, 2005HELLO,please help me, how to read txt file and then insert it to sql table.i have file like this name.txe<...01230123192312390213200323423i4u23490342342342343....>I have 4 column and want to fractionise this text '012301231923123902132003'and put in to table.Best regardsRobert
View 3 Replies View RelatedRead Text File
Apr 24, 2007Hi,
I have the text file in my server.
Is it possible to read that text file and insert into the table.
pls advice
Regards
Antony
Read Text File From SqlServer
Apr 23, 2007Hi Frinds,
I have the text file in my server.
Is it possible to read the text file and insert into table via sql server stored procedure.
Need To Read One Text File Into Three Tables
Jul 24, 2001Can the header record be read into one table, the detail into another and the trailer into a third within one data transformation or would this have to be done with an active script task.
I then have to take this data and write it back out to one file with a header and detail lines.
Would vb be better for that part or can it be done easily with active script using the file system object. I could write the one header record and append the detail lines after.
Any help would be appreciated.
Thanks,
DTS newbie.
Read Text File To Dataset
Mar 26, 2007with the help of the following code am able to read the text file
now i need to insert values to the datatable in dataset
can anybody help me on this regard
string path = Server.MapPath("Account.txt");
StreamReader sr = File.OpenText(path);
string contents = sr.ReadToEnd();
Label1.Text = contents;
Label1.Text = contents.Replace(Environment.NewLine,"<br>");
sr.Close();
how shall i spilt them and add each value to two fields of the datatable
for example i have the fields in text file as
Username1:Password1
Username2:Password2
etc
i need to add the username seperatelt to a field called username in table
and password in password field of the datatable
an example with code would be better and helpful
thanks
shruhti
Read Data From Text File And Insert To Sql Table
Mar 28, 2008hi my friends
i want read data from text file,and write to my table in sql,
please help me
M.O.H.I.N
BULK INSERT From A Text (.csv) File - Read Only Specific Columns.
Apr 23, 2008I am using Microsoft SQL 2005, I need to do a BULK INSERT from a .csv I just downloaded from paypal. I can't edit some of the columns that are given in the report. I am trying to load specific columns from the file.
bulk insert Orders
FROM 'C:Users*******DesktopDownloadURL123.csv'
WITH
(
FIELDTERMINATOR = ',',
FIRSTROW = 2,
ROWTERMINATOR = ''
)
So where would I state what column names (from row #1 on the .csv file) would be used into what specific column in the table.
I saw this on one of the sites which seemed to guide me towards the answer, but I failed.. here you go, it might help you:
FORMATFILE [ = 'format_file_path' ]
Specifies the full path of a format file. A format file describes the data file that contains stored responses created using the bcp utility on the same table or view. The format file should be used in cases in which:
The data file contains greater or fewer columns than the table or view.
The columns are in a different order.
The column delimiters vary.
There are other changes in the data format. Format files are usually created by using the bcp utility and modified with a text editor as needed. For more information, see bcp Utility.
Integration Services :: How To Read A Specific String From Text File As Output Variable In SSIS
Nov 5, 2015I am downloading a webpage as a text file in order to read a specific string to assign it as a variable/parameter in order to create an output file name. I would like to know how would I be able to look for a specific string and output as another variable for the rest of the package.
2015 Conforming Loan Limits
------------------------------------------------------------------------
o _Loan Limits for Calendar Year 2015--All Counties _[XLS]
</DataTools/Downloads/Documents/Conforming-Loan-Limits/FullCountyLoanLimitList2015_HERA-BASED_FINAL_FLAT.xlsx>_ ,
_[PDF]
</DataTools/Downloads/Documents/Conforming-Loan-Limits/FullCountyLoanLimitList2015_HERA-BASED_FINAL.pdf>_
β o _List of 46 Counties with Increases in Loan Limits for 2015
[Code] ...
To explain it a more better way, I have a sample webpage text here. I should be searching for "FullCountyLoanLimitList" appended by the current year (like FullCountyLoanLimitList2015) and copy the entire file name in the text file and assign it to another variable so that I can download that specific file using WebClient connection.
Read Text From Message Tab
Feb 28, 2008I am trying to catch and parse the message after passing a transaction to SQL from a clr proc. For example in the below code after passing sp.ExecuteAndSend(qry) how can I receive the message of "(1 row(s) affected)" from the messages tab. I know that I can write to the message tab with sp.Send, but is it possible to read from the output?
Thank you in advance for your assistance
Code Snippet
Imports System.Data.SqlClient
Partial Public Class StoredProcedures
_
Public Shared Sub prepxml _
(ByVal sInputFile As String)
Dim sContents As String
Dim sp As SqlPipe = SqlContext.Pipe()
Dim qry As New SqlCommand()
Dim stmReader As New StreamReader(sInputFile)
Try
'Create a string reader and pass it the input parameter of the
'file path. The sContents variable will receive the content of
'the stream reader
sContents = stmReader.ReadToEnd()
stmReader.Close()
'Replace single quotes to enclose the quotes so errors aren't raised
sContents = sContents.Replace("'", "''''")
'Create a query object and convet the sContents to an xml data
'type
qry.CommandText = " SELECT CONVERT(XML, '" & _
sContents & "')"
'Execute the query and pass the result set back to SQL
sp.ExecuteAndSend(qry)
sp.Send(qry.CommandText.ToString.Substring(1, 3000))
Catch ex As Exception
'Cath any exceptions and use a sqlpipe to send the error
'message back to sql
sp.Send(ex.Message.ToString)
End Try
End Sub
End Class
SQL 2012 :: How To Do Selective Read Of File Stored In File Table
Jul 2, 2015I have a filetable that contains a binary file. I need to do a selective read of the file stored in the file table. I can write a C# CLR function that will open the file, read n bytes the from a starting byte. Or I can write a SQL statement that reads the stream in the filetable into a VARBINARY variable using SUBSTRING beginning at the starting byte (offset from 1) for the same n bytes.
Both give me the same result. However, the SQL statement takes considerably longer to read. I know there is overhead in reading through SQL (interpreted language), but the difference in performance is substantial, and I can only attribute this performance degradation if SQL first tries to "load" the entire stream before it identifies the portion of the stream that it needs to read beginning at the starting byte offset.
I wonder if this is the case or if there is another option to read a stream from a filetable directly through SQL queries that is more efficient.
Help In Using ADO.NET To Read From Text Files(CSV Delimited)
Mar 2, 2007
Hello there
I have written a small code that is used to read records from a txt or a csv file, but when i try to read from a txt or csv file which is in unicode, i get the following error
No value given for one or more required parameters........this error is recieved on the following line
dtadpt.Fill(DataSet.Tables("table2"))
The code for reading from a file is as follows
Dim dtrow As DataRow
Dim cmdtxt, file, path
Dim dtadpt As New System.Data.OleDb.OleDbDataAdapter
Dim Command As System.Data.OleDb.OleDbCommand
Dim Conn As System.Data.OleDb.OleDbConnection
file = 'name of the file'
path = 'path of file'
Conn = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & path & ";" & "Extended Properties=""text;HDR=YES;FMT=Delimited""")
For Each dtrow In DataSet.Tables("table1").Rows
Dim id
id = dtrow.Item("id")
cmdtxt = "Select * from " & file & " where id = " & id
Command = New System.Data.OleDb.OleDbCommand(cmdtxt, Conn)
dtadpt.SelectCommand = Command
Next
DataSet.Tables.Add("table2")
dtadpt.Fill(DataSet.Tables("table2"))
this code works if i choose a csv or txtfile which is in ANSI format. But i want to read from a unicode file because i have some information in that unicode format that i want to store
any idea how this can be possible
thanks and best regards
Saad
How Read File CSV File In Remote Server Using Bulk
Mar 24, 2008Hi All,
I need to read a csv file, which is in remote server using SQl Bulk Insert Command.
Can I read a file Which is in remote server using BULK INSERT.
Thank you.......
Read Or Capture Complete Running Sql Text?
Feb 11, 1999Hello:
We are running some test on our application which involves long-running transaction in an application that was purchased from a vendor. It is a PB
and we don't have access to all of the code. We are not sure why it runs so slow when we start this long_running process. We are not sure if they are using a stored procedure or not.
We ran updaet statistics becuase alot of data was added before we kicked off the process. We want to capture all of the sql code that is running so we can perhaps add indices.
Under EM, we click on the thread and can get a dialog box that shows the running sql code but the dialog box isn't expandable
THerefore I am wondering if there is a storec provedure or if someone has a script, to see what the complete text of sql is for a running proess? Any shareware tools?
Any information that you can provide will be appreciated. Thanks.
David Spaisman
--------------------------------------------------------------------------------
|
Excel: Read Column As Text (IMXE=1 Does Not Seem To Help)
Sep 8, 2006Hi!
We currently have a problem while reading Excel files. The file has one column we want to read as DT_(W)STR which Jet delivers to us only as DT_R8 as there are plenty of numerical entries in that column before one alphanumerical appears.
We first tried with IMEX=1 but as described in http://support.microsoft.com/kb/194124/en-us will only parse the first eigth characters as "TypeGuessRows" is set to 8 and can't be edited.
Is there any way to read a column in Excel in a way typed by the SSIS designer (DT_(w)STR in our case)?
With best regards,
Hauke Schlichting
PS: Designing a "decoy" Excel file with column headers and first columns set is not really considered an option ;-)
How To Read Text Inside HTML Files
Apr 16, 2008Hi,
I just want to know how to search text/record inside the Html files. I had one column in my database named Path I saved the html file names in this column and the physical files in a folder. Full Text search is enabled on this column. Whenever I try to search it returns nothing.
Just need one small example to know how it works, what functions used to search inside the files and how.
Thanks
Navi
What Is The Best Way To Combine Two Text File Into One Text File In SSIS
Mar 20, 2008
Hi,
I am looking for a way to combine two text files into one file. I am thinking of using a batch file (DOS command ) to do it. Any suggestion please?
Read XML File From File System?
Jan 8, 2002Anyone reading XML disk-files into SQL Server?
I have a process that I may want to do this with.
It would be a stored procedure that would read the XML attributes into 2 tables, the number of attributes could be 1-N, so I thought XML would be a good choice. Also, one of the attributes could be up to 4000 characters. I think this may limit our options, can 100-150 4000 character strings be passed in a standard call to a query/proc in SQL?
Currently the client application makes round-trip network calls to save upwards of 100 pairs of data. 1 header row, and many detail rows. All within a transaction.
I think If we move an XML file to the SQL box, then do all the import/save work on the "Server" side it would be much better. Cutting the transaction time down a lot by not doing so many round-trips at network speed...
Thoughts?
Error Msg 6522, Level 16, State 1 Receives When Call The Assembly From Store Procedure To Create A Text File And To Write Text
Jun 21, 2006Hi,
I want to create a text file and write to text it by calling its assembly from Stored Procedure. Full Detail is given below
I write a code in class to create a text file and write text in it.
1) I creat a class in Visual Basic.Net 2005, whose code is given below:
Imports System
Imports System.IO
Imports Microsoft.VisualBasic
Imports System.Diagnostics
Public Class WLog
Public Shared Sub LogToTextFile(ByVal LogName As String, ByVal newMessage As String)
Dim w As StreamWriter = File.AppendText(LogName)
LogIt(newMessage, w)
w.Close()
End Sub
Public Shared Sub LogIt(ByVal logMessage As String, ByVal wr As StreamWriter)
wr.Write(ControlChars.CrLf & "Log Entry:")
wr.WriteLine("(0) {1}", DateTime.Now.ToLongTimeString(), DateTime.Now.ToLongDateString())
wr.WriteLine(" :")
wr.WriteLine(" :{0}", logMessage)
wr.WriteLine("---------------------------")
wr.Flush()
End Sub
Public Shared Sub LotToEventLog(ByVal errorMessage As String)
Dim log As System.Diagnostics.EventLog = New System.Diagnostics.EventLog
log.Source = "My Application"
log.WriteEntry(errorMessage)
End Sub
End Class
2) Make & register its assembly, in SQL Server 2005.
3)Create Stored Procedure as given below:
CREATE PROCEDURE dbo.SP_LogTextFile
(
@LogName nvarchar(255), @NewMessage nvarchar(255)
)
AS EXTERNAL NAME
[asmLog].[WriteLog.WLog].[LogToTextFile]
4) When i execute this stored procedure as
Execute SP_LogTextFile 'C:Test.txt','Message1'
5) Then i got the following error
Msg 6522, Level 16, State 1, Procedure SP_LogTextFile, Line 0
A .NET Framework error occurred during execution of user defined routine or aggregate 'SP_LogTextFile':
System.UnauthorizedAccessException: Access to the path 'C:Test.txt' is denied.
System.UnauthorizedAccessException:
at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy)
at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share, Int32 bufferSize, ileOptions options)
at System.IO.StreamWriter.CreateFile(String path, Boolean append)
at System.IO.StreamWriter..ctor(String path, Boolean append, Encoding encoding, Int32 bufferSize)
at System.IO.StreamWriter..ctor(String path, Boolean append)
at System.IO.File.AppendText(String path)
at WriteLog.WLog.LogToTextFile(String LogName, String newMessage)
Read File
Aug 28, 2003Any one can help me how to read a file(.txt) with in the store procedure?
View 2 Replies View RelatedRead From .ini File
Jan 4, 2006hi,
i have a requirement in which i need to read from a .ini file in the stored procedure of sql server 2K.
is it possible? i tried searching on google but i cannot find anything that can help.
Read XMl File
Feb 14, 2007Can any one tell me how to read the XML file using sql syntax.
I hav also posted my full requirement why I need to read the XML file in Transact_Sql forum list.
Cannot Read Some DBF File
Jun 6, 2007Hi All
I am trying to load DBF files into SQL server within CLR (actually if just running the select statement outside, say within the SQLQuery window, i got the same result), but with the following error:
Msg 7314, Level 16, State 1, Line 1
The OLE DB provider "VFPOLEDB" for linked server "MYDBF" does not contain the table "T8866064". The table either does not exist or the current user does not have permissions on that table.
I created the linked server in this way
EXEC sp_addlinkedserver
@server = 'MYDBF',
@provider = 'VFPOLEDB',
@srvproduct = 'My Data',
@datasrc = 'c:data'
i did not create the login since my SQL instance is running under a superaccount with all privilege.
What frustrates me is that i can read most of the dbf files, but just a few of them is not readable.
Can anyone give me some hints on it?
by the way, i am using vfp9.0
thanks
michael
How To Read Binary File
Jun 6, 2007Need help reading a binary file see below for details...
I have uploaded a csv file into a sql table.
Now i want to extract the data and insert the data in the csv file into another sql table.
What commands can i use in sql to extract/ read the data ?
Creating A CSV File But Can't Read It.
Nov 4, 2003Hi, I wrote a report builder that creates a CSV file but I can't redirect to the file to view it. ERROR Cannot find the Server!. This works fine on the dev machine but deploy it and it does not.
Dim Conn As New SqlConnection(ConfigurationSettings.AppSettings("ConStr"))
Dim Cm As SqlCommand
Dim dr As SqlDataReader
Dim FileName As String = Guid.NewGuid.ToString & ".csv"
Dim FilePath As String = Server.MapPath("") & "CSVFiles" & FileName
Dim fs As FileStream = New FileStream(FilePath, FileMode.Create, FileAccess.Write)
Dim sw As StreamWriter = New StreamWriter(fs)
Dim Line As String
Dim lItem As System.Xml.XmlElement
Dim i As Int16
If ValidatePage() Then
Cm = New SqlCommand(BuildQuery, Conn)
Conn.Open()
dr = Cm.ExecuteReader()
Dim lXmlDoc As New System.Xml.XmlDocument()
lXmlDoc.LoadXml(txtHXml.InnerText)
For Each lItem In lXmlDoc.DocumentElement.SelectSingleNode("SELECT").ChildNodes
Select Case lItem.InnerText
Case "chkPRId"
Line &= "PR Id,"
Case "chkDateIssued"
Line &= "Date Issued,"
Case "chkOriginator"
Line &= "Originator,"
Case "chkBuyer"
Line &= "Buyer,"
Case "chkVendor"
Line &= "Vendor,"
Case "chkCostCode"
Line &= "Cost Code,"
Case "chkOracleRef"
Line &= "Oracle Reference,"
Case "chkTotal"
Line &= "Total,"
End Select
Next
Line = Line.Substring(0, Line.Length - 1)
sw.WriteLine(Line)
Line = ""
While dr.Read
For i = 0 To dr.FieldCount - 1
Line = Line & dr(i) & ","
Next
sw.WriteLine(Line)
Line = ""
End While
dr.Close()
Conn.Close()
sw.Close()
fs.Close()
Response.Redirect(FilePath)
End If
ResetPage()
PopulateListBoxFromXML()
Read DataBase File
Jan 17, 2002Hi,
I received a SQL Server 6.5 database (.dat and .Log files). I'm currently running 7.0. Is there any way to read/import this data?
I tried to do this:
I have installed SQL Server 6.5. And After install, I have created a database with the same names (.dat an .log) and make it bigger than the first .dat and .log files.
Then, I have stoped the SQL server service, have deleted the .DAT and .LOG files that i have created, and have copied the first .DAT and .LOG in their place. I have Started the SQL server service and the database was beeing suspected. I stil unable to read data.
Can you help me?
READ A FILE WITH EXTENSION Dat
Aug 16, 1999Hi,
I need to read a binary file with extention *.dat like this:
tat.dat and import it in ms sql server 7.0.
Thanks for any help
Dr Bangaly Dianι
How Can I Read One External File (*.txt,*.csv) Through
Dec 14, 2001How can I read one external file (*.txt,*.csv) through
stored procedure in MS Sql Server 2000 ?
On the database Interbase its very simple :
[CREATE TABLE table [EXTERNAL [FILE] <filespec>]
( <col_def> [, <col_def> | <tconstraint>
]);
EXTERNAL [FILE]<filespec>
Declares that data for the table under creation resides
in a table or file outside the database;
<filespec> is the complete file specification of the
external file or table]
Please advice
Thanks
Patricio
Read Data From File Imp Into MS-SQL
Mar 24, 2006Hi,
Ik like to read data from a file (well formatted) into as MS-SQL database.
What is the best way to do this.
Jim
How To Read Active Log File
Apr 30, 2006I am programming to read read sqlserver log file without shutdowning sqlserver. However it is said that the file is used by another process which I believe is sqlserver.
Is there any way to open log file without shutdowning server? I know that Log Explorer can read online log file. But I do not know the technology they are using.
How Can I Read An Xml File From SQLServer
Mar 31, 2004I need to know how to read an xml file using SQL. (in SQLServer)
Then i will manipulate the data and update my tables.
May be i can put the xml file in the IIS root.
Then what is required for the rest..
Thanks in advance
Benny