[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

returning date as time in csv file

Posted on 2011-10-19
13
Medium Priority
?
281 Views
Last Modified: 2012-05-12
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

0
Comment
Question by:Tagom
  • 8
  • 4
13 Comments
 
LVL 16

Expert Comment

by:Easwaran Paramasivam
ID: 36992514
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
 
LVL 17

Expert Comment

by:Carlos Villegas
ID: 36992690
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
 
LVL 17

Expert Comment

by:Carlos Villegas
ID: 36992736
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:Tagom
ID: 36992897
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
 
LVL 17

Expert Comment

by:Carlos Villegas
ID: 36992913
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
 
LVL 17

Accepted Solution

by:
Carlos Villegas earned 2000 total points
ID: 36993218
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
 
LVL 17

Expert Comment

by:Carlos Villegas
ID: 36993226
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
 

Author Comment

by:Tagom
ID: 36993385
@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
 
LVL 17

Expert Comment

by:Carlos Villegas
ID: 36993404
0
 

Author Comment

by:Tagom
ID: 36993407
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
 
LVL 17

Expert Comment

by:Carlos Villegas
ID: 36993487
Yes, only the cmdSend_Click method.
0
 

Author Closing Comment

by:Tagom
ID: 36993596
That worked perfect! Thank you very much
0
 
LVL 17

Expert Comment

by:Carlos Villegas
ID: 36993610
Glad to help!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
Q&A with Course Creator, Mark Lassoff, on the importance of HTML5 in the career of a modern-day developer.
This video teaches viewers about errors in exception handling.
The goal of the tutorial is to teach the user how to use functions in C++. The video will cover how to define functions, how to call functions and how to create functions prototypes. Microsoft Visual C++ 2010 Express will be used as a text editor an…
Suggested Courses
Course of the Month18 days, 3 hours left to enroll

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question