CSV File - confused

[leeolive]

Hi

I am still new to ASP. I have created a single form. I retrieve the data from the form and place it in variables and then submit the data to an access database.

Another company then requires certain fields from the database *once a week/monthly(to be established in CSV file format.

How will my asp code below know when and how to write to the CSV file? Surely I should add some code to my asp code so that when it submits the data to the database, it submits the values to the CSV file? Or, does the writing to the CSV file not happen when the data is submitted to the database? Do I do this manually?

If the data needs to be written to a CSV file once a week, then how does this happen and who does it and whereabouts does it happen? Where should a command be run and and what command should be run to 'write it'.

Here's my code which submits to the database. I retrieve a lot more data, but have cut it down to simplify. Also, I may want to only write some of the data retrieved from the form - not all.

Set cndb = server.CreateObject("adodb.connection&quot
Set cmdaddtodatabase = server.CreateObject("adodb.command&quot

cndb.CommandTimeout = 15
cndb.CursorLocation = adUseServer
'cndb.ConnectionString = "DSN=cXX" 'this is for local
cndb.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=d:\webs/xxxxxxx/db/1CM_S2P.mdb"
on error resume next
cndb.open

if err <> 0 then
response.write &quot;Error connecting to the database&quot;
response.end
end if

With cmdaddtodatabase
.CommandTimeout = 15
set .ActiveConnection = cndb
.CommandType = adCmdText
.CommandText = &quot;INSERT INTO S2P_Applicant_Details (Title, FirstName) VALUES ('&quot;&strTitle&&quot;','&quot;&strFirstName&&quot;')&quot;
.Parameters.Append(.CreateParameter (&quot;pTitle&quot;, adVarChar,adParamInput,50, strTitle))
.Parameters.Append(.CreateParameter (&quot;pFirstName&quot;, adVarChar,adParamInput,50, strFirstName))

on error resume next
.Execute

if err.number <> 0 then
Response.write err.description
Response.End
end if
End with

%>

Really appreciate this.
Thanks
Lee

 

[mwolf00]

You will need to write a new page that queries the DB and writes the new file using the file system object. To run the page on a schedule read this:

http://www.asp101.com/articles/john/schedule/default.asp

If you need help writing the page, let me know...

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook (No, I'm not Rick)

 

[leeolive]

Thanks mWolf

I do need help!

I have now found some code which looks like a good starting point(through your link you sent).

My (many) questions are:
1. How do I set up the actual .csv file to send data to? (I don't know what a csv file looks like)
2. where in my code below do I put the name/path of this file?
3. If the CSV file is written, how do I actually get this csv file to the company that requires it?
4. obviously I need to change the connection string and command to suit my needs. what else do I need to change?
5. If this other company needs this data then should I create the csv file and send it to them or could I set it up so that they can do it?(I still want to learn *how* to do it though)
6. Surely I should have some means of 'catching' an error if there is one?

Here's the code.

<%@ Language=VBScript %>
<%option explicit%>

<%
Dim rsResult, objConn, sQuery, FSOFile, sFileName
Dim sSaveToPath,DumpFile

Set objConn = Server.CreateObject(&quot;ADODB.Connection&quot
objConn.Open &quot;PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=your_db_location_including_path&quot;

Set rsResult = Server.CreateObject(&quot;ADODB.RecordSet&quot
sQuery = &quot;SELECT Name, Sex FROM People WHERE Age > 10&quot;
rsResult.Open sQuery, objConn
CloseDB

Set FSOFile = CreateObject(&quot;Scripting.FileSystemObject&quot
sFileName = &quot;Dump_From_DB_(&quot;+Replace(Time(),&quot;:&quot;,&quot;&quot+&quot.csv&quot;
sSaveToPath = Server.MapPath(sFileName)
Set DumpFile = FSOFile.CreateTextFile(sSaveToPath,True)

Do while not rsResult.EOF
DumpFile.WriteLine rsResult(&quot;Name&quot & &quot;,&quot; & rsResult(&quot;Sex&quot & vbCRLF
rsResult.MoveNext
Loop

DumpFile.Close

Set FSOFile = Nothing
Set DumpFile = Nothing
%>

 

[mwolf00]

Can you give me a bit more info on what's in the CSV file? I don't like them when handling most data since an erroneous comma or quotation can screw them up. I have code that runs once a day and creates and Excel file which is sent as an attachment via email. WOuld that help?

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook (No, I'm not Rick)

 

[leeolive]

Hi mwolf

The CSV File will be sending first name, surname(all personal details), dates. This is a form which user to completes for a pension. I have been told by the company that it must be in csv file format. No choice there. However, when a user updates their personal details online at a later stage, I have been asked to send this info via excel spreadsheet and via email. Your code you mentioned would definitely help!

I still then need to figure out how to send the data to a csv file, if you have any ideas there..

Please assume I know nothing as I am really new to asp and self taught and not from a techy background, but learrrning.

Thanks, much appreciated!
Lee

 

[mwolf00]

Since those values don't contain commas, this is a basic method for doing what you want...

This assumes that you have a file named &quot;CSV&quot; on your C: Drive (C:\CSV)


<%
monNum = month(date())
dayNum = day(date())

if monNum < 10 then monNum = &quot;0&quot; & monNum
if dayNum < 10 then dayNum = &quot;0&quot; & dayNum

g_filename=&quot;c:\csv\weekly&quot;& &quot;_&quot; & year(date()) & monNum & dayNum &&quot;.csv&quot;

set fso = createobject(&quot;scripting.filesystemobject&quot


if fso.FileExists(g_fileName) then
response.write (&quot;already exists&quot
else
Set cn = Server.CreateObject( &quot;ADODB.Connection&quot; )
cn.Open Application(&quot;connectString&quot

set rs = cn.execute(&quot;select * from myTable&quot

Set act = fso.CreateTextFile(g_filename, true)
do while not rs.eof
act.writeLine rs(0) & &quot;,&quot; & rs(1) & &quot;,&quot; & rs(2) & &quot;,&quot; & rs(3)
rs.movenext
loop
act.close

'now mail them'

set objMail = server.createObject(&quot;CDONTS.newMail&quot
with objMail
.From = &quot;me@here.com&quot;
.To = you@there.com
.Subject = &quot;Weekly Report&quot;
.Body = &quot;Your report is attached&quot;
call .AttachFile (g_filename, , 1)
.bodyFormat = 0 'HTML'
.mailFormat = 0 'MIME Format'
.send
end with

end if
%>


Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook (No, I'm not Rick)

 

[leeolive]

Thanks mwolf, I will give it a go.
Just to double check - Is this to write the data to a csv file or excel spreadsheet?
Cheers
Lee

 

[mwolf00]

I put the extension &quot;.csv&quot; on the file so it will be seen as such. A csv file is simply a structured text file where data is separated by commas and rows....

rec1,date1,field1
rec2,date2,field2


and so on...

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook (No, I'm not Rick)

 

[leeolive]

mwolf

Few questions..
What do you mean by name the file CSV and place in c drive?
Should I save a file as csv.csv? What extension should it have?

Also, must the file be completely blank ie. no field names as headings etc?

One more thing, the way you have written the connection etc is different to what I know. Will this be okay?
cn.Open Application(&quot;DSN=cjrcls&quot

Thanks!
Lee

 

[mwolf00]

You need to write the file to a file on the web server. I made a file called csv and placed it on the c drive form simplicity. The page will write files with the names like this...

weekly_20031030.csv

If you want to put headings in the first row then you can - it depends on what others plan on doing with it, but it shouldn't cause any problems.

your connection will work, it's just different from mine

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook (No, I'm not Rick)

 

[leeolive]

Thanks mwolf

Just so I am absolutely *clear*, how do I create the file on my c drive if I don't give it an extension? Apologies but I haven't seen this before..
(C:\CSV) - this would mean to me that CSV is a folder and not a file.

I feel daft, but not getting it!

 

[mwolf00]

doh - create the folder - the files will go inside of it - sorry.

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook (No, I'm not Rick)

 

[leeolive]

Hokey mwolf, I fear we are nearing the end..

I have just created my first csv file! Can't *believe* it. By the way, the adovbs file had gotten lost amid all my test files and that's why I was getting so many errors.

The only thing now is that it's not sending the email with the file. This is what I now have

<%@ Language=VBScript %>

<!-- #include file = &quot;ADOVBS.INC&quot; -->

<%
Dim monNum, dayNum
Dim cn, rs, act, fso, objMail, cmdcsv

monNum = month(date())
dayNum = day(date())

if monNum < 10 then monNum = &quot;0&quot; & monNum
if dayNum < 10 then dayNum = &quot;0&quot; & dayNum

g_filename=&quot;c:\csv\weekly&quot;& &quot;_&quot; & year(date()) & monNum & dayNum &&quot;.csv&quot;

set fso = createobject(&quot;scripting.filesystemobject&quot

if fso.FileExists(g_fileName) then
response.write (&quot;already exists&quot
else
'set connection to local DNS or change to web host DNS

Set cn = Server.CreateObject( &quot;ADODB.Connection&quot
Set rs = Server.CreateObject( &quot;ADODB.recordset&quot
Set cmdcsv = Server.CreateObject( &quot;ADODB.command&quot

cn.CommandTimeout = 15
cn.CursorLocation = adUseServer
cn.ConnectionString = &quot;DSN=cjrcls&quot; 'this is for local
'cndb.ConnectionString = &quot;Provider=Microsoft.Jet.OLEDB.4.0; Data Source=&quot;&quot;
on error resume next
cn.Mode = 3 '3 = adModeReadWrite
cn.open

With cmdcsv
.CommandTimeout = 15
set .ActiveConnection = cn
.CommandType = adCmdtext
.CommandText = &quot;SELECT * FROM ApplicantDetails&quot;
End with

rs.Open cmdcsv,,3,3

Set act = fso.CreateTextFile(g_filename, true)

'Set cn = Server.CreateObject( &quot;ADODB.Connection&quot
'Set rs = Server.CreateObject( &quot;ADODB.recordset&quot
'cn.Open Application(&quot;DSN=cjrcls&quot
'set rs = cn.execute(&quot;select * from ApplicantDetails&quot
'Set act = fso.CreateTextFile(g_filename, true)

do while not rs.eof
act.writeLine rs(0) & &quot;,&quot; & rs(1) & &quot;,&quot; & rs(2) & &quot;,&quot; & rs(3)
rs.movenext
loop
act.close

'now mail them
set objMail = server.createObject(&quot;CDONTS.newMail&quot

with objMail
.From = &quot;leeolive@btconnect.com&quot;
.To = &quot;leeolive@btconnect.com&quot;
.Subject = &quot;Weekly Report&quot;
.Body = &quot;Your report is attached&quot;
call.AttachFile (g_filename,,1)
.bodyFormat = 0 'HTML'
.mailFormat = 0
'MIME Format'
.send
end with
end if%>

 

[mwolf00]

Is it sending email at all? Is it set up for it (you have mail service running)? Is just the attachement missing?

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook (No, I'm not Rick)

 

[leeolive]

I'm using Outlook Express. Nothing at all has been sent or received. My mail service is working 100%.

I have just tried to perform the function again. It doesn't add a file to the csv folder, nor does it come up with the message saying that the file has already been created. Obviously it's going by the day and month so if it has been created today then it should say 'file already exists'?

Very useful mail function by the way. Always wondered how forums send you you forgotten password.

 

[mwolf00]

sending email is a whole other can of worms. I take it you haven't done it before? Are you using windows 2000?

Yes it should say file already exists. what are you getting?

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook (No, I'm not Rick)

 

[leeolive]

It's now coming up with 'file exists' - all fine.

No, I haven't sent email before. I am Windows 98. Hope that's not a problem..Definitely would like to get it working.

Thanks!

 

[mwolf00]

Start a new thread and figure out how to send mail w. win98 - if it's possible at all

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook (No, I'm not Rick)