Results 1 to 20 of 20

Thread: Data Grid Replaces the next File Data instead of copying after the first file

  1. #1

    Thread Starter
    Banned
    Join Date
    Apr 2020
    Location
    https://t.me/pump_upp
    Posts
    61

    Data Grid Replaces the next File Data instead of copying after the first file

    Dear All

    I am having a code which copies excel files data from specific folder and paste them to Data Grid view.

    The Data Grid Read all data of all files but the problem that it does not give the data of the first file and the second file respectively but it replaces the first file data with the second file data as a result, it always gives the last file data only instead of giving the first and the second file data together, I know there is something missing in my code which I do not know

    Here is my code

    Code:
       Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            If FolderBrowserDialog1.ShowDialog = System.Windows.Forms.DialogResult.OK Then
                DataGridView1.Rows.Clear()
                Dim Directory = FolderBrowserDialog1.SelectedPath
                Dim Files() As System.IO.FileInfo
                Dim DirInfo As New System.IO.DirectoryInfo(Directory)
                Files = DirInfo.GetFiles("*", IO.SearchOption.AllDirectories)
                For Each File In Files
                    Dim MyConnection As System.Data.OleDb.OleDbConnection
                    Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
                    Dim DtSet As DataSet
                    MyConnection = New System.Data.OleDb.OleDbConnection
        ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & File.FullName & "';  
        Extended Properties=Excel 4.0;")
                   MyCommand = New OleDbDataAdapter("select SN, BarMark, Diameter, Length, Quantity 
        ,BBSName from [SCHEDULE$]", MyConnection)
                    MyCommand.TableMappings.Add("Table", "Test")
                    DtSet = New DataSet
                    MyCommand.Fill(DtSet)
                    DataGridView1.DataSource = DtSet.Tables(0)
                    MyConnection.Close()
                Next
            End If
        End Sub
    Appreciate your support since this is important to my work

    Thanks, Regards

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: Data Grid Replaces the next File Data instead of copying after the first file

    This is what happens when people write code without knowing what it is supposed to do. They consider the end result but not the steps to get there. If you had considered the steps and then written code to implement those steps then you would not have made this mistake. Look at these lines of your code:
    Code:
                For Each File In Files
                    Dim MyConnection As System.Data.OleDb.OleDbConnection
                    Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
                    Dim DtSet As DataSet
                    MyConnection = New System.Data.OleDb.OleDbConnection
        ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & File.FullName & "';  
        Extended Properties=Excel 4.0;")
                   MyCommand = New OleDbDataAdapter("select SN, BarMark, Diameter, Length, Quantity 
        ,BBSName from [SCHEDULE$]", MyConnection)
                    MyCommand.TableMappings.Add("Table", "Test")
                    DtSet = New DataSet
                    MyCommand.Fill(DtSet)
                    DataGridView1.DataSource = DtSet.Tables(0)
                    MyConnection.Close()
                Next
    Think about what that code is actually doing. For every file in your list, you create a DataSet, populate it and then bind it to your grid. What exactly do you expect to happen on the second iteration of that loop? You're creating a new DataSet and binding it to the grid, so what exactly do you think will happen to the data you got on the previous iteration? If memory serves, I answered an earlier question of yours relating to the user of the FullName of the FileInfo objects and I raised this exact issue and it seems that you just ignored the warning and now you're wondering why exactly what I said would happen is happening.

    If what you want is a single DataTable contain all the data from every file then just create one DataTable, pass it to every Fill method and then bind it once when you're done. This is exactly why you should have thought about the logic and the steps involved before writing the code. You should have known that you wanted to create a single DataTable so you should have written code to create one DataTable. The #1 reason that beginners have problems with their code is that they don't stop to think about what the code is supposed to do BEFORE writing it. They try to develop the logic as they are writing the code. That's something that experienced developers can do with simple code but even they need be more methodical when the code is more complex. Beginners are simply incapable in almost all cases but they almost always try to do it because planning how to write code is not sexy and they all want to do the sexy part. The sooner you get over that, the sooner you'll write code with fewer issues.

  3. #3
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,040

    Re: Data Grid Replaces the next File Data instead of copying after the first file

    Quote Originally Posted by meho2020 View Post
    Dear All

    I am having a code which copies excel files data from specific folder and paste them to Data Grid view.

    The Data Grid Read all data of all files but the problem that it does not give the data of the first file and the second file respectively but it replaces the first file data with the second file data as a result, it always gives the last file data only instead of giving the first and the second file data together, I know there is something missing in my code which I do not know

    Here is my code

    Code:
       Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            If FolderBrowserDialog1.ShowDialog = System.Windows.Forms.DialogResult.OK Then
                DataGridView1.Rows.Clear()
                Dim Directory = FolderBrowserDialog1.SelectedPath
                Dim Files() As System.IO.FileInfo
                Dim DirInfo As New System.IO.DirectoryInfo(Directory)
                Files = DirInfo.GetFiles("*", IO.SearchOption.AllDirectories)
                For Each File In Files
                    Dim MyConnection As System.Data.OleDb.OleDbConnection
                    Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
                    Dim DtSet As DataSet
                    MyConnection = New System.Data.OleDb.OleDbConnection
        ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & File.FullName & "';  
        Extended Properties=Excel 4.0;")
                   MyCommand = New OleDbDataAdapter("select SN, BarMark, Diameter, Length, Quantity 
        ,BBSName from [SCHEDULE$]", MyConnection)
                    MyCommand.TableMappings.Add("Table", "Test")
                    DtSet = New DataSet
                    MyCommand.Fill(DtSet)
                    DataGridView1.DataSource = DtSet.Tables(0)
                    MyConnection.Close()
                Next
            End If
        End Sub
    Appreciate your support since this is important to my work

    Thanks, Regards
    besides what JMC said, see marked part of your Excel Connection (Extended Properties=Excel 4.0) really Excel 4.0 ?

    I would create a List of the Excel Files and sheetnames first
    here a Console sample
    Code:
    Imports System.Data.OleDb
    
    Module Module1
    
        Sub Main()
            Dim dt As Date
            Dim di As New DirectoryInfo("E:\Testfolder\ExcelTest") 'read Files in Folder
            Dim DateTime(dt = Directory.GetCreationTime(Environment.CurrentDirectory))
            Dim lFiles As FileInfo() = di.GetFiles("*.xls*")
            Dim fi As System.IO.FileSystemInfo
            For Each fi In lFiles
                Using con As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & fi.FullName & ";Extended Properties='Excel 12.0 Xml;HDR=YES'")
                    con.Open()
                    Dim sheets As DataTable = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "TABLE"})
                    For Each sheet As DataRow In sheets.Rows
                        Dim tableName As String = sheet("TABLE_NAME").ToString()
    
    
                        Dim sSql As String = "SELECT * From [" & tableName & "] "
                        sSql &= "IN '" & fi.FullName & "' 'Excel 12.0 Xml;'"
                        'Console.WriteLine(fi.FullName & ";" & tableName)
                        Console.WriteLine(sSql)
                    Next
                  
                End Using
            Next
            Console.WriteLine("-----------------------------------------------------------")
            Console.ReadKey()
        End Sub
    
    End Module
    if you have mixed Excel Versons (.xls and .xlsx) you will have to check that also
    and change the SQL to which verson the Excel File is.
    Last edited by ChrisE; Mar 9th, 2021 at 02:48 AM.
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  4. #4

    Thread Starter
    Banned
    Join Date
    Apr 2020
    Location
    https://t.me/pump_upp
    Posts
    61

    Re: Data Grid Replaces the next File Data instead of copying after the first file

    Dear Chris

    Thanks your reply, However your code is difficult for me , the point that my code is working and it reads the 1st file then it reads the 2nd file but the error is it is replacing the 1st file with the 2nd one , means there is a little thing needs to be added to my code and I do not know what is it?

    But the code is working but it does not give the result in sequence but replacing the data, I used the data table and it gives the same result , can I make the list with using my code because it is simple

    Thanks, Regards

    Moheb Labib

  5. #5
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,040

    Re: Data Grid Replaces the next File Data instead of copying after the first file

    Quote Originally Posted by meho2020 View Post
    Dear Chris

    Thanks your reply, However your code is difficult for me , the point that my code is working and it reads the 1st file then it reads the 2nd file but the error is it is replacing the 1st file with the 2nd one , means there is a little thing needs to be added to my code and I do not know what is it?

    But the code is working but it does not give the result in sequence but replacing the data, I used the data table and it gives the same result , can I make the list with using my code because it is simple

    Thanks, Regards

    Moheb Labib
    see Post#2
    what does your Loop Return ?
    every Excel File ?
    every Excel sheet ?
    does it overwrite the first one ?

    I mean I can give you the solution, but I think you want to understand what your Loop is doing!!!!!!

    did you try Post#3 ?
    did it catch every Excel File?
    did it catch every Excel sheet?
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  6. #6

    Thread Starter
    Banned
    Join Date
    Apr 2020
    Location
    https://t.me/pump_upp
    Posts
    61

    Re: Data Grid Replaces the next File Data instead of copying after the first file

    Dear Chris

    The loop searches the Specified Folder by Browser then it opens the 1st File and copy the selected columns in the Schedule Sheet and paste it in the Data grid then it goes to the next file and make the same, the problem that the 2nd file data replaces the 1st file data instead of coming after the 1st file, this is simply the error happened and I do not know why since I put:

    For Each File in Files
    Next

    Thanks, Regards
    Moheb Labib

  7. #7
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,040

    Re: Data Grid Replaces the next File Data instead of copying after the first file

    Quote Originally Posted by meho2020 View Post
    Dear Chris

    The loop searches the Specified Folder by Browser then it opens the 1st File and copy the selected columns in the Schedule Sheet and paste it in the Data grid then it goes to the next file and make the same, the problem that the 2nd file data replaces the 1st file data instead of coming after the 1st file, this is simply the error happened and I do not know why since I put:

    For Each File in Files
    Next

    Thanks, Regards
    Moheb Labib
    take a close look at your
    For Each File in Files
    Next
    what belongs in that Loop?
    my Task
    get each Excel file in a Folder
    get each Sheet named [SCHEDULE$] from that Excel File
    get certain columns from that Sheet
    put all Results into a DataSet for all Excelfiles, but not For Each Excel File

    Code:
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            If FolderBrowserDialog1.ShowDialog = System.Windows.Forms.DialogResult.OK Then
                DataGridView1.Rows.Clear()
                Dim Directory = FolderBrowserDialog1.SelectedPath
                Dim Files() As System.IO.FileInfo
                Dim DirInfo As New System.IO.DirectoryInfo(Directory)
                Files = DirInfo.GetFiles("*", IO.SearchOption.AllDirectories)
                For Each File In Files
                    Dim MyConnection As System.Data.OleDb.OleDbConnection
                    Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
                    Dim DtSet As DataSet
                    MyConnection = New System.Data.OleDb.OleDbConnection
        ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & File.FullName & "';  
        Extended Properties=Excel 4.0;")
                   MyCommand = New OleDbDataAdapter("select SN, BarMark, Diameter, Length, Quantity 
        ,BBSName from [SCHEDULE$]", MyConnection)
                    MyCommand.TableMappings.Add("Table", "Test")
                    DtSet = New DataSet
                    MyCommand.Fill(DtSet)
                    DataGridView1.DataSource = DtSet.Tables(0)
                    MyConnection.Close()
                Next
            End If
        End Sub
    Last edited by ChrisE; Mar 9th, 2021 at 05:34 AM.
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  8. #8

    Thread Starter
    Banned
    Join Date
    Apr 2020
    Location
    https://t.me/pump_upp
    Posts
    61

    Re: Data Grid Replaces the next File Data instead of copying after the first file

    Dear Chris

    Thanks your reply, However it gives the same result, I think we need something like getting the last record and paste after it , can we do this in data set?

    Thanks, Regards

    Moheb Labib

  9. #9
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,040

    Re: Data Grid Replaces the next File Data instead of copying after the first file

    Quote Originally Posted by meho2020 View Post
    Dear Chris

    Thanks your reply, However it gives the same result, I think we need something like getting the last record and paste after it , can we do this in data set?

    Thanks, Regards

    Moheb Labib
    what did you change?
    what does you code look like now?
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  10. #10

    Thread Starter
    Banned
    Join Date
    Apr 2020
    Location
    https://t.me/pump_upp
    Posts
    61

    Re: Data Grid Replaces the next File Data instead of copying after the first file

    Dear Chris

    I took your amended code and pasted it as it is

    Code:
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            If FolderBrowserDialog1.ShowDialog = System.Windows.Forms.DialogResult.OK Then
                DataGridView1.Rows.Clear()
                Dim Directory = FolderBrowserDialog1.SelectedPath
                Dim Files() As System.IO.FileInfo
                Dim DirInfo As New System.IO.DirectoryInfo(Directory)
                Files = DirInfo.GetFiles("*", IO.SearchOption.AllDirectories)
                For Each File In Files
                    Dim MyConnection As System.Data.OleDb.OleDbConnection
                    Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
                    Dim DtSet As DataSet
                    MyConnection = New System.Data.OleDb.OleDbConnection
        ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & File.FullName & "';  
        Extended Properties=Excel 4.0;")
                   MyCommand = New OleDbDataAdapter("select SN, BarMark, Diameter, Length, Quantity 
        ,BBSName from [SCHEDULE$]", MyConnection)
                    MyCommand.TableMappings.Add("Table", "Test")
                    DtSet = New DataSet
                    MyCommand.Fill(DtSet)
                    DataGridView1.DataSource = DtSet.Tables(0)
                    MyConnection.Close()
                Next
            End If
        End Sub

  11. #11
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,040

    Re: Data Grid Replaces the next File Data instead of copying after the first file

    I can't see any change, looks just like Post#1
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  12. #12

    Thread Starter
    Banned
    Join Date
    Apr 2020
    Location
    https://t.me/pump_upp
    Posts
    61

    Re: Data Grid Replaces the next File Data instead of copying after the first file

    Dear Chris

    Which part of the code do you want me to change, the code is running and reading correctly but I am missing how to get the data of next file to be shown after the first one in the data grid but the problem that it is replacing the first file, I do not know why? can you guide me to this point?

    Thanks, Regards
    Moheb Labib

  13. #13
    PowerPoster
    Join Date
    Nov 2017
    Posts
    3,116

    Re: Data Grid Replaces the next File Data instead of copying after the first file

    Quote Originally Posted by meho2020 View Post
    Dear Chris

    Which part of the code do you want me to change
    Probably the code that has been pointed out numerous times in this thread and in your previous thread as being the problem.


    Quote Originally Posted by meho2020 View Post
    the code is running and reading correctly but I am missing how to get the data of next file to be shown after the first one in the data grid but the problem that it is replacing the first file, I do not know why? can you guide me to this point?
    We all know that. You've made that clear numerous times in this thread, so you don't need to keep repeating yourself. Just like jmc and chris don't need to keep repeating themselves either.

    You've been told the problem and been told how to fix it. You haven't been given a "copy and paste solution", and I doubt you will, since the expectation here is that people are programmers, and programmers can write their own code.

    Good luck.

  14. #14
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    25,464

    Re: Data Grid Replaces the next File Data instead of copying after the first file


  15. #15
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,040

    Re: Data Grid Replaces the next File Data instead of copying after the first file

    Quote Originally Posted by meho2020 View Post
    Dear Chris

    Which part of the code do you want me to change, the code is running and reading correctly but I am missing how to get the data of next file to be shown after the first one in the data grid but the problem that it is replacing the first file, I do not know why? can you guide me to this point?

    Thanks, Regards
    Moheb Labib
    you have been guided twice to the problem/issue, once in red and once in blue

    perhaps I explain crap, so I will let other's give it a try

    good luck
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  16. #16
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    25,464

    Re: Data Grid Replaces the next File Data instead of copying after the first file

    Something like this... I haven't tested it...

    Code:
    Private Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button1.Click
        If FolderBrowserDialog1.ShowDialog = System.Windows.Forms.DialogResult.OK Then
            DataGridView1.Rows.Clear()
            Dim Directory = FolderBrowserDialog1.SelectedPath
            Dim DtSet As DataSet
            Dim firstTable As Boolean = True
            Dim Files() As System.IO.FileInfo
            Dim DirInfo As New System.IO.DirectoryInfo(Directory)
            Files = DirInfo.GetFiles("*", IO.SearchOption.AllDirectories)
            For Each File In Files
                Dim MyConnection As System.Data.OleDb.OleDbConnection
                Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
                MyConnection = New System.Data.OleDb.OleDbConnection
        ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & File.FullName & "';
         Extended Properties=Excel 4.0;")
                MyCommand = New OleDbDataAdapter("select SN, BarMark, Diameter, Length, Quantity 
        ,BBSName from [SCHEDULE$]", MyConnection)
                MyCommand.TableMappings.Add("Table", "Test")
                If firstTable Then
                    DtSet = New DataSet
                    MyCommand.Fill(DtSet, "MergedTable")
                    firstTable = False
                Else
                    MyCommand.Fill(DtSet, "tempTable")
                    DtSet.Tables("MergedTable").Merge(DtSet.Tables("tempTable"))
                    DtSet.Tables("tempTable").Clear()
                End If
            Next
            DataGridView1.DataSource = DtSet.Tables("MergedTable")
            MyConnection.Close()
        End If
    End Sub

  17. #17

    Thread Starter
    Banned
    Join Date
    Apr 2020
    Location
    https://t.me/pump_upp
    Posts
    61

    Re: Data Grid Replaces the next File Data instead of copying after the first file

    Dear Paul

    Great Thanks to you, your idea worked fine, I was desperate and you made my day, Thanks, Again

  18. #18
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: Data Grid Replaces the next File Data instead of copying after the first file

    Quote Originally Posted by .paul. View Post
    Something like this... I haven't tested it...

    Code:
    Private Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button1.Click
        If FolderBrowserDialog1.ShowDialog = System.Windows.Forms.DialogResult.OK Then
            DataGridView1.Rows.Clear()
            Dim Directory = FolderBrowserDialog1.SelectedPath
            Dim DtSet As DataSet
            Dim firstTable As Boolean = True
            Dim Files() As System.IO.FileInfo
            Dim DirInfo As New System.IO.DirectoryInfo(Directory)
            Files = DirInfo.GetFiles("*", IO.SearchOption.AllDirectories)
            For Each File In Files
                Dim MyConnection As System.Data.OleDb.OleDbConnection
                Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
                MyConnection = New System.Data.OleDb.OleDbConnection
        ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & File.FullName & "';
         Extended Properties=Excel 4.0;")
                MyCommand = New OleDbDataAdapter("select SN, BarMark, Diameter, Length, Quantity 
        ,BBSName from [SCHEDULE$]", MyConnection)
                MyCommand.TableMappings.Add("Table", "Test")
                If firstTable Then
                    DtSet = New DataSet
                    MyCommand.Fill(DtSet, "MergedTable")
                    firstTable = False
                Else
                    MyCommand.Fill(DtSet, "tempTable")
                    DtSet.Tables("MergedTable").Merge(DtSet.Tables("tempTable"))
                    DtSet.Tables("tempTable").Clear()
                End If
            Next
            DataGridView1.DataSource = DtSet.Tables("MergedTable")
            MyConnection.Close()
        End If
    End Sub
    Why create multiple tables and merge them when you can just create one table and Fill it multiple times? Any existing data in the table doesn't get cleared when you call Fill. There's a lot of shuffling data around for no good reason there.

  19. #19
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,040

    Re: Data Grid Replaces the next File Data instead of copying after the first file

    I wonder how Meho is going to handle which Data came from which Excel File/Sheet
    if some Data is "wierd" or "what a funny Value" if put all in one File.

    I don't think he has thought this threw very well.
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  20. #20
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    25,464

    Re: Data Grid Replaces the next File Data instead of copying after the first file

    Quote Originally Posted by jmcilhinney View Post
    Why create multiple tables and merge them when you can just create one table and Fill it multiple times? Any existing data in the table doesn't get cleared when you call Fill. There's a lot of shuffling data around for no good reason there.
    To be honest, I didn’t know Fill worked that way...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width