以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 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 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
4. 导出带双加号的csv文件，UTF-8无Bom格式，可批处理使用。
private void ExportCust()
{
TextBuffer textBuffer;
str FileName,FilePath, File;
CustTable custTable;
FileIOPermission fioPermission;
int Lines;
str custAccount;
str data;
System.Text.Encoding encoding = new System.Text.UTF8Encoding(false);
#File
;
File = @'C:\Tmp\accounts.csv';
try
{
fioPermission = new FileIOPermission(File ,"RW");
fioPermission.assert();
textBuffer = new TextBuffer();
while select firstOnly custTable
{
textBuffer.appendText('"' + custTable.AccountNum + '",');
textBuffer.appendText('"' + strReplace(custTable.name(), "\n", "") + '",');
textBuffer.appendText('"' + strReplace(custTable.address(), "\n", "") + '"');
textBuffer.appendText("\n");
}
Lines = textBuffer.numLines();
info(textBuffer.getText());
data = textBuffer.getText();
textBuffer.toFile(File,FileEncoding::UTF8);
CodeAccessPermission::revertAssert();
try
{
new InteropPermission(InteropKind::ClrInterop).assert();
System.IO.File::WriteAllText(File, data, encoding);
CodeAccessPermission::revertAssert();
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;
}
}
Wishs to help you.
