Comma-separated value (CSV) files are widely used across various systems. Although nowadays modern systems use XML formats for data exchange, CSV files are still popular because of the simplicity of their format. Normally, the data in the file is organized so one line corresponds to one record and each line contains a number of values normally separated by commas. Record and value separators could be any other symbol depending on the system requirements.
I have been successfully using CSV files for custom data migration tasks from/to Dynamics AX when the standard Data export/import utility is not enough. Speaking about Dynamics AX Data export/import utility, I have to mention that besides Binary and Excel formats, it can also handle CSV files.
In this recipe, we will learn how to create a custom comma-separated file from code. We will export a list of ledger accounts—account number and name.
Open AOT, and create a new class called CreateCommaFile with the following code. Replace
<documents>
with your own path (use double backslashes for folder separation, i.e.\\
):class CreateCommaFile { } public static client void main(Args _args) { CommaIo file; container line; LedgerTable ledgerTable; #define.filename('<documents>\\accounts.csv') #File ; file = new CommaIo(#filename, #io_write); if (!file || file.status() != IO_Status::Ok) { throw error("File cannot be opened."); } while select ledgerTable { line = [ ledgerTable.AccountNum, ledgerTable.AccountName]; file.writeExp(line); } }
Run the class. A new file called
accounts.csv
should be created in the specified folder. Open that file with Notepad to view the results:
In the variable declaration section of the main()
method of the CreateCommaFile class along with other variables we define a name for the output file. Normally, this should be replaced with a proper input variable. Here, we also define a standard #File
macro which contains a number of file-handling modes like #io_read
, #io_write
, #io_append
, etc., file types, delimiters, and other things.
Next, we create a new CSV file by calling new()
on a standard CommaIo class. It accepts two parameters—filename and mode. For mode, we use #io_write
from the #File
macro to make sure a new file is created and opened for further writing. If a file with the given name already exists, then it will be overwritten. To make sure that a file is created successfully, we check if the object file
exists and its status is valid, otherwise we show an error message.
In multilingual environments, it is better to use CommaTextIo. It behaves the same way as CommaIo does plus it supports Unicode, which allows us to process data with various language-specific symbols.
And finally, we loop though the LedgerTable table, store its account number and name fields into a container, and write them to the file using writeExp()
.
In this way, we create a new comma-separated value file with the list of ledger accounts.
You probably already noticed that the main()
method has a client
modifier, which forces its code to run on the client. When dealing with large amounts of data, it is more effective to run the code on the server. To do that, we need to change the modifier to server
. The class below generates exactly the same file as before, except that this file is created on the folder on the server's file system:
class CreateCommaFileServer { } public static server void main(Args _args) { CommaIo file; container line; LedgerTable ledgerTable; FileIoPermission perm; #define.filename('<documents>\\accounts.csv') #File ; perm = new FileIoPermission(#filename, #io_write); perm.assert(); file = new CommaIo(#Filename, #io_write); if (!file || file.status() != IO_Status::Ok) { throw error("File cannot be opened."); } while select ledgerTable { line = [ ledgerTable.AccountNum, ledgerTable.AccountName]; file.writeExp(line); } CodeAccessPermission::revertAssert(); }
Make sure you replace <documents>
with your own folder on the server. Use double backslashes for folder separation, i.e. \\
.
File manipulation on the server is protected by Dynamics AX code access security and we must use the FileIoPermission class to make sure we match the requirements. That's why we have to call the following code before opening the file to assert permissions:
perm = new FileIoPermission(#Filename, #io_write); perm.assert();
and the following code after we completed all file operations to make sure we clear assertions: