How do I import the most recent excel file to SQL db

Question

0.00/5 (No votes)

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 31-May-20 5:55am

Stevean974

Updated 3-Jun-20 5:15am

Add a Solution

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

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Richard Deeming · Answer 1 · 2020-06-02T01:34:00

Assuming your problem is with finding the latest file based on the timestamp in the name, try something like this:

VB.NET

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
    ...

phil.o · Answer 2 · 2020-06-02T03:30:00

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.

Posted 2-Jun-20 3:30am

phil.o

MarcusCole6833 · Answer 3 · 2020-06-03T05:16:00

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[^]

Posted 3-Jun-20 5:16am

MarcusCole6833