从AX2012系统批量生成CSV格式数据

以CSV格式导出数据,大家应该都很熟悉,最近遇到导出CSV格式的数据格式,这里分享下。导出CSV格式的数据,我这里总结了三种:

1.以逗号分隔的csv文件(字段内容无双引号,不推荐用,若导入数据值中有逗号,容易错列)

Public boolean AX3PLExport_Cust_Bth()
{
    TextBuffer              textBuffer;
    str                     FileName,FilePath, File;
    CustTable               custTable;
    FileIOPermission        fioPermission;
    int                     Lines;
    str                     custAccount;
    #File
    ;
	
    FilePath    = "C:\Test"	;
    Filename    = Test.csv;
    File  = FilePath + '\\\\' + Filename;

    try
    {
        fioPermission = new FileIOPermission(File ,"RW");
        fioPermission.assert();

        textBuffer  = new TextBuffer();
        textBuffer.appendText("CustAccount,");
        textBuffer.appendText("CustName,");
        textBuffer.appendText("CustAddress");  
        textBuffer.appendText("\n");
		
        ttsBegin;

        while select  forupdate custTable
            index hint AccountIdx
            where custTable.Transferred == NoYes::No
              &&  custTable.AccountNum
        {
            textBuffer.appendText(custTable.AccountNum); //textBuffer.appendText('"' + custTable.AccountNum + '",'); 可手动加双引号
            textBuffer.appendText(strReplace(custTable.name(), "\n", "")); 
            textBuffer.appendText(strReplace(custTable.address(), "\n", "")); 
            textBuffer.appendText("\n");
        }
		
        ttsCommit;
        Lines = textBuffer.numLines();
        try 
        {               
            if (textBuffer.toFile(File,FileEncoding::UTF8))  
                info( strfmt("File Generated as %1.total insert %2 Lines",File,Lines));  
        }  
        catch ( Exception::Error )  
        {  
            error (strFmt("No Data export for MainProducts",File));  
        }    
        CodeAccessPermission::revertAssert();
    }
    catch(Exception::Deadlock)
    {
        retry;
    }
    if (Lines > 0)
    {
        return true;
    }
    else
    {
        return False;
    }
}

导出格式如下:若需要导出待双引号的csv格式,可参考代码注释的地方,强行加入双引号,但这样就比较繁琐了  

2. 导出双加号的csv文件,UTF-8格式(带BOM的的UTF-8)

Public boolean HSO_AX3PLExport_CustCSV_Bth()
{
    CommaTextIo             commaTextIo;
    container               line;
    str                     FileName,FilePath, File;
    CustTable               custTable;
    FileIOPermission        fioPermission;
    int                     Lines;
    str                     custAccount;
    #File
    ;

    FilePath    = "C:\Test" ;
    Filename    = "Test.csv";
    File  = FilePath + '\\\\' + Filename;
	
    try
    {
        fioPermission = new FileIOPermission(File ,"RW");
        fioPermission.assert();

        commaTextIo  = new CommaTextIo(File, #io_write, 65001);//65001代表UTF-8的编码格式,默认是带Bom的UTF-8

        if (!file || commaTextIo.status() != IO_Status::Ok)
        {
            throw error("File cannot be opened.");
        }

        ttsBegin;

        while select forUpdate custTable
            where  custTable.Transferred == NoYes::No
             &&    custTable.AccountNum
        {
             line = [custTable.AccountNum,
                    strReplace(custTable.name(), "\n", ""),
                    strReplace(custTable.address(), "\n", "")];

            commaTextIo.writeExp(line);
            Lines++;
        }

        ttsCommit;

        try 
        {               
            if (File)  
                info( strfmt("File Generated as %1.total insert %2 Lines",File,Lines));  
        }  
        catch ( Exception::Error )  
        {  
            error (strFmt("No Data export for MainProducts",File));  
        }         
        CodeAccessPermission::revertAssert();
    }
    catch(Exception::Deadlock)
    {
        retry;
    }
}

导出的格式如下。这样的格式大多数情况下就满足了我们的需求。但假如是导出的接口数据,某些奇葩系统没法解析,只认不带BOM的UTF-8格式就不好用了。

 

 3. 导出双加号的csv文件,UTF-8格式(不带BOM的的UTF-8)

 

Public boolean HSO_AX3PLExport_CustCSV_Bth()
{
    CommaTextIo             commaTextIo;
    container               line;
    str                     FileName,FilePath, File;
    CustTable               custTable;
    FileIOPermission        fioPermission;
    int                     Lines;
    str                     custAccount;
    System.Text.Encoding    encoding = new System.Text.UTF8Encoding(false);
    #File
    ;

    FilePath    = "C:\Test" ;
    Filename    = "Test.csv";
    File  = FilePath + '\\\\' + Filename;

    try
    {
        fioPermission = new FileIOPermission(File ,#io_write);
        fioPermission.assert();
        commaTextIo  = new CommaTextIo(File, #io_write, 65001);
        if (!file || commaTextIo.status() != IO_Status::Ok)
        {
            throw error("File cannot be opened.");
        }
		
        ttsBegin;
        while select forUpdate custTable
            where  custTable.Transferred == NoYes::No
             &&    custTable.AccountNum
        {
             line = [custTable.AccountNum,
                    strReplace(custTable.name(), "\n", ""),
                    strReplace(custTable.address(), "\n", "")];

            commaTextIo.writeExp(line);
            Lines++;
        }        
        CodeAccessPermission::revertAssert();
        commaTextIo = null;
        ttsCommit;
        
        new InteropPermission(InteropKind::ClrInterop).assert();
        System.IO.File::WriteAllText(File, System.IO.File::ReadAllText(File), encoding);
        CodeAccessPermission::revertAssert();

        try 
        {               
            if (File)  
                info( strfmt("File Generated as %1.total insert %2 Lines",File,Lines));  
        }  
        catch ( Exception::Error )  
        {  
            error (strFmt("No Data export for MainProducts",File));  
        }  
       
    }
    catch(Exception::Deadlock)
    {
        retry;
    }
}

结果如下:

第三种方法详情可参考这篇博客:

https://community.dynamics.com/ax/f/microsoft-dynamics-ax-forum/220574/how-to-export-to-csv-file-in-utf-8-without-bom

 

Wishs to help you.

转载于:https://www.cnblogs.com/sunny-technology/p/11585786.html

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值