|
SQL Server ASP Discussions about ASP programming with Microsoft's SQL Server. For more ASP forums, see the ASP forum category. |
Welcome to the p2p.wrox.com Forums.
You are currently viewing the SQL Server ASP section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
|
|
|
September 29th, 2003, 05:47 PM
|
Registered User
|
|
Join Date: Sep 2003
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Microsoft Text Driver Use
Anyone used the Microsoft Text Driver in code to connect to a text file for input? You should be able to create a DSN-less connection and then use that as an input file with SQL etc. It looks like it can be used under Provider=Microsoft.Jet.OLEDB.4.0; with extended properties as text and by Driver=(Microsoft Text Driver (*.txt *.csv);
|
September 30th, 2003, 02:46 AM
|
Friend of Wrox
|
|
Join Date: Jun 2003
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
|
|
I've not used it from ASP, only from VB, but as long as the anon user has permissions to the file it shouldn't be a problem. Are you looking for the syntax for a connection string? Try this:
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\Temp\;" & _
"Extended Properties=""text;HDR=No;FMT=Delimited"""
Note that the directory goes in the DataSource part of the conn string (I've never found a reference to what Extended Properties can be used). The filename goes in the actual SQL, like this:
oRs.Open "SELECT * FROM fileName", oConn, _
adOpenForwardOnly, adLockReadOnly, adCmdText
Is that what you were looking for?
Phil
|
The Following User Says Thank You to pgtips For This Useful Post:
|
|
September 30th, 2003, 04:02 PM
|
Registered User
|
|
Join Date: Sep 2003
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
That's mostly what I have found. Any yes I have not found any docs on the methods at all even in MSDN. I see this form as you state with the Extended properties and then I see a form as below:
"Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
"Dbq=c:\somepath\;" & _
"Extensions=asc,csv,tab,txt"
Both seem to have methods and properties that don't seem to be documented anywhere that I can find. They also discuss a Schema.ini file that defines the text file as a table type that can then be used by the SQL SELECT statement.
How did you define the text file to the program?
|
October 1st, 2003, 02:54 AM
|
Friend of Wrox
|
|
Join Date: Jun 2003
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
|
|
That other form is just a different driver, the ODBC text driver.
What do you mean by "define the text file to the program"? Are you talking about accessing the "fields" in the file? If so, I just use ordinal position, e.g. oRs.Fields(0).Value. You can use a schema.ini to define field names if you like, but you don't have to.
rgds
Phil
|
October 1st, 2003, 05:00 PM
|
Registered User
|
|
Join Date: Sep 2003
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
I think it finally clicked for concept on the textfile driver. The link setup is to the directory not the file. The files in that directory are the tables. Conceptually it is the same as the SQL where you link to the SQL database which is like a directory and then call out the tables which are files within the directory.
|
October 2nd, 2003, 02:42 AM
|
Friend of Wrox
|
|
Join Date: Jun 2003
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
|
|
Yep, you've got it ronhawker, that's exactly the concept. Sorry I didn't make this clearer originally.
rgds
Phil
|
October 3rd, 2003, 12:42 PM
|
Registered User
|
|
Join Date: Sep 2003
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
I can see the text file input fine now.
I don't have any problem with the connection as I can see both the SQL in a recordset and the text file values with Dreamweaver recordsets. I don't have a problem if the join is two tables in a single connection. The problem seems to be how to connect and define two different connections. It seems to be the way I am setting up the connections either through a command or conneciton object or my approach.
<%@LANGUAGE="VBSCRIPT"%>
<%
' FileName="Connection_odbc_conn_dsn.htm"
' Type="ADO"
' DesigntimeType="ADO"
' HTTP="false"
' Catalog=""
' Schema=""
'Dim MM_arrowhead_STRING
'MM_arrowhead_STRING = "dsn=arrowhead;uid=xxx;pwd=xxx;"
%>
<%
' FileName="Connection_ado_conn_string.htm"
' Type="ADO"
' DesigntimeType="ADO"
' HTTP="true"
' Catalog=""
' Schema=""
'Dim MM_lsprint_STRING
'MM_lsprint_STRING = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\url/public;Extended Properties=Text;"
%>
<HTML>
<HEAD>
<TITLE>Parts OBS Seach by Batch</TITLE>
</HEAD>
<BODY>
<%
Dim objCommandA, objRSA
Set objCommandA = Server.CreateObject("ADODB.Command")
objCommandA.ActiveConnection = MM_arrowhead_STRING
Dim objCommandL, objRSL
Set objCommandL = Server.CreateObject("ADODB.Command")
objCommandL.ActiveConnection = MM_lsprint_STRING
objCommandA.CommandText = "SELECT hawker.Summarytest.PartNumber,LSPRINT#TXT.PARTNUMB ER " & _
"FROM LSPRINT#TXT INNER JOIN hawker.SummaryTest " & _
"ON hawker.Summarytest.PartNumber = LSPRINT#TXT.PARTNUMBER"
' objCommandA.CommandType = adCmdText
Set objRSA = objCommandA.Execute
Set objCommandA = Nothing
While Not objRSA.EOF
Response.Write objRSA("PARTNUMBER") & "<BR>"
objRSA.MoveNext
Wend
objRSA.Close
Set objRSA = Nothing
objRSL.Close
Set objRSL = Nothing
%>
</BODY>
</HTML>
|
October 6th, 2003, 03:50 AM
|
Friend of Wrox
|
|
Join Date: Jun 2003
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
|
|
SQL runs against a single connection. I don't know of any way to bring a 2nd connection into play, and I doubt there is one (though I'm wary of saying you definitely cannot do it )
The only way I can think to get both the db and the text file within a single connection is to set up the text file directory as a linked server within SQL Server. Alternatively you could use the OPENDATASOURCE command, which is like a temporary linked server set-up. It depends how frequently you want to do this, and how much access you have to your SQL Server config.
Here's an example of the OPENDATASOURCE syntax:
Code:
SELECT hst.PartNumber, lt.F1
FROM hawker.Summarytest hst
INNER JOIN
OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=c:\Temp;Extended Properties="Text;HDR=No;FMT=Delimited"')...LSPRINT#TXT lt
ON hst.PartNumber = lt.F1
Note that the field names within the text file are F1, F2 etc.
Let me know if you want some help on linked server set-up.
rgds
Phil
|
October 6th, 2003, 04:31 AM
|
Friend of Wrox
|
|
Join Date: Jun 2003
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
|
|
Thought I may as well post how to do the linked server set-up...
From Enterprise Manager:
Security > Linked Servers > New Linked Server
General tab inputs:
Linked Server : type anything, e.g. TEXTTEST
Server type - click 'other data source'
Provider : choose Microsoft Jet 4.0 OLEDB Provider
Product Name : leave blank
Data Source : type in the directory, e.g. C:\Temp
Provider String : type in Text;HDR=No;FMT=Delimited
leave other tabs with default values
then SQL becomes:
Code:
SELECT hst.PartNumber, lt.F1
FROM hawker.Summarytest hst
INNER JOIN
TEXTTEST...LSPRINT#TXT lt
ON hst.PartNumber = lt.F1
|
December 19th, 2004, 06:19 PM
|
Registered User
|
|
Join Date: Dec 2004
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
When I use this connection method, I am only receiving numerical data and none of the string text from the CSV file.
Any ideas on why this would be happening?
I am using the code from:
http://www.danielklann.com/excel/imp...files_into.htm
Thank you for your help,
Ziv
|
|
|