How Can I Read One External File (*.txt,*.csv) Through
Dec 14, 2001
How 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]
OBJECTIVE: 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.
I have to write a query that queries a database table and a text file wich is delimited using fixed width values. I have seen some examples using access or another databse but not much about a text file. Is this the best way? Idon't want to set up a linked server. I want everything to be within the query.
My understanding is that I should be able to do it since a text file can be a valed OLE DB datasource, but I am having trouble finding example syntax. Can all my connection info be passed with the OPENROWSET function? Can I do it by just writing a query? Or do I have to set up and configure other things?
Any guidence, tips, advice, examples, or links of syntax appreciated.
I'm having an issue with a CLR Stored procedure. Everything works great in a 32 bit environment, I have a CLR SP that updates an xml file stored on a local drive. When I execute the Stored proc it does go and update what I want it to in a 32 bit system. When I run the CLR SP on a 64 bit cluster, I seem to have give the "everyone" group write permissions to my G: drive (which is where the file is located that I'm updating). What security context is this SP running under? I thought it would be either under the SQL Service account (which is a domain user in the local administrators group) or what I'm logged in as when I run it from Management Studio (which is a domain admin, also in the local administrators group). If I have given the local administrators group "Full Control" access to the G: drive, why isn't this enough? Why do I have to give the Everyone group write access?
The security context seems odd to me, it seems like it's not running as either one of those 2 users I mentioned, because if it was, then it should be able to update the xml file.
Any help appreciated. Here's the error I'm getting:
Msg 6522, Level 16, State 1, Procedure usp_XMLWriter, Line 0
A .NET Framework error occurred during execution of user defined routine or aggregate 'usp_XMLWriter':
System.UnauthorizedAccessException: Access to the path 'g:ssisPackagesBuildCalendarandy.dtsConfig' is denied.
System.UnauthorizedAccessException:
at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
Hello 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
I 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.
For security reason, the sensitive information must be encrypted, and they should be configurable dynamically (by an ASP.NET application).
so I tried several ways to achieve this,
1. use SSIS Package Configurations to generate an XML config file It's a convenient way to generate config file. but the passwords are not encrypted (or I don't know how).
2. use Variables and Property Expressions I can set variables by reading from an external custom xml config file which was content encrypted (read and decrypt the custom config file in a script task). but in a FTP Connection Manager entity, the Password property can not be set via Property Expressions.
Is any way to store password encrypted to an external file?
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...
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.
Need 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 ?
Hi, 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()
Hi, 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?
I 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.
patelnimisha writes "i am try to read three xml files and transfer its data into sql databse. so i have no any idea about this qurey. give me coding for this query"
Hello,I have:- server MS SQL MSDE (2000)- database 'COLLBASE'- table 'MAIN'- row: column 'NAME' value 'version' and column 'VALUE' value '003'I make backup files by SQL query.I have more backups for different versions of this database (differentvalue in field 'VALUE' in table 'MAIN').I would like to read this value from backup file without server assist,to get backup as ordinary file.How to do it?How to move in this file?What is physical structure of backup file?I can't to read this file from begin by compare strings, because thesefiles are very large, few 100MB.regardsZenek
HELLO,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
I have some problem, I User Visual Web Developer 2005 Express to Create DateBase Files, when I copy the code and Database file to Server then user remote client to insert records and update, but it alert the error :
Hello, I am trying to read a file "xlsx" from ASP, the problem is when I try to delete the file, ASP inform not have permits to delete it, it is as if the connection to file not closing properly, the code is:
Code Snippet
if extension = "xlsx" or extension = "xls" then 'si el archivo es excel
if extension = "xls" then sFileConnectionString = "Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq="&sFilePath&";DefaultDir="&sDataDir&";" else sFileConnectionString = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};READONLY=TRUE;DBQ="&sFilePath&";DefaultDir="&sDataDir&";" end if sFileSQL = "SELECT * FROM [" & sSheetName & "$]"
set oFileCN = server.createobject("ADODB.Connection") 'creo objeto adodb oFileCN.Open sFileConnectionString 'abro el exel set oFileRS = oFileCN.Execute(sFileSQL) 'selecciono los registros if not oFileRS.EOF then 'si es distinto de EOF obtengo un array con los valores de las columnas aSourceData = oFileRS.getRows() end if oFileRS.Close set oFileRS = nothing oFileCN.Close set oFileCN = nothing
'asigno los mails a la variabls "readDatafile" readDatafile = aSourceData
and the error
Microsoft VBScript runtime error '800a0046'
Permission denied
/gestion/edit_import_db_mail.asp, line 27
Any ideas?, Greetings and forgiveness for my English
