GL import scenario debit/credit amounts

  • 28 January 2021
  • 2 replies

Userlevel 3

I have a csv file that I am using for a GL transaction import scenario. The debit and credit amounts are in one column, debits positive number, credits negative numbers.  How can I get the import scenario to populate the debit and credit fields properly?

thanks, coleen


Best answer by mikedavidson07 28 January 2021, 22:23

View original

2 replies

I just found below solution for this, in case anyone else later read this post. 
Used below formula to assign Net value to Receipt or Disbursement based on the value of Net field:
Disbursement=IIf( CDec([Net]) < 0, -CDec([Net] ) , '0' )
Receipt=IIf( CDec([Net]) > 0, CDec([Net]) , '0' )


Userlevel 4
Badge +1
  1. In excel Sort the column by the amount.
  2. Insert another column next to this column and label it “Original”.
  3. Go to your sorted column with the data, select all negative numbers and copy them to your new column (shown as original column below).  Make sure they are removed from your original column.
  4. Create a new column and label it “Debit”
  5. Create a formula in your Debit column as show =G2*1 (this will reverse the number).
  6. Confirm the column H now has amounts reversed.

  7. Select all of column H, then right-click your mouse and select copy.

  8. In the same culumn H in cell H2, right-click your mouse and select paste special and select values as forumla.  this will get rid of the formula.  You can also now delect column G “Original”.

    This approach fixes the data in excel and usually just import in the Journal Transaction entry but would also work in an import scenario.  


About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved