A customer maintenance form in Uniface 10, part 8 - a CSV export that Excel opens correctly

Sooner or later every business application gets the request "can I have that in Excel?". With the main menu from part 7 there is finally a place for functions that belong to no single form, and a CSV export is the natural first one.

The requirements were short:

• one line per customer, with the billing address if there is one,
• optionally including inactive customers (soft delete from part 5),
• opens in Excel with umlauts intact - this is a German customer base, so "Müller" must not become "Müller".

The result is a service EXPORT_SVC, a test service with ten tests, and one menu button. Along the way: a look at the bytes lfiledump actually writes, and a compiler error that turned out to be an argument limit.

One statement, not a loop of reads

The obvious Uniface way would be: retrieve the customer entity, and for every occurrence retrieve its addresses. That is n+1 reads and a lot of code for picking "the" billing address. SQLite can do it in one statement, and sql/data returns the whole result as a list that ProcScript can walk.

"The billing address" needs a definition, because a customer can have several. The rule: the billing address with the lowest ID, i.e. the first one entered. A correlated subquery in the join condition expresses exactly that:

SELECT c.CUSTOMER_ID, c.LAST_NAME, c.FIRST_NAME, c.EMAIL, c.PHONE,
       CASE WHEN COALESCE(c.IS_ACTIVE, 1) = 1 THEN 'Y' ELSE 'N' END,
       a.STREET, a.POSTAL_CODE, a.CITY, a.COUNTRY
FROM CUSTOMER c
LEFT JOIN CUSTOMER_ADDRESS a
  ON a.ADDRESS_ID = (SELECT MIN(b.ADDRESS_ID)
                     FROM CUSTOMER_ADDRESS b
                     WHERE b.CUSTOMER_ID = c.CUSTOMER_ID
                       AND b.ADDRESS_TYPE = 'BILLING')
WHERE COALESCE(c.IS_ACTIVE, 1) = 1
ORDER BY LOWER(c.LAST_NAME), LOWER(c.FIRST_NAME), c.CUSTOMER_ID

• LEFT JOIN keeps customers without any address - they get empty address columns.
• A customer with only a delivery address also gets empty columns, not the delivery address.
• COALESCE(IS_ACTIVE, 1) is the same "NULL counts as active" rule as everywhere else in the app. The WHERE line is left out when inactive customers are requested.
• CUSTOMER_ID as last sort key makes the order deterministic for two "Anna Müller".

The statement was run against a copy of the database first, before it went into any Uniface code.

The service

operation EXPORT_CUSTOMERS
params
    string pFileName : IN
    boolean pWithInactive : IN
    numeric pCount : OUT
    string pError : OUT
endparams
variables
    string vSql, vWhere, vData, vRow, vVal, vOut, vLine, vCsv, vNl
    numeric vStatus, vErrCode, vCol
endvariables
    pCount = 0
    pError = ""
    vNl = "%%^"
    if (pWithInactive)
        vWhere = ""
    else
        vWhere = " WHERE COALESCE(c.IS_ACTIVE, 1) = 1"
    endif
    vSql = "SELECT ... (the statement above, without WHERE and ORDER BY)"
    vSql = $concat(vSql, vWhere, " ORDER BY LOWER(c.LAST_NAME), LOWER(c.FIRST_NAME), c.CUSTOMER_ID")
    sql/data vSql, "CUSTOMERS"
    vStatus = $status
    vErrCode = $procerror
    if (vStatus < 0)
        pError = $concat("The customers could not be read (status ", vStatus, ", error ", vErrCode, ").")
        return -1
    endif
    vData = $result
    vCsv = "ID;Last name;First name;E-mail;Phone;Active;Street;Postal code;City;Country"
    forlist vRow in vData
        vLine = ""
        vCol = 1
        while (vCol <= 10)
            vVal = ""
            getitem vVal, vRow, vCol
            call ESCAPE_CSV(vVal, vOut)
            if (vCol = 1)
                vLine = vOut
            else
                vLine = $concat(vLine, ";", vOut)
            endif
            vCol = vCol + 1
        endwhile
        vCsv = $concat(vCsv, vNl, vLine)
        pCount = pCount + 1
    endfor
    vCsv = $concat(vCsv, vNl)
    lfiledump vCsv, pFileName, "UTF-8"
    vStatus = $status
    vErrCode = $procerror
    if (vStatus < 0)
        pError = "The file %%(pFileName) could not be written (status %%(vStatus), error %%(vErrCode))."
        return -1
    endif
    return 0
end

A few notes for readers who do not write ProcScript every day:

• sql/data puts the result in $result as a nested list: one item per row, each row a list of columns. forlist walks the rows, getitem picks a column by position.
• %%^ inside a string literal is a line break.
• $status and $procerror are copied into variables immediately. The next statement overwrites them, including the $concat that builds the error text.
• The separator is ;, not ,. German Excel expects a semicolon because the comma is the decimal separator.
• The caller gets a count and a readable error text, never a raw status code.

Quoting

A field that contains the separator, a double quote or a line break must be wrapped in double quotes, and quotes inside it are doubled - the usual CSV convention:

entry ESCAPE_CSV
params
    string pValue : IN
    string pOut : OUT
endparams
variables
    string vQ
endvariables
    pOut = pValue
    if (pValue = "")
        return 0
    endif
    vQ = $string(""")
    if ($scan(pValue, vQ) > 0 | $scan(pValue, ";") > 0 | $scan(pValue, $string("&uNL;")) > 0)
        pOut = $concat(vQ, $replace(pValue, 1, vQ, $concat(vQ, vQ), -1), vQ)
    endif
    return 0
end

The quote character comes from $string(""") instead of being written inside a string literal. That avoids any doubt about how the compiler treats a " inside "...". ESCAPE_CSV is a local entry; a thin public operation CSV_FIELD exposes it so the test service can reach it.

The compiler error that was an argument limit

The first version built the write error like this:

pError = $concat("The file ", pFileName, " could not be written (status ", vStatus, ", error ", vErrCode, ").")

The compiler answered:

1000 - Syntax error (wrong number of arguments)

Nothing was wrong with the syntax. $concat simply does not accept seven arguments in this version. The fix is the substitution syntax, which is more readable anyway:

pError = "The file %%(pFileName) could not be written (status %%(vStatus), error %%(vErrCode))."

Since then, every $concat in the project stays at five arguments or fewer, and longer texts use %%(variable).

What lfiledump really writes

"UTF-8" in lfiledump sounds unambiguous, but for Excel two details matter: whether there is a byte order mark, and which line ending is used. Rather than guess, the exported file was dumped byte by byte (od -c) after a test export with a customer called "Jürgen Müller":

0000000 357 273 277   I   D   ;   L   a   s   t       n   a   m   e   ;
...
0000100   e   ;   C   i   t   y   ;   C   o   u   n   t   r   y  \r  \n
0000120   1   4   ;   M 303 274   l   l   e   r   ;   J 303 274   r   g
0000140   e   n   ;   ;   ;   Y   ;   ;   ;   ;  \r  \n

• 357 273 277 = EF BB BF: a UTF-8 BOM at the start of the file. That is exactly what Excel needs to recognise the encoding when the file is opened by double-click.
• 303 274 = C3 BC: "ü" correctly encoded as UTF-8.
• \r\n: %%^ ends up as CRLF on Windows.

So no extra work was needed - but now it is a verified fact instead of an assumption. The file opened in Excel with "Müller" intact.

Tests against a real file

EXPORT_TST_SVC follows the pattern from part 3: each test calls a CHECK entry that writes PASS/FAIL via putmess, and the run ends with rollback, so the database is unchanged afterwards.

The export tests insert their own data with IDs that cannot collide with real customers:

• customer 999991 "Anna Testexport", active, with a delivery address (ID 999991) and a billing address Main St; Apt 1 (ID 999992) - the semicolon forces quoting, and the delivery address must not be picked;
• customer 999992 "Zora Testexport", inactive, no address.

Then the service writes a real file, the test reads it back with fileload and searches for the expected lines:

vExpected = $concat("999991;Testexport;Anna;[e-mail];;Y;", $string("""), "Main St; Apt 1", $string("""), ";12345;Teststadt;DE")
call CHECK("EXPORT line uses the billing address and quoting", ($scan(vContent, vExpected) > 0), vContent, pTests, pFailures)

The log of the run:

Export service tests started
PASS: CSV plain value unchanged
PASS: CSV empty value stays empty
PASS: CSV value with separator is quoted
PASS: CSV quotes are doubled
PASS: EXPORT active customers only
PASS: EXPORT line uses the billing address and quoting
PASS: EXPORT has a header line
PASS: EXPORT with inactive customers
PASS: EXPORT inactive customer marked N without address
PASS: EXPORT to a missing folder reports an error
Export service: 10 tests, 0 failures

The last test writes to a folder that does not exist and expects a negative status and a non-empty error text - the path a user would hit with a wrong configuration.

The menu button

In MAIN_MNU a new button "Export customers..." sits between "Addresses..." and "Exit":

entry DO_EXPORT
variables
    string vFile, vError
    numeric vCount
    boolean vWithInactive
endvariables
    askmess/question "Include inactive customers in the export?~Export customers", "Yes,No,Cancel"
    if ($status = 3 | $status = 0)
        return 0
    endif
    if ($status = 1)
        vWithInactive = 1
    else
        vWithInactive = 0
    endif
    vFile = "C:/Uniface_Projekte/Kundenverwaltung/export/customers_export.csv"
    activate "EXPORT_SVC".EXPORT_CUSTOMERS(vFile, vWithInactive, vCount, vError)
    if ($status < 0)
        message/error vError
        return -1
    endif
    message/info $concat(vCount, " customer(s) exported to ", vFile, ".")
    return 0
end

The file name is fixed for now. A file dialog would be nicer; it is on the list.

Compiler: EXPORT_SVC 0 errors, 1 warning (1016, the non-modeled dummy entity every service needs); MAIN_MNU 0 errors, 1 warning (1016).

Regression

After the export was in place, all three test services ran again:

Test service	Result
CUSTOMER_TST_SVC	35 tests, 0 failures
ADDRESS_TST_SVC	21 tests, 0 failures
EXPORT_TST_SVC	10 tests, 0 failures

The database was unchanged afterwards.

Takeaways

Let the database do the joining. One sql/data with a LEFT JOIN replaced what would have been a nested retrieve loop, and the rule "first billing address" is one readable subquery.

Verify bytes, not assumptions. Whether a file has a BOM or CRLF is a two-second od -c. Excel compatibility is decided at that level.

Test with data that is designed to fail. A billing address containing a semicolon, a customer that has only a delivery address, an inactive customer with no address at all - each one targets a specific line of the service.

Mind the small limits. $concat with seven arguments is a syntax error. %%(variable) substitution is shorter and has no such limit.

This is the last part that is fully implemented and tested. The next steps - login and roles, a change history, contacts and notes, import, a printable report and a backup - are designed and partly written as services, and they will get their own posts once they have passed the same kind of tests.