Export from SQL Server to CSV

Discussion in 'ASP General' started by A.S., Feb 17, 2004.

  1. A.S.

    A.S. Guest

    I have an app that gets data from SQL Server and writes it to a csv
    file. My problem is that my query returns 25K + records. IE seems to
    'hang' and the csv file never gets created. Any Ideas how I can get this
    to work? Below is my code:

    Response.Expires = -1
    Response.Buffer = True
    'Response.ContentType="application/vnd.ms-excel"
    Server.ScriptTimeout = 10000

    fileName2 = createGUID()
    filesavepath2 = rootDir
    &"\assets\docs\"&Request.Cookies("supplypoint")("mepSite")&"_personnel.csv"

    headerRow = ""
    headerRow = headerRow & "SupplyPoint ID, Org Name, Title, Title2, Job
    Function, Prefix, First Name, Middle, Last Name,"
    headerRow = headerRow & "Suffix, Extended Info, Email, Phone"

    dataRows = ""

    'FOR STORED PROCEDURE BEGIN
    strsql = "sp_exportKeyPersonnel '" & mepId & "', '" & showType & "'"
    Set rs = DB.Execute (strsql)
    'FOR STORED PROCEDURE END
    While (NOT rs.EOF)
    currentOrgId = rs("id")

    dataRows = dataRows & """" & rs("msccId") & """,""" & rs("name")
    &""",""" & rs("title") &""",""" & rs("title2") &""",""" &
    rs("jFunction") &""",""" & rs("prefix") &""",""" & rs("firstName") &""","
    dataRows = dataRows & """" & rs("MI") &""",""" & rs("lastName") &""","""
    & rs("suffix") &""",""" & rs("extendedInfo") &""","
    dataRows = dataRows & """" & rs("email") &""",""" & rs("phone") &""""
    dataRows = dataRows & ""&vbcrlf&""
    rs.MoveNext()
    Wend
    rs.close
    Set rs = Nothing
    Set DB = Nothing

    set FSO = Server.CreateObject("scripting.FileSystemObject")
    set csvfile = fso.CreateTextFile(filesavepath2, true)
    csvfile.WriteLine(headerRow & vbcrlf)
    csvfile.WriteLine(dataRows)
    csvfile.Close

    Ariel
     
    A.S., Feb 17, 2004
    #1
    1. Advertising

  2. Your page will be timing out as you move through your recordset. As it
    times out and all you are doing is concatinating lots of text nothing
    appears to have been done.

    The way you are adding to the same variable is incredibly slow and
    inneficient. Rather than creating one big var then writing it to FSO in one
    go, open your file then write to it line by line and it'll be quicker.
    Hopefully enough to complete before it times out.

    "A.S." <> wrote in message
    news:...
    > I have an app that gets data from SQL Server and writes it to a csv
    > file. My problem is that my query returns 25K + records. IE seems to
    > 'hang' and the csv file never gets created. Any Ideas how I can get this
    > to work? Below is my code:
    >
    > Response.Expires = -1
    > Response.Buffer = True
    > 'Response.ContentType="application/vnd.ms-excel"
    > Server.ScriptTimeout = 10000
    >
    > fileName2 = createGUID()
    > filesavepath2 = rootDir
    >

    &"\assets\docs\"&Request.Cookies("supplypoint")("mepSite")&"_personnel.csv"
    >
    > headerRow = ""
    > headerRow = headerRow & "SupplyPoint ID, Org Name, Title, Title2, Job
    > Function, Prefix, First Name, Middle, Last Name,"
    > headerRow = headerRow & "Suffix, Extended Info, Email, Phone"
    >
    > dataRows = ""
    >
    > 'FOR STORED PROCEDURE BEGIN
    > strsql = "sp_exportKeyPersonnel '" & mepId & "', '" & showType & "'"
    > Set rs = DB.Execute (strsql)
    > 'FOR STORED PROCEDURE END
    > While (NOT rs.EOF)
    > currentOrgId = rs("id")
    >
    > dataRows = dataRows & """" & rs("msccId") & """,""" & rs("name")
    > &""",""" & rs("title") &""",""" & rs("title2") &""",""" &
    > rs("jFunction") &""",""" & rs("prefix") &""",""" & rs("firstName") &""","
    > dataRows = dataRows & """" & rs("MI") &""",""" & rs("lastName") &""","""
    > & rs("suffix") &""",""" & rs("extendedInfo") &""","
    > dataRows = dataRows & """" & rs("email") &""",""" & rs("phone") &""""
    > dataRows = dataRows & ""&vbcrlf&""
    > rs.MoveNext()
    > Wend
    > rs.close
    > Set rs = Nothing
    > Set DB = Nothing
    >
    > set FSO = Server.CreateObject("scripting.FileSystemObject")
    > set csvfile = fso.CreateTextFile(filesavepath2, true)
    > csvfile.WriteLine(headerRow & vbcrlf)
    > csvfile.WriteLine(dataRows)
    > csvfile.Close
    >
    > Ariel
     
    Adrian Forbes [ASP MVP], Feb 18, 2004
    #2
    1. Advertising

  3. A.S.

    A.S. Guest

    I will try that out.

    Remember, it is not timing out, just hanging. Very odd behavior.

    Ariel


    Adrian Forbes [ASP MVP] wrote:
    > Your page will be timing out as you move through your recordset. As it
    > times out and all you are doing is concatinating lots of text nothing
    > appears to have been done.
    >
    > The way you are adding to the same variable is incredibly slow and
    > inneficient. Rather than creating one big var then writing it to FSO in one
    > go, open your file then write to it line by line and it'll be quicker.
    > Hopefully enough to complete before it times out.
    >
    > "A.S." <> wrote in message
    > news:...
    >
    >>I have an app that gets data from SQL Server and writes it to a csv
    >>file. My problem is that my query returns 25K + records. IE seems to
    >>'hang' and the csv file never gets created. Any Ideas how I can get this
    >>to work? Below is my code:
    >>
    >>Response.Expires = -1
    >>Response.Buffer = True
    >>'Response.ContentType="application/vnd.ms-excel"
    >>Server.ScriptTimeout = 10000
    >>
    >>fileName2 = createGUID()
    >>filesavepath2 = rootDir
    >>

    >
    > &"\assets\docs\"&Request.Cookies("supplypoint")("mepSite")&"_personnel.csv"
    >
    >>headerRow = ""
    >>headerRow = headerRow & "SupplyPoint ID, Org Name, Title, Title2, Job
    >>Function, Prefix, First Name, Middle, Last Name,"
    >>headerRow = headerRow & "Suffix, Extended Info, Email, Phone"
    >>
    >>dataRows = ""
    >>
    >>'FOR STORED PROCEDURE BEGIN
    >>strsql = "sp_exportKeyPersonnel '" & mepId & "', '" & showType & "'"
    >>Set rs = DB.Execute (strsql)
    >>'FOR STORED PROCEDURE END
    >>While (NOT rs.EOF)
    >>currentOrgId = rs("id")
    >>
    >>dataRows = dataRows & """" & rs("msccId") & """,""" & rs("name")
    >>&""",""" & rs("title") &""",""" & rs("title2") &""",""" &
    >>rs("jFunction") &""",""" & rs("prefix") &""",""" & rs("firstName") &""","
    >>dataRows = dataRows & """" & rs("MI") &""",""" & rs("lastName") &""","""
    >>& rs("suffix") &""",""" & rs("extendedInfo") &""","
    >>dataRows = dataRows & """" & rs("email") &""",""" & rs("phone") &""""
    >>dataRows = dataRows & ""&vbcrlf&""
    >> rs.MoveNext()
    >>Wend
    >>rs.close
    >>Set rs = Nothing
    >>Set DB = Nothing
    >>
    >>set FSO = Server.CreateObject("scripting.FileSystemObject")
    >>set csvfile = fso.CreateTextFile(filesavepath2, true)
    >>csvfile.WriteLine(headerRow & vbcrlf)
    >>csvfile.WriteLine(dataRows)
    >>csvfile.Close
    >>
    >>Ariel

    >
    >
    >
     
    A.S., Feb 18, 2004
    #3
  4. Maybe this then

    http://www.aspfaq.com/show.asp?id=2180

    "A.S." <> wrote in message
    news:O$...
    > I will try that out.
    >
    > Remember, it is not timing out, just hanging. Very odd behavior.
    >
    > Ariel
    >
    >
    > Adrian Forbes [ASP MVP] wrote:
    > > Your page will be timing out as you move through your recordset. As it
    > > times out and all you are doing is concatinating lots of text nothing
    > > appears to have been done.
    > >
    > > The way you are adding to the same variable is incredibly slow and
    > > inneficient. Rather than creating one big var then writing it to FSO in

    one
    > > go, open your file then write to it line by line and it'll be quicker.
    > > Hopefully enough to complete before it times out.
    > >
    > > "A.S." <> wrote in message
    > > news:...
    > >
    > >>I have an app that gets data from SQL Server and writes it to a csv
    > >>file. My problem is that my query returns 25K + records. IE seems to
    > >>'hang' and the csv file never gets created. Any Ideas how I can get this
    > >>to work? Below is my code:
    > >>
    > >>Response.Expires = -1
    > >>Response.Buffer = True
    > >>'Response.ContentType="application/vnd.ms-excel"
    > >>Server.ScriptTimeout = 10000
    > >>
    > >>fileName2 = createGUID()
    > >>filesavepath2 = rootDir
    > >>

    > >
    > >

    &"\assets\docs\"&Request.Cookies("supplypoint")("mepSite")&"_personnel.csv"
    > >
    > >>headerRow = ""
    > >>headerRow = headerRow & "SupplyPoint ID, Org Name, Title, Title2, Job
    > >>Function, Prefix, First Name, Middle, Last Name,"
    > >>headerRow = headerRow & "Suffix, Extended Info, Email, Phone"
    > >>
    > >>dataRows = ""
    > >>
    > >>'FOR STORED PROCEDURE BEGIN
    > >>strsql = "sp_exportKeyPersonnel '" & mepId & "', '" & showType & "'"
    > >>Set rs = DB.Execute (strsql)
    > >>'FOR STORED PROCEDURE END
    > >>While (NOT rs.EOF)
    > >>currentOrgId = rs("id")
    > >>
    > >>dataRows = dataRows & """" & rs("msccId") & """,""" & rs("name")
    > >>&""",""" & rs("title") &""",""" & rs("title2") &""",""" &
    > >>rs("jFunction") &""",""" & rs("prefix") &""",""" & rs("firstName")

    &""","
    > >>dataRows = dataRows & """" & rs("MI") &""",""" & rs("lastName") &""","""
    > >>& rs("suffix") &""",""" & rs("extendedInfo") &""","
    > >>dataRows = dataRows & """" & rs("email") &""",""" & rs("phone") &""""
    > >>dataRows = dataRows & ""&vbcrlf&""
    > >> rs.MoveNext()
    > >>Wend
    > >>rs.close
    > >>Set rs = Nothing
    > >>Set DB = Nothing
    > >>
    > >>set FSO = Server.CreateObject("scripting.FileSystemObject")
    > >>set csvfile = fso.CreateTextFile(filesavepath2, true)
    > >>csvfile.WriteLine(headerRow & vbcrlf)
    > >>csvfile.WriteLine(dataRows)
    > >>csvfile.Close
    > >>
    > >>Ariel

    > >
    > >
    > >
     
    Adrian Forbes [ASP MVP], Feb 18, 2004
    #4
  5. A.S.

    A.S. Guest

    Definitely not antivirus.

    Adrian Forbes [ASP MVP] wrote:
    > Maybe this then
    >
    > http://www.aspfaq.com/show.asp?id=2180
    >
    > "A.S." <> wrote in message
    > news:O$...
    >
    >>I will try that out.
    >>
    >>Remember, it is not timing out, just hanging. Very odd behavior.
    >>
    >>Ariel
    >>
    >>
    >>Adrian Forbes [ASP MVP] wrote:
    >>
    >>>Your page will be timing out as you move through your recordset. As it
    >>>times out and all you are doing is concatinating lots of text nothing
    >>>appears to have been done.
    >>>
    >>>The way you are adding to the same variable is incredibly slow and
    >>>inneficient. Rather than creating one big var then writing it to FSO in

    >
    > one
    >
    >>>go, open your file then write to it line by line and it'll be quicker.
    >>>Hopefully enough to complete before it times out.
    >>>
    >>>"A.S." <> wrote in message
    >>>news:...
    >>>
    >>>
    >>>>I have an app that gets data from SQL Server and writes it to a csv
    >>>>file. My problem is that my query returns 25K + records. IE seems to
    >>>>'hang' and the csv file never gets created. Any Ideas how I can get this
    >>>>to work? Below is my code:
    >>>>
    >>>>Response.Expires = -1
    >>>>Response.Buffer = True
    >>>>'Response.ContentType="application/vnd.ms-excel"
    >>>>Server.ScriptTimeout = 10000
    >>>>
    >>>>fileName2 = createGUID()
    >>>>filesavepath2 = rootDir
    >>>>
    >>>
    >>>

    > &"\assets\docs\"&Request.Cookies("supplypoint")("mepSite")&"_personnel.csv"
    >
    >>>>headerRow = ""
    >>>>headerRow = headerRow & "SupplyPoint ID, Org Name, Title, Title2, Job
    >>>>Function, Prefix, First Name, Middle, Last Name,"
    >>>>headerRow = headerRow & "Suffix, Extended Info, Email, Phone"
    >>>>
    >>>>dataRows = ""
    >>>>
    >>>>'FOR STORED PROCEDURE BEGIN
    >>>>strsql = "sp_exportKeyPersonnel '" & mepId & "', '" & showType & "'"
    >>>>Set rs = DB.Execute (strsql)
    >>>>'FOR STORED PROCEDURE END
    >>>>While (NOT rs.EOF)
    >>>>currentOrgId = rs("id")
    >>>>
    >>>>dataRows = dataRows & """" & rs("msccId") & """,""" & rs("name")
    >>>>&""",""" & rs("title") &""",""" & rs("title2") &""",""" &
    >>>>rs("jFunction") &""",""" & rs("prefix") &""",""" & rs("firstName")

    >
    > &""","
    >
    >>>>dataRows = dataRows & """" & rs("MI") &""",""" & rs("lastName") &""","""
    >>>>& rs("suffix") &""",""" & rs("extendedInfo") &""","
    >>>>dataRows = dataRows & """" & rs("email") &""",""" & rs("phone") &""""
    >>>>dataRows = dataRows & ""&vbcrlf&""
    >>>> rs.MoveNext()
    >>>>Wend
    >>>>rs.close
    >>>>Set rs = Nothing
    >>>>Set DB = Nothing
    >>>>
    >>>>set FSO = Server.CreateObject("scripting.FileSystemObject")
    >>>>set csvfile = fso.CreateTextFile(filesavepath2, true)
    >>>>csvfile.WriteLine(headerRow & vbcrlf)
    >>>>csvfile.WriteLine(dataRows)
    >>>>csvfile.Close
    >>>>
    >>>>Ariel
    >>>
    >>>
    >>>

    >
    >
     
    A.S., Feb 18, 2004
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. JP

    Export Data to .CSV file

    JP, Jul 3, 2003, in forum: ASP .Net
    Replies:
    3
    Views:
    3,248
    dave wanta
    Jul 3, 2003
  2. =?Utf-8?B?RGF2aWQgVmFsbGU=?=

    Invalid export DLL or export format

    =?Utf-8?B?RGF2aWQgVmFsbGU=?=, Oct 29, 2003, in forum: ASP .Net
    Replies:
    0
    Views:
    3,631
    =?Utf-8?B?RGF2aWQgVmFsbGU=?=
    Oct 29, 2003
  3. VB Programmer
    Replies:
    1
    Views:
    431
    VB Programmer
    Jul 31, 2004
  4. =?Utf-8?B?U2Ft?=

    Export a Web Page to .XML or CSV

    =?Utf-8?B?U2Ft?=, Oct 12, 2004, in forum: ASP .Net
    Replies:
    1
    Views:
    1,336
    Diego
    Oct 12, 2004
  5. Maarten Porters
    Replies:
    1
    Views:
    433
    Florian Gilcher
    Jul 28, 2008
Loading...

Share This Page