Microsoft Dynamics AX Support

This blog contains posts by the Microsoft Dynamics AX Support teams Worldwide

Importing a General Journal using Data Import/Export Framework AX 2012

Importing a General Journal using Data Import/Export Framework AX 2012

Rate This
  • Comments 2

Here is a tutorial on how to configure Dynamics AX 2012 to import a general journal from a CSV file. The steps below are using Contoso demo data.

  1. Create a new folder on the root of the C: Drive and name it “DIEF”:


  2. Navigate to Data import export framework | Setup | Data import export framework parameters. Click “Browse” next to “Shared working directory” and select the “DIEF” folder we created. Once selected, click “Validate”.


  3. Close the “Data import export framework parameters” form.
  4. Go to Data import export framework | Setup | Source data formats. Enter “GLJOURNAL” for the “Source name” and “Type” = “File. In the parameters on the right side, enter “File format” = “Delimited”, “First row header” = TRUE, “Row delimiter” = “{CR}{LF}”, “Column delimiter” = “Comma {,}”, “Text Qualifier” = “*”, and “Role separator” = “;”. Click “Application”, and then select “CostCenter”, “Department”, and “ExpensePurpose”. Enter “CostCenter-Department-ExpensePurpose” for the “Dimension format” value.



  5. Go to Data import export framework | Setup | Target entities. Click “New” and enter “Entity type” = “Entity”, “Entity” = “Custom”, “Entity name” = “GLJOURNAL”, “Staging table” = “DMFLedgerJournalEntity”, “Entity class” = “DMFLedgerBalanceEntityClass”, and “Target entity” = “DMFLedgerJournalTransEntity”. Close the “Target entites” form.


  6. Go to Data import export framework | Common | Processing group. Type “GLJOURNAL” for the “Group name”, Ctrl+S to save, and click “Entities”.


  7. On the “Select entities for processing group” form, enter “GLJOURNAL” for both the “Entity name” and the “Source data format”. Click “Generate source file”.


  8. On the “Wizard” form, click “Next”. For the “Display data” fields, select the following and put them in the following sequence: JournalName, JournalNum, LineNum, CurrencyCode, TransDate, Voucher, AccountType, LedgerDimension, AmountCurDebit, AmountCurCredit, OffsetAccountType, OffsetLedgerDimension. Click “Generate sample file”.


  9. A .txt file should open, and save it to the root of the C: drive.


  10. Click “Finish” on the “Wizard” form. Close the “Select entities for processing group” form. Close the “Processing group” form.
  11. Go to General ledger | Setup | General ledger parameters. Click “Number sequences”. Right-click “Gene_10” next to “Journal batch number” and click “View details”.


  12. Click “Edit”, change the “_010” to “JN”, and click “Move up”. Click the “General” fast tab and note the “Next” value, in my case “JN000421”.


  13. Close the “Number sequences” form and the “General ledger parameters” form.
  14. Navigate to General ledger | Setup | Journals | Journal names. Select “GenJrn” and right-click the “Acco_18” next to “Voucher series”, and then click “View details”.
  15. On the “Number sequences” form, click “Edit”. In the “Segments” fast tab, click “Add”, select “Constant” for the “Segment”, and type “VN” for “Value”. Move this new segment to the top by clicking “Move up”. Note the next number in the series. In my case, “VN00000038”.


  16. Close all forms.
  17. Open Excel. Click File | Open. Navigate to the C: drive and select the “GLJOURNAL.txt” file (You may need to change the drop menu to “All Files (*.*)”)
  18. On the “Text Import Wizard” form, click “Delimited” and click “Next”. Check the box for “Comma” and click “Finish”.
  19. In line 2, enter the following values for each header:
    1. JournalName = GenJrn
    2. JournalNum = JN000421 (Value from step 12)
    3. LineNum = 1
    4. CurrencyCode = USD
    5. TransDate = 8/19/2013
    6. Voucher = VN00000038 (Value from step 16)
    7. AccountType = Ledger
    8. LedgerDimension = 110180-OU_1-OU_3566-Training
    9. AmountCurDebit = 10
    10. OffsetAccountType = Ledger
    11. OffsetLedgerDimension = 110101-OU_1-OU_3566-Training

  20. Click File | Save As. Click “CSV (Comma delimited)” for the “Save as type” drop-menu.


  21. Close Excel.
  22. Go to Data import export framework | Common | Processing group. Select the line for “GLJOURNAL” and click “Entities”.


  23. Click the folder icon next to “Sample file path”, and select the .csv file from step 20. Click “Generate source mapping”. Close the infolog.


  24. Close the “Select entities for processing group” form.
  25. On the “Processing group” form, select the line for “GLJOURNAL” and click “Get staging data”.


  26. A form for “Create a job ID for the staging data job” should open and populate with a “Job ID”. Click “OK”.
  27. On the “Staging data execution” form, click “Preview”. Verify the columns are correct, and then click “Run”.


  28. Close the infolog.


  29. On the “Processing group” form, click “Copy data to target”. Select the “Job ID” created earlier, and click “OK”. On the “Target data execution” form, click “Run”, then click “OK”.


  30. Close the Infolog.


  31. Go to General ledger | Journals | General journal. Locate the imported journal, and click “Lines”.
  32. Notice the values imported properly, and click Post | Post. The journal posts successfully.

 

Tyler Lewin

Senior Support Engineer - Dynamics AX

Leave a Comment
  • Please add 3 and 2 and type the answer here:
  • Post
  • Hi

    Thanks for the post.

    I have found two issues with the standard opening balance import entity:

    1. When using it for importing customer balances and you also need to import settlement discounts, the discounts do not post through to the customer transactions, although it is specified in the journal.  We had to run a separate job after the import to initialise the discount creation.

    2. When import fixed assets depreciation transactions using this entity, the asset transaction type also do not get updated.

    Regards

  • Hi Eddie -

    I'm not seeing anything previously reported on the customer discounts issue. I would recommend either submitting a support ticket to have an Engineer investigate it or submit a product suggestion on our Connect site:

    connect.microsoft.com/dynamicssuggestions

    It looks like we've released a hotfix for AX 2012 R2 for the issues with importing the fixed assets depreciation journal and the transaction type. The hotfix is KB 2911714 which can be downloaded from PartnerSource. You'll need to log in first, but here is the link:

    mbs2.microsoft.com/.../kbdisplay.aspx

    I hope this helps!

    Tyler

Page 1 of 1 (2 items)