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

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

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”, copy the format. To copy click the “General” fast tab and note the “Next” value, in my case format is “INMF-00441”.
    
    
13. Close the “Number sequences” form and the “General ledger parameters” form.
14. Navigate to General ledger | Setup | Journals | Journal names. Select “GenJn” and right-click the “Acco_18” next to “Voucher series”, and then click “View details”.
15. On the “Number sequences” form, click “Edit”. copy the number sequence format as copied earlier. In my case format is, “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 = GenJn
2. JournalNum = INMF-00441(Value from step 12)
3. LineNum = 1
4. CurrencyCode = USD
5. TransDate = 01/01/2015
6. Voucher = VN00000038 (Value from step 16)
7. AccountType = Ledger
8. LedgerDimension = 110180-OU_1-OU_3566-Training "Or" 110110-022-001
9. AmountCurDebit = 10
10. OffsetAccountType = Ledger
11. OffsetLedgerDimension = 110101-OU_1-OU_3566-Training "Or" 110120-022-001

• Click File | Save As. Click “CSV (Comma delimited)” for the “Save as type” drop-menu.
  
• Close Excel.
• Go to Data import export framework | Common | Processing group. Select the line for “GLJOURNAL” and click “Entities”.
  
• Click the folder icon next to “Sample file path”, and select the .csv file from step 20. Click “Generate source mapping”. Close the infolog.
  
• Close the “Select entities for processing group” form.
• On the “Processing group” form, select the line for “GLJOURNAL” and click “Get staging data”.
  
• A form for “Create a job ID for the staging data job” should open and populate with a “Job ID”. Click “OK”.
• On the “Staging data execution” form, click “Preview”. Verify the columns are correct, and then click “Run”.
  
• Close the infolog.
  
• 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”.
  
• Close the Infolog.
  
• Go to General ledger | Journals | General journal. Locate the imported journal, and click “Lines”.
• Notice the values imported properly, and click Post | Post. The journal posts successfully.

reference: https://community.dynamics.com/ax/b/axsupport/archive/2014/07/17/importing-a-general-journal-using-data-import-export-framework-ax-2012.aspx