Click here to Skip to main content
14,575,900 members
Rate this:
Please Sign up or sign in to vote.
See more:
Hi all,

I am working on VB.net Desktop Application of 2010 version. I want to upload Excel sheet to Database (sql server), but not display data in Gridview and only the most recent one excel file. Myexcel file name has been set to be the latest date and time. The structure is "filename_DD-MM-YYYY_hh-mm-ss.xls"

Please give me solution if You have.

Thanks in Advance

What I have tried:

 Function NewestFile(myPath, FileSpec)
        Dim Filenm As String
        Dim RecentFile As String
        Dim LatestDate As Date
               Filenm = Dir(myPath & "File_Flt" & Format(Now(), "dd-MM-yyyy_hh-mm-ss") & ".xls", 0)
        If Filenm <> "" Then
            RecentFile = Filenm
            LatestDate = FileDateTime(myPath & Filenm)
            Do While Filenm <> ""
                'LND = FileDateTime(myPath & Filenm)
                If FileDateTime(myPath & Filenm) > LatestDate Then
                    RecentFile = Filenm
                    LatestDate = FileDateTime(myPath & Filenm)
                End If
                Filenm = Dir()
            Loop
        End If
        NewestFile = RecentFile
    End Function
Private Sub Label1_Click()
        Dim filename As String
        Dim myPath As String
        myPath = "D:\Data\Folders\"
        filename = NewestFile(myPath, "*.xls")
         Dim ExcelConnection As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename + " ;Extended Properties=""Excel 12.0 Xml;HDR=Yes""")
        ExcelConnection.Open()
        Dim expr1 As Object
        Dim expr As String = "SELECT * FROM [Sheet1$]"
        Dim objCmdSelect As OleDbCommand = New OleDbCommand(expr, ExcelConnection)
        Dim objDR As OleDbDataReader

        Dim SQLconn As New SqlConnection()
        Dim ConnString As String = "Data Source=sqldb;Initial Catalog=StgSQL;Integrated Security=True"
        SQLconn.ConnectionString = ConnString
        SQLconn.Open()


        Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(SQLconn)
            bulkCopy.DestinationTableName = "ar_inv"

            Try
                objDR = objCmdSelect.ExecuteReader
                bulkCopy.WriteToServer(objDR)
                objDR.Close()
                SQLconn.Close()
                MsgBox("Data's are imported to SQL Succesfully", MsgBoxStyle.Information)
            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try
        End Using
    End Sub
Posted
Updated 3-Jun-20 5:15am
Comments
Richard MacCutchan 31-May-20 12:45pm
   
What is the problem with your code?
CHill60 1-Jun-20 4:36am
   
You have stated what you want to do but have not told us what problem you are having
Rate this:
Please Sign up or sign in to vote.

Solution 1

Assuming your problem is with finding the latest file based on the timestamp in the name, try something like this:
Private Shared Function ExtractTimestamp(ByVal filePath As String) As DateTime
    Dim fileName As String = IO.Path.GetFileNameWithoutExtension(filePath)
    If fileName.Length < 19 Then Return DateTime.MinValue
    
    Dim result As DateTime
    Dim value As String = fileName.Substring(fileName.Length - 19)
    DateTime.TryParseExact(value, "dd-MM-yyyy_HH-mm-ss", Nothing, DateTimeStyles.None, result)
    Return result
End Function

Private Shared Function FindNewestFile(ByVal folderPath As String, ByVal baseFileName As String) As String
    Dim fileSpec As String = baseFileName & "??-??-????_??-??-??.xls"
    Return IO.Directory.EnumerateFiles(folderPath, fileSpec).
        OrderByDescending(Function(filePath) ExtractTimestamp(filePath)).
        ThenByDescending(Function(filePath) IO.File.GetLastWriteTimeUtc(filePath)).
        FirstOrDefault()
End Function

Private Sub Label1_Click()
    Dim myPath As String = "D:\Data\Folders\"
    Dim filePath As String = FindNewestFile(myPath, "File_Flt")
    If filePath Is Nothing Then
        MessageBox.Show("No files found to import.")
        Return
    End If
    ...
   
Comments
Maciej Los 2-Jun-20 7:54am
   
5ed!
Rate this:
Please Sign up or sign in to vote.

Solution 2

In addition to solution 1, would you use the format yyyy-mm-dd for the file names, then you could just sort them alphabetically (descending) to find the most recent file.
   
Rate this:
Please Sign up or sign in to vote.

Solution 3

If it is a one off event you can actually do it via the import wizard supplied in SSMS see Docs Below



Start the SQL Server Import and Export Wizard - Integration Services (SSIS) | Microsoft Docs[^]
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100