Search code examples
dynamics-ax-2009x++axapta

Perform Excel "Text to Columns" action in CSV file using X++

In Excel you can open a CSV file and click on "Text to Columns" (under "Data" tab) to have a column delimited file.

Is there any way to simulate this using X++? A class that would open the CSV file and apply the exact same Excel "Text to Columns" action?

Solution

  • You can use the InFieldDelimiter method of the CommaIo class to use the correct delimiters.

    Or use str2con to split a string.

    You can even read an Excel file using ExcelIo.

    Update: As your file is not CSV use str2con:

    for (con = commaIo.read(); commaIo.status() == IO_Status::Ok; con = commaIo.read())
{
    lineNum++;
    if (lineNum > 1 && conlen(con))
        con = str2con(conPeek(con,1));
    info(str2con(con));
}

    This may not remove quotes and trailing spaces, do this for interesting string fields:

    purchId = strRtrim(strRem(conPeek(con,3),'"'));