Creating a comma-separated value file

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.

In this recipe, we will learn how to create a custom comma-separated file from code. We will export a list of ledger accounts—the CSV format.

How to do it...

Carry out the following steps in order to complete this recipe:

1. 1. Open the AOT, and create a new class named CreateCommaFile with the following code:

   class CreateCommaFile
   {
   }
   public static client void main(Args _args)
   {
   CommaTextIo file;
   container line;
   MainAccount mainAccount;
   #define.filename(@'C:\Temp\accounts.csv')
   #File
   file = new CommaTextIo(#filename, #io_write);
   if (!file || file.status() != IO_Status::Ok)
   {
   throw error("File cannot be opened.");
   }
   while select MainAccountId, Name from mainAccount
   {
   line = [
   mainAccount.MainAccountId,
   mainAccount.Name];
   file.writeExp(line);
   }
   info(strFmt("File %1 created.", #filename));
   }
   

2. 2. Run the class. A new file named accounts.csv should be created in the specified folder. Open that file with Notepad or any other text editor to view the results:

   

How it works...

In the variable declaration section of the main() method of the newly created CreateCommaFile class, we define a name for the output file, along with other variables. 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, such as #io_read, #io_write, #io_append, and so on, file types, delimiters, and other things.

Next, we create a new CSV file by calling the new() method 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. In order to make sure that a file is created successfully, we check if the file object exists and its status is valid, otherwise we show an error message.

In multilingual environments, it is better to use the CommaTextIo class. It behaves the same way as the CommaIo class does plus it supports Unicode, which allows us to process data with various language-specific symbols.

Finally, we loop though the MainAccount table, store all account numbers and their names in a container, and write them to the file using the writeExp() method.

In this way, we create a new comma-separated value file with the list of ledger accounts.

There's more...

You probably already noticed that the main() method has the 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. In order to do that, we need to change the modifier to server. The following class generates exactly the same file as before, except that this file is created in the folder on the server's file system:

class CreateCommaFileServer
{
}
public static server void main(Args _args)
{
CommaTextIo file;
container line;
MainAccount mainAccount;
FileIoPermission perm;
#define.filename('C:\\Temp\\accounts.csv')
#File
perm = new FileIoPermission(#filename, #io_write);
perm.assert();
file = new CommaTextIo(#filename, #io_write);
if (!file || file.status() != IO_Status::Ok)
{
throw error("File cannot be opened.");
}
while select mainAccount
{
line = [
mainAccount.MainAccountId,
mainAccount.Name];
file.writeExp(line);
}
CodeAccessPermission::revertAssert();
info(strFmt("File %1 created.", #filename));
}

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.

Finally, we call CodeAccessPermission::revertAssert() to revert the previous assertion.