Reading a comma-separated value file

Besides data import/export, CSV files can be used for integration between systems. It is probably the most simple integration approach, when one system generates CSV files in some network folder and another one reads those files at specified intervals. Although this is not very sophisticated real-time integration, in most cases it does the job and does not require any additional components, such as Dynamics AX Application Integration Framework or something similar.

Another well-known example is when external companies are hired to manage the payroll. On a periodic basis, they send CSV files to the finance department, which are then loaded into the General journal in Dynamics AX and processed as usual.

In this recipe, we will learn how to read CSV file from code. As an example, we will process the file created in a previous recipe.

How to do it...

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

1. 1. In the AOT, create a new class named ReadCommaFile with the following code:

   class ReadCommaFile
   {
   }
   public static client void main(Args _args)
   {
   CommaTextIo file;
   container line;
   #define.filename(@'C:\Temp\accounts.csv')
   #File
   file = new CommaTextIo(#filename, #io_read);
   if (!file || file.status() != IO_Status::Ok)
   {
   throw error("File cannot be opened.");
   }
   line = file.read();
   while (file.status() == IO_Status::Ok)
   {
   info(con2Str(line, ' - '));
   line = file.read();
   }
   }
   

2. 2. Run the class to view the file's content, as shown in the following screenshot:

   

How it works...

As in the previous recipe, we first create a new file object using the CommaTextIo class. This time we use #io_read as the mode to make sure that the existing file is read only. We also perform the same validations to make sure that the file object is correctly created, otherwise we show an error message.

Finally, we read the file line by line until we reach the end of the file. Here we use the while loop until the file status becomes not IO_Status::OK, meaning we have reached the file end. Inside the loop, we call the read() method on the file object, which returns the current line as a container and moves the internal file cursor to the next line. File data is then simply output to the screen using the standard global info() function in conjunction with the con2Str() function, which converts a container to a string for displaying.

The last element of code, where the data is output, should normally be replaced by proper code that processes the incoming data.

There's more...

File reading, could also be executed in a similar way as file writing on a server to improve performance. The modifier client has to be changed to server, and code with the FileIoPermission class has to be added to fulfil the code access security requirements. The modified class should look similar to the following code:

class ReadCommaFileServer
{
}
public static server void main(Args _args)
{
CommaTextIo file;
container line;
FileIoPermission perm;
#define.filename('C:\\Temp\\accounts.csv')
#File
perm = new FileIoPermission(#filename, #io_read);
perm.assert();
file = new CommaTextIo(#filename, #io_read);
if (!file || file.status() != IO_Status::Ok)
{
throw error("File cannot be opened.");
}
line = file.read();
while (file.status() == IO_Status::Ok)
{
info(con2Str(line, ' - '));
line = file.read();
}
CodeAccessPermission::revertAssert();
}