Hello… friends,
please help me for following issue…
i want to import record from Excel to AX Vend Table… for that i have created new field IFSC_Code in Vend Table
now i want to fetch the IFSC code from Excel Sheet to IFSC_Code field of table if vend account field in table matches the vend account column in Excel sheet .
please suggest logic…
waiting for reply.
thanks in advance…
ADO output to an excel template.
This program can help provide you with a starting point to creating a method for importing from an excel file using ADO.
Its written as job, so its simple to implement so you can play around with it.
First, create an Excel template on your desktop:
On row 1 of the excel, create a header row. If the first row is empty, it will not work.
On this excel, I will create a simple macro called AF() This will perform an autofit of the columns:
Now, create a new job in the AOT:
static void WriteToExcel_Via_ADO(Args _args) { // This is what DAX uses as a counter when exporting to excel: SysGridToExcelStatusInterop counterBox = new SysGridToExcelStatusInterop(); Custtable custtable; str ADOFilePath; str templateFilePath; str fileName str lineText; Name daxUser; CCADOConnection adoConnection; CCADOCommand adoCommand; CCADORecordSet adoRecordSet; //CCADOFields adoFields; FileName _fileNameADO; int _row; int _col; // Filename filepath; SysExcelApplication _sysExcelApplication = SysExcelApplication::construct(); SysExcelWorkbook _sysExcelWorkbook; SysExcelWorkbooks _sysExcelWorkbooks; SysExcelWorksheet _sysExcelWorksheet; SysExcelCells _sysExcelCells; // field index and string array. Use can use memo type if you need to be // sure to fit the string. str f[255]; int idx; freeTxt CommandStr; str fieldString; str valueString; counter tmpCounter; str filePath; counter cntr; int64 recCounter; // function to build each line before passing to adoCommand.commandText() : void adoComStr(freetxt dataStr) { // clean the string of any bad chars: dataStr = strReplace(dataStr, num2char(39), " “); f[idx] = dataStr; if(f[idx]) { if(fieldString) { fieldString = strfmt(”%1,F%2",fieldString,idx); valueString = strfmt("%1,’%2’",valueString,f[idx]); } else { fieldString = strfmt(“F%1”,idx); valueString = strfmt("’%1’",f[idx]); } } } ; // Create a file from template in the path to the users desktop: // C:\Documents and Settings<username>\Desktop\ADO TEST.xlsx /* The template is simply an excel document with a header row. NOTE: Since its being treated like a table in a DB, it must have a header row like a table would typically have. This could be anything like 1,2,3,4,5… or A,B,C,D,E…, as long as it has a header row it will work. / templateFilePath = “C:\Documents and Settings\\Desktop\tmplt.xlsm”; //The ADOFilePath is the output file created from the template. //In this example, Im making a copy of the template, and calling it “ADO TEST.xlsm”. ADOFilePath = “C:\Documents and Settings\\Desktop\ADO TEST.xlsm”; if(substr(ADOFilePath,(strlen(ADOFilePath)-4),5) == ‘.xlsm’) { _fileNameADO = strfmt(@"%1",ADOFilePath); } else { _fileNameADO = strfmt(@"%1.xlsm",ADOFilePath); } // if theres a file already there of the same name from a previous run, we can make a backup: // ADO TEST(Old).xlsx if(WinAPI::fileExists(ADOFilePath)) { WinAPI::copyFile(ADOFilePath,“C:\Documents and Settings\\Desktop\ADO TEST(Old).xlsm”,true); WinAPI::deleteFile(ADOFilePath); } WinAPI::copyFile(templateFilePath, _fileNameADO,true); //Now we create and open a connection to the excel file. adoConnection = new CCADOConnection(); adoConnection.open(“Provier=Microsoft.ACE.OLEDB.12.0;Data Source=” + _fileNameADO + “;Extended Properties=‘Excel 8.0;HDR=NO’”); //excel2007 adoCommand = new CCADOCommand(); adoCommand.ActiveConnection(adoConnection); // initialize vars _row=3; _col=1; // Using the counter that DAX uses for Excel // show the counter: counterBox.ShowDialog(); counterBox.SetDialogCaption(“ADO Export”); while select * from custtable { // NOTES: // We need to use “’ '” for all fields, or we get COM error. eg: “valueString = strfmt(”’%1’",f[idx]); " //write the Excel: idx = 1; _row++; _col=1; fieldString=’’; valueString=’’; adoComStr(strFmt(’%1’,custtable.AccountNum)); idx++; adoComStr(strFmt(’%1’,custtable.Name)); idx++; adoComStr(strFmt(’%1’,custtable.Email)); idx++; adoComStr(strFmt(’%1’,custtable.City)); idx++; adoComStr(strFmt(’%1’,custtable.State)); idx++; adoComStr(strFmt(’%1’,custtable.ZipCode)); idx++; // just add more fields, as many as you want by following the pattern as above. Simple. counterBox.SetButtonText(strfmt(‘Row = %1’,_row)); counterBox.ShouldContinue(); if(_row ==500){ break;} //This is only an example, so lets limit this to only 500 lines. CommandStr = “INSERT INTO [SHEET1$] (” + fieldString + “) VALUES(” + valueString + “)”; info(strfmt(’%1, %2’,_row, CommandStr)); adoCommand.commandText(strfmt(@"%1",CommandStr)); adoCommand.execute(); } counterBox.CloseDialog(); adoConnection.close(); info(strfmt(‘File %1 created’,_fileNameADO)); _sysExcelWorkbooks = _sysExcelApplication.workbooks(); _sysExcelWorksheet = _sysExcelApplication.activeSheet(); _sysExcelWorkbooks.open(strfmt(_fileNameADO)); // On this line, you can execute a macro. Here, Im executing a macro on the excel named “AF” _sysExcelApplication.comObject().Run(“AF”); // (Optional) run the macro called “AF” / The vb code on the excel template is simple. Its calling the ‘autofit’ fuction: Sub AF() Cells.Select Cells.EntireColumn.AutoFit End Sub */ _sysExcelApplication.visible(true); _sysExcelWorkbooks = _sysExcelApplication.workbooks(); }