Skip to main content

CSV

CSV is still the most common format for file-based exchange: EDI partners, warehouse systems, banks, and legacy applications almost all speak it. It is the fastest format to parse and produces the smallest files — at the cost of having no data types and no nesting.

Classes (in the DEVCommon model, usable outside the integration framework too):

Reading uses Microsoft.VisualBasic.FileIO.TextFieldParser rather than TextIO, which cannot handle a delimiter inside a quoted value or a line break inside a value. TextFieldParser handles both and ships with D365FO, so no external reference is needed.

Reading a file​

The reader loads the whole file into a container, then you walk it row by row. Configuration (delimiter, quoting, date format) must be set before openFile():

DEVFileReaderCSV fileReader = new DEVFileReaderCSV();

fileReader.parmInFieldDelimiter(';');      // default is ','
fileReader.parmIsIgnoreCSVQuotes(false);   // false = honour "quoted,values"
fileReader.setDateFormat('yyyyMMdd');      // how date columns are formatted

fileReader.openFile(messageTable.getFileStream());
fileReader.readHeaderRow();                // consumes row 1 and maps column names

while (fileReader.readNextRow())
{
    staging.clear();
    staging.MessageId   = messageTable.RecId;
    staging.MainAccount = fileReader.getStringByName('MainAccount');
    staging.Amount      = fileReader.getRealByName('Amount');
    staging.insert();
}

readCSVFile(stream, delimiter) is a shorthand for setting the delimiter and opening in one call.

Typed getters​

One of the big advantages of these classes is that they allow reading columns by name rather than by a fixed number. readHeaderRow() maps the header text to column numbers, so getStringByName('MainAccount') finds the column wherever it sits — the file can gain a column, or have its columns reordered, and the code still works. An unknown name throws and says which one.

Every getter therefore exists in two flavours: ...ByName('ColumnName') for a file with a header row, and ...ByIndex(n) (1-based) for a fixed layout with no header.

MethodReturnsNotes
getStringByNamestrTrimmed
getRealByNamerealEmpty cell returns 0
getIntByNameint64
getDateByNamedateParsed with setDateFormat; without it, the current culture is used
getDateTimeByNameutcdatetime
getEnumByName(name, enumNum(NoYes))intThrows with the enum name and the bad value if the text does not match
getCurRow / getCurRowDataint / containerRow number and the raw row — useful in error messages

Because every CSV value is text, dates and numbers are converted on read. Pass the format string the file uses, and the reader converts date columns with it:

fileReader.setDateFormat('yyyyMMdd');

Without a format the server culture decides how a date string is read.

Optional columns​

getColumnIdByName throws when a column is missing — that is what makes a wrong file fail loudly. For genuinely optional columns, test first:

if (fileReader.isColumnExists('Description'))
{
    staging.Description = fileReader.getStringByName('Description');
}
note

readNextRow() returns false when the first three columns of a row are all empty, so a blank row inside the data ends the loop.

A more complex example​

EDI-style files often have no header row at all. Instead, column 1 carries a record type and the meaning of the remaining columns depends on it: H for the order header, D for a line, S for a summary. Read those by index, and keep track of which header the lines belong to:

DEVFileReaderCSV fileReader = new DEVFileReaderCSV();
fileReader.parmInFieldDelimiter('|');
fileReader.parmIsIgnoreCSVQuotes(true);   // partner sends raw text, quotes are data
fileReader.setDateFormat('yyyy-MM-dd');
fileReader.openFile(messageTable.getFileStream());   // note: no readHeaderRow()

int lineNum;

while (fileReader.readNextRow())
{
    lineNum++;
    messageProcessResult.parmProcessPrefix(strFmt("Reading line %1", lineNum));

    str recordType = fileReader.getStringByIndex(1);

    switch (recordType)
    {
        case 'H':
            if (headerStaging.RecId)
            {
                throw error("The file contains multiple headers, this is not supported");
            }
            headerStaging.clear();
            headerStaging.MessageId          = messageTable.RecId;
            headerStaging.PartnerStoreCode   = fileReader.getStringByIndex(3);
            headerStaging.CustPurchaseOrder  = fileReader.getStringByIndex(4);
            headerStaging.RequestedShipDate  = fileReader.getDateByIndex(5);
            DEV::validateWriteRecordCheck(headerStaging);
            headerStaging.insert();
            break;

        case 'D':
            linesStaging.clear();
            linesStaging.MessageId   = messageTable.RecId;
            linesStaging.HeaderRecId = headerStaging.RecId;
            linesStaging.ItemBarCode = fileReader.getStringByIndex(2);
            linesStaging.SalesQty    = fileReader.getRealByIndex(3);
            linesStaging.Price       = fileReader.getRealByIndex(4);
            DEV::validateWriteRecordCheck(linesStaging);
            linesStaging.insert();
            break;

        case 'S':
            headerStaging.selectForUpdate(true);
            headerStaging.TotalQtyFile = fileReader.getRealByIndex(2);
            headerStaging.update();     // validate against the sum of lines later
            break;
    }
}

parmProcessPrefix puts "Reading line 42" in front of any error raised inside the loop, so the message log points at the offending row.

Capture the summary record even if you do not need its values — comparing the partner's totals with the lines you parsed is a simple way to detect a truncated file.

A file that arrives sometimes with a header row and sometimes without is handled the same way — detect the header text in column 1, call readHeaderRow(false) (which maps the current row without advancing), and switch to ...ByName getters:

if (fileReader.getStringByIndex(1) == 'LineType')
{
    fileReader.readHeaderRow(false);
    readAsHeaders = true;
    continue;
}

Writing a file​

Bulk export​

A periodic export class extends DEVIntegExportBulkBase and gets the CSV plumbing from the base class. The delimiter and file name come from the Outbound message type, so they can be changed without a deployment:

class DEVIntegTutorialExportBulkInventOnhand extends DEVIntegExportBulkBase
{
    public void exportData()
    {
        container lineData;
        InventSum inventSum;

        this.initCSVStream();     // opens the stream with the configured delimiter

        this.writeHeaderLine(['Company', 'ItemId', 'InventLocationId', 'AvailPhysical']);

        while select crosscompany sum(AvailPhysical) from inventSum
            group by ItemId, InventLocationId, DataAreaId
            where inventSum.AvailPhysical
        {
            lineData = [inventSum.DataAreaId, inventSum.ItemId,
                        inventSum.InventLocationId, inventSum.AvailPhysical];
            this.writeDataLine(lineData);
        }

        this.sendFileToStorage();
    }
}

Full class: DEVIntegTutorialExportBulkInventOnhand.

MethodWhat it does
initCSVStream()Opens the output stream using the message type's delimiter
writeHeaderLine(container)Writes a row without counting it as data
writeDataLine(container)Writes a row and increments the line counter shown in the Export log
sendFileToStorage()Delivers to SFTP / Azure File Share — or to your browser on a Test run
sendFileToStorageNotEmpty()Same, but skips delivery when no data lines were written

Values are written with writeExp, which quotes any value containing the delimiter, so ordinary text is safe without escaping.

Controlling how values look​

When a partner specification says "amount with two decimals, dot separator", format the value explicitly instead of passing a real — otherwise the user's regional settings decide how the file looks:

this.writeDataLine(["D",
    num2str(custInvoiceTrans.LineNum, 1, 0, 0, 0),          // integer, no separators
    barCodeStr,
    custInvoiceTrans.itemName(),
    num2str(custInvoiceTrans.Qty, 1, 2, 1, 0),              // 2 decimals, dot, no thousands
    num2str(custInvoiceTrans.LineAmount, 1, 2, 1, 0)]);

The same applies to dates — build the exact string the partner expects rather than relying on date2str defaults.

Several record types in one file​

Writing an EDI document is the mirror image of reading one: a header row, the lines, then a summary row with counts and totals the receiver can check.

void exportRecord(CustInvoiceJour _custInvoiceJour)
{
    int  lineCounter;
    Qty  totalQty;

    this.writeDataLine(["H", "ContosoUSA", _custInvoiceJour.InvoiceId, invoiceDateStr]);

    while select custInvoiceTrans
        order by LineNum
        where custInvoiceTrans.ParentRecId == _custInvoiceJour.RecId
    {
        lineCounter++;
        totalQty += custInvoiceTrans.Qty;
        this.writeDataLine(["D", num2str(custInvoiceTrans.LineNum, 1, 0, 0, 0), /* ... */ ]);
    }

    this.writeDataLine(["S",
        num2str(lineCounter, 1, 0, 0, 0),
        num2str(_custInvoiceJour.InvoiceAmount, 1, 2, 1, 0),
        num2str(totalQty, 1, 0, 0, 0)]);
}

Full class, including the incremental logic that exports only new invoices: DEVIntegTutorialExportBulkCustInvEDIInc.

Practical notes​

  • Delimiter. When the file does not use commas, construct the reader yourself and call parmInFieldDelimiter before openFile. Pipe (|) is the friendliest choice for new interfaces — it almost never occurs in data.
  • Quotes as data. Some partners send raw text where a " is a literal character (inches, dimensions). parmIsIgnoreCSVQuotes(true) turns quote handling off; the delimiter must then never appear in a value.

Tutorials​