|
You should show you SQL code.
SQL is veru powerful and can do many things without intermediate database writing.
Patrice
“Everything should be made as simple as possible, but no simpler.” Albert Einstein
|
|
|
|
|
myQuery = "SELECT *, Format([End Time],'YYYY-MM-DD') AS EndTimeDate from CDRecords.csv " & " WHERE Format([End Time],'YYYY-MM-DD') BETWEEN '" & searchStartDate & "' AND '" & searchEndDate & "'" & " ORDER BY [End Time] ASC"
myQuery = "SELECT *, R.[AreaName] from DateSlicedCDR.csv AS Q LEFT OUTER JOIN AreaCodes.csv AS R ON Q.[AreaPrefix] = R.[AreaPrefix] ORDER BY [EndTimeDate] ASC"
Those are the two queries. They work fine. NB: Formats have to match on the dates.
searchStartDate and searchEndDate variables are properly formatted dates obtained from the user form.
|
|
|
|
|
Not serious !
csv files gave never been databases.
You need to use real databases, bt setting indexes, you will get dramatic improvement.
Patrice
“Everything should be made as simple as possible, but no simpler.” Albert Einstein
|
|
|
|
|
Well.... it comes in text file format, so, it would be yet another step to convert it to something else, but I could do that. ?What would you suggest, if I did do that?
Performance isn't horrible right now but definitely yes I'd like to speed it up plus as numbers of records get higher it will get worse.
Use currently appends the text file with new data, so chances are the first file I get will be in text format - however - looking to the future, yes, it could be way better. I'm listening...
|
|
|
|
|
Situation is complicated, would need to know full details on data flow to have global view and give correct advices.
All I can say is that actual situation resort to brute force and need heavy changes to optimize.
This is getting by far out of scope of this forum.
You need to hire a professional.
Patrice
“Everything should be made as simple as possible, but no simpler.” Albert Einstein
|
|
|
|
|
Also if R returns null on the match for area code, I write *Not Found* to area name field in the final file, that gets rid of null which is sometimes bothersome, also it points out to the user, that they need to add some codes (if they want).
|
|
|
|
|
Break you application into a number of components.
ExtrcLoadTransform (ELT)
Load the data from your text file into a database (I prefer SQL server). You need to change the way the user inputs the data (append may not be the best way to go forward).
Your target table should be an exact match to the excel source file. Don't try and format or type the data, take it in as varchar.
Transform the loaded data into your working table doing any validation and data typing in the transform.
Now you can use SQL to query the data as required - this not becomes trivial and speed will not be an issue. You may need to tune the indexes but SQL Server supplies a tool for that as well.
As you get more familiar with TSQL you will find that you can probably output the data in the exact format you require (although pivot is excellent in Excel).
Good luck
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks Mycroft. This is helping a Lot.
Jim
|
|
|
|
|
I am new to vb.net programming.The CButton ctrls by SSdiver2112 look interesting but I have no idea on how to use.
Any help would be appreciat ed.
|
|
|
|
|
You also appear to be new to talking to people in a forum environment.
What are you talking about? If it's an article here on CP, there's forum at the bottom of the article to ask questions like this.
If it's not an article here, you're going to have to provide a link to whatever it is you're talking about.
|
|
|
|
|
I assume you're talking about this article: Custom Button Control with Gradient Colors and Extra Image (VB.NET)[^]
The source download includes a test/demo-Form. Did you take a look at that?
Please note that this forum has nothing to do with that article besides being part of the same website. If you need help with an article you should post your question to the message board of the article which you'll always find at the bottom of the article. The author of the article will then (hopefully) respond to you and obviously he's the one best suited to answer your question. In case you don't get a response from the author you're certainly free to ask here but then you should a) mention the article which you're talking about and b) explain your problem in more detail than you did here.
If the brain were so simple we could understand it, we would be so simple we couldn't. — Lyall Watson
|
|
|
|
|
I am planning to use OpenQA.Selenium to automate website filling in Google Chrome.
Here is the part of vb.net code,
<pre>Public driver1 As ChromeDriver = Nothing
Public WithEvents driver As EventFiringWebDriver
Dim service As ChromeDriverService = ChromeDriverService.CreateDefaultService
Dim chromeOptions As New OpenQA.Selenium.Chrome.ChromeOptions()
chromeOptions.AddExcludedArgument("ignore-certifcate-errors")
chromeOptions.AddArgument("test-type")
service.HideCommandPromptWindow = True
driver1 = New ChromeDriver(service, chromeOptions)
Here,
driver_Navigated fired only when pages are navigated using code. When I navigate manually in Chrome browser driver_Navigated is not firing.
What mistake is there in my code?
Are there any other working example codes for Website automation in Chrome?
Please help.
|
|
|
|
|
Did you install the chrome driver? Is it available in the system path?
ChromeDriver is maintained / supported by the Chromium project iteslf. WebDriver works with Chrome through the chromedriver binary (found on the chromium project’s download page). You need to have both chromedriver and a version of chrome browser installed. chromedriver needs to be placed somewhere on your system’s path in order for WebDriver to automatically discover it.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
I have installed chrome driver and it is available for the program.
|
|
|
|
|
"Available" means that it can be found in the system path? If unsure, open a console and type "path", it should include the directory where the driver can be found. It would also be helpfull to test it with another driver (like one for IE) to ensure that it is the driver that is causing the problems, not some other part.
Next to that, you could check chromedriver issues[^], to see whether it is a known problem.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Chromedriver is included in the project ( project\bin\debug). As I said 'driver_Navigated' firing only when pages are navigated using code. When I navigate manually in Chrome browser driver_Navigated is not firing.
|
|
|
|
|
srikrishnathanthri wrote: Chromedriver is included in the project ( project\bin\debug). The site states that it needs to be on the system path; having it in the project directory does not include it in that path.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
I recently started using MS Excel 2016 (online). When I debug a macro, variable values no longer show when I put the mouse on them. Is this a "works as intended" or is there an option to turn on somewhere?
Thanks so much!
|
|
|
|
|
Hi,
I wrote 2 addins doing the same. one for word 2007 and one for 2013. all works fine in develepment but when I try to load it as a com addin I get the message that it's not a valid office addin. someone knows what I'm doing wrong?
Jan
|
|
|
|
|
Hi.
I have hit a snag. I need to send a continuous data stream to a Access Db using VB 2010. I can only send one instance of it at a time at the moment. Is it possible to send data continuously to a Access Db?
Regards.
|
|
|
|
|
AFAIK, MSAccess does not support streaming out of the box. SQL Server has some functions, but that would be assuming that you are streaming to a single field (most likely a varbinary(max)). If you keept streaming to that single record, you'd end up with a table containing one record and one very huge and hard to manage blob.
I'd recommend you write your stream in chuncks (like now) to some buffer (a queue, or a memory mapped file), and have some other application put those chunks in Access (a record for each chunk).
Is there any reason why you would need to save it as a "continious blob"? You might be receiving data as a very long array of bytes, but that does not mean that you're only allowed to save it in that format
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Hi.
It must be continuous as another app will get info from that field. The field will be cleared regularly per minute as to curb the size of the field.
I have got it to write to the field but... The loop keeps on freezing the app. What bugs me though is that I coded a MsgBox to confirm the connection; then the app works fine but as soon as I click 'OK' the app freezes.
Any suggestions?
|
|
|
|
|
Gus113 wrote: It must be continuous as another app will get info from that field. The field will be cleared regularly per minute as to curb the size of the field. Those two sentences contradict each other. It should not matter to the consuming process how it is stored, only needs to now whether there is data. A queue in any form would be the way to go. That way the consuming process is also not influenced by any hiccups in reading.
Gus113 wrote: What bugs me though is that I coded a MsgBox to confirm the connection; then the app works fine but as soon as I click 'OK' the app freezes. Sounds like you are doing your processing work in the UI-thread; if that is busy doing other things than it will stop painting. Move your work to a separate thread.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Yeah, this doesn't sound like Access, or any database for that matter, is the proper solution for this problem.
This sounds like more of a thing handled by Message Queuing (MSMQ). But, even this is speculation because we have no idea what this data is, what it's used for, how fault tolerant it needs to be, ... can you go without data at all for a period of time, ...??
As for the app freezing, you're probably doing stuff on the UI thread that you should be doing in a separate thread.
|
|
|
|
|
Thanks Dave. I have got it to work.
|
|
|
|