INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Formatting Date Column form Excel Data - New to this and lost

Formatting Date Column form Excel Data - New to this and lost

(OP)
Below is a snippet with a simplified version of what is giving me trouble... The issue is that the date data is coming across formatted incorrectly.

I realize that I really need to make the PL/SQL take parameters and pass them but I am just trying to make it work first before I go bone up on PL/SQL as I am a T-SQL and VBA programmer first. So this foray into VB.net is a bit bumpy for me. I am lost trying to understand if the Excel data is coming across as a date or it is not and my attempts to format it are foolish. I did not see a way to determine the datatype of the column in a dataset. That's pretty much it, there are too many things I don't understand yet to figure out how to troubleshoot or fix this. Writing this, it occurs to me I could probably just adjust the format on the SQL side but I'd like to understand what fundamental I am missing other than Excel data is not strongly typed so many experiences are painful.

CODE

Dim ds As DataSet = OExcelHandler.GetDataFromExcel(Session("Fullname"), lblStatus, Session("FileType"))

    If ds IsNot Nothing Then
        For Each dr As DataRow In ds.Tables(0).Rows
            'There is an issue with the formatting of dr.Item("Request Date")... it is using a Euro dd/mm/yyyy format using tostring instead of the desired mm/dd/yyyy.
            strSQL = "INSERT INTO SomeTable (Somefield)" & _
                            " VALUES (" & "to_date('" & dr.Item("Request Date").Tostring() & "', 'mm/dd/yyyy') " & _
                                      ") "
         Next
    End if 

And the class I cobbled together from a few sites...

CODE

Public Class ExcelHandler

    ' Return data in dataset from excel file. '   
    Public Function GetDataFromExcel(ByVal a_sFilepath As String, lbl As Label, strExtenstion As String) As DataSet

        Dim ds As New DataSet()
        Dim cn As OleDbConnection
        Select Case strExtenstion
            Case ".xls"
                cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & a_sFilepath & ";Extended Properties= Excel 8.0")
            Case ".xlsx"
                cn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & a_sFilepath & ";Extended Properties= Excel 8.0")
        End Select

        Try
            cn.Open()
        Catch ex As OleDbException
            Console.WriteLine(ex.Message)
        Catch ex As Exception
            Console.WriteLine(ex.Message)
        End Try

        ' It Represents Excel data table Schema.'
        Dim dt As New System.Data.DataTable()
        dt = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
        If dt IsNot Nothing OrElse dt.Rows.Count > 0 Then
            For sheet_count As Integer = 0 To dt.Rows.Count - 1
                Try
                    ' Create Query to get Data from sheet. '
                    Dim sheetname As String = dt.Rows(sheet_count)("table_name").ToString()
                    Dim da As New OleDbDataAdapter("SELECT * FROM [" & sheetname & "]", cn)
                    da.Fill(ds, sheetname)
                Catch ex As DataException
                    lbl.Text = ex.Message
                Catch ex As Exception
                    lbl.Text = ex.Message
                End Try
            Next
        End If
        cn.Close()
        Return ds
    End Function
End Class 

RE: Formatting Date Column form Excel Data - New to this and lost

Try using VB's Format function:

CODE

Dim ReqDate As String

For Each dr As DataRow In ds.Tables(0).Rows
    
    ReqDate = dr.Item("Request Date")
    ReqDate = Format("ReqDate, "MM/dd/yyyy")  'note: MM is month with leading 0 if needed; mm is minutes

    strSQL = "INSERT INTO SomeTable (Somefield)" & _
    " VALUES (" & "to_date('" & ReqDate & "', 'mm/dd/yyyy') " & _
                                      ") "
Next 

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

RE: Formatting Date Column form Excel Data - New to this and lost

(OP)
I see. That should work assuming it is a date which I suspect it is. My mind was trying to make everything a method off the base type since my earliest playing overlooked base type methods so I was lost in the weeds. Good comment on "mm" being minutes since I am coming from the VBA world where mm is month and nn is minutes. Reminds me of the time I got lost with DateAdd in TSQL because it is the same as Jet/ACE SQL EXCEPT that it takes some sort of constant parameters instead of format strings.

Now to figure out why the whole solution quit building before I can test... It's related to the legacy Oracle use for sure. I still haven't figured out the resistance to ODAP.Net but that's a problem for another day.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close