使用XmlPort导入CSV至BC Gen. Journal Line表时过账日期报错排查

问题场景 #

尝试将以下CSV文件通过XmlPort导入Business Central的Gen. Journal Line(81)表时，报错提示Posting Date must have a value in Gen. Journal Line: Journal Template Name=, Journal Batch Name=, Line No.=0. It cannot be zero or empty.，但CSV中明明包含过账日期值。

CSV内容 #

"GENERAL","DEFAULT","TLPUK0099","","","95.460","01/01/2018"
"GENERAL","DEFAULT","TLPUK0099","Revalued Source","","-5.370","31/01/2018"
"GENERAL","DEFAULT","TLPUK0099","Revalued Source","REVERSING ENTRY","5.370","01/02/2018"
"GENERAL","DEFAULT","TLPUK0099","Revalued Source","","-8.500","28/02/2018"
"GENERAL","DEFAULT","TLPUK0099","Revalued Source","REVERSING ENTRY","8.500","01/03/2018"
"GENERAL","DEFAULT","TLPUK0099","Revalued Source","","-5.760","10/10/2019"
"GENERAL","DEFAULT","TLPUK0099","Revalued Source","","-5.760","10/10/2019"
"GENERAL","DEFAULT","TLPUK0099","Revalued Source","REVERSING ENTRY","5.760","01/05/2019"
"GENERAL","DEFAULT","TLPUK0099","Revalued Source","REVERSING ENTRY","5.760","01/05/2019"

原XmlPort代码 #

xmlport 50000 XmlPortGenJrnl
{
    Format = FixedText;
    Direction = Import;
    TextEncoding = UTF8;
    UseRequestPage = false;
    TableSeparator = '';

    schema
    {
        textelement(Root)
        {
            tableelement(GenJournalLine; "Gen. Journal Line")
            {
                XmlName = 'GenJnlLine';
                fieldelement(JournalTemplateName; GenJournalLine."Journal Template Name") { }
                fieldelement(JournalBatchName; GenJournalLine."Journal Batch Name") { }
                fieldelement(AccNum; GenJournalLine."Account No.") { }
                fieldelement(Description; GenJournalLine.Description) { }
                fieldelement(DocumentNo; GenJournalLine."Document No.") { }
                fieldelement(Amount; GenJournalLine.Amount) { }
                fieldelement(PostingDate; GenJournalLine."Posting Date") { }
            }
        }
    }

    var
        myInt: Integer;
}

问题原因 #

1. 格式类型错误：XmlPort设置为FixedText（固定宽度文本格式），但目标文件是逗号分隔的CSV，应该使用DelimitedText格式。FixedText会按固定宽度拆分字段，导致字段映射完全错位，过账日期字段根本没读到对应的值。
2. 缺少文本限定符配置：CSV字段用双引号包裹，未指定TextQualifier = '"'，解析时会把引号当作字段内容的一部分，导致字段值异常。
3. 日期格式不匹配：CSV中的日期是DD/MM/YYYY格式，默认解析逻辑无法识别，需明确指定格式字符串。
4. 金额格式未适配：CSV中的金额带有三位小数（如95.460），未配置对应数值格式可能导致解析错误。

修复后的XmlPort代码 #

xmlport 50000 XmlPortGenJrnl
{
    Format = DelimitedText;
    Direction = Import;
    TextEncoding = UTF8;
    UseRequestPage = false;
    TableSeparator = '\r\n'; // 按行分隔记录
    FieldSeparator = ',';     // 字段分隔符为逗号
    TextQualifier = '"';      // 文本限定符为双引号

    schema
    {
        textelement(Root)
        {
            tableelement(GenJournalLine; "Gen. Journal Line")
            {
                XmlName = 'GenJnlLine';
                fieldelement(JournalTemplateName; GenJournalLine."Journal Template Name") { }
                fieldelement(JournalBatchName; GenJournalLine."Journal Batch Name") { }
                fieldelement(AccNum; GenJournalLine."Account No.") { }
                fieldelement(Description; GenJournalLine.Description) { }
                fieldelement(DocumentNo; GenJournalLine."Document No.") { }
                fieldelement(Amount; GenJournalLine.Amount) 
                {
                    FormatString = '#,##0.000'; // 适配三位小数的金额格式
                }
                fieldelement(PostingDate; GenJournalLine."Posting Date") 
                {
                    FormatString = 'dd/MM/yyyy'; // 匹配CSV的日期格式
                }
            }
        }
    }

    var
        myInt: Integer;
}

额外注意事项 #

• 若日期仍解析失败，可在OnBeforeInsertRecord触发器中手动转换日期字符串，适配系统区域设置。
• 检查金额字段正负号的识别逻辑，必要时在触发器中调整数值符号。

内容的提问来源于stack exchange，提问作者NickSO