Writing to a csv text file

[Matt Massie]

Say I ran a query that pulled 100 records with 10 fields into a RecordSet
I'll call "rsQryResult"
How would I:
1. write this data to a .csv text file and
2. Send the file to the user, with the "Open, Save, Cancel" download box
appearing for them?
Also, Can I set a variable value according to whether the file downloaded to
the client successfully?
--
Matt Massie

[Matt Massie]

I just figured out that my question was misworded. Can I perform the query,
then dump the recordset holding to queried info into a text file, or should
I write into a textfile as the query is in process?

I could be writing very large (+20mb files) sometimes. Once I use have made
the textfile, How do I initialize at file upload to the user?

MM

[Bob Barrows]

Matt Massie wrote:

> I just figured out that my question was misworded. Can I perform the
> query, then dump the recordset holding to queried info into a text
> file, or should I write into a textfile as the query is in process?
>
> I could be writing very large (+20mb files) sometimes. Once I use
> have made the textfile, How do I initialize at file upload to the
> user?
>
> MM

20 mb?

If you were dealing with smaller amounts of data, I would simply point you
at these pages (go ahead and read them anyways - very interesting and
potentially useful information):
[url]http://www.aspfaq.com/filesystem.asp[/url]
[url]http://www.aspfaq.com/show.asp?id=2467[/url]
[url]http://www.aspfaq.com/show.asp?id=2161[/url]
[url]http://www.aspfaq.com/show.asp?id=2205[/url]


However ... 20 mb? And you want to do it with ASP? This is not recommended.
You should not use slow vbscript code to do the work that a database engine
can do hundreds of times faster. The problem is: the thread in which the
data export/file creation is taking place will tie up the web server, not
just for the user who requested the data transfer, but for other users as
well. a data transfer of this size should take place external to the the asp
process.

Does it have to be an on-demand process? Could you consider scheduling the
transfer to take place at some idle time, and allow the user to "pick up"
the file (preferably using ftp) after the scheduled task is complete? The
export from Access can be done very efficiently using the tools available
within Access. At it's simplest, you could schedule a task which would start
Access using the appropriate command-line switches (or an auto-start macro)
to kick off the data export. If you were using SQL Server, or MSDE, it would
be a snap to use DTS to do this.

If it has to be on-demand for some reason, do you have the expertise
available to create a component (perhaps in VB) that will perform the task
asynchronously? The idea would be that the client would click a button that
would send the request to a page on the server which would kick off the
process. After that, the client would periodically check to see if the
transfer is complete, prompting the user to download the file

HTH,
Bob Barrows

PS. Please make it a habit to always specify the database type and version
you are using. I remember you from yesterday, so I strongly suspect you are
using Access, but I can't be 100% sure (you could be working on two or more
separate projects, each dealing with a different database)

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Matt Massie]

From your kind replies, and digging through examples of source on the
development pages, this is what I've come up with. My data from the query is
being held by "rsSelectQuery" Again, I'm going through the logic on my XP
Home laptop, and testing the syntax each evening. I'm going to set up an FTP
to my server, so I can test immediately. I feel like the 7th line is
incorrect, but haven't worked it out yet.

Dim FileSystemObject 'File System Object Variable
Dim TextFile 'Text File Variable

SET FileSystemObject = Server.CreateObject("Scripting.FileSystemObject")
SET TextFile = FileSystemObject.CreateTextFile("NewRecords.txt", True,
False) 'Create A text file 'called NewRecords.txt, Overwritable, ASCII
TextFile.Write(rsSelectQuery)
TextFile.Close
set TextFile = nothing
set FileSystemObject = nothing

[Bob Barrows]

So you're determined, huh? :-) One more time: it is not a good idea to do it
this way.

OK, I feel better now. You can ignore me at your own risk ;-)

More below:
Matt Massie wrote:

> From your kind replies, and digging through examples of source on the
> development pages, this is what I've come up with. My data from the
> query is being held by "rsSelectQuery"

That's a hell of a long name to type isn't it? Isn't a recordset obviously
the result of a select query? Why restate the obvious? Just call the
recordset "rs". Of course, if you just like to type ... ;-)

> Again, I'm going through the
> logic on my XP Home laptop, and testing the syntax each evening. I'm
> going to set up an FTP to my server, so I can test immediately. I
> feel like the 7th line is incorrect, but haven't worked it out yet.
>
> Dim FileSystemObject 'File System Object Variable
> Dim TextFile 'Text File Variable
>
> SET FileSystemObject = Server.CreateObject("Scripting.FileSystemObject")

It's a bad idea to use the name of a built-in object as your variable name.
It can cause the compiler to become confused. Why not just use "fso" for
this variable name?

> SET TextFile = FileSystemObject.CreateTextFile("NewRecords.txt", _

True, False)

> 'Create A text file 'called NewRecords.txt, Overwritable, ASCII
> TextFile.Write(rsSelectQuery)

If this is line 7, then you're right: it's incorrect. The Write method
expects to be fed a string, not a recordset. If you look at Antonin's link
([url]http://www.pstruh.cz/tips/detpg_RSConvert.htm[/url]) from the .general newsgroup
again, you will see that they used GetString in the example to convert the
recordset's contents to a string. Like this:
TextFile.Write(rsSelectQuery.GetString(,",",vbcrlf )

> TextFile.Close
> set TextFile = nothing
> set FileSystemObject = nothing

However, if you look at Antonin's link, you should see that there is no need
to create this text file on the server. You can stream it directly to the
client, who will be given the choice to as to whether or not to save it to
his hard drive. Look at this:

Dim RS, SQL, Conn
Response.ContentType = "text/plain"
SQL = "Some query ...."
Set RS = Conn.Execute (SQL)

'this part can be skipped if you don't need to
'include the field names
'**************************************
Dim F, Head
For Each F In RS.Fields
Head = Head & ", " & F.Name
Next
Head = Mid(Head,3) & vbCrLf
Response.Write Head
'**************************************
Response.Write RS.GetString(,,", ",vbCrLf,"")


And no, you cannot programmatically cause the file to be saved in a
predetermined place on the user's machine without involving the user, unless
you use an HTML Application (.hta). If you need to know more about hta's,
ask on a client-side code newsgroup, such as m.p.scripting.jscript or
m.p.scripting.jscript, or a group with "dhtml" in its name.

HTH,
Bob Barrows

PS. If it turns out that this performs acceptably with 20 mb of data, I
would like to hear about it, please.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Matt Massie]

I finally got it all working, but I have a few issues I would like to clear
up.
1. the Server.MapPath command. Right now, my ASP application accesses a
database which resides in the same directory as the ASP itself. To set the
connection, I use
strDB = "Access.mdb"
and a few lines later I use
....DBQ;" & Server.MapPath(strDB)
This works great, but I can't seem to move the .mdb, and properly direct to
it.
If my ASP routine is in
c:\inetpub\wwwroot\somedir\somefile.asp, and I want to move my database from
that directory to, say
c:\inetpub\databases\Access.mdb, How do I use Server.Mappath to do so? So
far, I get an error similar to "The Path parameter for the MapPath method
must be a virtual path. A physical path was used"
I'm not using c:\ in the path!

2. The only way my ASP works is if I give the IUSR_ acct Full Permissions on
c:\WINNT, C:\inetpub and c:\winnt\temp. I obviously can't leave it like
this. In the short time I enabled the IUSR acct to test, some brat tried to
use my server as a dump site. I disabled all the anon incoming for now. Do I
have to go through one by one and remove permissions from files and folders
until I get it to work with the fewest possible permissions. It seems odd.
If I so much as change a .dll in system32 to from full to r,w,ex it
interupts to ASP.

BTW, Mr. Barrows, I appreciate you advice about truncating my variable names
a bit. The reason they were that way was because it was easier to keep track
of the flow of things while learning ASP.

MM

[Bob Barrows]

Matt Massie wrote:

> I finally got it all working, but I have a few issues I would like to
> clear up.
> 1. the Server.MapPath command. Right now, my ASP application accesses
> a database which resides in the same directory as the ASP itself. To
> set the connection, I use
> strDB = "Access.mdb"
> and a few lines later I use
> ...DBQ;" & Server.MapPath(strDB)
> This works great, but I can't seem to move the .mdb, and properly
> direct to it.
> If my ASP routine is in
> c:\inetpub\wwwroot\somedir\somefile.asp, and I want to move my
> database from that directory to, say
> c:\inetpub\databases\Access.mdb, How do I use Server.Mappath to do
> so? So far, I get an error similar to "The Path parameter for the
> MapPath method must be a virtual path. A physical path was used"
> I'm not using c:\ in the path!

You can no longer use Server.MapPath when pointing to a folder outside of
wwwroot. You need to supply the physical path to the database in the
connection string:

" ... data source=c:\inetpub\databases\Access.mdb"

>
> 2. The only way my ASP works is if I give the IUSR_ acct Full
> Permissions on c:\WINNT, C:\inetpub and c:\winnt\temp. I obviously
> can't leave it like this. In the short time I enabled the IUSR acct
> to test, some brat tried to use my server as a dump site. I disabled
> all the anon incoming for now. Do I have to go through one by one and
> remove permissions from files and folders until I get it to work with
> the fewest possible permissions. It seems odd. If I so much as change
> a .dll in system32 to from full to r,w,ex it interupts to ASP.

You're out of my field of expertise now. I never had to explicitly grant
IUSR permissions for anything except the specific files and folders I needed
it to use. If no one else replies, you may want to raise this question on
..inetserver.iis or .inetserver.iis.security.

>
> BTW, Mr. Barrows,

I'm only "Mr. Barrows" to my neighbors' kids. If you are old enough to be
writing asp code, then you are old enough to call me Bob. :-)

> I appreciate you advice about truncating my
> variable names a bit. The reason they were that way was because it
> was easier to keep track of the flow of things while learning ASP.
>

OK, understood.

Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Don Grover]

IUSER needs to write a temp file to the / a temp folder i solved this by
granting everyone write/ modify ability to temp folder.

"Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
news:%23%23dQZUOnDHA.1072@TK2MSFTNGP09.phx.gbl...

> Matt Massie wrote:

> > I finally got it all working, but I have a few issues I would like to
> > clear up.
> > 1. the Server.MapPath command. Right now, my ASP application accesses
> > a database which resides in the same directory as the ASP itself. To
> > set the connection, I use
> > strDB = "Access.mdb"
> > and a few lines later I use
> > ...DBQ;" & Server.MapPath(strDB)
> > This works great, but I can't seem to move the .mdb, and properly
> > direct to it.
> > If my ASP routine is in
> > c:\inetpub\wwwroot\somedir\somefile.asp, and I want to move my
> > database from that directory to, say
> > c:\inetpub\databases\Access.mdb, How do I use Server.Mappath to do
> > so? So far, I get an error similar to "The Path parameter for the
> > MapPath method must be a virtual path. A physical path was used"
> > I'm not using c:\ in the path!

>
> You can no longer use Server.MapPath when pointing to a folder outside of
> wwwroot. You need to supply the physical path to the database in the
> connection string:
>
> " ... data source=c:\inetpub\databases\Access.mdb"
>
>

> >
> > 2. The only way my ASP works is if I give the IUSR_ acct Full
> > Permissions on c:\WINNT, C:\inetpub and c:\winnt\temp. I obviously
> > can't leave it like this. In the short time I enabled the IUSR acct
> > to test, some brat tried to use my server as a dump site. I disabled
> > all the anon incoming for now. Do I have to go through one by one and
> > remove permissions from files and folders until I get it to work with
> > the fewest possible permissions. It seems odd. If I so much as change
> > a .dll in system32 to from full to r,w,ex it interupts to ASP.

>
> You're out of my field of expertise now. I never had to explicitly grant
> IUSR permissions for anything except the specific files and folders I

needed

> it to use. If no one else replies, you may want to raise this question on
> .inetserver.iis or .inetserver.iis.security.
>

> >
> > BTW, Mr. Barrows,

>
> I'm only "Mr. Barrows" to my neighbors' kids. If you are old enough to be
> writing asp code, then you are old enough to call me Bob. :-)
>

> > I appreciate you advice about truncating my
> > variable names a bit. The reason they were that way was because it
> > was easier to keep track of the flow of things while learning ASP.
> >

> OK, understood.
>
> Bob Barrows
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>