-
Mar 7th, 2021, 02:55 AM
#1
Thread Starter
Banned
How to Browse Files While Using OLEDB Connection in the same time
Dear All
I am using a code to loop through excel files in a Directory using FolderBrowserDialog and it is working well but Slow, And I want to speed up the procedure by using OLEDB Connection , What I Discovered that the OLEDB is working well with one file having one path but when I tried to use it for looping for many files in a Directory it is failed
Here is my Code
Code:
If FolderBrowserDialog1.ShowDialog = System.Windows.Forms.DialogResult.OK Then
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 DtSet As DataSet
Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
MyConnection = New System.Data.OleDb.OleDbConnection
("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & Directory & "\" & "*.xls" & "'
;Extended Properties=Excel 8.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
Notice I tried to use Files or File But it Does not work either
Appreciate your informing me what is missing here or how can I use OLEDB for group of files
Thanks, Regards
Moheb Labib
-
Mar 7th, 2021, 03:51 AM
#2
Re: How to Browse Files While Using OLEDB Connection in the same time
Originally Posted by meho2020
Dear All
I am using a code to loop through excel files in a Directory using FolderBrowserDialog and it is working well but Slow, And I want to speed up the procedure by using OLEDB Connection , What I Discovered that the OLEDB is working well with one file having one path but when I tried to use it for looping for many files in a Directory it is failed
It's not clear what you want.
1)OLEDB for selecting a Excel File and then selecting a sheet?
2)select all Excel Files with all sheet's and perform a Select Statment?
no1. makes sence
no2. will blow up in your face if you have 200> Excel Files in a Folder with say 3 Sheet's each
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 7th, 2021, 04:01 AM
#3
Re: How to Browse Files While Using OLEDB Connection in the same time
You can't use a wildcard in a connection string. That's crazy. You need to specify an actual file in your connection string. Look at this:
Code:
For Each File In Files
Now look at this:
Code:
MyConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & Directory & "\" & "*.xls" & "';Extended Properties=Excel 8.0;")
How does that make sense? You've got the file path so use it:
Code:
MyConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & File & "';Extended Properties=Excel 8.0;")
There are numerous ways that the code could be further improved but the fundamental issue is that you're trying to connect to a file without specifying the file.
-
Mar 7th, 2021, 04:24 AM
#4
Thread Starter
Banned
Re: How to Browse Files While Using OLEDB Connection in the same time
Dear Chris
What I want is to select some columns in the first sheet and add them to the datagrid , then goes to the next file and select the same columns and add them after the last record in the datagrid and so on
-
Mar 7th, 2021, 04:30 AM
#5
Thread Starter
Banned
Re: How to Browse Files While Using OLEDB Connection in the same time
Dear Jmcilhinney
Thanks your reply , However when I use
Code:
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & File & "';Extended Properties=Excel 8.0;"
it says Operator & is not defined for types string and fileinfo
Thanks
-
Mar 7th, 2021, 04:53 AM
#6
Re: How to Browse Files While Using OLEDB Connection in the same time
I didn't notice that you were using DirectoryInfo and FileInfo because there seems no reason to do so. Just call Directory.GetFiles and that will return a String array instead of a FileInfo array and the code I provided will work. If you must stick with FileInfo for some reason (I see no such reason) then use its FuleName property to get the file path.
-
Mar 7th, 2021, 05:14 AM
#7
Thread Starter
Banned
Re: How to Browse Files While Using OLEDB Connection in the same time
Dear jmcilhinney
Thanks I used File.FullName and it gave me the first file, now How can I call Directory.GetFiles
Regards
Moheb Labib
-
Mar 7th, 2021, 05:25 AM
#8
Re: How to Browse Files While Using OLEDB Connection in the same time
Originally Posted by meho2020
How can I call Directory.GetFiles
What do you not understand about the information you found when you searched the web for yourself? Look first, ask questions later. We are not a substitute for your own effort.
-
Mar 7th, 2021, 05:26 AM
#9
Re: How to Browse Files While Using OLEDB Connection in the same time
Looking more closely at your code, how does it make sense to loop through multiple files and try to display them all in a single DataGridView anyway?
-
Mar 7th, 2021, 05:36 AM
#10
Thread Starter
Banned
Re: How to Browse Files While Using OLEDB Connection in the same time
Dear jmcilhinney
It is the core of the system which are developing is to loop through folder and get the specific columns data from the first file then the second until we finish the files and we use these data in another procedure, I may not use data grid view , I can put them in another excel sheet, the most important thing is not to miss any row of data
Thanks, Regards
Moheb Labib
-
Mar 7th, 2021, 11:25 AM
#11
Re: How to Browse Files While Using OLEDB Connection in the same time
Originally Posted by meho2020
Dear jmcilhinney
It is the core of the system which are developing is to loop through folder and get the specific columns data from the first file then the second until we finish the files and we use these data in another procedure, I may not use data grid view , I can put them in another excel sheet, the most important thing is not to miss any row of data
Thanks, Regards
Moheb Labib
what does the result file look like ?
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.
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
|