-
Mar 9th, 2021, 01:51 AM
#1
Thread Starter
Banned
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
-
Mar 9th, 2021, 02:17 AM
#2
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.
-
Mar 9th, 2021, 02:44 AM
#3
Re: Data Grid Replaces the next File Data instead of copying after the first file
Originally Posted by meho2020
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.
-
Mar 9th, 2021, 03:49 AM
#4
Thread Starter
Banned
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
-
Mar 9th, 2021, 04:25 AM
#5
Re: Data Grid Replaces the next File Data instead of copying after the first file
Originally Posted by meho2020
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.
-
Mar 9th, 2021, 05:00 AM
#6
Thread Starter
Banned
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
-
Mar 9th, 2021, 05:30 AM
#7
Re: Data Grid Replaces the next File Data instead of copying after the first file
Originally Posted by meho2020
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.
-
Mar 9th, 2021, 05:42 AM
#8
Thread Starter
Banned
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
-
Mar 9th, 2021, 06:30 AM
#9
Re: Data Grid Replaces the next File Data instead of copying after the first file
Originally Posted by meho2020
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.
-
Mar 9th, 2021, 06:49 AM
#10
Thread Starter
Banned
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
-
Mar 9th, 2021, 07:00 AM
#11
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.
-
Mar 9th, 2021, 07:53 AM
#12
Thread Starter
Banned
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
-
Mar 9th, 2021, 10:03 AM
#13
Re: Data Grid Replaces the next File Data instead of copying after the first file
Originally Posted by meho2020
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.
Originally Posted by meho2020
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.
-
Mar 9th, 2021, 12:04 PM
#14
Re: Data Grid Replaces the next File Data instead of copying after the first file
- Coding Examples:
- Features:
- Online Games:
- Compiled Games:
-
Mar 9th, 2021, 12:22 PM
#15
Re: Data Grid Replaces the next File Data instead of copying after the first file
Originally Posted by meho2020
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.
-
Mar 9th, 2021, 12:58 PM
#16
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
- Coding Examples:
- Features:
- Online Games:
- Compiled Games:
-
Mar 10th, 2021, 01:00 AM
#17
Thread Starter
Banned
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
-
Mar 10th, 2021, 03:27 AM
#18
Re: Data Grid Replaces the next File Data instead of copying after the first file
Originally Posted by .paul.
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.
-
Mar 10th, 2021, 03:42 AM
#19
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.
-
Mar 10th, 2021, 10:46 AM
#20
Re: Data Grid Replaces the next File Data instead of copying after the first file
Originally Posted by jmcilhinney
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...
- Coding Examples:
- Features:
- Online Games:
- Compiled Games:
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|