There got to be a better way than CSV

Results 1 to 5 of 5

Thread: There got to be a better way than CSV

  1. #1
    Join Date
    Dec 1969
    Posts
    11

    Default There got to be a better way than CSV

    Well now I&#039ve got my ASP reading a CSV textfile (will explain later), but there is this small issue of the actual size of the textfile. THere are currently 36 fields in the CSV, 35 are single character fields with the 36th field being a mm/dd/yyyy datestamp. (comma delimited)<BR><BR>The issue at hand is the amount of time ASP requires to process the CSV. A few hundred records isnt bad, but once you pass say 600 records you better increased your time-out to above 6 minutes (unacceptable to say the least). 3000 records? forget it!!<BR><BR>Is there a easy way to take a regular CSV file and have ASP import the contents directly into a Access DB for better processing? If so, where can I see an example or sample code?<BR><BR>(explanatory note)<BR>The CSV file:<BR>We are gather public feedback on a UNiX Netscape server on the internet and we would like to have the stats from this survey viewable on a NT server inside of our firewall. Currently PERL is creating/appending to the CSV file with no issues, however ASP is not very friendly towards reading the data in large quantities. Ideas??<BR><BR>Thanks<BR>Mark

  2. #2
    Join Date
    Dec 1969
    Posts
    2,849

    Default RE: There got to be a better way than CSV

    Access has an Import option from the File menu. You can use that to import a CSV file into Access...

  3. #3
    Join Date
    Dec 1969
    Posts
    11

    Default RE: There got to be a better way than CSV

    Unfortunately, this is not an option. The end users (behind the firewall) will not have access to the CSV file or use a Access 97 database. Only via the web can they view the results from the form. Hence, the issue arises - how? Access 97 is the preferred DB, but how can I get the data into it via ASP on a daily/on-demand basis?<BR><BR>We are now looking into using PerlScript/Perl on the NT box to perform an analysis of the results, as ASP is not working out to be suitable for this type of task. <BR><BR>Mark

  4. #4
    Join Date
    Dec 1969
    Posts
    41

    Default RE: There got to be a better way than CSV

    Can&#039t you access a CSV file through ADO ? I am assuming you are reading it line by line and splitting by commas.<BR>If that is the case you should try acessing it as a database.<BR>I haven&#039t tried this but you can access a csv through ODBC.<BR><BR>Damian<BR>


  5. #5
    Join Date
    Dec 1969
    Posts
    11

    Default RE: There got to be a better way than CSV

    Thanks for everyones words of hope -----but....<BR><BR>Actually we just found the issue yesterday - the server needed reset!!!!<BR><BR>Apparently, a piece of Perl kept a few threads open sucking up the processor. A reboot allowed me to parse the CSV just like a normal DB. I am now retrieving 36 recordsets from 20,000+ records (each rec has 35 fields) in less than 20 seconds all in one connection. <BR><BR>I&#039ve included the source code below for any one who needs to connect to a CSV without installing a DSN on the server. (requires 2 files, a dsn file and the actual csv file) - the below is only using 7 recordsets, but ya&#039ll know how to do that.<BR><BR>==== beginning of file "CSV.DSN"===<BR>[ODBC]<BR>DRIVER=Microsoft Text Driver (*.txt; *.csv)<BR>UID=admin<BR>UserCommitSync=Yes<BR>Threa ds=3<BR>SafeTransactions=0<BR>PageTimeout=5<BR>Max ScanRows=25<BR>MaxBufferSize=512<BR>ImplicitCommit Sync=Yes<BR>FIL=text<BR>Extensions=txt,csv,tab,asc <BR>DriverId=27<BR>=== end of file "CSV.DSN" ===<BR><BR>====begin readcsv.asp======<BR>&#060;%<BR>Dim sDSNFile<BR>sDSNFile = "csv.dsn"<BR><BR>Dim sScriptDir<BR>sScriptDir = Request.ServerVariables("SCRIPT_NAME")<BR>sScriptD ir = StrReverse(sScriptDir)<BR>sScriptDir = Mid(sScriptDir, InStr(1, sScriptDir, "/"))<BR>sScriptDir = StrReverse(sScriptDir)<BR><BR>Dim sPath, sDSN<BR>sPath = Server.MapPath(sScriptDir) & "\"<BR>sDSN = "FileDSN=" & sPath & sDSNFile & _<BR>";DefaultDir=" & sPath & _<BR>";DBQ=" & sPath & ";"<BR><BR>Dim Conntemp, rs<BR><BR>sql1 = "select * from formtext.txt "<BR>sql2 = "select q1 from formtext.txt where q1 = 1 " &#039 1= Yes<BR>sql3 = "select q2 from formtext.txt where q2 = 5" &#039Question 2 HIGH answer<BR>sql4 = "select q2 from formtext.txt where q2 = 4" &#039Question 2 Above Avg. answer<BR>sql5 = "select q2 from formtext.txt where q2 = 3" &#039Question 2 AVG answer<BR>sql6 = "select q2 from formtext.txt where q2 = 2" &#039Question 2 Less than AVG answer<BR>sql7 = "select q2 from formtext.txt where q2 = 1" &#039Question 2 LOW answer<BR><BR><BR>set conntemp=server.createobject("adodb.connection")<B R>conntemp.open SDSN,conn_user,conn_pass<BR>set rs = server.createobject("adodb.recordset")<BR>rs.curso rlocation = 3<BR>rs.open sql1,conntemp,adoOpenDynamic<BR>set rs1 = server.createobject("adodb.recordset")<BR>rs1.curs orlocation = 3<BR>rs1.open sql2,conntemp,adoOpenDynamic<BR>set rs2 = server.createobject("adodb.recordset")<BR>rs2.curs orlocation = 3<BR>rs2.open sql3,conntemp,adoOpenDynamic<BR>set rs3 = server.createobject("adodb.recordset")<BR>rs3.curs orlocation = 3<BR>rs3.open sql4,conntemp,adoOpenDynamic<BR>set rs4 = server.createobject("adodb.recordset")<BR>rs4.curs orlocation = 3<BR>rs4.open sql5,conntemp,adoOpenDynamic<BR>set rs5 = server.createobject("adodb.recordset")<BR>rs5.curs orlocation = 3<BR>rs5.open sql6,conntemp,adoOpenDynamic<BR>set rs6 = server.createobject("adodb.recordset")<BR>rs6.curs orlocation = 3<BR>rs6.open sql7,conntemp,adoOpenDynamic<BR>%&#062;<BR>....htm l stuph with asp thrown in ....<BR><BR>==== end readcsv.asp======<BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •