Help with conversion code - (Moved)

[Jokerman]

Hello everyone!

I have an application that is a conversion tool that converts address files into the correct formatting for 40+ different applications. In the process of converting I need to take into account specific requirements by the particular applications for how things are formatted both in terms of full name vs abbreviation, but also in exact spellings since many of the applications my tool supports don't follow a standard naming scheme (it would be cake if they all just used the ISO 3166:1 and 3166:2 standards!). To add to the fun my tool also supports importing from many of the applications so the function needs to be able to both accept and write multiple formats/spellings. Anyway, because of these particulars I've been running some conversion code that's functional - but long and ugly - and I really need to come up with a better way if at all possible. So far I have worldwide country conversion, as well as state conversion for 21 countries, and that include is already pushing 11k lines. In addition, by necessity my tool's supported conversions (both applications and countries' states) will continue to grow which will, obviously, only continue to make that include ever more unwieldy.

The code is pretty straightforward and simple. Not that it's probably necessary but I've included the function header for additional information. I'm open to any and all solutions that can be implemented without a need to actively pull the information from an outside source. Everything will be stored internally but I don't care whether that's in Arrays, JSONs, XML files, a DB accessed from memory/buffer, whatever (although that's my order of preference if I was able to choose 😉). Anyway, on to the code...

expandcollapse popup

; #FUNCTION# ====================================================================================================================
; Name ..........: _StateConvertAU
; Description ...: Converts the passed Australia state in $sState to the needed format
; Syntax ........: _StateConvertAU($sState [, $bAbbrev = False [, $sAppName = ""]])
; Parameters ....: $sState              - The state being processed as a String.
;                  $bAbbrev             - [optional] Whether to return the abbreviation instead of the full name as Boolean. Default is False.
;                  $sAppName            - [optional] The name of the app being processed as a String. Default is "".
; Return values .: State name/abbreviation as a String.
; Author ........: Jokerman
; Modified ......: 
; Remarks .......: 
; Related .......: 
; Link ..........: 
; Example .......: No
; ===============================================================================================================================
Func _StateConvertAU($sState, $bAbbrev = False, $sAppName = "")
    If $bAbbrev = Default Then $bAbbrev = False
    If $sAppName = Default Then $sAppName = ""
    
    Local $sStateAbbrevExample = "ACT"
    Local $sStateNameExample = "Australian Capital Territory"
    Switch StringUpper($sState)
        Case "ACT", "AUSTRALIAN CAPITAL TERRITORY"
            Switch $sAppName
                Case $g__sAppNameRLB
                    Return "ACT"
            EndSwitch
            If Not $bAbbrev Then
                Return "Australian Capital Territory"
            Else
                Return "ACT"
            EndIf
        Case "NSW", "NEW SOUTH WALES"
            Switch $sAppName
                Case $g__sAppNameRLB
                    Return "NSW"
            EndSwitch
            If Not $bAbbrev Then
                Return "New South Wales"
            Else
                Return "NSW"
            EndIf
        Case "NT", "NORTHERN TERRITORY"
            Switch $sAppName
                Case $g__sAppNameRLB
                    Return "NT"
            EndSwitch
            If Not $bAbbrev Then
                Return "Northern Territory"
            Else
                Return "NT"
            EndIf
        Case "QLD", "QUEENSLAND"
            Switch $sAppName
                Case $g__sAppNameRLB
                    Return "QLD"
            EndSwitch
            If Not $bAbbrev Then
                Return "Queensland"
            Else
                Return "QLD"
            EndIf
        Case "SA", "SOUTH AUSTRALIA"
            Switch $sAppName
                Case $g__sAppNameRLB
                    Return "SA"
            EndSwitch
            If Not $bAbbrev Then
                Return "South Australia"
            Else
                Return "SA"
            EndIf
        Case "TAS", "TASMANIA"
            Switch $sAppName
                Case $g__sAppNameRLB
                    Return "TAS"
            EndSwitch
            If Not $bAbbrev Then
                Return "Tasmania"
            Else
                Return "TAS"
            EndIf
        Case "VIC", "VICTORIA"
            Switch $sAppName
                Case $g__sAppNameRLB
                    Return "VIC"
            EndSwitch
            If Not $bAbbrev Then
                Return "Victoria"
            Else
                Return "VIC"
            EndIf
        Case "WA", "WESTERN AUSTRALIA"
            Switch $sAppName
                Case $g__sAppNameRLB
                    Return "WA"
            EndSwitch
            If Not $bAbbrev Then
                Return "Western Australia"
            Else
                Return "WA"
            EndIf
        Case Else
            Return SetError(1, $g__eError_UnknownState, $sState)
    EndSwitch
EndFunc ;==> _StateConvertAU()

Any help would be greatly appreciated! And if anyone has questions please don't hesitate to ask! At some point I'd like to share the conversion code as a UDF but I don't want to do that unless I know it's accomplishing its goal using the best method possible. Thanks again!

Jokerman

[JLogan3o13]

Moved to the appropriate forum, as the Developer General Discussion forum very clearly states:

Quote

General development and scripting discussions. If it's super geeky and you don't know where to put it - it's probably here.

---

Do not create AutoIt-related topics here, use the AutoIt General Help and Support or AutoIt Technical Discussion forums.

Moderation Team

[Jokerman]

Apologies @JLogan3o13, I figured since I was looking more for a method than an AutoIt-specific solution the other forum might have been the appropriate location. I guess I was wrong. 🙁

[JLogan3o13]

No worries, @Jokerman. By moving here, you will get more eyes on your topic; we have a large community of volunteers that are willing to provide input and assistance.

[rudi]

Hi.

 

[Apps]
; 0 = default behaviour
; 1 = always return Abrev
; 2 = always return Full

App1=0
App2=1
App3=2


[Codes]
Australian Capital Territory=ACT
NEW SOUTH WALES=NSW
NORTHERN TERRITORY=NT

expandcollapse popup

#include <Array.au3>
#include <Debug.au3>





$ReturnVal = StateConverter("NT")
ConsoleWrite('@@ Debug(' & @ScriptLineNumber & ') : $ReturnVal = ' & $ReturnVal & @CRLF & '>Error code: ' & @error & @CRLF) ;### Debug Console


Func StateConverter($_input, $Short = 0, $sAppName = "") ; Col 0 = Abrev, Col 1 = Full Name
    switch $short
        case 0,1
            $Col=$Short
        case else
            $Col=0
    endswitch
        
    $INI = "C:\Temp\CountryTable.INI"
    $Sect = "Codes"
    $aCodes = IniReadSection($INI, $Sect)
    _DebugArrayDisplay($aCodes)

    $row = _ArraySearch($aCodes, $_input, 1) ; searching Col 0 = Full Names
    If $row = -1 Then
        $row = _ArraySearch($aCodes, $_input, 1, 0, 0, 0, 1, 1) ; searching Col 1 = Abreviations
        If $row = -1 Then
            ; Neither Col 0 nor Col 1
            Return SetError(False, 1, $_input)
        EndIf
    EndIf
    $Sect = "apps"
    $default = 0
    $ForceValue = IniRead($INI, $Sect, $sAppName, $default) ; $default = 0 : Return what func call asked for. 1 = Always Abrev, 2 = Always FullName
    Switch $ForceValue
        Case 0
            $col = $Short
        Case 1
            $col = 1 ; Abbrev
        Case 2
            $col = 0 ; full name
    EndSwitch
    Return $aCodes[$row][$col]
EndFunc   ;==>StateConverter

 

Edited November 22, 2019 by rudi

[Nine]

15 hours ago, Jokerman said:

I'm open to any and all solutions that can be implemented without a need to actively pull the information from an outside source. Everything will be stored internally...

The solution proposed by @rudi required that you have an .ini outside file.  Take a look at FileInstall to pull the ini file from the compiled script into a temp folder that you can delete at script end.  If that doesn't work you could add a file containing your conversion codes as a resource here :

 

One other internal solution is to create a Global Const $Array[999][2] listing in it all the corresponding values of your conversion codes.

Edited November 22, 2019 by Nine

[Zedna]

I would store all codes as global variable with initial value in your include file before function _StateConvertAU() like this:

Global $g_Codes = _
    "ACT=AUSTRALIAN CAPITAL TERRITORY#" & _
    "NSW=NEW SOUTH WALES#" & _
    ... & _
    "NT=NORTHERN TERRITORY"

$g_Codes = StringSplit($g_Codes, "#")

Func _StateConvertAU($sState, $bAbbrev = False, $sAppName = "")
    ...
EndFunc ;==> _StateConvertAU()

 

Edited November 22, 2019 by Zedna

[rudi]

On 11/22/2019 at 2:30 PM, Nine said:

The solution proposed by @rudi required that you have an .ini outside file.  Take a look at FileInstall to pull the ini file from the compiled script into a temp folder that you can delete at script end.  If that doesn't work you could add a file containing your conversion codes as a resource here :

 

One other internal solution is to create a Global Const $Array[999][2] listing in it all the corresponding values of your conversion codes.

@Zedna Well, having an INI file *OUTSIDE* the exe (on some file server share) has the advantage, that it's possible to maintain the "ruling INI" file on that central location without the need to replace all the copies of the EXE file as required, if the "ruling data" are compiled inside the script.

 

CU, Rudi.

[Jokerman]

Thank you to all of you for your replies and suggestions! I apologize for having been gone for a while but I've been trying to complete v2 of our software and things have been insane. Things have calmed down a bit (for the moment, at least) so I have some time to look at this again.

I like the ideas (and I'm already using and loving ResourcesEx) but I'm not sure how I can incorporate them into the situation I'm dealing with. I'm afraid the example I gave may not have effectively put into perspective just how difficult the situation is. Here's an another example of our conversion functions - this one for Japan prefectures:

expandcollapse popup

Func _StateConvertJP($sState, $iProfileNum = -1, $bAbbrev = False, $sAppName = "")
    ; Setting passed variables if Default was passed
    If $iProfileNum = Default Then $iProfileNum = -1
    If $bAbbrev = Default Then $bAbbrev = False
    If $sAppName = Default Then $sAppName = ""
    
    ; Setting $bAbbrev to false for g__sAppNameA since the rest of the states besides JP are abbreviated
    If $sAppName = $g__sAppNameA Then $bAbbrev = False
    
    ; For the main Switch we need to remove extra spaces from $sState (because of apps like $g__sAppNameO and $g__sAppNameP)
    ; and force to upper for matching
    Switch StringUpper( StringStripWS($sState, $STR_STRIPLEADING + $STR_STRIPTRAILING + $STR_STRIPSPACES) )
        Case "JP-23", "AICHI", "AICHI-KEN", "AICHI KEN", "愛知県", "愛知"
            Switch $sAppName
                Case $g__sAppNameA, $g__sAppNameB, $g__sAppNameC, $g__sAppNameD, $g__sAppNameE, $g__sAppNameF
                    Return "Aichi"
                Case $g__sAppNameG, $g__sAppNameH
                    Return "aichi"
                Case $g__sAppNameI, $g__sAppNameJ
                    Return "Aichi-ken"
                Case $g__sAppNameK, $g__sAppNameL, $g__sAppNameM, $g__sAppNameN
                    Return "愛知県"
                Case $g__sAppNameO, $g__sAppNameP ; g__sAppNameO and g__sAppNameP need a space in front of the Kanji
                    Return " 愛知県"
                Case $g__sAppNameQ ; Needed because g__sAppNameQ requires both formats but in different fields
                    If $bAbbrev Then
                        Return "JP-23"
                    Else 
                        Return "愛知県"
                    EndIf
            EndSwitch
            If Not $bAbbrev Then
                Return "Aichi"
            Else
                Return "JP-23"
            EndIf
        Case Else
            Return SetError(1, $g__eError_UnknownState, $sState)
    EndSwitch
EndFunc ;==> _StateConvertJP()

I only included a single prefecture but I think this code better demonstrates just how complicated things are with all the apps we're trying to support conversion for. Some apps require a particular format, some apps need both abbreviations and full names depending on the field. It's really a nightmare. On the upside, because I've used Switch statements throughout the conversion code it's very fast - it just sucks to create and maintain. 😆

Honestly, I don't know if there's a good answer for what I'm trying to accomplish but what I do know is I certainly haven't been able to come up with a good alternative. Thank you again for the suggestions already provided, as well as any possible solutions anyone might come up with in the future! 🙏

[jchd]

Given the flexibility for storing that many variants and possibly a significant volume of data, I'd go for an SQLite database.  It would support huge data, as many and complex tables you'll require, extremely powerful querying facilities and portability across any hadware/software platform you can think of.

[Jokerman]

That's an interesting approach! Unfortunately, I'm not really a database guy so I can't picture how I would best utilize a database for this purpose. Would I be storing a separate conversion for each country/state supported by each application? Or would each individual country be stored in a single table which is queried for all applications? Also, I was trying to avoid having an external file associated with the conversion but I suspect using SQLite would require just that. While that isn't a deal breaker I have to ask about security. Not being a database guy my questions are: How secure is SQLite and/or what measures need to be put in place to make it secure? And if database encryption is employed what kind of a hit does it have on performance (compared to SQLite w/o encryption as well as strictly AutoIt code)?

Btw, I have read some of the benefits of using SQLite with AutoIt for quite a few things (many of them by or because of you, jchd!) but I haven't yet delved into those waters yet. It's a little intimidating, and thinking in terms of databases hasn't clicked for me yet. Any advice? Or good example code I can go look at?

Thanks again for taking your time out to read and reply!

[jchd]

It's way too early to provide any design advice since we have little clue about the actual set of requirements in your use case.

There is a version of SQLite which allow strong encryption (see https://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki), rendering the file --an SQLite DB is just a single file-- completely unintelligible to anyone not having state-size resources. Its enssentially transparent and shouldn't be a cycle hog.

What you can do to avoid having a separate file is make it a resource of your program, extract it to use it, eventually changing it, then storing it back as a new resource in place of the initial one.  It's a bit circonvoluted but that should work.

The first step for you is to exhaustively list all entities that you'll be dealing with, their type, nature and their relationships.  Then list the queries that you know you'll use most.  Post a few examples of all these.

[Jokerman]

The version which allows strong encryption sounds like a great fit. Obviously, it isn't necessary for the purposes of country and state conversion but it could be key for future feature additions we've been thinking about. I love that it's essentially transparent since, as you know, the single task nature of AutoIt tends to make any type of stall very visible (unless accounted for with something like a progress animation, of course).

I get the idea of extracting and re-integrating the database as a resource but I don't think I can take advantage of the idea in our case. Unfortunately, I believe storing a modified database back into the executable will invalidate the digital signature on our executable. However, taking our future plans into account, and that the DB can be encrypted, I think storing the DB externally will be just fine.

I'll give a high level view of the current process that data goes through in order to help give some additional insight into how the conversion code is being used. Currently, the way things work is the user imports their data using a standard template which is imported as either XLSX or tab delimited. When imported our software does some validation on the data and converts all countries and states (among other fields) to our internal default of abbreviations to optimize memory and time (I make sure all the Case lines for my Switch statements begin with the abbreviation). All user data is currently held internally in 2D arrays. Once the user selects which apps they want to export to and hit Next our software starts the conversion. At this point we simply loop through the apps 1 by 1, and for each app loop through the profiles 1 by 1, simultaneously a) converting the data to the preferred format for the app currently being converted, and b) placing the formatted data into the correct places in the output variable (for most apps it's a nested array representing a JSON, but for a few it's a simple 2D array if the app needs delimited or XML). For most of the apps we support I have a nested array of configuration data that tells our software how to process that app like which fields need what data and how that data should be formatted. Each of those app processing variables includes which fields are countries or states and whether they need to be full names or abbreviations. When our software reaches one of those fields it basically just plugs the configuration elements from that sub-array into the conversion function passed variables and the conversion function returns the proper format.

Right now my main state conversion functions each support a single country and depend on 3 main passed variables - the state being converted, the name of the app currently being converted (in order to determine the correct formatting in case the app has "special needs" like $g__sAppNameA through $g__sAppNameQ above), and $bAbbrev indicating whether to return the abbreviation or the full name. This is the main functionality I want to recreate using another method. I also have a country conversion function to return the correct format using the same 3 passed variables (except country name rather than state name). In addition, I have 2 more functions named _SetStateByCountry() and _SetStateAndCountry() which use the same 3 variables but add 1 more to have both the country and the state passed - these work by calling the already described conversion functions and do exactly what they say. All functions work using Return values except _SetStateAndCountry() which uses ByRef for both the country and state.

I don't know if that's what you were looking for but I hope it at least gives you some of the information you need. My apologies for being so database illiterate that I'm not sure how to provide what you're looking for but I've never had a need for databases in the past. Or, probably more accurately, I never knew I had a need for databases in the past. 🤣

[jchd]

OK.  It took me some time to partly digest this.  I focus on the actual data you'll be storing and querying.  The rest of your program workflow is something completely separate.

So from what I gathered, you have to store at least countries, their states and potentially many abbreviations for them.
Alongside you also have to deal with a list of apps (whatever that means) and the preference they have set for conversion of the above data.

I'd advise that you download a good 3rd-party SQLite DB manager, like SQLite Expert (the freeware version offers a lot of features without nagware, adware nor anything).  Take some time to tour the thing, play with examples.  That tool will allow you to easily setup DB schemas (with some help), try them on real-world or example data, execute queries and determine what to change until the result is satisfactory w.r.t. your context.

A database is organized in tables (think "mahematical sets", an SQL table has no intrinsic order😞 a number of rows (like in a spreadsheet) and one or more columns which store attributes for the row.  A row must have a unique ID, generally a meaningless unique integer (somehow similar to the name or memory address of a variable).  You may have to create one or more indices to speed up searching the data in the table.

A typical DB contains several tables, most of the time related by strong relationships and satisfying some constraints.  The goal of a DB schema (its design) is to enforce those constraints and relationships in all cases.  For instance a genealogy DB must satisfy that noone is it's own parent, noone is his sister parent, that everyone has one male and one female parent... Even on what seems a trivial example, you can feel that "parent" and "parenthood" has to be made rigourous notions to deal with real-world cases (adoption vs biological parents, gamete donation, gay parents, surrogate mother, transgender, whatelse).  If you don't precisely enough define the entities and their relationships, your DB is going to make you very sorry some day.

Countries: the ISO codes (ISO2 & ISO3, i.e. US & USA, but some countries don't have an ISO3 code assigned) are good candidates to identify a country.  Yet, some countries aren't recognized in some contexts!  More fuzzy are country names: there are several "official standards" to choose from and this gets worse with country names translations: is my country France, Frankreich, Frànkrich, Frankrijk, Francia, Francià, França, Γαλλία, Fransän, Франция, საფრანგეთი, کومرٱ ڤلات فرانسٱ , whatelse?

Depending on your use, you'll have to store translations of country names elsewhere from fixed columns of the country table for as many languages or standard of your choice, or choice of the apps you deal with.  That needs to be precisely defined as you'll see below with examples.  Countrynames (nicknames) are stored in a separate table, each with a link to their row in country table.  This machinery is the opposite to data pointers in many IT languages: here, the child "points" to the parent.  This is called a foreign key in DB world.  'Foreign' in that it links an entry in one table to one entry in another table, here the country table.

States within a given country: a state has to be in relationship with one and only one country, even if one can find states with the same name in distinct countries.  In some countries "region" has the same meaning as "state in the US".  A state will carry a unique ID (say an integer), one name or abbreviation or translation AND a key to a country (the ID of the country, also an integer).  This is a many to one relationship (names to countries).

Now if you'll have to handle countries and/or states/regions in several languages, you'll find it much easier to have created table linking a name with a unique country (another foreign key).  This way you can search this table for "Франция" and using the foreign key, find that it refers to FR (ISO2) or FRA (ISO3).
If you choose a wrong design and store alternative/translated names or abbreviations in columns of the country table, you'd have to search every such column separately, involving more work and more maintenance if things evolve.  OTOH, with such a separate table you can instantly list all know names of a country, or all states/region in a country, all with one fast query.

String are stored internally as Unicode (as you can see in table currencies).  You can search case-sensitively or not, use wildcards % and _ (equivalent for DOS * and ?) and even use add-on functions like fuzzy search or regex (compatible with AutoIt PCRE).  Trying to do the same with arrays is going to be messy and painful.

I'm joining an example toy DB.  Countries table derived from my own, countrynames in a restricted number of languages and conventions, currencies linked by foreign-key from the country table, statesnames in another table.

As examples, you can list all the countrynames like '%anm%' by using the Name header of the countrynames table in the Data tab,
or by using the following query in the SQL tab and hitting F5:
select * from countrynames where countryname like '%anm%';

To find the ISO2 code and intl phone prefix for cyprus or chyprus or chypre or ..., and check that there is no ambiguity on the name (only one prefix should result):
select distinct phoneintl from countries join countrynames using (countryid) where countryname like 'c%pr%';

Find a list of official ENiso names of countries using currencyISO 'AUD':
select countryname from countrynames join countries using (countryid) join currencies using (currencyid) where currencyISO = 'AUD' and countrynamelang = 'EN' and countrynametype = 'ISO';

Obviously this example isn't any close to what you actually need but it should give you a global idea about where you're heading if you follow this route and how powerful the beast can be (and that's only the surface).  Not every piece of data is complete in every table, I've rushed that from several pieces floating around.

The table countrynames includes one extra unpopulated column: countrynametype.  Feel free to populate these with some data of your own, countrynametype allowing you to classify what kind of name or abbreviation the row contains.

States/regions/provinces use yet another table with foreign key to countries table.  StateNames table gives alternate names or attributes to official states listed in States table.

List all states of Australia with their codes and French names, order by statecode ascending:
select distinct statecode, statename as FRname from countries join countrynames using(countryid) join states using (countryid) join statenames using (stateid) where countryname like 'australia' and statenamelang = 'FR' order by statecode

You can have dozens or hundreds tables in a DB, large DBs can have hundreds thousand and reach 100 TB.
Don't be afraid by syntax, I know it seems weird at first: it's always clearer and shorter than code which would produce the same result!
SQL language allows you to precisely describe what you want, not how to get there.

Abbrevs.sq3

 

Sorry folks for the text wall.

 

[Jokerman]

Wow! Thank you for all the info and advice, jchd! I don't mind the wall of text, particularly when it's pack with as much good stuff as yours. 😲

Luckily, I do have a little bit of experience with databases (including SQL) so while I have a hard time coming up with the layout and design myself I think I understand (pretty well, anyway lol) what you've described. I've already read through your post a couple of times and downloaded the SQLite Expert installer and your example DB. I think I'm going to simply need to do some playing with what you've given me and add on from there to see what I come up with. I figure once I come up with the final design then I'll create a brand new DB based on the final design so I start from a clean DB after I know not to do the ridiculous stuff I'll no doubt start off doing. 🤣

Thank you, again, for the detail and incredible amount of information you provided, jchd! I believe I have a serious amount of work and learning ahead of me!

[jchd]

Perfect. Play with this early Xmas toy and think how the global idea can fit your use case for storage/querying best.  I didn't have enough clues about your actual requirements so my tentative may focus too much on language for example but ignore other aspects important to you; again I set this up quite fast as a basic sample.

Be aware that there are a lot more features in SQLite, like FTS tables (Full Text Search), virtual tables and support for add-on functions.  The joined archive contains one extension of mine (unifuzz), which offers several string functions to help dealing with Unicode, like fuzzy search (Typos), Latin languages unaccentuation, language-agnostic collations and more.  The C source is included.  The DLL is 32-bit but I know others have compiled it for x64.  You can "auto-load" this extension in Expert so it's always transparently available.  Another useful extension is regex, a PCRE implementation 100% compatible with what's behind AutoIt StringRegex().  I also have extensions providing math functions, hash (MD5) and others, but those aren't going to be very useful to you right now.  Use x86 versions of SQLite Expert to use that stuff out of the zip.

Don't hesitate to ring my bell for help in designing your schema or any other aspect of SQLite.  The SQLite website is also of great help.  Also remember that SQLite is by far the most widespread RDBMS in the world ever, one of the most ubiquitous piece of software along with curl and ziplib and runs on any hardware/software platform you can think of and any SQLite DB file is portable on every implementation.  In fact you unknowingly use SQLite daily: you smart TV, GPS, smartphone, modern modem/router, tablet, PC, applications (Firefox, Win 10, all Adobe, ...) all use SQLite with probability > 98%.

unifuzz.zip

sqlite3-pcre.zip