array descriptions - what's differ

[Albireo]

Thank you for your time and commitment.

...I try to simplify my questions on this forum. This means that the questions do not always show the depth of my wishes...

That's not always a good idea.

I do not agree.
To get a correct description of a wish, much information is needed about surrounding equipment / configuration / program / data structure etc .. and it is easy that the focus comes on everything that the reader does not know something about or understand.
When I ask a question, I usually try to get an example with AHK code, which often is possible to run.
The best (for me) had been to ask "straight to the point", but when my problem is complicated…
The description becomes too long, and no one can be able / have time to understand my desire.
When I have tried, nobody has answered my question / desire. (My conclusion is that no one have time to get involved in the issue - I understand).
I am happy when I get any answer in the right direction. (the problems are not always easy for me to describe).
Just describe a question is difficult. The question should be simple and at the same time describe everything. (I may not be so good at it)
When a question is asked and the answers begin to come,
1) I discover "flaws" in my description. eg. the headings do not come in sequence, article numbers can start with zero "0", not in sequense, certain text may need to be attached to quotation marks etc.
2) I discover solutions that I have not thought of.
For example: Understanding how the headings should be handled, in maybe 4 years - when the conditions are changed.

...You are processing different CSV files...

Yes! Most CSV files do not have a header line and some have - depending on how they are created.
Files with header and without header should be handled in the "same way".

You want to write an application able to handle all kind of files..

Will build one unique AHK-function() for each CSV-file. (all these should handle the data in the same way).

I can give two examples .:

---

Example1 .:

• The CSV file is checked for availability.
• The date on the CSV-file is checked, it is not too old.
• Check if the CSV file has a headline.
• etc.

• I want to know something about a user.
  1. Does the user exist?
  2. What's the name of the user?

Code: Select all

User = 205
SecCode = 8423
...
oUser := CheckUser(User, SecCode)
; or 
; oUser := CheckUser(User, SecCode, Computer, File)
If ( oUser = "NotFound" )
{	Error handling - User not found.
}
...
MsgBox % oUser.name     ; ( Clara Andersson )

ExitApp


CheckUser(UserName, UserSec, ComputerName := "BackStore", FileCSV := "c:\Temp\OPERATOR.csv")
{	; Analyze if the file has no headlines
	...
	; No headlines in the file - create
	; - - -   Backstore   -   Rubriker   i   OPERATOR.db   - - - - - - - - - - - - - - - - - - - - -
	If ( ComputerName = "BackStore" )
	{	; FronStore = FS & BackStore = BS var denna information finns
		aHead =
		( LTrim Comments
			"KeyName"	 | "opr_operator",	; (FS/BS) Nyckel till objektet - Rubrik används förmodligen inte (ex. 203)
			"Name"		 | "opr_name",		; (FS/BS) Användarnamn (ex. Frida Larsson)
			"Place"		 | "opr_department",	; (BS) Platsen användaren tillhör (ex. 200=Ort1, 300=Ort2 osv.)
			"Pass"		 | "opr_code",		; (FS/BS) Lösenord / Säkerhetskod (ex. 345)
			"Disc1"		 | "opr_discount",	; (FS/BS) Nivå på personalrabatt (ex. 20 = 20%)
			"Undefined1"	 | "opr_purchper",	; (BS) Används ej
			"Undefined2"	 | "opr_purchytd",	; (BS) Används ej
			"Level"		 | "opr_level",		; (FS/BS) Nivå på behörighhet i kassan (ex. 1-9) 5=normal 9=hög 
			"class"		 | "opr_class",		; (BS) Behörigheter (0-999) - 100 Butikssäljare, 200 Driftansvarig, 500 HK, 999 Administratör
			"Undefined3"	 | "opr_salesper",	; (BS) Används ej
			"Undefined4"	 | "opr_cardid",	; (BS) Används ej
			"Undefined5"	 | "opr_salesytd",	; (BS) Används ej
			"Date"		 | "opr_valid",		; (BS) Obehörig när datum (20190528) passerat
			"Disc2"		 | "opr_max_disc",	; (FS/BS) Max rabatt personen kan ge (50 => 50%)
			"Chip"		 | "opr_chipkey"	; (FS/BS) Om chip används vid t.ex. inloggning (vi använder inte)
		)
	}
	
	; - - -   FrontStore   -   Rubriker   i   OPERATOR.db   - - - - - - - - - - - - - - - - - - - - 
	If ( ComputerName = "FrontStore" )
	{	aHead =
		( LTrim Comments 
			"KeyName"	| "opr_id",		; (FS/BS) Nyckel till objektet - Rubrik används förmodligen inte (ex. 203)
			"Name"		| "name",		; (FS/BS) Användarnamn (ex. Frida Larsson)
			"Pass"		| "security",		; (FS/BS) Lösenord / Säkerhetskod (ex. 345)
			"Undefined1"	| "credit",		; (FS) Används ej
			"Level"		| "acc_level",		; (FS/BS) Nivå på behörighhet i kassan (ex. 1-9) 5=normal 9=hög
			"Disc1"		| "empl_discount",	; (FS/BS) Nivå på personalrabatt (ex. 20 = 20%)
			"Disc2"		| "discount",		; (FS/BS) Max rabatt personen kan ge (50 => 50%)
			"Chip"		| "chipkey"		; (FS/BS) Om chip används vid t.ex. inloggning (vi använder inte)
		)
	}
	; Some Code
	If !oOperator.HasKey(KeyValue)
	{	MsgBox  % "Error handling! `n`nThe User (" Key ") is NOT found!"	
		Return "Error - Not found"	 ; Return error message
	}
        ...
	Return Operator  ; Return some fields / information along with the object
}

The following values ​​are specified
To the function() .:

• User Code
• User SecurityCode
• Which Computer ( not required - normally )
• Which CSV-file ( not required - normally )

In the function() .:

• Create an object to use in the function - oName[Key(firstkolumn), HeaderName]
  
• I check if the user exist, if the usercode is correct and the user have permission to use the system.
  (otherwise, return an error message (has not decided if the error handling should be in the function or after the call to the function())
• The headings are managed based on which computer it is, and which program created the CSV files.
• If headers are in the CSV file, it is the same header name as in my middle column above.

From the function() .:

• Return the second object with some information about the user. eg. oUser.name
  To view the information - enter the name of the object and my header name (the first column above)
  eg. oUser.name or oUser.pass
• Or possibly, an error message about something that did not work in the function().

---

Example2 .:

• I want to know something about an article.
  1. Does the article exist?
  2. What is the barcode for an article?

Code: Select all

ArticleNo = 023456-7654
...
oArt := CheckArt(ArticleNo)
...
MsgBox % oArt.BarCode	; ( 7350053850019 )

ExitApp


CheckArt(ArtNo, ComputerName := "BackStore", FileCSV := "c:\Temp\ARTICLES.csv")
{
...
	; - - -   Backstore   -   Rubriker   i   ARTICLES.db   - - - - - - - - - - - - - - - - - - - - -
	If ( ComputerName = "BackStore" )
	{	...
	}
	
	; - - -   FrontStore   -   Rubriker   i   ARTICLES.db   - - - - - - - - - - - - - - - - - - - - 
	If ( ComputerName = "FrontStore" )
	{	
	}	
...
	Return Article
}

The questions look a little different and the result can also be more complex, but the handling of the headlines will be the same.

---

How do you distinguish which own header names shall be used for a specific file (with/without header)?

The short answer is, different functions for different tasks, but the handling of the headings will be the same.
Because I want to run the same AHK program with as few changes as possible, on several different computers, with CSV files of different structure. Then I felt that the handling of headlines becomes difficult.
I have tested some different solutions, but at present this management of the headings is easiest to handle for me, when all the headline information is in the same string.

This is where this link began - How to convert a string to an array.

[just me]

Hi Albireo,

I'm sorry, but the more you explain your goals the more I get confused.

Do you want to write special functions to get special information out of special CSV files? Or shall the functions return an array containg the information of the complete file?

[swagfag]

u could start with massively elaborating on:

Some of the [CSV] files to be analyzed do not have headers, so they must be added afterwards.
For a long time I was thinking how the headings in these cases would be handled. (now and in the future) [not relevant]
- The headings must be placed in exactly the same order, as in the CSV file to be analyzed. (can be 10 or 70 headers) [i can only assume this refers to when u parse a .csv file without headers. ur code needs to recognize the type of file being parsed and supply the correct headers for this particular type of file in the correct order]
- The columns must get the correct title name if other similar files with headings are to be compared.
- The columns in the CSV-file can change location. The headings must then also be changed. [what???? if i give u a .csv file without headers and shift the columns around, how would ur script possibly know which set of headers it needs to use to pair up with the .csv file, and more importantly how would it know what the correct order is supposed to be]
- The names of headers can be changed on the CSV-file. (doesn't happen often) [taking the next 2 points into account, "just use ur own headers", universally, all the time]
- Some headings are long and describe poorly what the column contains. (wishes own naming in some cases)
- When one of my programs runs with different configurations, CSV-result files get different headings, with the same content
(in that case I use my headers)
- The maintenance of these headings should be simple and intuitive.

the biggest wtf is the column reordering u mentioned. explain how u handle the case where they are reordered and no headers are provided. how do u identify which column is which

[Albireo]

... I'm sorry, but the more you explain your goals the more I get confused.

I'm probably not better at simply explaining my wish.

• - Input to the function(), (two - four values)
• - In the function() - Create headers, create first object with the CSV file and headers, and do some checks. Create the second object with other information and structure and return this (or an error message) from the function().
• - From the function() return one of these object to the AHK-program.

We "rub" only a small part into a larger structural image. The structure is one thing, but then comes the details.
Right now was an opportunity to improve the functioning of the AHK programs, I use today. (create object / easier to maintain, etc.)

Do you want to write special functions to get special information out of special CSV files?

No! not as I see it.
An example.
Suppose a user should send me a message.
When the user clicks on the message icon on the windows desktop.
The AHK program starts another program, which in turn creates a CSV file with user information from the database (without headers).
Now the user enters their user number and a security code. (in the AHK program).
Some checks are made that the user is present, may use the system and what permissions the user has. (This should be done in my AHK function()).
The user writes their message and press the "send" button.
The AHK program in this case creates an email to me with the message + some information Who sent the message / from which computer / date etc.
Certainly this wish could have been solved in many other ways.
for example, if I had been able to send SQL queries directly to the database with AHK. It would have been completely different - but I can't ...

… Shall the functions return an array containg the information of the complete file?

Not in this case. (but maybe in other cases I even want to return the item with all fields in the CSV file, from my function())
In this case, I just want to return the object with some information about the user.

---

... the biggest wtf is the column reordering u mentioned. explain how u handle the case where they are reordered and no headers are provided. how do u identify which column is which

To find out the original title names, I use a program that reads the database tables (several tables are involved), where I get to know the structure (heading / text field / numeric field, etc.).
Then that information must be handled manually by me in my AHK program. It is not often that changed but over the past 5 years it has changed three times (if I do not remember wrong). Every time it changes, I have to go through all the headings and manually change in the AHK program. Depending on which computer the AHK program is running on, the database structure looks different.

How do I know that the structure has been changed?
May have changed after a structural change in the database program or that my AHK program stops working.

[just me]

Well, once again:
> An example.
> Suppose a user should send me a message.
- OK
> When the user clicks on the message icon on the windows desktop.
- OK
> The AHK program starts another program, which in turn creates a CSV file with user information from the database (without headers).
- Which AHK program starts another program. What information does the CSV file contain? Only information about this special user? Would you please provide an example?
> Now the user enters their user number and a security code. (in the AHK program).
- OK
> Some checks are made that the user is present, may use the system and what permissions the user has. (This should be done in my AHK function()).
- OK, for some of the checks you need to read the CSV file.
> The user writes their message and press the "send" button.
- OK
> The AHK program in this case creates an email to me with the message + some information Who sent the message / from which computer / date etc.
- OK, but you will only need some information about this user and computer to do this job.
> Certainly this wish could have been solved in many other ways.
- Perhaps!

[swagfag]

The AHK program starts another program,

which program? u had .db in some of ur comments. is that an SQLite db? MSAccess? are u opening a DB viewer?

which in turn creates a CSV file with user information from the database (without headers).

how does it create the CSV file? does it just all the data into the CSV automatically? do u tell it what to do? u said u wished u could send SQL queries directly to the DB from AHK. do u write the queries in the program urself? if so, why would there ever be a case where u dont know how the columns in the result set are ordered? the query defines the order of the results! also, ahk can interface with DBs through https://github.com/Jim-VxE/AHK-Lib-ADOSQL

the problem of "how do i parse a CSV file so i end up with an AHK object that i can access like so: MyObject[rowIndex, columnName] / MyObject[3].firstName" was solved many moons and many threads ago, i feel like

if ure going with CSV parsing, the more sensible approach would be ignore any headers produced by the program and simply use ur own defined headers everywhere, all the time

the biggest wtf is the column reordering u mentioned. explain how u handle the case where they are reordered and no headers are provided. how do u identify which column is which

To find out the original title names, I use a program that reads the database tables (several tables are involved), where I get to know the structure (heading / text field / numeric field, etc.).
Then that information must be handled manually by me in my AHK program... Every time it changes, I have to go through all the headings and manually change in the AHK program. Depending on which computer the AHK program is running on, the database structure looks different.

then u have no real way of programmatically figuring out what the order is. u have to define the order urself

e: cant find the post(ironic, isnt it? since there have been so many already?) but this is the simplest way i can think of of explaining how to build the data structure(if u can even call it that) that ure trying to build, out of a csv file:

Code: Select all

csvFile = 
(
year,make,model
2006,Chevrolet,Express G1500
2006,Hyundai,Tucson GL
2006,Toyota,Sienna CE
)

MyHeaders := ["year", "make", "model"] ; which u manually define and whose order u manually define

MyObject := [] ; indexed array
Loop Parse, csvFile, `n, `r ; loop over ever line of the csv file
{
	csvRow := A_LoopField

	; if the first line of the csv file "looks" like a header, skip it
	if (A_Index = 1) && rowIsHeader(csvRow, MyHeaders)
		continue

	; otherwise, the row is filled with data we want to process
	RowData := {}
	Loop Parse, csvRow, CSV ; loop over every csv field, eg 2006 >>> Chevrolet >>> Express G1500
	{
		dataField := A_LoopField ; 2006
		headerField := MyHeaders[A_Index] ; year
		RowData[headerField] := dataField ; {"year": 2006, ...}  more to be inserted dureing the next iterations
	}

	MyObject.Push(RowData) ; [{"year": 2006, "make": "Chevrolet", "model": "Express G1500"}, ...]  more to be inserted dureing the next iterations
}

MsgBox % MyObject[2].make ; Hyundai

rowIsHeader(csvRow, Headers) {
	for each, field in Headers ; test every header field YOU have predefined...
		if (csvRow ~= "\b" field "\b") ; ... to see if the row contains at least one of these header fields
			return true

	return false
}

[Albireo]

...Which AHK program starts another program...

An AHK-program I have made before.

...What information does the CSV file contain? Only information about this special user?...

in this example, the CSV file contains only information about all our users.

Would you please provide an example?

Previously I have shown these headlines for two different systems (FrontStore / BackStore) But do not feel that this is so relevant, for my question, Other tables have other headings etc.

...for some of the checks you need to read the CSV file...

The first AHK-object would contain the whole CSV file. I thought of the structure of this object like this .:
oUser[UserCode, HeaderName]. Then the analysis is performed on the result. The answer from the analysis can give two results either some "problem", or an object with some information about the user like this aUser.name.
What is to be returned from the function() is an error message or the object aUser
(Has not considered whether it adds to any problem. - I want the return value as a string or an object ..)

...you will only need some information about this user and computer to do this job...

From one perspective is it so.

---

...which program? u had .db in some of ur comments. is that an SQLite db? MSAccess? are u opening a DB viewer? how does it create the CSV file? does it just all the data into the CSV automatically? ...

Right now I use a command-controlled program called table2text.exe (32-bit) to read the database tables. The database is an old 32-bit from Sage (Not a SQLite or MS Access). The only way I can communicate with the database is through ODBC. Not all SQL-questions work for me.

...would there ever be a case where u dont know how the columns in the result set are ordered?...

Sometimes changes / updates occur in the database program. New functions are implemented or headers get new functions. (nothing I can influence)[/quote]

ahk can interface with DBs through https://github.com/Jim-VxE/AHK-Lib-ADOSQL

Began to look a little on AHK-Lib-ADOSQL and also asked a question Read database tables with ODBC and SQL But got no response (I suspect the tables must be MS Access or similar).

...But this is the simplest way i can think of of explaining how to build the data structure...

You have a clear description of what happens, step by step in your example. (I like it - How did you get the result view?) but...
1) You don't have an unique field (a obvious Key-field in your example)
I always have an unique field as I want to use as Key in the object.

An example which would be more like my "input" is .:

Code: Select all

csvfile =
(
phone,name,town
01234,Carl,Stockholm
987-123,Cecilia,Gothenburg
56742,Emelie,Malmo
)

MyHeaders := ["Phone", "Name", "Town"]

2) The way to reach the result differs (although the same result is shown). You do it in this way .:

Code: Select all

MyObject[2].make ; Hyundai

I want to do it like this .: (I can't use your example - first field is not a unique field)

Code: Select all

; The first object oPhone
Phone = 987-123
HeadName = Name
oPhone[Phone,HeadName]   ; Cecilia
; And the second object aPhone - (The values is now only from the key value 987-123
aPhone.Phone = 987-123
aPhone.Name ; Cecilia
aPhone.Town ; Gothenburg

My goal is to handle a variable with the following structure oPhone[Phone,Town] or oPhone["987-123",Town] (not oPhone[2].Town)
and aPhone.Name (not aPhone[2]) - Although the result may be the same.
(I know my desire is more complicated to solve)

But to make it even more perfect, I want to have the opportunity to put my own headlines.
Suppose the headings in the database are similar to the following .: Header1,Header2;Header3 but I want Phone,Name,Town
For easy handling of these double headings, I have come to the conclusion that the best way is to create a string like this .:

Code: Select all

aHead =
( LTrim Comments
	Phone	| Header1	; A person's phone number
	Name	| Header2	; The name of a person
	Town	| Header3	; A city where the person lives
)

and the result object should be like this .: oPhone[Phone,Header3](the database headers) and aPhone.Name(my header)

[just me]

Right now I use a command-controlled program called table2text.exe (32-bit) to read the database tables.

If all CSV files are created by the same programm, why do some files include headers and others not?

(IMHO, you suppress all thoughts not matching your current concept.)

[Albireo]

Again, thanks for your commitment!

...If all CSV files are created by the same programm, why do some files include headers and others not? ...

Some exports, which are not time-critical, are made from the database program, from selected computers, several times a day, these have the title names in CSV export. These exports cannot be done on all computers and must be handled otherwise.
There are tables I cannot read with table2text.exe, and must be handled in other ways ...

Some imports do not require headings, while others require … The example in this thread does not generate any headings.
This means that the information I want must be handled differently depending on the computer.
One way is to do this to make different programs on different computers in different places ...
(maybe more easy, but this will be difficult to handle - remember what differences the computers require in case of changes / improvements)
My desire is to build functions and structures that work on all my computers.
If the object is easy to read / understand in the AHK code, debugging / changes can be easier to perform.

---

In the future, there will be about 10 different exports. Some timed and some on demand.
(Today it is not as many, but I realize the problems and feel how I want to maintain the AHK-programs)

---

I know that I will face other challenges (think about how the statistics can be handled in a smart way - but I will return to it in another thread. - I have to define which statistics I want and in what way. - is AHK fast enough to do this on request, or do the calculations have to be done at night?)

[swagfag]

for table2text.exe to work a working odbc driver and the correct connection string are required. since ure currently using table2text to export stuff, this must mean u have the correct connection string and also a working odbc driver. so why then can u not simply use adosql.ahk or hell even the plain ADO comobject to establish a connection, bypassing the need for jumping through all these unnecessary hoops, exporting, converting, parsing?

then u say some databases u dont have access to. i dont understand how u plan to realize ur dreams of keeping maintenance costs low. so far uve said the table columns can be reordered randomly at a whim, some have headers, some dont. ur onyl way of figuring out what the order is is looking at the tables! lol, seems like ull have to fill the headers in manually either way. thats anything but maintainable.

did u understand how the example i gave u works? to use a string as the key, create an object instead of an indexed array MyObject := {}, cache the field u want to use as a key(thats done inside the csv parsing loop), then use the cached variable as a key at the end instead of .Pushing (MyObject[myCachedKey] := RowData)

[Albireo]

Thank you for your dedication!

... so why then can u not simply use adosql.ahk or hell even the plain ADO comobject to establish a connection, bypassing the need for jumping through all these unnecessary hoops, exporting, converting, parsing?

Easy! I want, but did not get ADOSQL to work for me. I have no idea why. (maybe a 32/64-bit problem or ADOSQL tries to retrieve data directly from the tables, not through ODBC ....) In any case, I didn't manage to find out where it didn't work for me.
When I search for solutions for ODBC, all questions / answers seem to be old (often 4-5 years ...). Perhaps ODBC is not used so much today? (I found command-controlled - table2txt - and got a result - jippi ) or There are not many AHK programmers who can or use ODBC?. (For me, the result is the same)

...then u say some databases u dont have access to...

I have information and access to all database tables (username and password etc.) But I do not know how the structure looks, which tables are used in our configuration and so on (The database consists of 3-500 tables) I am only interested in about 10 of these database tables. When the tables become too large, Sage's own database program (PvxView) cannot view these. A few years ago our supplier got to rebuild one of the tables - create multiple tables from one database table. ("everything" was change). I think we will be able to make major changes within a year ... Maybe new tables / new headers - I have no idea....

...said the table columns can be reordered randomly at a whim, some have headers, some dont…

Yes!, and some files may have field separators "," ";" "TAB" or … and in Sweden (and some other countries) currencies are presented with "," (decimal comma) like this "15,50" or 1 200,00 or 1.200,00 … (and AHK only counts numbers with "." (decimal point) and no spaces ...)
My desire is to program a "funnel" to stop a CSV-file in the funnel. From the "funnel" comes a very specific result. (all above must be handled in some way).

did u understand how the example i gave u works?

Do you mean MyObject[2].make ; Hyundai? Yes!, but "2" say nothing to me - I have to look elsewhere to know what "2" means.

...to use a string as the key, create an object instead of an indexed array MyObject := {}, cache the field u want to use as a key(thats done inside the csv parsing loop), then use the cached variable as a key at the end instead of .Pushing (MyObject[myCachedKey] := RowData)...

You are right in the examples I have specified, because only "one thing" is searched for, and from the function only one object is returned. (or an error message). Have other ideas why I want to create an object with all fields from the CSV file - therefore this structure is desired. (right now).
Please send an example of your proposal. (the result I want, is still MyObject.MyHeader )
I might need to use that solution when processing the large CSV files. (maybe AHK is too slow?)