Search code examples
asp-classicvbscriptrecordset

Reading CSV in Recordset?

Let me start off by saying that I am very greatful to have a place to go to when I need help with some code and I'm even more thankful when I see people trying to help out, so for everyone here Thank you for looking at my question/problem even if you don't have an answer.

With that said, on with my question/problem:

I have been trying to get this to work but I cannot seem to find the syntax error!! :-(

Can anyone please help me...

Here is the code:

dim strPathtoCSVFolder,strPathtoCSVFile,strPathtoCSVFileTWO

strPathtoCSVFolder="D:\classic_asp\test\" & Request.QueryString("XTNO") & "\Data\"
strPathtoCSVFile="Unit_" & Request.QueryString("XTNO") & "_Year_" & Request.QueryString("year") & "_Q_" & Request.QueryString("q") & "_MERGE_DataCsv.csv"
strPathtoCSVFileTWO="Unit_" & Request.QueryString("XTNO") & "_Year_" & Request.QueryString("year") & "_Q_" & Request.QueryString("q") & "_MERGE_DataCsv_SORTED.csv"

Set Conn = CreateObject("ADODB.Connection")
Set RS = CreateObject("ADODB.Recordset")
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strPathtoCSVFolder & ";Extended Properties=""text;HDR=YES;FMT=Delimited"""
dim strDirInfoX
strDirInfoX="SELECT STATE, SUM(GALLONS) as Total FROM " & strPathtoCSVFile & " GROUP BY STATE "

'''''' response.write strDirInfoX
dim strTxttoMem

dim strsource
strsource="RS.Open " & strDirInfoX & " , Conn, 1, 3, &H0001"
RS.Open strsource
'response.write strsource

redim FieldNames(rs.fields.count)
redim FieldTypes(rs.fields.count)
For i = 0 To (rs.Fields.Count - 1)
    FieldNames(i) = cstr(trim(rs.Fields.Item(i).Name))
    FieldTypes(i) = cstr(trim(rs.Fields.Item(i).Type))
Next
RS.Close

RS.Open strDirInfoX, Conn, 3, 3, &H0001
Do Until RS.EOF
''''    for i=0 to ubound(FieldNames)-1
'''     response.write(FieldNames(i) & " = " & RS.Fields.Item(FieldNames(i)) & "<br>")

strTxttoMem=strTxttoMem & RS("STATE") & RS("total")

''  next
    RS.MoveNext
Loop
RS.Close
Conn.Close

dim fs,tfile
set fs=Server.CreateObject("Scripting.FileSystemObject")
set tfile=fs.CreateTextFile(strPathtoCSVFolder & strPathtoCSVFileTWO)


tfile.WriteLine(strTxttoMem)
tfile.close
set tfile=nothing
set fs=nothing

Thank you so much for any help...

Solution

  • Well, without running your code, I spotted an error in this part:

    dim strsource 
strsource="RS.Open " & strDirInfoX & " , Conn, 1, 3, &H0001" 
RS.Open strsource

    or to shorten it, you are doing this:

    RS.Open "RS.Open " & strDirInfoX & " , Conn, 1, 3, &H0001"

    change it to RS.Open strDirInfoX, Conn, 1, 3, &H0001 and that part will run better.