ASP-to-SPROC-to tab delimited text? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

ASP-to-SPROC-to tab delimited text?

Author

Topic

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2003-02-11 : 15:45:39

Hi,

I'm trying to streamline input files that are fed into a DOS-based analysis program. I've cut quite a bit of complexity out of the process by using SQL Server scripts to hit an Excel linked server, with query results kicked out to Excel 8.0 via DTS.

What I'd like to do is kick this output directly to a tab-delimited text file, which is what the DOS program needs.

Ultimately, I'll offer a web-based form which will allow users to do some limited structuring of the query. This will run over top of an SPROC, with query results sent to the tab delimited text file. Right now I can successfully do this by outputting to a CSV or Excel directly, but not tab delimited.

Thanks.

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-02-11 : 15:53:38

It's possible that you can use a reporting tool to output to the format you wish. I know both Crystal Reports and Active reports allow you to export a report in several different formats. I prefer active reports.

http://www.datadynamics.com/activereports/default.htm

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2003-02-11 : 16:09:03

Unless I'm not understanding the question, what about using BCP? Seems a little like overkill but is easy to code and use. Based on your needs you can simply bcp the data out using the -c (for character) and rely on the default column delimiter, which is tab.

darinh
Yak Posting Veteran

58 Posts

Posted - 2003-02-11 : 19:15:09

How about doing some processing during your DTS and outputing everything to one field per row?

e.g.

SELECT FieldA + CHAR(9) + FieldB + CHAR(9) + ..... FROM

pretty ugly, but since it's going to a text file anyway it shouldn't matter. You might have to cast some of your fields to change their type before you concatenate them.

Edited by - darinh on 02/11/2003 19:15:32

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-02-11 : 19:54:31

And if you want ANOTHER way of doing it:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=22970

You're already there. Just change the GetString call and remove all of the parameters. It will default to tab as a column separator and CR-LF as row separator.

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2003-02-14 : 14:10:40

Rob, thanks! Works flawlessly. I'd like to add another feature and wonder if you can help. For the output, I'd like to insert a numerical value, which I'll hardcode into the ASP script. I want to add this value as the first entry in the first row -- just the value in A1 and the rest of the first row blank. Any ideas on how to do that?

I though about handling this in the SPROC, but that would mean using a temporary table to allow for an UPDATE before sending results to ASP page -- I want to avoid the temp table here since I'm using Excel linked server and want to pull data directly from linked server to ASP page, no temp table in between. Anyway, here's the code as it has morped from the learnasp.com piece, to the additions you made, to the changes I've made -- I commented out all the linestarts, delimiters, etc:

=======================================================

<%

'Collect the needed form fields

strTableChoice = cStr(Request.Form("TableChoice"))

whichname="/temp/transmit_input.txt"
myDSN="dbserver"
mySQL="sp_some_input '" & strTableChoice &"'"
showblank=""
shownull="<null>"
linestart=chr(34)
lineend=chr(34) & vbCRLF 'This is changed from the original
'xdelimiter=chr(34) & "," & chr(34)
'xdelimitersub=""

whichFN=server.mappath(whichname)

set conntemp=server.createobject("adodb.connection")
conntemp.open myDSN
set rstemp=conntemp.execute(mySQL)

' this code detects if data is empty
If rstemp.eof then
response.write "No data to convert for SQL statement<br>"
response.write mySQL & "<br>"
connection.close
set connection=nothing
response.end
end if

'xdata=linestart 'starts the data value with a delimiter
'xfor each whatever in rstemp.fields 'for each field in the recordset
'xdata=data & whatever.name & delimiter 'add the field name and delimit it
'xnext

'trim the last delimiter and add the lineend
'xdata=Left(data, Len(data)-Len(delimiter)) & lineend

'convert the recordset to a string using the delimiter, lineend, and shownull replacements

data=data & rstemp.GetString()

=============================================

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-02-14 : 20:27:03

Couldn't you just do:

data=data & numericValue & lineend & rstemp.GetString()

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2003-02-18 : 09:56:21

Thanks Rob, it works. I just removed the chr(34) leader for lineend to get rid of the " and it's just what I need.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources