VB.NET .CSV file dropping part of date

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

savache27Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
it_saigeConnect With a Mentor DeveloperCommented:
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
 
it_saigeDeveloperCommented:
Have you checked the regional settings on the machine to ensure that the Date Format is mm/dd/YYYY?

-saige-
0
 
savache27Author Commented:
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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
savache27Author Commented:
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
 
it_saigeDeveloperCommented:
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
 
it_saigeDeveloperCommented:
Are you telling me that the data in the field is entered as May-11?

-saige-
0
 
savache27Author Commented:
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
 
savache27Author Commented:
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
 
savache27Author Commented:
Thanks for looking at this for me and taking the time to help me out. I appreciate the information.
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.