Getting appending html in csv file when open in excel

[mia_islina]

I have created a CSV file on the server and want to send it to the user using the following code:

Dim strPhysicalPath As String 
strPhysicalPath = Server.MapPath( "CSV/" & PathVirtual)
Dim objFileInfo As System.IO.FileInfo = New System.IO.FileInfo(strPhysicalPath)
Response.Clear()
Response.ContentType = "application/vnd.ms-excel" 
Response.AddHeader("Content-Disposition", "attachment; filename=" & objFileInfo.Name)
Response.AddHeader("Content-Length", objFileInfo.Length.ToString())
Response.WriteFile(strPhysicalPath)
Response.Flush()
Response.End()

This seems to work fine except when the downloaded file is viewed in Excell the html code behind the page from which it came is also appended to the file. Does anyone have any idea why it does this? Thanks.Please guide me as i am a newbie in asp.net.Thanks in advance.

[kvprajapati]

Please check/verify the content of csv file.

[mia_islina]

The csv file in server looks fine but when i downloaded it into my local,i got html codes appended to the csv file.Why does this happen?Plz help me..

[kvprajapati]

If you can, please attach that csv file.

[mia_islina]

Ok..I already attached 2 csv file.One is come from server,and the other one file(that contains html codes) is the one i got after download the file from server into local computer.Hope you can give me guidelines through this.Thanks..

[mia_islina]

Plz help me...Your help is much appreciated..

[kvprajapati]

CSV seems okay! I think there maybe a little problem in your code. Here is a sample code I've attached with this post. Please post complete code .aspx and .aspx.vb here if problem is not resolve.

[mia_islina]

Here is the codes that i used to export csv file into server and download to local computer:

Protected Sub Button2_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button2.Click
        Try
            Dim sArray(16) As String
            Dim cellColumnIndex As Integer
            Dim excelFN As String
            Dim iFldNames As Integer
            Dim FldNames2() As String
            Dim duration As String = Format(dtFrom.SelectedDate, "ddMMMyy") & "-" & Format(dtTo.SelectedDate, "ddMMMyy")

            Dim RowsCount As Int32 = Me.DataGridView1.Rows.Count - 1
            If RadioButtonList1.SelectedIndex = 2 Then
                RowsCount = Me.DataGridView2.Rows.Count - 1
            End If

            Button2.Enabled = False
            If RowsCount > -1 Then
                If RadioButtonList1.SelectedIndex = 0 Then
                    Dim FldNames() As String = {"RecordNumber", "TransactionDate", "TotalTransaction", "Amout(RM)"}
                    FldNames2 = FldNames
                    iFldNames = FldNames.Length
                    excelFN = RadioButtonList1.SelectedValue & "_" & cbMonth.Text & cbYear.Text & "_" & cbAgency.Text & "_" & cbKioskID.Text
                ElseIf RadioButtonList1.SelectedIndex = 1 Then
                    Dim FldNames() As String = {"RecordNumber", "ListOfAgencies", "TotalTransaction", "Amout(RM)"}
                    FldNames2 = FldNames
                    iFldNames = FldNames.Length
                    excelFN = RadioButtonList1.SelectedValue & "_" & duration & "_" & cbKioskID.Text
                Else 'If RadioButtonList1.SelectedIndex = 2 Then
                    If cbStatus.Text = "Successful" Then
                        Dim FldNames() As String = {"Bill", "AgencyName", "KioskInfo", "TransactionDate", "TransactionTime", "TransactionNo", "Pocode", "AccountNo", "BillNo", "BillAmount", "PaidAmount", "ActualBillAmount", "Commission", "PaytoPOS", "BillStatus"}
                        FldNames2 = FldNames
                        iFldNames = FldNames.Length
                    Else
                        Dim FldNames() As String = {"Bill", "AgencyName", "KioskInfo", "TransactionDate", "TransactionTime", "TransactionNo", "Pocode", "AccountNo", "BillNo", "BillAmount", "PaidAmount", "ActualBillAmount", "Commission", "PaytoPOS", "BillStatus", "RefundDateTime"}
                        FldNames2 = FldNames
                        iFldNames = FldNames.Length
                    End If
                    excelFN = RadioButtonList1.SelectedValue & "_" & duration & "_" & cbAgency.Text & "_" & cbKioskID.Text & "_" & cbStatus.Text
                End If
                excelFN &= "_" & Format(Now, "ddMMyyyy")

                Dim DataArr(RowsCount, iFldNames - 1) As Object
                Dim ColsCounter As Int32 = 0

                If RadioButtonList1.SelectedIndex = 2 Then
                    For RowsCounter As Int32 = 0 To RowsCount
                        For Each cell As DataControlFieldCell In Me.DataGridView2.Rows(RowsCounter).Cells
                            sArray = Split(cell.UniqueID, "$")
                            cellColumnIndex = CInt(Replace(sArray(2), "ctl", ""))

                            If cell.Text = " " Then
                                cell.Text = ""
                            End If

                            If cellColumnIndex <> 3 Then
                                If cbStatus.Text = "Successful" Then
                                    If cellColumnIndex <> 13 Then
                                        If (cellColumnIndex = 8 Or cellColumnIndex = 9)Then
                                            DataArr(RowsCounter, ColsCounter) = vbTab & cell.Text
                                            ColsCounter = ColsCounter + 1
                                        Else
                                            DataArr(RowsCounter, ColsCounter) = cell.Text
                                            ColsCounter = ColsCounter + 1
                                        End If
                                    End If
                                Else
                                    If (cellColumnIndex = 8 Or cellColumnIndex = 9) Then
                                        DataArr(RowsCounter, ColsCounter) = vbTab & cell.Text
                                        ColsCounter = ColsCounter + 1
                                    Else
                                        DataArr(RowsCounter, ColsCounter) = cell.Text
                                        ColsCounter = ColsCounter + 1
                                    End If
                                End If
                            End If
                        Next cell
                        ColsCounter = 0
                    Next RowsCounter
                Else
                    For RowsCounter As Int32 = 0 To RowsCount
                        For Each cell As DataControlFieldCell In Me.DataGridView1.Rows(RowsCounter).Cells
                            sArray = Split(cell.UniqueID, "$")
                            cellColumnIndex = CInt(Replace(sArray(2), "ctl", ""))

                            If cell.Text = "&nbsp;" Then
                                DataArr(RowsCounter, ColsCounter) = ""
                            Else
                                DataArr(RowsCounter, ColsCounter) = cell.Text
                            End If
                            ColsCounter = ColsCounter + 1
                        Next cell
                        ColsCounter = 0
                    Next RowsCounter
                End If

                Dim fs As New FileStream(AppStartupPath & "\MemberPages\CSV\" & excelFN & ".csv", FileMode.Create, FileAccess.Write)
                Dim s As New StreamWriter(fs)

                For jLoop = 0 To iFldNames - 1
                    s.Write("""" & FldNames2(jLoop) & """,")
                Next jLoop
                s.WriteLine("")

                For iLoop = 0 To RowsCount
                    For jLoop = 0 To iFldNames - 1
                        s.Write("""" & DataArr(iLoop, jLoop) & """,")
                    Next jLoop
                    s.WriteLine("")
                Next iLoop

                s.Close()
                fs.Close()
                s = Nothing
                fs = Nothing

                DisplayDownloadDialog(excelFN & ".csv")
                appendFileContents(AppStartupPath, excelFN & ".csv file had been created.")
            End If
            Button2.Enabled = True
        Catch ex As Exception
            Button2.Enabled = True
            appendFileContents(AppStartupPath, ex.Message)
        End Try
    End Sub


Protected Sub DisplayDownloadDialog(ByVal PathVirtual As String)
        Try
            Dim strPhysicalPath As String
            strPhysicalPath = Server.MapPath("CSV/" & PathVirtual)
            appendFileContents(AppStartupPath, "strPhysicalPath : " & strPhysicalPath)
            Dim objFileInfo As System.IO.FileInfo
            objFileInfo = New System.IO.FileInfo(strPhysicalPath)
            Response.Clear()
            Response.AddHeader("Content-Disposition", "attachment; filename=" & objFileInfo.Name)
            Response.AddHeader("Content-Length", objFileInfo.Length.ToString())
            Response.Redirect(objFileInfo.FullName)
            Response.ContentType = "text/csv"
            Response.Write(objFileInfo.FullName)
            Response.End()
        Catch ex As Exception
            appendFileContents(AppStartupPath, ex.Message)
        End Try
    End Sub


Protected Sub appendFileContents(ByVal p1 As String, ByVal p2 As String)
        Dim fs As New FileStream(p1 & "\log.txt", FileMode.Append, FileAccess.Write)
        Dim s As New StreamWriter(fs)
        s.WriteLine(Now & " : " & p2)
        s.Close()
        fs.Close()
        s = Nothing
        fs = Nothing
   End Sub

Plz help me..thanks..

[kvprajapati]

@mia_islina

Use

[code]

... ... put_your_code here

[/code]

tags. Always wrap program code with

tags.

I'm bit confused with your post/code. Can you attach whole project/app here?[code]
tags.

I'm bit confused with your post/code. Can you attach whole project/app here?

[mia_islina]

Ops,i am so sorry.. :)
Here is the codes:

Protected Sub Button2_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button2.Click
  Try
            Dim sArray(16) As String
            Dim cellColumnIndex As Integer
            Dim excelFN As String
            Dim iFldNames As Integer
            Dim FldNames2() As String
            Dim duration As String = Format(dtFrom.SelectedDate, "ddMMMyy") & "-" & Format(dtTo.SelectedDate, "ddMMMyy")

            Dim RowsCount As Int32 = Me.DataGridView1.Rows.Count - 1
            If RadioButtonList1.SelectedIndex = 2 Then
                RowsCount = Me.DataGridView2.Rows.Count - 1
            End If

            Button2.Enabled = False
            If RowsCount > -1 Then
                If RadioButtonList1.SelectedIndex = 0 Then
                    Dim FldNames() As String = {"RecordNumber", "TransactionDate", "TotalTransaction", "Amout(RM)"}
                    FldNames2 = FldNames
                    iFldNames = FldNames.Length
                    excelFN = RadioButtonList1.SelectedValue & "_" & cbMonth.Text & cbYear.Text & "_" & cbAgency.Text & "_" & cbKioskID.Text
                ElseIf RadioButtonList1.SelectedIndex = 1 Then
                    Dim FldNames() As String = {"RecordNumber", "ListOfAgencies", "TotalTransaction", "Amout(RM)"}
                    FldNames2 = FldNames
                    iFldNames = FldNames.Length
                    excelFN = RadioButtonList1.SelectedValue & "_" & duration & "_" & cbKioskID.Text
                Else 'If RadioButtonList1.SelectedIndex = 2 Then
                    If cbStatus.Text = "Successful" Then
                        Dim FldNames() As String = {"Bill", "AgencyName", "KioskInfo", "TransactionDate", "TransactionTime", "TransactionNo", "Pocode", "AccountNo", "BillNo", "BillAmount", "PaidAmount", "ActualBillAmount", "Commission", "PaytoPOS", "BillStatus"}
                        FldNames2 = FldNames
                        iFldNames = FldNames.Length
                    Else
                        Dim FldNames() As String = {"Bill", "AgencyName", "KioskInfo", "TransactionDate", "TransactionTime", "TransactionNo", "Pocode", "AccountNo", "BillNo", "BillAmount", "PaidAmount", "ActualBillAmount", "Commission", "PaytoPOS", "BillStatus", "RefundDateTime"}
                        FldNames2 = FldNames
                        iFldNames = FldNames.Length
                    End If
                    excelFN = RadioButtonList1.SelectedValue & "_" & duration & "_" & cbAgency.Text & "_" & cbKioskID.Text & "_" & cbStatus.Text
                End If
                excelFN &= "_" & Format(Now, "ddMMyyyy")

                Dim DataArr(RowsCount, iFldNames - 1) As Object
                Dim ColsCounter As Int32 = 0

                If RadioButtonList1.SelectedIndex = 2 Then
                    For RowsCounter As Int32 = 0 To RowsCount
                        For Each cell As DataControlFieldCell In Me.DataGridView2.Rows(RowsCounter).Cells
                            sArray = Split(cell.UniqueID, "$")
                            cellColumnIndex = CInt(Replace(sArray(2), "ctl", ""))

                            If cell.Text = " " Then
                                cell.Text = ""
                            End If

                            If cellColumnIndex <> 3 Then
                                If cbStatus.Text = "Successful" Then
                                    If cellColumnIndex <> 13 Then
                                        If (cellColumnIndex = 8 Or cellColumnIndex = 9) Then
                                            DataArr(RowsCounter, ColsCounter) = vbTab & cell.Text
                                            ColsCounter = ColsCounter + 1
                                        Else
                                            DataArr(RowsCounter, ColsCounter) = cell.Text
                                            ColsCounter = ColsCounter + 1
                                        End If
                                    End If
                                Else
                                    If (cellColumnIndex = 8 Or cellColumnIndex = 9) Then
                                        DataArr(RowsCounter, ColsCounter) = vbTab & cell.Text
                                        ColsCounter = ColsCounter + 1
                                    Else
                                        DataArr(RowsCounter, ColsCounter) = cell.Text
                                        ColsCounter = ColsCounter + 1
                                    End If
                                End If
                            End If
                        Next cell
                        ColsCounter = 0
                    Next RowsCounter
                Else
                    For RowsCounter As Int32 = 0 To RowsCount
                        For Each cell As DataControlFieldCell In Me.DataGridView1.Rows(RowsCounter).Cells
                            sArray = Split(cell.UniqueID, "$")
                            cellColumnIndex = CInt(Replace(sArray(2), "ctl", ""))

                            If cell.Text = "&nbsp;" Then
                                DataArr(RowsCounter, ColsCounter) = ""
                            Else
                                DataArr(RowsCounter, ColsCounter) = cell.Text
                            End If
                            ColsCounter = ColsCounter + 1
                        Next cell
                        ColsCounter = 0
                    Next RowsCounter
                End If

                Dim fs As New FileStream(AppStartupPath & "\MemberPages\CSV\" & excelFN & ".csv", FileMode.Create, FileAccess.Write)
                Dim s As New StreamWriter(fs)

                For jLoop = 0 To iFldNames - 1
                    s.Write("""" & FldNames2(jLoop) & """,")
                Next jLoop
                s.WriteLine("")

                For iLoop = 0 To RowsCount
                    For jLoop = 0 To iFldNames - 1
                        s.Write("""" & DataArr(iLoop, jLoop) & """,")
                    Next jLoop
                    s.WriteLine("")
                Next iLoop

                s.Close()
                fs.Close()
                s = Nothing
                fs = Nothing

                DisplayDownloadDialog(excelFN & ".csv")
                appendFileContents(AppStartupPath, excelFN & ".csv file had been created.")
            End If
            Button2.Enabled = True
        Catch ex As Exception
            Button2.Enabled = True
            appendFileContents(AppStartupPath, ex.Message)
        End Try
    End Sub


Protected Sub DisplayDownloadDialog(ByVal PathVirtual As String)
        Try
            Dim strPhysicalPath As String
            strPhysicalPath = Server.MapPath("CSV/" & PathVirtual)
            appendFileContents(AppStartupPath, "strPhysicalPath : " & strPhysicalPath)
            Dim objFileInfo As System.IO.FileInfo
            objFileInfo = New System.IO.FileInfo(strPhysicalPath)
            Response.Clear()
            Response.AddHeader("Content-Disposition", "attachment; filename=" & objFileInfo.Name)
            Response.AddHeader("Content-Length", objFileInfo.Length.ToString())
            Response.Redirect(objFileInfo.FullName)
            Response.ContentType = "text/csv"
            Response.Write(objFileInfo.FullName)
            Response.End()
        Catch ex As Exception
            appendFileContents(AppStartupPath, ex.Message)
        End Try
    End Sub

  Protected Sub appendFileContents(ByVal p1 As String, ByVal p2 As String)
        Dim fs As New FileStream(p1 & "\log.txt", FileMode.Append, FileAccess.Write)
        Dim s As New StreamWriter(fs)
        s.WriteLine(Now & " : " & p2)
        s.Close()
        fs.Close()
        s = Nothing
        fs = Nothing
    End Sub

[kvprajapati]

@mia_islina - I need whole/complete code to sort out this problem. So will you attach a copy your project here please?

[mia_islina]

Sure..As attached.

Thanks.

-mia-

[kvprajapati]

This is the wrong code :

Protected Sub DisplayDownloadDialog(ByVal PathVirtual As String)
        Try
            Dim strPhysicalPath As String
            strPhysicalPath = Server.MapPath("CSV/" & PathVirtual)
            appendFileContents(AppStartupPath, "strPhysicalPath : " & strPhysicalPath)
            Dim objFileInfo As System.IO.FileInfo
            objFileInfo = New System.IO.FileInfo(strPhysicalPath)
            Response.Clear()
            Response.AddHeader("Content-Disposition", "attachment; filename=" & objFileInfo.Name)
            Response.AddHeader("Content-Length", objFileInfo.Length.ToString())
            Response.Redirect(objFileInfo.FullName)
            Response.ContentType = "text/csv"
            Response.Write(objFileInfo.FullName)
            Response.End()
        Catch ex As Exception
            appendFileContents(AppStartupPath, ex.Message)
        End Try
    End Sub

You need to write/change:

Protected Sub DisplayDownloadDialog(ByVal PathVirtual As String)
        Try
            Dim strPhysicalPath As String
            strPhysicalPath = Server.MapPath("CSV/" & PathVirtual)
            appendFileContents(AppStartupPath, "strPhysicalPath : " & strPhysicalPath)
            Dim objFileInfo As System.IO.FileInfo
            objFileInfo = New System.IO.FileInfo(strPhysicalPath)
            Response.Clear()
            Response.ClearHeaders()
            Response.AddHeader("Content-Disposition", "attachment; filename=" & objFileInfo.Name)
            Response.AddHeader("Content-Length", objFileInfo.Length.ToString())
            Response.ContentType = "text/csv"
            Response.WriteFile(strPhysicalPath)
            Response.Flush()
            Response.End()
        Catch ex As Exception
            appendFileContents(AppStartupPath, ex.Message)
        End Try
    End Sub

[mia_islina]

Thanks for the quick reply.
I already put the new codes,but it also seems doesn't works.
The appended html still showing in the csv file.

[kvprajapati]

>I already put the new codes,but it also seems doesn't works.

Attach zip of whole project including database if possible.

[mia_islina]

As you can see,the naming of the csv file in the server and when download to the local is a bit different.In server it saved as :

Daily Summary_01Dec10-01Dec10_All_10122010

but when I download it into my local,it save as :

Daily_Summary_01Dec10-01Dec10_All_10122010

Does the error is related to this?