Saad Posted December 3, 2023 Share Posted December 3, 2023 Good day everyone. I receive multiple large tab separated files. The only thing is shared among these files are the names of the first five fields ("Country" "Profile" "Region" "Service" "Source"). The content of these files are unknown. I attached a sample data file with sample data, In real life, each file will have one Source. The file content could reach 500,00 lines. So, for one country with 2 profile, 2 regions, 2 services, and 2 sources, I could have 16 files with a total of 8 million lines. I need to present the first five fields in a treeview. I also need to present the whole file in a listview based on the select field in the treeview. Using one array to hold millions of rows introduced a long delay when display or manipulating the listview. I thought about using multiple arrays, one for each source but don't know how to create these arrays. Thoughts? Services.txt Link to comment Share on other sites More sharing options...
Andreik Posted December 4, 2023 Share Posted December 4, 2023 You can use regex to get the content of the files to array. #include <Array.au3> $sData = FileRead('services.txt') $aData = StringRegExp($sData, '(?:"[\-a-zA-Z0-9]+"\t*)+', 3) _ArrayDisplay($aData) When the words fail... music speaks. Link to comment Share on other sites More sharing options...
Saad Posted December 4, 2023 Author Share Posted December 4, 2023 Thanks for the info. I have no issues parsing, reading, displaying the data. My question is: Using one array to hold millions of rows introduced a long delay when searching the array, display or manipulating the listview. I thought about using multiple arrays, one for each source but don't know how to create these arrays. Thoughts? Link to comment Share on other sites More sharing options...
Andreik Posted December 4, 2023 Share Posted December 4, 2023 (edited) Probably a good approach is to insert the parsed data into a sqlite table so you can search and filter data pretty quick. You can even have different tables for different sources. Edited December 4, 2023 by Andreik When the words fail... music speaks. Link to comment Share on other sites More sharing options...
orbs Posted December 4, 2023 Share Posted December 4, 2023 (edited) 19 hours ago, Saad said: I need to present the first five fields in a treeview. that one eludes me. what exactly do you mean by that? coding a treeview with known values should not be too hard. 19 hours ago, Saad said: I also need to present the whole file in a listview do you really need to display millions of records simultaneously? i doubt any human would find that useful. if you really do, there are solutions to that; but please try to explain exactly what is the user supposed to see and work with. now, if i understand correctly, the user is supposed to select which "Country","Profile","Region","Service" and "Source" they are interested to see, and your program should display the relevant file, with the rest of the fields? (or probably only the rest of the fields, because the first five are predetermined by the user). is this correct? if so, your solution is quite simple. create a single SQLite table with six columns, and for each input file, dump all rows but only the first five fields into that table, and in the sixth column, add the name of the file. so when the user selects the values for the first five fields, query the table to know which file to present, and load into memory only that file, to display in a listview, or suggest the user some additional filtering before displaying the file. one other, crude and simple but probably not the most efficient approach, is to simply dump the entire batch of files into a single table, with whatever columns required, leaving non-existent columns empty. then query that table according to users' needs. so, please be more detailed in your description of user interface, so we can suggest the better solution. a screenshot perhaps might prove helpful. Edited December 4, 2023 by orbs Signature - my forum contributions: Spoiler UDF: LFN - support for long file names (over 260 characters) InputImpose - impose valid characters in an input control TimeConvert - convert UTC to/from local time and/or reformat the string representation AMF - accept multiple files from Windows Explorer context menu DateDuration - literal description of the difference between given dates Apps: Touch - set the "modified" timestamp of a file to current time Show For Files - tray menu to show/hide files extensions, hidden & system files, and selection checkboxes SPDiff - Single-Pane Text Diff Link to comment Share on other sites More sharing options...
Saad Posted December 5, 2023 Author Share Posted December 5, 2023 @orbs Thank you so much for the valuable information. I have no issues parsing, reading, displaying the data in treeview or listview. You are correct with describing the user interface. It is a simple GUI with one treeview and one listview. When the user selects a treeview node, corresponds to the first five fields, the gui should present the user with records/lines for the chosen treeview node. *** I was told a file can have up to 500,000 lines but typically the file will have 2,000 to 5,000 lines and that what I will work with for now. Also, for now I'd like not use SQLite because MS SQL will be used in the feature. *** Does it make sense to create an array for each file? if yes, any suggestion on how to create dynamic arrays and keep track of them? Link to comment Share on other sites More sharing options...
orbs Posted December 5, 2023 Share Posted December 5, 2023 5 hours ago, Saad said: for now I'd like not use SQLite because MS SQL will be used in the feature. SQLite is a typical choice for simple apps, therefore i mentioned it, but merely as an example. of course any database will do the job. the point i'm trying to make is you should NOT load all files simultaneously to memory. only once the user has selected a file (by the five field criteria), that file is loaded and displayed. once the user is finished dealing with that file, dump the file from memory and load another one. as for displaying and working with a file, another option you have is not to load the file in your app, but summon the all-mighty Excel to do it for you. Excel is ideal for simple tabular displays (and for rather more complex ones also, if you are familiar with VBA), and you can pre-format a template to be visually appealing and functional for your needs. Excel user interface is highly - and i cannot stress "highly" enough - customizable. 5 hours ago, Saad said: I have no issues parsing, reading, displaying the data in treeview or listview. so, if you can manage the part where the user selects a file by the five field criteria, and you can display and work with the file, then what challenges are you still facing? Saad 1 Signature - my forum contributions: Spoiler UDF: LFN - support for long file names (over 260 characters) InputImpose - impose valid characters in an input control TimeConvert - convert UTC to/from local time and/or reformat the string representation AMF - accept multiple files from Windows Explorer context menu DateDuration - literal description of the difference between given dates Apps: Touch - set the "modified" timestamp of a file to current time Show For Files - tray menu to show/hide files extensions, hidden & system files, and selection checkboxes SPDiff - Single-Pane Text Diff Link to comment Share on other sites More sharing options...
junkew Posted December 6, 2023 Share Posted December 6, 2023 Saad 1 FAQ 31 How to click some elements, FAQ 40 Test automation with AutoIt, Multithreading CLR .NET Powershell CMDLets Link to comment Share on other sites More sharing options...
orbs Posted December 7, 2023 Share Posted December 7, 2023 On 12/4/2023 at 9:19 PM, orbs said: do you really need to display millions of records simultaneously? i doubt any human would find that useful. if you really do, there are solutions to that that's what i linked to in the above quote; not too obvious, apparently... still, this would come in handy once the program knows which file to load, with respective fields; and that is what the OP is asking, or so i think. Signature - my forum contributions: Spoiler UDF: LFN - support for long file names (over 260 characters) InputImpose - impose valid characters in an input control TimeConvert - convert UTC to/from local time and/or reformat the string representation AMF - accept multiple files from Windows Explorer context menu DateDuration - literal description of the difference between given dates Apps: Touch - set the "modified" timestamp of a file to current time Show For Files - tray menu to show/hide files extensions, hidden & system files, and selection checkboxes SPDiff - Single-Pane Text Diff Link to comment Share on other sites More sharing options...
Saad Posted December 18, 2023 Author Share Posted December 18, 2023 @orbs Thank you so much for your recommendations/suggestions. @junkew Thank you so much for your recommendations/suggestions. This looks promising. I'll give this a try in the couple of weeks and let you know the outcome. I got assigned to another project with a higher priority. Thanks again and have a great holiday. Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now