Dynamics AX : error while importing data from excel on client

Question

I did a custom X++ code to import data from Excel to General Ledger, however the import works very well on the server directly but while running it from the end user(client) it imports several records (like 24 records) then throw an error

the number of argument provided is different from the number of argument provided to the method

it is obvious that the error is related to connectivity issue since I have tried the same Excel file on the on the server and successfully imported.

in order to prevent this issue I was thinking to alternative solution rather than looping the through the excel file and do the business and insert the records, instead I thought it might be useful if I save the file directly/ bulk save in a table or something else then try to loop through the table to prevent the connectivity issue.

note: several solution are available on google such as windows ghosting but none works for me

could anyone please advise about that or suggest the suitable solution

Jan B. Kjeldsen · Accepted Answer · 2017-01-19T08:19:01.597

2

I would recommend you save the Excel file as tab-separated text, then do the import using the TextIO class.

You will also benefit from a +10 times increase in performance!

static void ExcelTest(Args _args)
{
    #Excel
    FilePath excelFile = @'C:\Users\user\Documents\MyExcelFile.xlsx';
    FilePath textFile  = @'C:\Users\user\Documents\MyTextFile.txt';
    Microsoft.Office.Interop.Excel.Application  application = new Microsoft.Office.Interop.Excel.ApplicationClass();
    Microsoft.Office.Interop.Excel.Workbooks    workBooks   = application.get_Workbooks();
    Microsoft.Office.Interop.Excel.Workbook     workBook; 

    // Save the excel file as tab-separated text
    application.set_DisplayAlerts(false);
    application.set_Visible(false);
    new FileIOPermission(excelFile, 'r').assert();
    workBooks.Open(excelFile, 0, true, 5, '', '', true, #xlWindows, '', false, false, 1, false, false, 1);
    CodeAccessPermission::revertAssert();
    workBook  = workBooks.get_Item(1);
    new FileIOPermission(textFile, 'w').assert();
    CodeAccessPermission::revertAssert();
    workBook.SaveAs(textFile, #xlTextWindows, '', '', false, false, null, #xlLocalSessionChanges, false, null, null, false);
    workBooks.Close();
    application.Quit();

    // Now read the text file
    new FileIOPermission(textFile, 'r').assert();
    io = new TextIo(textFile, 'r');
    if (!io)
        throw error("@SYS18447");
    io.inFieldDelimiter('\t');
    for (con = io.read(); io.status() == IO_Status::Ok; con = io.read())
    {
        info(con2str(con));
    }
}

edited Jan 19 '17 at 08:19

answered Jan 19 '17 at 08:12

Jan B. Kjeldsen

17,817
5
32
50

Thank your for your answer, I will try the above solution, do you suggest to make the Excel test method to run on server? I think if we run this method on the server it will save the txt file on the AOS server and we might avoid the connection issue, am I right? – khaled alomar Jan 19 '17 at 08:26
As written this is a job, which runs on the client. You will probably need to split it in at least two methods (a static one for text conversion, and one for reading the text). The first may run on the server provided Excel is installed. If you need to run in batch, you must test that scenario. – Jan B. Kjeldsen Jan 19 '17 at 08:30
Also beware that files created on the server is not available on the client (and reverse), you probably should run both methods on the same tier. – Jan B. Kjeldsen Jan 19 '17 at 08:32
I am getting a syntax error on `Microsoft.Office.Interop.Excel.Application ` even though the Excel is installed , I am not able to see Microsoft.Office – khaled alomar Jan 19 '17 at 09:07
You need to add the `Microsoft.Office.Interop.Excel` in the Reference node of the AOT. – Jan B. Kjeldsen Jan 19 '17 at 09:14
the code works in a job but when I put it in `Static server` method in a `class` and call that `method` from a job I got the following error `Object 'CLRObject' could not be created ` – khaled alomar Jan 19 '17 at 13:00
the way am calling it from the job is : `myclass :: myMethod()` – khaled alomar Jan 19 '17 at 13:03
Try `static client`. Maybe Excel is not installed on AOS. – Jan B. Kjeldsen Jan 19 '17 at 13:06
what we specify client. does it mean when the end user run the code so the file will be created on the end user machine? – khaled alomar Jan 19 '17 at 13:10
It depends on the file path. It could be an UNC path. – Jan B. Kjeldsen Jan 19 '17 at 13:12
that makes me worry since the whole point there is a problem in connection between client and AOS server and I did that in order to upload the excel to the AOS server in order to avoid connection issue while looping, I think when the end user run the code(after `static client`) so the file will be created on the end user pc – khaled alomar Jan 19 '17 at 13:36
I dont think you had a connection problem. You had an API problem. – Jan B. Kjeldsen Jan 19 '17 at 13:40
do you think that reading from the file instead of the Excel will avoid the API problem? – khaled alomar Jan 19 '17 at 13:46
That would be my guess – Jan B. Kjeldsen Jan 19 '17 at 14:06
Thank you for your help, I will try that solution – khaled alomar Jan 19 '17 at 14:20

Dynamics AX : error while importing data from excel on client

1 Answers1