Solved

VB.NET .CSV file dropping part of date

Posted on 2011-03-02
9
187 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
  • 5
  • 4
9 Comments
 
LVL 32

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 32

Accepted Solution

by:
it_saige earned 500 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
 

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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 32

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 32

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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

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.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This video discusses moving either the default database or any database to a new volume.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

708 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now