Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Credit card import usi...
Microsoft Dynamics AX (Archived)

Credit card import using AIF and transform with an Excel template

(0) ShareShare
ReportReport
Posted on by Community Member Microsoft Employee

I've followed the steps outlined in this blog post http://blogs.msdn.com/b/axsa/archive/2013/01/08/importing-credit-card-statements-into-dynamics-ax-2012-using-aif-and-transformation-library.aspx to import credit card expenses using AIF with a custom transform.  

The thinking was to have an Excel template that would map the client credit card csv (as in the example from the link).  This way we wouldn't have to hard-code the mapping values. 

When I run the process I get the error message:

It basically doesn't like the Excel template location. 

Does the DLL and Excel template need to be in a specific folder within the Dynamics AX tree or am I not allowed to call the Excel file from the custom transform DLL?

thanks.

*This post is locked for comments

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Credit card import using AIF and transform with an Excel template

    Thanks for your help Martin! It's much appreciated. Have a great weekend.

  • Martin Dráb Profile Picture
    Martin Dráb 230,514 Most Valuable Professional on at
    RE: Credit card import using AIF and transform with an Excel template

    I'm glad that you've resolved it and shared the solution, so everybody can find it here if needed.

    I don't think I deserve credit for solving this problem, I merely pushed you to focus on the core problem.

    Nevertheless when a forum thread is answered and doesn't require any more help, you should mark it as answered by clicking Did this answer your question => Yes. The thread will be then displayed with the green check mark and people can quickly navigate to answers by switching the display to Only Answers. In this case, you should verify your own answer.

  • Verified answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Credit card import using AIF and transform with an Excel template

    Hi Martin,

    Sorry for the flood of messages.

    I should have read the error message more carefully and google it.

    It wasn't the code.

    Here's the post that made sense:

    blogs.msdn.com/.../error-microsoft-office-excel-cannot-access-the-file-while-accessing-microsoft-office-11-0-object-library-from-ssis.aspx

    Then i followed what it suggested and created the following directory:

    C:\Windows\System32\config\systemprofile\Desktop

    And then the credit card import say it processed with 0 errors.

    Thank you so much for taking the time to attempt to help me.

    Is there a way for me to vote for you as helping me solve my question?  I really appreciate  the help.

    Cheers and have a great weekend.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Credit card import using AIF and transform with an Excel template

    Hi Martin,

    Silly me. It has been a long day.

    I just wrote some simple stuff just to test that it works or not.  

    But here it goes:

    using Excel = Microsoft.Office.Interop.Excel;

    Excel.Application excelApp = new Excel.Application();

    Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(@"C:\AXCreditCardTransform\TrvPBSMaindata.xlsx");

    Excel.Worksheet excelWorksheet = excelWorkbook.Sheets[1]; //Template Sheet

    //Then it loops the rows (skipping the foreach code) and gets the values.

    Array sheetValues = (Array)excelWorksheet.get_Range("A" + rowIndex.ToString(), "I" + rowIndex.ToString()).Cells.Value;

    //Then i get the values from the cell to build the element so it looks like using XMLTextWriter:  <AmountCurr>66.55</AmountCurr>

    //At the end close the workbook

    excelApp.Quit();

  • Martin Dráb Profile Picture
    Martin Dráb 230,514 Most Valuable Professional on at
    RE: Credit card import using AIF and transform with an Excel template

    That's all nice, but how are you opening TrvPBSMaindata.xlsx? The opening is what fails, therefore you should tell us at least something about it.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Credit card import using AIF and transform with an Excel template

    Thanks for the reply Martin.  I know it's hard without code.

    So let me try with a semi-simple explanation :)

    It's basically an Excel template that I have that lists out the elements from TrvPBSMaindata and then I have an adjacent column that a user would fill out its CSV mapping value.

    For example: ElementName is the TrvPBSMainData name and CSVNameToMap is the name to map from the CSV import file.

    I would fill it out like this in the Excel template:

    ElementName      CSVNameToMap

    ----------------    --------------------

    AmountCur              Total Cost

    AmountLocal            Total Cost

    CardNumber            Account Number

    and so on.  

    We would have an Excel template for each credit card that can be reusable for any number of companies that provide a csv credit card file.

    The transform would loop through the "ElementName" and map it with the "CSVNameToMap" it pulled from the Excel File and then create the XML elements that requires for the Import To Credit Card processing in AX.

    That's basically what it does in a nutshell.

    I should have tested it out in the transform process before making a fancy Excel template with dropdowns and stuff.  It worked in my simple proof-of-concept console app that I thought I could just create a class library using the same principles.

    Oh well.

    Thanks again for taking a look.

  • Martin Dráb Profile Picture
    Martin Dráb 230,514 Most Valuable Professional on at
    RE: Credit card import using AIF and transform with an Excel template

    Everything is possible, but I have no idea what your code does, so I can't comment on it.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Credit card import using AIF and transform with an Excel template

    Hi Martin. I'm using the demo R3 CU8 VM.  It's just on the C:\ directory and I've given the folder permissions to everyone.

    I was just doing a bit more searching...and I found that it could import CSV.  Could it be that it doesn't like the Excel?

  • Martin Dráb Profile Picture
    Martin Dráb 230,514 Most Valuable Professional on at
    RE: Credit card import using AIF and transform with an Excel template

    Is the path correct? Its it on the application server? Has the AOS account permissions to read the file? And so on...

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Congratulations 2024 Spotlight Honorees

Kudos to all of our 2024 community stars! 🎉

Meet the Top 10 leaders for December

Congratulations to our December super stars! 🥳

Start Your Super User Journey Pt 2

Join the ranks of our community heros! 🦹

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,820 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,514 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans