Weird error when creating a .CSV file with VBScript on IIS6.

Discussion in 'ASP General' started by timstu, Apr 10, 2006.

  1. timstu

    timstu Guest

    Hi all, thanks for your help in advance.

    I have a page that creates a .CSV file that has been migrated over to
    Windows Server 2003 from Windows 2000 Server. It worked fine before I
    moved it, but now when it creates the .CSV file, and you open it, the
    first row opens in cell A1 and the second row opens in cell A2.

    If you open the file in Notepad, and copy and paste the date into a new
    document and save it as a .CSV, it opens fine.


    Here's my code...., and I'll paste the contents of my resulting file at
    the end of this.





    fname = "csv\loan" + request.Form("lid") + ".csv"
    fname2 = "loan" + request.Form("lid") + ".csv"
    Set filetxt =
    filesys.CreateTextFile(Server.MapPath(fname), 2, True)
    strQuery = "SELECT * FROM vwSendLoan WHERE nUserUID = "
    + request.Form("uid")
    Set rs = dbConn.Execute(strQuery)

    if not rs.EOF then
    FirstName = rs.Fields("cFirstName")
    MiddleName = rs.Fields("cMiddleName")
    LastName = rs.Fields("cLastName")
    else
    end if


    filetxt.Write
    "nUserUID,Employer_Partner_PID,First_Name,Middle_Name,Last_Name,Name_Suffix,"
    + _

    "SSN,Password,Email,Primary_Phone,Home_Address1,Home_Address2,Home_City,Home_State,Home_Zip,"
    + _

    "DL_Number,DL_State,Source_Of_Funds,Business_Phone,Business_Ext,Business_Fax,Business_Address1,"
    + _

    "Business_Address2,Business_City,Business_State,Business_Zip,Employer,Occupation,Employment_Length,Ref_Name1,Ref_Num1,"
    + _

    "Ref_Relation1,Ref_Name2,Ref_Num2,Ref_Relation2,Ref_Name3,Ref_Num3,Ref_Relation3,Bank_Name,"
    + _

    "Bank_Address,Bank_City,Bank_State,Bank_Zip,Routing_Number,Manager_Phone,Manager_Name,Account_Number,"
    + _

    "Is_Confirmed,Status,Last_IP,Pay_Period,Confirmation,Referred_By,Lead_Source,Birth_Date,"
    + _

    "PayDay1,PayDay2,PayDay3,Account_Date,Last_Login,Salary,Over_Payment,MonthsAtResidence,Own_Home,"
    + _

    "Number_Of_Transactions,Total_NSFs,Ending_Statement_Balance,Payroll_Garnishment,Months_At_Bank,NSF2,"
    + _

    "NSF6,Check_Number_Used,PasswordReset,ExistingCustomer,Docs_Received,Direct_Deposit,Selected,Add_Date,"
    + _

    "Add_User_UID,Updated_Date,Updated_User_UID,Hold"
    filetxt.WriteLine
    strWrite = ""
    i = 1
    for each item in rs.Fields
    if(i = 8) then
    strWrite = strWrite +
    Left(rs.Fields("cLastName"),4) + Right(rs.Fields("cSSN"),4) + ","
    elseif(i = 79) then
    strWrite = strWrite + ","
    elseif(item = "--Non") then
    strWrite = strWrite + ","
    else
    if(not isNull(item)) then
    if(InStr(CStr(item),",") <> 0)
    then
    strWrite = strWrite +
    """" + Trim(CStr(item)) + ""","
    else
    strWrite = strWrite +
    Trim(CStr(item)) + ","
    end if
    else
    strWrite = strWrite + ","
    end if
    end if
    i = i + 1
    next

    strWrite = Left(strWrite,Len(strWrite)-1)
    filetxt.Write(strWrite)
    filetxt.WriteLine
    filetxt.Close




    Here's the contents of my resulting file. Again, if I PASTE this into
    a fresh notepad document, and save it as a .CSV, it works.

    nUserUID,Employer_Partner_PID,First_Name,Middle_Name,Last_Name,Name_Suffix,SSN,Password,Email,Primary_Phone,Home_Address1,Home_Address2,Home_City,Home_State,Home_Zip,DL_Number,DL_State,Source_Of_Funds,Business_Phone,Business_Ext,Business_Fax,Business_Address1,Business_Address2,Business_City,Business_State,Business_Zip,Employer,Occupation,Employment_Length,Ref_Name1,Ref_Num1,Ref_Relation1,Ref_Name2,Ref_Num2,Ref_Relation2,Ref_Name3,Ref_Num3,Ref_Relation3,Bank_Name,Bank_Address,Bank_City,Bank_State,Bank_Zip,Routing_Number,Manager_Phone,Manager_Name,Account_Number,Is_Confirmed,Status,Last_IP,Pay_Period,Confirmation,Referred_By,Lead_Source,Birth_Date,PayDay1,PayDay2,PayDay3,Account_Date,Last_Login,Salary,Over_Payment,MonthsAtResidence,Own_Home,Number_Of_Transactions,Total_NSFs,Ending_Statement_Balance,Payroll_Garnishment,Months_At_Bank,NSF2,NSF6,Check_Number_Used,PasswordReset,ExistingCustomer,Docs_Received,Direct_Deposit,Selected,Add_Date,Add_User_UID,Updated_Date,Updated_User_UID,Hold
    66015,,Trevor,,Albrecht,--
    Non,123-23-1234,Albr1234,,1231231234,1234 My
    Street,,ORange,CA,92869,ca12323745,CA,Checking,555-555-5555,,,adefdf,adfd,adsfdf,CA,34879,Cashtrax,vp
    operta,123,Friend 1,555-555-5555,sadfdf,Friend
    2,444-444-4444,adfd,Friend 3,333-333-3333,adfdf,KEYPOINT CREDIT
    UNION,505 N MATHILDA
    AVE,SUNNYVALE,CA,94086,321180515,,,123456789,False,R,,1ST AND
    15TH,,,Google,4/11/2006,4/14/2006,5/1/2006,5/15/2006,4/10/2006 11:52:52
    AM,4/10/2006 11:39:25
    AM,1234,,123,True,,,,False,123,,,1234,False,False,False,False,False,4/6/2006
    12:42:45 PM,,4/10/2006 11:36:42 AM,66015,False


    Thanks again, everyone!!!
    timstu, Apr 10, 2006
    #1
    1. Advertising

  2. "timstu" <> wrote in message
    news:...
    > Hi all, thanks for your help in advance.
    >
    > I have a page that creates a .CSV file that has been migrated over to
    > Windows Server 2003 from Windows 2000 Server. It worked fine before I
    > moved it, but now when it creates the .CSV file, and you open it, the
    > first row opens in cell A1 and the second row opens in cell A2.
    >
    > If you open the file in Notepad, and copy and paste the date into a new
    > document and save it as a .CSV, it opens fine.
    >
    >
    > Here's my code...., and I'll paste the contents of my resulting file at
    > the end of this.
    >
    >
    >
    >
    >
    > fname = "csv\loan" + request.Form("lid") + ".csv"
    > fname2 = "loan" + request.Form("lid") + ".csv"
    > Set filetxt =
    > filesys.CreateTextFile(Server.MapPath(fname), 2, True)
    > strQuery = "SELECT * FROM vwSendLoan WHERE nUserUID = "
    > + request.Form("uid")
    > Set rs = dbConn.Execute(strQuery)
    >
    > if not rs.EOF then
    > FirstName = rs.Fields("cFirstName")
    > MiddleName = rs.Fields("cMiddleName")
    > LastName = rs.Fields("cLastName")
    > else
    > end if
    >
    >
    > filetxt.Write
    >

    "nUserUID,Employer_Partner_PID,First_Name,Middle_Name,Last_Name,Name_Suffix,
    "
    > + _
    >
    >

    "SSN,Password,Email,Primary_Phone,Home_Address1,Home_Address2,Home_City,Home
    _State,Home_Zip,"
    > + _
    >
    >

    "DL_Number,DL_State,Source_Of_Funds,Business_Phone,Business_Ext,Business_Fax
    ,Business_Address1,"
    > + _
    >
    >

    "Business_Address2,Business_City,Business_State,Business_Zip,Employer,Occupa
    tion,Employment_Length,Ref_Name1,Ref_Num1,"
    > + _
    >
    >

    "Ref_Relation1,Ref_Name2,Ref_Num2,Ref_Relation2,Ref_Name3,Ref_Num3,Ref_Relat
    ion3,Bank_Name,"
    > + _
    >
    >

    "Bank_Address,Bank_City,Bank_State,Bank_Zip,Routing_Number,Manager_Phone,Man
    ager_Name,Account_Number,"
    > + _
    >
    >

    "Is_Confirmed,Status,Last_IP,Pay_Period,Confirmation,Referred_By,Lead_Source
    ,Birth_Date,"
    > + _
    >
    >

    "PayDay1,PayDay2,PayDay3,Account_Date,Last_Login,Salary,Over_Payment,MonthsA
    tResidence,Own_Home,"
    > + _
    >
    >

    "Number_Of_Transactions,Total_NSFs,Ending_Statement_Balance,Payroll_Garnishm
    ent,Months_At_Bank,NSF2,"
    > + _
    >
    >

    "NSF6,Check_Number_Used,PasswordReset,ExistingCustomer,Docs_Received,Direct_
    Deposit,Selected,Add_Date,"
    > + _
    >
    > "Add_User_UID,Updated_Date,Updated_User_UID,Hold"
    > filetxt.WriteLine
    > strWrite = ""
    > i = 1
    > for each item in rs.Fields
    > if(i = 8) then
    > strWrite = strWrite +
    > Left(rs.Fields("cLastName"),4) + Right(rs.Fields("cSSN"),4) + ","
    > elseif(i = 79) then
    > strWrite = strWrite + ","
    > elseif(item = "--Non") then
    > strWrite = strWrite + ","
    > else
    > if(not isNull(item)) then
    > if(InStr(CStr(item),",") <> 0)
    > then
    > strWrite = strWrite +
    > """" + Trim(CStr(item)) + ""","
    > else
    > strWrite = strWrite +
    > Trim(CStr(item)) + ","
    > end if
    > else
    > strWrite = strWrite + ","
    > end if
    > end if
    > i = i + 1
    > next
    >
    > strWrite = Left(strWrite,Len(strWrite)-1)
    > filetxt.Write(strWrite)
    > filetxt.WriteLine
    > filetxt.Close
    >
    >
    >
    >
    > Here's the contents of my resulting file. Again, if I PASTE this into
    > a fresh notepad document, and save it as a .CSV, it works.
    >
    >

    nUserUID,Employer_Partner_PID,First_Name,Middle_Name,Last_Name,Name_Suffix,S
    SN,Password,Email,Primary_Phone,Home_Address1,Home_Address2,Home_City,Home_S
    tate,Home_Zip,DL_Number,DL_State,Source_Of_Funds,Business_Phone,Business_Ext
    ,Business_Fax,Business_Address1,Business_Address2,Business_City,Business_Sta
    te,Business_Zip,Employer,Occupation,Employment_Length,Ref_Name1,Ref_Num1,Ref
    _Relation1,Ref_Name2,Ref_Num2,Ref_Relation2,Ref_Name3,Ref_Num3,Ref_Relation3
    ,Bank_Name,Bank_Address,Bank_City,Bank_State,Bank_Zip,Routing_Number,Manager
    _Phone,Manager_Name,Account_Number,Is_Confirmed,Status,Last_IP,Pay_Period,Co
    nfirmation,Referred_By,Lead_Source,Birth_Date,PayDay1,PayDay2,PayDay3,Accoun
    t_Date,Last_Login,Salary,Over_Payment,MonthsAtResidence,Own_Home,Number_Of_T
    ransactions,Total_NSFs,Ending_Statement_Balance,Payroll_Garnishment,Months_A
    t_Bank,NSF2,NSF6,Check_Number_Used,PasswordReset,ExistingCustomer,Docs_Recei
    ved,Direct_Deposit,Selected,Add_Date,Add_User_UID,Updated_Date,Updated_User_
    UID,Hold
    > 66015,,Trevor,,Albrecht,--
    > Non,123-23-1234,Albr1234,,1231231234,1234 My
    >

    Street,,ORange,CA,92869,ca12323745,CA,Checking,555-555-5555,,,adefdf,adfd,ad
    sfdf,CA,34879,Cashtrax,vp
    > operta,123,Friend 1,555-555-5555,sadfdf,Friend
    > 2,444-444-4444,adfd,Friend 3,333-333-3333,adfdf,KEYPOINT CREDIT
    > UNION,505 N MATHILDA
    > AVE,SUNNYVALE,CA,94086,321180515,,,123456789,False,R,,1ST AND
    > 15TH,,,Google,4/11/2006,4/14/2006,5/1/2006,5/15/2006,4/10/2006 11:52:52
    > AM,4/10/2006 11:39:25
    >

    AM,1234,,123,True,,,,False,123,,,1234,False,False,False,False,False,4/6/2006
    > 12:42:45 PM,,4/10/2006 11:36:42 AM,66015,False
    >
    >
    > Thanks again, everyone!!!


    It'll be because it's being encoded as Unicode.

    Add this to the top of your code:-

    Response.Codepage = "Windows-1252"

    Anthony.
    Anthony Jones, Apr 11, 2006
    #2
    1. Advertising

  3. timstu

    timstu Guest

    Wow, thanks! It worked like magic! Add one to my box of tricks.
    timstu, Apr 11, 2006
    #3
  4. "timstu" <> wrote in message
    news:...
    > Wow, thanks! It worked like magic! Add one to my box of tricks.
    >


    Response.codepage is new in IIS6 don't use it in something that needs also
    to work in IIS5
    Anthony Jones, Apr 11, 2006
    #4
  5. timstu

    Ven Guest

    Active users

    I need to display information a list of Active users on a web site.

    I can do the count using the global.asa but need to display individual
    information about each "active user" without writing it all to a
    database and then pulling it out again.

    Info I need :

    IP Address - Request.ServerVariables("REMOTE_HOST")
    The page they are on - Request.ServerVariables("SCRIPT_NAME")
    the page they were on - Request.ServerVariables("HTTP_REFERER")

    Can I write this stuff into sessions and call them all out?

    Thanks

    ven
    Ven, May 1, 2006
    #5
  6. Re: Active users

    "Ven" <> wrote in message
    news:...
    > I need to display information a list of Active users on a web site.
    >
    > I can do the count using the global.asa but need to display individual
    > information about each "active user" without writing it all to a
    > database and then pulling it out again.
    >
    > Info I need :
    >
    > IP Address - Request.ServerVariables("REMOTE_HOST")
    > The page they are on - Request.ServerVariables("SCRIPT_NAME")
    > the page they were on - Request.ServerVariables("HTTP_REFERER")
    >
    > Can I write this stuff into sessions and call them all out?
    >
    > Thanks
    >
    > ven


    Did you really intend this to be posted a reply to this theard?

    Data in a session object is only available in that session so there isn't
    any point storing the above items in the session object.

    Storing this stuff in the application object would be real performance
    bottleneck.

    What are you trying to achieve?

    Anthony.
    Anthony Jones, May 2, 2006
    #6
  7. timstu

    Ven Guest

    Re: Active users

    Trying to see all live users on the site

    ven


    Anthony Jones wrote:
    > "Ven" <> wrote in message
    > news:...
    >> I need to display information a list of Active users on a web site.
    >>
    >> I can do the count using the global.asa but need to display individual
    >> information about each "active user" without writing it all to a
    >> database and then pulling it out again.
    >>
    >> Info I need :
    >>
    >> IP Address - Request.ServerVariables("REMOTE_HOST")
    >> The page they are on - Request.ServerVariables("SCRIPT_NAME")
    >> the page they were on - Request.ServerVariables("HTTP_REFERER")
    >>
    >> Can I write this stuff into sessions and call them all out?
    >>
    >> Thanks
    >>
    >> ven

    >
    > Did you really intend this to be posted a reply to this theard?
    >
    > Data in a session object is only available in that session so there isn't
    > any point storing the above items in the session object.
    >
    > Storing this stuff in the application object would be real performance
    > bottleneck.
    >
    > What are you trying to achieve?
    >
    > Anthony.
    >
    >
    Ven, May 3, 2006
    #7
    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. Tintin92
    Replies:
    1
    Views:
    1,672
    Andrew Thompson
    Feb 14, 2007
  2. jliu66
    Replies:
    0
    Views:
    492
    jliu66
    Oct 19, 2007
  3. NanQuan
    Replies:
    5
    Views:
    492
    NanQuan
    Oct 23, 2004
  4. Scott Gordo

    ASP/VBScript to randomly select name from csv?

    Scott Gordo, Mar 9, 2006, in forum: ASP General
    Replies:
    2
    Views:
    160
    MikeR
    Mar 10, 2006
  5. Li Chen
    Replies:
    18
    Views:
    643
    Azmi Farih
    Mar 23, 2010
Loading...

Share This Page