« Back

How to read CSV, TXT, XLS or SQL Files in ADOxx?

Combination View Flat View Tree View
Threads [ Previous | Next ]
toggle
How to read CSV, TXT, XLS or SQL Files in ADOxx?
community documentation adoscript constructs xml-adl importexport
Answer
27/03/14 11:03
How to read external CSV, TXT, XLS or SQL Files in ADOxx? and How to set the values to the model attributes  of ADOxx?

RE: How to read CSV, TXT, XLS or SQL Files in ADOxx?
Answer
04/02/14 10:33 as a reply to Fadi.
ADOxx provides possibilities to read content from files and databases to be included in the modelled information (object or model level). Below, code snipplets are provided to show how read from text, CSV, XLS and DB sources can be realized. Below for all sources the generic approach and an example implementation is provided.

TEXT
ADOxx has standard functionality for reading text files.
The contents of files can be read using the command call FREAD from the "AdoScript" messageport.
The syntax of the command call is:

1CC "AdoScript" FREAD file: filename [ binary: boolValue ] [ base64: boolValue ] .
2--> RESULT ecode: intValue text: strValue .
You can read the contents of a text file in a string variable using the above command call and then parse the resulting text according to its format and your needs.

EXAMPLE - TEXT
In the example below, the text file contains a list of <name>:<value> pairs separated by a blank space (" ") representing the configuration for a fictive server, also attached to this post

1IP-address:192.168.0.1 Port:8080 Username:testuser Password:testpass

The following code snippet reads the contents of the file and saves the values of the respective attributes of an object:

 1CC "Core" GET_ATTR_VAL objid: (idMyObjID) attrname: ("Configuration File")
 2     # --> RESULT ecode: intValue val: strValue
 3
 4CC "AdoScript" FREAD file: ( val )
 5 # --> RESULT ecode: intValue text: strValue .
 6SET sTxtData: ( text )
 7
 8FOR sParameter in: (sTxtData) sep: " " {
 9    SET sName: (token(sParameter,0,":"))
10    SET sValue: (token(sParameter,1,":"))
11    CC "Core" SET_ATTR_VAL objid: (idMyObjID) attrname: (sName) val: (sValue)
12     # --> RESULT ecode: intValue
13}

The example above assumes that the object ID has been determined beforehand and set to the variable "idMyObjID" and the parameter names in the text file are the exact names of the attributes (IP-address,  Port, Username, Password)

CSV
If the text file has a CSV-format, the rows are separated by the charracter "\n" and within a row, the columns are separated by ";". As an example, the following CSV data can be read in the string variable sCsvData and then parsed line by line:
11;6;11
22;7;12
33;8;13
44;9;14
55;10;15

EXAMPLE - CSV

 1CC "AdoScript" FREAD file: ("sample.csv")
 2# --> RESULT ecode: intValue text: strValue .
 3SET sCsvData: ( text )
 4
 5SET nRow:0
 6SET nColumn:0
 7FOR sRow in: (sCsvData) sep: "\n" {
 8  SET nColumn:0
 9  FOR sCell in: (sRow) sep: ";" {
10    CC "AdoScript" INFOBOX ("Row: " + STR nRow + ", Column: " + STR nColumn + ", Value: " + sCell)
11    SET nColumn: (nColumn + 1)   
12  }
13  SET nRow: (nRow + 1)
14}
The example above does not update the model, but reads the file and provides INFOBOX output. This output can be replaced by SET_ATTR_VAL statements to update objects for each line.

XLS
If the data is stored in XLS files, it can be accessed by using the command CALL for calling functions stored in the DLL file "ado2Xls.dll". The DLL file is included in the ADOxx 1.3 UL1 package and can be found in the folder <ADOxx_folder>\tools\misc\ .
The function AdoXslFetchText is used for reading the data from an XSL file. The function AdoXslCloseAll() is used for closing all XSL files previously opened for reading withe the function AdoXlsFetchText.

EXAMPLE - XLS
 1SET sDLLpath: ("tools\\misc\\ado2Xls.dll")
 2SET sXSLfile: ("C:\\Temp\\test.xls")
 3SET sXSLsheet: ("T1")
 4SET sXSLcell: ("2,2")
 5
 6CALL dll: (sDLLpath) function:"long AdoXlsFetchText( char *strsource, char *strcell, char **pstrtext )"
 7              strsource: (sXSLfile + ">" + sXSLsheet) strcell: (sXSLcell)
 8
 9CC "AdoScript" INFOBOX ("The value of the cell located at row 2, column 2 in the sheet T2 is: " + pstrtext)
10
11CALL dll: (sDLLpath) function:"long AdoXlsFetch( char *strsource, char *strcell, char **pstrvalue )"
12              strsource: (sXSLfile + ">" + sXSLsheet) strcell: (sXSLcell)
13
14CALL dll: (sDLLpath) function:"long AdoXlsCloseAll()"


DB
If the data is stored in a database, it can be read using the functions stored in the DLL file "ado2Odbc.dll", also included in the ADOxx 1.3 UL1 package and located in the folder <ADOxx_folder>\tools\misc\ .
Since the communication with the server is a bit more complex than reading from a file, several functions are required:
AdoOdbcConnect   - for connecting to the database
AdoOdbcFetch     - for reading a single value from the database
AdoOdbcExecute   - for executing a query that returns more than one value
AdoOdbcFetchNext - for retrieving the next data record from the database; is always used after calling the AdoOdbcExecute function

EXAMPLE - DB
 1SET sDLLpath: ("tools\\misc\\ado2Odbc.dll")
 2SET sODBCconnection: "testdb"
 3SET sODBCusername: "testuser"
 4SET sODBCpass: "testpass"
 5
 6CALL dll: (sDLLpath) function:"long AdoOdbcConnect( char *strdbparam, char **pstrdbinst )"
 7        strdbparam: (sODBCconnection + " " + sODBCusername + " " + sODBCpass)
 8SETL sDbHandle: (pstrdbinst)
 9
10SET sSelect: ("SELECT ID, Name, address FROM testdb01 WHERE ID>1")
11
12CALL dll: (sDLLpath) function:"long AdoOdbcFetch( char *strdbinst, char *strselect, char **pstrresult )"
13        strdbinst: (sDbHandle) strselect: (sSelect)
14#Only returns the first cell from the first row resulting from the SELECT statement       
15
16CALL dll: (sDLLpath) function:"long AdoOdbcExecute( char *strdbinst, char *strselect, char **pstrcolumns )"
17              strdbinst: (sDbHandle)
18              strselect: (sSelect)
19              # --> pstrcolumns (list of column names separated by ASCII #128)
20
21SET eCode:0
22SET sResults:""
23WHILE ( eCode = 0 ) {
24    CALL dll: (sDLLpath) function:"long AdoOdbcFetchNext( char *strdbinst, long bmaskresult, char **pstrresult )"
25        strdbinst: (sDbHandle) bmaskresult:0
26     # --> pstrresult
27    SET eCode: ( result )
28    SET sResults: (sResults + "\n" + pstrresult)
29}
30
31CC "AdoScript" EDITBOX text: (sResults) title: "Results..."
32
33CALL dll: (sDLLpath) function:"long AdoOdbcClose( char *strdbinst )"
34          strdbinst: (sDbHandle)
Attachments: EXAMPLE DATA.csv (0.0k), EXAMPLE DATA.txt (0.1k), EXAMPLE DATA.xls (13.5k), scriptDBread.asc (1.7k), scriptXLSread.asc (0.6k)