Find duplicate while importing Excel records into Ax 2012 Table

Here i am creating Log file as Text file using Textbuffer class as well as AsciiIo (File operation). So it will create two text files

static void FindDuplicate(Args _args)
{

SysExcelApplication application;
SysExcelWorkbooks workbooks;
SysExcelWorkbook workbook;
SysExcelWorksheets worksheets;
SysExcelWorksheet worksheet;
SysExcelCells cells;
COMVariantType type;
int row=1;
ItemId itemid;
Name name;
FileName filename;
Bill_Copy billcopy;
Bill_CopyTemp billcopytemp,billcopytemp1;
int DupCountBill,DupCountBillTemp;

BinData binData;
TextBuffer textBuffer;
AsciiIo logFile;
logFile = new AsciiIo(@”C:\Users\charlie\Desktop\Logfile.txt”,’a+’);
textBuffer = new TextBuffer();
textBuffer.setText(”);

application = SysExcelApplication::construct();
workbooks = application.workbooks();
//specify the file path that you want to read
filename = “E:\\Johnkrish1.xls”;
try
{
workbooks.open(filename);
}
catch (Exception::Error)
{
throw error(“File cannot be opened.”);
}

workbook = workbooks.item(1);
worksheets = workbook.worksheets();
worksheet = worksheets.itemFromNum(1);
cells = worksheet.cells();
do
{
row++;
name = cells.item(row, 1).value().bStr();
itemId = cells.item(row, 2).value().bStr();
//info(strfmt(‘%1 – %2’, itemId, name));

//select count(CustName) from billcopy where billcopy.CustName==name;
//DupCountBill = str2int(billcopy.CustName);

select billcopy where billcopy.CustName==name;
if(!billcopy)
{
billcopy.CustName=name;
billcopy.ItemName=itemid;
billcopy.insert();

}
else
{
billcopytemp.CustName=name;
billcopytemp.ItemName=itemid;
billcopytemp.insert();
// info(name);
}
type = cells.item(row+1, 1).value().variantType();

//info(strFmt(“Duplicate Record Count for this: %1 is : %2”,name,DuplicateCount));
}
while (type != COMVariantType::VT_EMPTY);

while select billcopy // group by CustName
{
select count(CustName) from billcopytemp where billcopytemp.CustName==billcopy.CustName;
DupCountBillTemp=str2int(billcopytemp.CustName);
// info(strFmt(“Duplicate Record Count for this: %1 is : %2”,billcopy.CustName,DupCountBillTemp));

logFile.write((strfmt(“Duplicate Record Count for Customer: %1 is : %2 \r\n”,billcopy.CustName,DupCountBillTemp)));

textBuffer.appendText(strfmt(“Duplicate Record Count for Customer: %1 is : %2 \r\n”,billcopy.CustName,DupCountBillTemp));
}

textBuffer.getText();

binData = new BinData();
binData.setStrData(textBuffer.getText());
binData.saveFile(@”C:\Users\charlie\Desktop\Log.txt”);

application.quit();

}

Advertisements

Copying or Duplicating a record in ax 2012 using X++

Using this code we can copy records and insert into the same table again. (ie) you can duplicate records

static void BillTableCopy(Args _args)
{
Bill_TabMethod bill_TabMethod1;
Bill_TabMethod bill_TabMethod2;
bill_TabMethod1 = Bill_TabMethod::find(‘Johnkrish’);
ttsBegin;

bill_TabMethod2.data(bill_TabMethod1);

if (!bill_TabMethod2.validateWrite())
{
throw Exception::Error;
}
bill_TabMethod2.insert();

ttsCommit;
}