returning date as time in csv file

I have a page which returns the data in a table to a csv file. I have one problem with the data.
The time is being returned as '12/30/1899 8:00:00 AM' the time part is correct - how do I get it to leave off the date part - which is the same for every row and obviously not right.
Additionally the dates are being returned with a time of 0:00 after then. I need to return only the date part.
front code
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:Button ID="cmdSend" runat="server" Text="Download File" 
            onclick="cmdSend_Click" /></br>
        </br>
    </div>
    </form>
</body>
</html>

Open in new window

back end
using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.IO;
using System.Data;
using System.Data.OleDb;
using System.Text;


public partial class _Default : System.Web.UI.Page
{
    private const string MDBFILE = "~/hgdeposition/App_Data/hgweb.mdb";



    private string GetConnectionString()
    {
        return "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath(MDBFILE) + ";";
    }


    protected void Page_Load(object sender, EventArgs e)
    {
    }

    /// <summary>
    /// This event is used to export datatable to CSV document
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>

    protected void cmdSend_Click(object sender, EventArgs e)
    {
        string SQL = "SELECT * FROM RainEvents";
        DataTable dTable = new DataTable();
        OleDbDataAdapter dbadp = new OleDbDataAdapter(SQL, GetConnectionString());
        dbadp.Fill(dTable);
        dbadp.Dispose();

        Response.ClearContent();
        Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", "File.csv"));
        Response.ContentType = "application/text";

        StringBuilder strbldr = new StringBuilder();
        foreach (DataColumn col in dTable.Columns)
        {
            //separting header columns text with comma operator
            strbldr.Append(col.ColumnName + ',');
        }
        //appending new line for gridview header row
        strbldr.Append("\n");
        foreach (DataRow row in dTable.Rows)
        {
            foreach (Object cell in row.ItemArray)
            {
                //separating gridview columns with comma
                strbldr.Append(Convert.ToString(cell) + ',');
            }

            //appending new line for gridview rows
            strbldr.Append("\n");
        }
        Response.Write(strbldr.ToString());
        Response.End();
    }
}

Open in new window

TagomAsked:
Who is Participating?
 
Carlos VillegasConnect With a Mentor Full Stack .NET DeveloperCommented:
Hello, I have made the changes to your code, replace your cmdSend_Click method with this:
protected void cmdSend_Click(object sender, EventArgs e)
{
    // Get the data
    DataTable dTable = new DataTable();
    using (OleDbConnection cn = new OleDbConnection(GetConnectionString()))
    {
        OleDbCommand cm = new OleDbCommand("SELECT * FROM RainEvents", cn);
        cn.Open();
        using (OleDbDataReader dr = cm.ExecuteReader())
            dTable.Load(dr);
    }

    Response.ClearContent();
    Response.Cache.SetNoStore();
    Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", "File.csv"));
    Response.ContentType = "application/text";

    int columnCount = 0;
        
    // Build header
    foreach (DataColumn col in dTable.Columns)
    {
        if (columnCount > 0)
            Response.Write(',');
            
        //separting header columns text with comma operator
        Response.Write(col.ColumnName);
            
        columnCount++;
    }
        
    //appending new line for gridview header row
    Response.Write(Environment.NewLine);

    // Build data rows
    System.Globalization.CultureInfo ciUs = new System.Globalization.CultureInfo("en-US");
    foreach (DataRow row in dTable.Rows)
    {
        columnCount = 0;
        foreach (Object cell in row.ItemArray)
        {
            if (columnCount > 0)
                Response.Write(',');

            if (cell is DateTime)// I'm assuming that your cell at index 2 contains the date, change the index to the correct one.
            {
                DateTime myDate = (DateTime)cell;
                if (myDate.TimeOfDay.TotalSeconds == 0)
                    Response.Write(myDate.ToString("d", ciUs));//When time is 0 return the Date part.
                else
                    Response.Write(myDate.ToString("t", ciUs));//otherwise return the Time part.
            }
            else
            {
                //separating gridview columns with comma
                Response.Write(Convert.ToString(cell));
            }

            columnCount++;
        }
            
        //appending new line for gridview rows
        Response.Write(Environment.NewLine);
    }

    Response.End();
}

Open in new window

0
 
Easwaran ParamasivamCommented:
You can use DateTime.TryParse to validate a string is date. If it is date, then Get time part from it using specific format. Look at sample code. Use it as a method and call for each cell value in your code.

Otherwise there is better option.. store the timevalue alone datatable by updating the database query to to fill the datatable. No need to check each and every value.....

string sDateTime = Convert.ToString(cell); //Check for each cell...
DateTime dt;
DateTime.TryParse(sDateTime ,out dt);

if(dt != null)
{
DateTime dt = DateTime.Parse(sDateTime); 
dt.ToString("HH:mm"); // 07:00 // 24 hour clock // hour is always 2 digits 
dt.ToString("hh:mm tt"); // 07:00 AM // 12 hour clock // hour is always 2 digits 
dt.ToString("H:mm"); // 7:00 // 24 hour clock dt.ToString("h:mm tt"); // 7:00 AM // 12 hour clock 
}

Open in new window

0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
Hello, I did these examples for you based in what you are asking and some observations:

Case 1, Assuming that your cell contains a System.String instead of a System.DataTime type, then you will need to parse its value to a DateTime type.
System.Globalization.CultureInfo ciUs = new System.Globalization.CultureInfo("en-US");
foreach (DataRow row in dTable.Rows)
{
    int cellCount = 0;
    foreach (Object cell in row.ItemArray)
    {
        if (cellCount == 2)// I'm assuming that your cell at index 2 contains the date, change the index to the correct one.
        {
            // Assuming that your cell contains a System.String instead of a System.DataTime type, then you will need to parse its value to a DateTime type.
            DateTime myDate = DateTime.MinValue;
            if (DateTime.TryParse(cell.ToString(), ciUs, System.Globalization.DateTimeStyles.None, out myDate))
            {
                if (myDate.TimeOfDay.TotalSeconds == 0)
                    strbldr.Append(myDate.ToString("d", ciUs) + ',');//When time is 0 return the Date part.
                else
                    strbldr.Append(myDate.ToString("t", ciUs) + ',');//otherwise return the Time part.
            }
            else
            {
                strbldr.Append(',');
            }
        }
        else
        {
            //separating gridview columns with comma
            strbldr.Append(Convert.ToString(cell) + ',');
        }

        cellCount++;
    }
    //appending new line for gridview rows
    strbldr.Append("\n");
}

Open in new window


Case 2, Assuming that your cell contain a System.DateTime value, instead of a string, this is a better way to cast its value.
System.Globalization.CultureInfo ciUs = new System.Globalization.CultureInfo("en-US");
foreach (DataRow row in dTable.Rows)
{
    int cellCount = 0;
    foreach (Object cell in row.ItemArray)
    {
        if (cellCount == 2)// I'm assuming that your cell at index 2 contains the date, change the index to the correct one.
        {
            // Assuming that your cell contain a System.DateTime value, instead of a string, this is a better way to cast its value.
            DateTime myDate = Convert.ToDateTime(cell);
            if (myDate.TimeOfDay.TotalSeconds == 0)
                strbldr.Append(myDate.ToString("d", ciUs) + ',');//When time is 0 return the Date part.
            else
                strbldr.Append(myDate.ToString("t", ciUs) + ',');//otherwise return the Time part.
        }
        else
        {
            //separating gridview columns with comma
            strbldr.Append(Convert.ToString(cell) + ',');
        }

        cellCount++;
    }
    //appending new line for gridview rows
    strbldr.Append("\n");
}

Open in new window


For both case you will need to change the index (in my example 2), of the cell that contain the DateTime value.

Also, your csv data rows are leaving an extra comma (,) at the end, I think that you need to remove it.
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
Carlos VillegasFull Stack .NET DeveloperCommented:
This other example will control the commas and the new line in a different way, is based in the case #1:
System.Globalization.CultureInfo ciUs = new System.Globalization.CultureInfo("en-US");
foreach (DataRow row in dTable.Rows)
{
    int cellCount = 0;
    foreach (Object cell in row.ItemArray)
    {
        if (cellCount > 0)
            strbldr.Append(',');

        if (cellCount == 2)// I'm assuming that your cell at index 2 contains the date, change the index to the correct one.
        {
            // Assuming that your cell contains a System.String instead of a System.DataTime type, then you will need to parse its value to a DateTime type.
            DateTime myDate = DateTime.MinValue;
            if (DateTime.TryParse(cell.ToString(), ciUs, System.Globalization.DateTimeStyles.None, out myDate))
            {
                if (myDate.TimeOfDay.TotalSeconds == 0)
                    strbldr.Append(myDate.ToString("d", ciUs));//When time is 0 return the Date part.
                else
                    strbldr.Append(myDate.ToString("t", ciUs));//otherwise return the Time part.
            }
        }
        else
        {
            //separating gridview columns with comma
            strbldr.Append(Convert.ToString(cell));
        }

        cellCount++;
    }
    //appending new line for gridview rows
    strbldr.AppendLine();
}

Open in new window

0
 
TagomAuthor Commented:
the fields in the database (access) are both date/times. I format them within the db to show the desired values
I am not accustomed to programming in aspx at all, I am not sure how to integrate the method into the file that I have now.
0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
Ok, I can help you with that, but I need the structure of your RainEvents table, so I can do the corrects changes to the code for you.
0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
Ignore the comment:
// I'm assuming that your cell at index 2 contains the date, change the index to the correct one.
In the previous post.
0
 
TagomAuthor Commented:
@yg989c,
I appreciate your help very much!
I am loading a jpg so you can see what the format for my columns are. the date-deployed and collection date are shortdate formats
and the collection_time long time format
I also apologize for the time in getting back with you. I was stuck in a meeting.
raineventsformat.JPG
0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
0
 
TagomAuthor Commented:
honestly I have not...I am working on it now, All I should have to do is replace the code in my aspx.cs page with the code you changed - correct?
0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
Yes, only the cmdSend_Click method.
0
 
TagomAuthor Commented:
That worked perfect! Thank you very much
0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
Glad to help!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.