Hi,
Is there a way to import excel file in to AX using batch job.
Export to Excel using batch job works fine using below but is there a way to import excel data in Batch as well?
Microsoft.Dynamics.AX.Fim.Spreadsheets.Spreadsheet spreadsheet;
Microsoft.Dynamics.AX.Fim.Spreadsheets.CellProperties cellProperties;
Microsoft.Dynamics.AX.Fim.Spreadsheets.ColumnProperties columnProperties;
Thanks,
Nadeem
*This post is locked for comments
Hi Kavitha,
I have copied over three methods from the class which I have used to export data to excel in Batch.
Run on property must be set to server for this class.
You can add methods to class for running in batch and modify the execute method accordingly to make it work for you.
public static void main(Args args)
{
AssetTrans assetTrans;
SysExcelApplication application;
SysExcelWorkBooks workbooks;
SysExcelWorkBook workbook;
SysExcelWorksheets worksheets;
sysExcelWorksheet worksheet;
SysExcelCells cells;
SysExcelCell cell;
int row;
DimensionAttributeValueSetItemView dimAttrSet;
DimensionAttribute dimAttr;
str dimAttrStr;
Map dims;
int dimNum;
ExcelExport Export = ExcelExport::newImportServer();
;
Export.init();
if (Export.prompt())
{
Export.run();
}
}
public void run()
{
setPrefix(ExcelExport::description());
if (this.validate())
{
this.execute();
info ("@SYS70406");
}
}
private void execute()
{
boolean fileFound;
boolean fileOk;
AssetTrans assetTrans;
int row;
DimensionAttributeValueSetItemView dimAttrSet,dimAttrSetMapVar;
DimensionAttribute dimAttr,dimAttrMapVar;
str dimAttrStr;
Map dims;
int dimNum;
str pathstr;
anytype amountstr;
str ak;
str MarketSalesRegion, costCenter, Product, SalesRep,Project;
Map map;
MapEnumerator mapEnumerator;
NameAlias nameAlias;
int counter = 1; // start map with 1
// Below custom table contians dimension names
AssetTransExcelExport AssetTransExcelExport,AssetTransExcelExport_del, AssetTransExcelExport_DimName, AssetTransExcelExport_DisplayVal;
#define.ReadWritePermission('RW')
#define.ExcelColumnWidth(08) // was 15
#define.ExcelCellFontSize("Microsoft.Dynamics.AX.Fim.Spreadsheets.CellFontSize")
#define.Size9("Size9")
Microsoft.Dynamics.AX.Fim.Spreadsheets.Spreadsheet spreadsheet;
Microsoft.Dynamics.AX.Fim.Spreadsheets.CellProperties cellProperties;
Microsoft.Dynamics.AX.Fim.Spreadsheets.ColumnProperties columnProperties;
void addColumn(str _name)
{
columnProperties = new Microsoft.Dynamics.AX.Fim.Spreadsheets.ColumnProperties();
columnProperties.set_Width(#ExcelColumnWidth);
spreadSheet.InstantiateColumn(columnProperties);
cellProperties = new Microsoft.Dynamics.AX.Fim.Spreadsheets.CellProperties();
cellProperties.set_FontSize(CLRInterop::parseClrEnum(#ExcelCellFontSize, #Size9));
cellProperties.set_Bold(true);
spreadSheet.AddStringCellToWorkbook(_name, cellProperties);
}
map = new Map(Types::String, Types::Integer);
select firstOnly * from assetParameters;
filepath = this.Modifyfilename(assetParameters.AssetTransExcelExportPath);
new FileIOPermission(filepath, #ReadWritePermission).assert();
spreadSheet = new Microsoft.Dynamics.AX.Fim.Spreadsheets.Spreadsheet();
if (!spreadSheet.CreateSpreadsheet(filepath))
{
spreadSheet.Dispose();
throw error(strFmt("@SYS72245", filepath));
}
addColumn("Voucher");
addColumn("Transaction date");
addColumn("Fixed asset number");
addColumn("Transaction type");
addColumn("Amount");
addColumn("Fixed asset group");
ttsBegin;
delete_from AssetTransExcelExport_del;
ttsCommit;
ttsBegin;
select forUpdate AssetTransExcelExport_DimName;
while select dimAttrSetMapVar
join Name from dimAttrMapVar
group by name
where dimAttrMapVar.RecId == dimAttrSetMapVar.DimensionAttribute
{
nameAlias = dimAttrMapVar.Name;
if (!map.exists(nameAlias))
{
map.insert(nameAlias, counter);
counter++;
}
AssetTransExcelExport_DimName.Name = dimAttrMapVar.Name;
AssetTransExcelExport_DimName.doInsert();
}
ttsCommit;
mapEnumerator = map.getEnumerator();
while (mapEnumerator.moveNext())
{
addcolumn(mapEnumerator.currentKey());
}
while select * from assetTrans
{
spreadSheet.MoveToNextRowInWorkbook();
cellProperties = new Microsoft.Dynamics.AX.Fim.Spreadsheets.CellProperties();
cellProperties.set_FontSize(CLRInterop::parseClrEnum(#ExcelCellFontSize, #Size9));
spreadSheet.AddStringCellToWorkbook(assetTrans.Voucher, cellProperties);
spreadSheet.AddStringCellToWorkbook(date2str(assetTrans.TransDate,123,2,DateSeparator::Slash,2,DateSeparator::Slash,4), cellProperties);
spreadSheet.AddStringCellToWorkbook(assetTrans.AssetId,cellProperties);
spreadsheet.AddStringCellToWorkbook(enum2str(assetTrans.TransType), cellProperties);
spreadSheet.AddStringCellToWorkbook(num2str(assetTrans.AmountCur,0,2,1,2),cellProperties);
spreadsheet.AddStringCellToWorkbook(assetTrans.AssetGroup, cellProperties);
while select dimAttrSet
where dimAttrSet.DimensionAttributeValueSet == assetTrans.DefaultDimension
join Name from dimAttr
order by Name
where dimattr.RecId == dimAttrSet.DimensionAttribute
{
select forUpdate firstOnly AssetTransExcelExport_DisplayVal
where AssetTransExcelExport_DisplayVal.Name == dimAttr.Name;
{
ttsBegin;
AssetTransExcelExport_DisplayVal.DisplayValue= dimAttrSet.DisplayValue;
AssetTransExcelExport_DisplayVal.doUpdate();
ttsCommit;
}
}
while select * from AssetTransExcelExport
{
spreadsheet.AddStringCellToWorkbook(AssetTransExcelExport.DisplayValue, cellProperties);
}
// clear table for next record
ttsBegin;
while select forUpdate * from AssetTransExcelExport_del
{
AssetTransExcelExport_del.DisplayValue = "";
AssetTransExcelExport_del.doUpdate();
}
ttsCommit;
}
spreadSheet.WriteFile();
spreadSheet.Dispose();
CodeAccessPermission::revertAssert();
}
Hi Nadeem,I used the same code in the given link sashanazarov.blogspot.co.uk/.../exporting-to-excel-with.html.
only the file location I have changed.In this post you have clearly mentioned export excel is working fine.Can you be more specific on this.
Hi Kavitha, I can't see you full code to identify the cause of error. It would be better to export the file as CSV and open the exported file in excel.
Hi Nadeem,
I followed this blog to create my excel export ,I can able to create a excel file .When i tried to do in batch ,i am getting error in this line.:spreadSheet.InstantiateColumn(columnProperties);
Check below post.
sashanazarov.blogspot.co.uk/.../exporting-to-excel-with.html
Hi nadeem,
As u said u r comfort with export to excel through batch process.When I try to do i am getting error when i tried to run in batch.
. System. NullReferenceException: Object reference not set to an instance of an object. at Dynamics. Ax.Application.
error line "spreadSheet.InstantiateColumn(columnProperties);".help me out to go ahead with my process.
Hi Thuyen Dang,
You can try DIXF and run the job as batch which will read the file from selected location at selected date/time and update AX.
Other option is to use CSV which will work accordingly as well.
Another options is to use Excel Add-In to publish data in to AX.
Hi Nadeem,
SysExcelApplication cannot work with batch job !
Thanks,
Thuyen Dang
Hi Nadeem,
I have used Speadsheet for export excel thru batchjob success, but I can't import excel with spreadsheet.
Can you show your code for import ?
Thank and regard,
Thuyen Dang
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
André Arnaud de Cal... 291,228 Super User 2024 Season 2
Martin Dráb 230,056 Most Valuable Professional
nmaenpaa 101,156