Saving multiple large files in arrays

[Saad]

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

[Andreik]

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)

 

[Saad]

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?

[Andreik]

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

[orbs]

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

[Saad]

@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?

[orbs]

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?

 

 

 

[junkew]

 

[orbs]

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.

[Saad]

@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.