Ledger transaction import – CSV – Customer/Vendors

There is sometimes the need to import ledger transactions for Customers and Vendors. Below is a script that will create a journal for AP and AR transactions from a CSV file.

static void DAX_ImportGLTrans(Args _args)
{
// Set these variables.
LedgerJournalNameId      journalName = ‘GENJ’;
CommaIo     IO;
container   data;
LedgerJournalName   ledgerJournalName;
LedgerJournalTable  ledgerJournalTable;
LedgerJournalTrans  ledgerJournalTrans;
CustTable           CustTable;
VendTable           VendTable;
CustAccount         CustAccount;
VendAccount         VendAccount;
;

Io = new CommaIO(“<filename>.csv”, “r”);
data = Io.read();
data = Io.read();
ttsBegin;
// Create journal header.
ledgerJournalName = LedgerJournalName::find(journalName);
//Journal header
ledgerJournalTable.clear();
ledgerJournalTable.initValue();
ledgerJournalTable.initFromLedgerJournalName(ledgerJournalName.JournalName);
ledgerJournalTable.JournalNum = NumberSeq::newGetNum(LedgerParameters::numRefJournalNum()).num();
ledgerJournalTable.Name = “Import Journal”;
ledgerJournalTable.insert();
while(Io.status() == IO_Status::Ok)
{
if(strLRTrim(conPeek(data,<field column>)) != “”)
{
ledgerJournalTrans.clear();
ledgerJournalTrans.initValue();
ledgerJournalTrans.JournalNum = ledgerJournalTable.JournalNum;
ledgerJournalTrans.TransDate = str2Date(strLRTrim(conPeek(data, <field column>)), 123);
switch(strLRTrim(conPeek(data, <field column>)))
{
case ‘Customer’:
ledgerJournalTrans.AccountType = LedgerJournalACType::Cust;
CustAccount = strLRTrim(conPeek(data, <field column>));
CustTable = CustTable::find(CustAccount);
ledgerJournalTrans.initFromCustTable(CustTable);
ledgerJournalTrans.LedgerDimension = DimensionStorage::getDynamicAccount(CustAccount, ledgerJournalTrans.AccountType);
ledgerJournalTrans.OffsetLedgerDimension =                     LedgerJournalTrans.getLedgerDimensionForLedgerType(DimensionStorage::getLedgerDefaultAccountFromLedgerDim(CustTable.summaryLedgerDimension()), LedgerJournalTrans.Company);
ledgerJournalTrans.amountCur2DebCred(conPeek(data, <field column>), false);
break;
case ‘Supplier’:
ledgerJournalTrans.AccountType = LedgerJournalACType::Vend;
VendAccount = strLRTrim(conPeek(data,5));
VendTable = VendTable::find(VendAccount);
ledgerJournalTrans.LedgerDimension =                   DimensionStorage::getDynamicAccount(VendAccount, ledgerJournalTrans.AccountType);
ledgerJournalTrans.OffsetLedgerDimension = LedgerJournalTrans.getLedgerDimensionForLedgerType(DimensionStorage::getLedgerDefaultAccountFromLedgerDim(VendTable.summaryLedgerDimension()), LedgerJournalTrans.Company);
ledgerJournalTrans.amountCur2DebCred(conPeek(data, <field column>), false);
break;
}
ledgerJournalTrans.Voucher =           NumberSeq::newGetVoucherFromCode(NumberSequenceTable::find(ledgerJournalName.NumberSequenceTable).NumberSequence).voucher();
ledgerJournalTrans.Txt = strLRTrim(conPeek(data, <field column>));
ledgerJournalTrans.CurrencyCode = strLRTrim(conPeek(data, <field column>));
ledgerJournalTrans.Due = str2Date(strLRTrim(conPeek(data, <field column>)), 123);
ledgerJournalTrans.DocumentNum = strLRTrim(conPeek(data, <field column>));
if (ledgerJournalTrans.validateWrite())
{
ledgerJournalTrans.insert();
}
}
data = Io.read();
}
    ttsCommit;
}

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s