I have a question regarding importing data from EXCEL. There is an example file ('get_data_from_excel') on the Brilliant Database website - I have attached a screenshot of the scripting of that example to supplement my question.
In the example, the number of loops is 'hard-coded' into the program (on line 5 'For [$i] = 1 to 6 Step 1'). Is there any way to have the program automatically determine when it has captured all the data from the EXCEL spreadsheet? The user could input the ending value but that is not desirable either as I want this to be an automatic feature. I then experimented with using a variable to define the ending variable. Any number greater than the actual number of rows in the data-containing EXCEL spreadsheet would work and once the program encountered blanks in all the columns for that respective row, I added a 'Break' command to have the program stop executing the script. It seems to work fine but I think that the 'Break' command is being misused in that case. Additionally, I do not like the fact that the user has to supply an ending value nor do I like programming in an artificially high number (for example, 1,000,000) so that the program appears to work right - that is just sloppy programming in my eyes.
Next, I attempted to have the program change the ending variable once blanks were encountered, but after repeated attempts, I made the discovery that once a 'For' loop was started in Brilliant Database that it was going to complete the original amount of loops that were specified at the beginning regardless of any changes to the variable after the first 'For' was encountered. The 'Break' command was the only way to have the program cease looping but I am still not convinced that the 'Break' command is being utilized properly; instead, it is my opinion that it is to be used for testing and debugging and not as part of the finished scripting... but I do not wish to debate the usage of the 'Break' command.
So, after my rather long-winded explanation, I am looking for a method that can be utilized when importing EXCEL files that can dynamically adjust to the size (number of rows)of the EXCEL file. Can someone help me with this?
Is there a reason for not using the "import database" function (XLS/MDB) in file menu? You can save the settings in to a "preset" and it will just import all the rows in your Excel file. You may have to import to a "temporary folder/form" if you need to process data but on the advanced tab it it allows old data to be deleted.
Also available as a script action.
Last edited by tamcind; 03-28-13 at 18:47.
Something I haven't tried but a "for interval loop" does appear to allow the manipulation of the loop with variables - say you started a loop at using variables set at 1 for 10 loops and 1 step, followed by a if to check blank data. If more data run loop again but start at 11 this time (with changed variable) etc. Might need to use a "user function for main loop" and a query to remove any blank records. Just a idea , maybe a doggy system but hey if some thing works well and good.
Did a bit more experimenting and seems to work as expected but "GO TO" protests after 10,000 loops so would look at calling user functions instead of "GO TO". I should add that this idea is intended ( with some effort) for use in selecting rows/columns in external DB operation action when you need to update the external file and not just get data out.
Last edited by tamcind; 03-28-13 at 22:03.
I did not know anything about the import function- I just opened up my database,looked around, and found it. Is this something that can be controlled with scripting (I just saw your statement that it can be - what is the action called? - Is it Import/Parse?) I apologize in advance for asking a question without at least attempting to find a solution but this function is brand new to me.
In script editor open "IMPORT FROM A DATABASE" (middle column 7 down). or to test/setup presets go to Menus- File -Import/Parse-Import from Access/Excel. Used this commonly and only had problems stress testing near 100,000 records.
Wow, that is a powerful command. Sometimes (oftentimes), I feel dumb because my questions seem to be so basic and I have inadvertently overlooked a lot of the built-in features available in Brilliant Database simply because I am still too new to this tool. I have to give the developer of this software much praise because he has included so much capability into Brilliant Database!
I really like the feature that David suggested but now I am in somewhat of a quandary... I posed this question because I included the option for the user of being able to download records. The download targets previously completed records and these currently reside in EXCEL files. My earlier scripting performs a sort of data validation on all of the incoming records. Since each EXCEL row is added to BD has as an individual record (rather than a mass download), I seized this as an opportunity - two fields on the incoming records are checked to ensure that the object in each field already exists in the database. If not, a message appears and the questionable field is color-coded. Once the field matches by either adding a new employee's name or correcting the spelling of their name from the downloaded data, the color-coding disappears. During testing, this works good although this current method takes roughly three minutes to add the 600+ records compared to the instantaneous addition where the records are directly imported. As the database grows in size the time required to run the scripting will increase accordingly but I cannot overlook the value of being able to check the validity of the data at the point it is added to the database. So I guess I am simple pondering out loud now... with words.
I am still drawn to Import ability is that David suggested. Is there any way that the 'Fields to Import' can be defined by scripting? As long as the EXCEL file columns match the BD fields, it should be a flawless transition. I see that it is easy to define and save a Preset but I need to have the capability to pull from many different files and required reconfiguration of the Import tool may confuse the end user (yes, I know that it is relatively easy to figure out but since this feature would be used an infrequent basis, the user may forget).
I apologize for my rambling here - all the extraneous wording can be skipped as my one and only question is highlighted. I do want to sincerely thank everyone here that has been so willing to help me with all of my questions!!!
If file is to remain in Excel format, the ony option I can think of are multiple presets to cater for all possible combinations (don' think there is a limit in script editor as there is option to sort list). Not sure how to decide which version of script to run with out detailed knowledge of excel file but the file name may help or some thing based on the "external DB operation" like BD sample file to get 1 row ony for decision making. Unless the user can be trusted to select correct button for a particular file? If excel was converted to text format, then that allows full parsing with variables etc ( but expect slow performance on large sets and tricky logic). Might be able to help further if knew how each excel file varied.
PS have you had any contact with support yet?