?
Solved

VB.NET .CSV file dropping part of date

Posted on 2011-03-02
9
Medium Priority
?
227 Views
Last Modified: 2012-05-11
Hi,

I have a application that creates a .CSV file. In the application there is a field for expected date of graduation, but all they really want is like "May 2011". Anyway, when the file is opened in Excel "May 2011" displays as May-11. Is there any way to change this? Why would it drop the 20 on 2011? Thanks!
Private Sub CreateDualEnrollmentFile()
        ' =========================================================
        ' Create text file 
        ' =========================================================
        Dim Myconn As SqlConnection
        Dim Mycomm As SqlCommand
        Dim dtr As SqlDataReader
        Dim dadapter As SqlDataAdapter
        Dim ds As DataSet
        Dim drow As DataRow
        Dim vStamp As DateTime = Now()
        Dim vStampForm = vStamp.ToString.Format("{0:MM-dd-yy_HH_mm_ss_tt}", DateTime.Now)

        Dim sql
        sql = "SELECT * FROM NSU_Dual_Enrollment_Application WHERE ([Stamp] > '" & gLastDate & "') ORDER BY Stamp ;"
       
        Dim vFileBody
        Myconn = New SqlConnection(ConfigurationSettings.AppSettings("gDataSource"))
        Mycomm = New SqlCommand(sql, Myconn)
        dadapter = New SqlDataAdapter
        dadapter.SelectCommand = Mycomm
        ds = New DataSet
        dadapter.Fill(ds, "NSU_Dual_Enrollment_Application")
        vFileBody = vFileBody & "FirstName" & "," & "MiddleName" & "," & "LastName" & "," & "SSN" & "," & "DOB" & "," & "Street" & "," & "City" & "," & "State" & ","
        vFileBody = vFileBody & "Zip" & "," & "Phone" & "," & "CellPhone" & "," & "Email" & ","
        vFileBody = vFileBody & "HighSchool" & "," & "HSGradDate" & "," & "PrevCollege" & ","
        vFileBody = vFileBody & "IntendedMajor" & "," & "Comments" & "," & "Stamp" & "," & vbCrLf
        If ds.Tables("NSU_Dual_Enrollment_Application").Rows.Count = 0 Then
            lblTextFile.Text = "No new applications were found."
        Else

            For Each drow In ds.Tables("NSU_Dual_Enrollment_Application").Rows
                vFileBody = vFileBody & Chr(34) & drow("FirstName") & Chr(34) & "," & Chr(34) & drow("MiddleName") & Chr(34) & "," & Chr(34) & drow("lastName") & Chr(34) & ","
                vFileBody = vFileBody & drow("SSN") & "," & drow("DOB") & "," & Chr(34) & drow("Street") & Chr(34) & "," & Chr(34) & drow("City") & Chr(34) & "," & Chr(34) & drow("State") & Chr(34) & "," & Chr(34) & drow("Zip") & Chr(34) & ","
                vFileBody = vFileBody & drow("Phone") & "," & drow("CellPhone") & "," & drow("Email") & "," & Chr(34) & drow("HighSchool") & Chr(34) & ","
                vFileBody = vFileBody & Chr(34) & drow("HSGradDate") & Chr(34) & "," & Chr(34) & drow("PrevCollege") & Chr(34) & "," & Chr(34) & drow("IntendedMajor") & Chr(34) & "," & Chr(34) & drow("Comments") & Chr(34) & ","
                vFileBody = vFileBody & drow("Stamp") & "," & vbCrLf
            Next

            Myconn.Close()
           
            'Create and save info in a text file.
            Dim vFileName = "EnrollmentData/DualEnrollmentReport_" & vStampForm & ".csv"
            
            '& Now() & "_" & "Scholarship_Report.txt"
            Try
                Dim FILENAME As String = Server.MapPath(vFileName)
                Dim objStreamWriter As StreamWriter
                objStreamWriter = File.CreateText(FILENAME)
                objStreamWriter.WriteLine(vFileBody)
                objStreamWriter.Close()            
                lblTextFile.Visible = True
                lblTextFile.Text = "File written and located <a href='" & vFileName & "'TARGET=_BLANK>here</a>."
                UpdateAdmissionsLastDateRan()
                btnText.Visible = False
            Catch Ex As Exception           
                lblTextFile.Visible = True
                lblTextFile.Text = "An error occurred attempting to produce the text file." & Ex.Message & sql             
            End Try
        End If
    End Sub

Open in new window

0
Comment
Question by:savache27
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 34

Expert Comment

by:it_saige
ID: 35022112
Have you checked the regional settings on the machine to ensure that the Date Format is mm/dd/YYYY?

-saige-
0
 

Author Comment

by:savache27
ID: 35022326
No, I haven't tried that. I'm not so much concerned about how it displays on my machine, but rather the people who will be downloading the report.

Also, when I create the file and and click on the location link an open/save dialog box pops up, but if you hit save it tries to save it as a .xls file rather than a .csv. And if you save it as a .xls  then it comes out completely wrong. If I select open and then go to file and try to save it it at least comes up with the .csv extension, but no longer has the name of the file. Any ideas on this?
0
 
LVL 34

Accepted Solution

by:
it_saige earned 2000 total points
ID: 35022555
First.  With regards to the way it displays.  You can send the date data from the db field, but the machine will use it's own regional settings to determines how the date data is formatted.  In general all date/time fields in databases contain all relavent date/time data regardless of whether the entered data matches the field data.  Relevant date/time information is generally MM/DD/YYYY hh:mm:ss.tttttt AM.  If you extract MM/DD/YYYY from the data field and enter the data into a csv that is opened with Excel.  Excel is going to give it's best guess as to the format of the data and display accordingly.  If Excel run's into a date time field, then it will display based upon the machines regional settings for the display of said data.

To ensure you are pulling down proper data from the server, first open the CSV file in something that will not reformat the contents of the file (something like notepad or wordpad) and see if the date field is showing the data you want to show.

-saige-
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:savache27
ID: 35022641
I checked my settings, they are fine. I probably should have mentioned it's not an actual date field, it's just a varchar. They didn't really need a specific format, but I'm not sure they'd want May-11 instead of May 2011. I guess maybe I should change it to a date field?
0
 
LVL 34

Expert Comment

by:it_saige
ID: 35022643
More information on date/time fields:

http://msdn.microsoft.com/en-us/library/ms180878.aspx

Something about how to extract the data from SQL in an independent regional settings format however, the reconversion may use regional settings (in other words when the client opens the file, regional settings are applied):

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_21942779.html

-saige-


0
 
LVL 34

Expert Comment

by:it_saige
ID: 35022654
Are you telling me that the data in the field is entered as May-11?

-saige-
0
 

Author Comment

by:savache27
ID: 35022669
No, it's entered as May 2011, and it displays as May-11. I just thought that it being a vachar field rather than date time might have something to do with it. Sorry for the confusion.
0
 

Author Comment

by:savache27
ID: 35027896
I guess I just don't understand why if I type, "I will graduate May 2011" it displays the May 2011. But if I don't put any words in front of it, it displays May-11. I suppose it's just an Excel thing.
0
 

Author Closing Comment

by:savache27
ID: 35028659
Thanks for looking at this for me and taking the time to help me out. I appreciate the information.
0

Featured Post

Introducing Priority Question

Increase expert visibility of your issues by participating in Priority Question, our latest feature for Premium and Team Account holders. Adjust the priority of your question to get emergent issues in front of subject-matter experts for help when you need it most.

Question has a verified solution.

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

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

771 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