Axapta Lessons: Export a table to a file

[Blog bot]

Источник: http://axapta-lessons-learned.blogsp...e-to-file.html
==============
Here is a job that enable us to dump any kind of table in a file, at least that is the objective. It might be it is not working for system tables. What is special to this job is that no reference to the table and/or fields is hardcoded in the job.
I am using an approach which I call dynamically instantiating a table object.

When the job runs it requires the following input:


Output File Name
Exported records log file
Not exported records log file
Field delimiter
Table Id to export

The TableId is the ID of the table as can be found in the properties of a table.

If all goes well at the end of the job a file is created with the specified name containing the table records. My file is a CSV file which I import in excell.

I will show you the job and explain some lines of code.
The first part is just the declaration part where the dialog and other objects are declared.



The second part establishes access to the files using the dialog as seen above.


I am using the method openFile() on the class SysDataIntegration to simplify a few things in accessing files. The method openFile() on the class SysDataIntegration deals with setting properties on objects of class type AsciiIo.

The method requires three parameters:
* FileName
* Mode
* Delimiter

The first parameter FileName is the name of the file on your machine that need to be accessed (created or read).

The second parameter Mode is the mode in which the file should be opened. These are the modes used by the new method on IO Classes (CommaIo, Comma7Io, BinaryIo, AsciiIo). Specify "R" for read, "W" for write, "A" for append (implies "W"), "T" for translate (text), "B" Binary.

The third parameter is the delimiter used to separate fields in records accessed by Io Classes. The method will use the delimiter to set the inFieldDelimiter and outFieldDelimiter on the object it creates.

Looking at the Io Classes you will notice the record delimiter is not defined as a parameter on the method openFile. The properties inRecordDelimiter and outRecordDelimiter are default set to ('\r\n') by the method.

Note: The reason for handling the outputFile differently is to catch the error that is thrown when the file is in use. Maybe there is another way but this goes beyound what I am trying to accomplish. You can leave it out and use the method openFile() from the class SysDataIntegration. You will not be told there is something wrong with the file but instead the program will stop running. The statement "if (outputFile)" which will see later is managing this.

In the last two parts of the job I will write the records to the file.
First a line of text containing all the field names is written to the file and thereafter the records are written to the file. I am using a container to store the field names or values. The content of the container is then written to the file.



A while back someone told me how to access table records from the class DictTable. This is what I have named dynamically instantiating a table object.

Now I am going to instantiate an object of type DictTable using the method makeRecord(). This method works similar as the method makeObject() does for classes. See the class DictClass for the method makeObject(). See the class DictTable for the method makeRecord(), but note the explanation is a bit confusing as it says "Create an empty record for this table".

I am instantiating an object of type DictTable for the table specified in the dialog window. Next, and maybe this could be done differently, leaving the tempTable out and using the dictTable, I instantiate another object tempTable for the table specified in the dialog.

Once this is done I loop through all the fields on the table using a for-loop.
An object of type dictField is instantiated for each field. This makes it possible to access the properties of the field. The name property is stored in the container using "line = consins(line, cntFlds, dictField.name()).

Note: The arraySize loop is to handle the array elements of extended data types especially dimensions. In case of the LedgerTable all dimensions are exported seperately this way. Here the field names are exported and later the values are exported.

In the last part I am retrieving the values of the fields using a while select loop on common. Common is a representation of the specified table. All field values in the common records are stored in the previously mentioned container.


At the end of the loop the container is written to the file. Meanwhile I implemented two counters to track the number of records exported and those not exported.

In the last lines of code the counter values are reported.



In my case the table exported was the Country table (TableId 45) and contained 18 records.


[End of Lesson]

You can find the Job as a text file on the Axapta Knowledge Village group/commuity in Files > Members > XPO - Jobs.


==============
Источник: http://axapta-lessons-learned.blogsp...e-to-file.html