Import Transactions (Page 2 of 7)

Hi,
I have adapted the extension to import multi line sales orders, and sales invoices from a spreadsheet. Invoices are either cash sale or credit sale.

The extension is working fine but I am getting a Cart: Read warning (yellow) whilst importing the Direct Sales Invoices.  The sales orders import fine. 

I do not want to rewrite the Cart Class simply to avoid a warning.

I am using the attached adapted British coa with a service item 001 and a product item 002. One customer connected to branch one and a second customer connected to branch 2.

Any ideas would be appreciated. See attached files please.

Regards

Ross

Hi raddison,

I am using your import transaction module with some limited success. Followed the instructions faithfully and all the bank statement for both customers' & suppliers' data checked ok during the trial run-check before importing.

For the customers/bank receipts, the data was imported correctly. For the suppliers/bank payments, the suppliers' name is missing. To complete the process, I have to adjust the bank payments manually to include the suppliers' names.

Any advice?

peace, peter.

@apmuthu, thanks for the advice. Will check structure.

peace, peter.

In modules/import_transactions/includes/import_transactions.inc replace line:

if ($person_id != null)

with

if (!empty($person_id))

In modules/import_transactions/import_transactions.php replace line:

$prev_ref = 9999;

with

$prev_ref = -1;

so that a practically impossible value is used for initialisation.

My Unofficial GitHub repo has been updated with white space fixes as well and the extra comma in the last line of templates/payment_en_GB.csv has been eliminated.

You may also further put in the constants for balance of $type == 0, 1, 2 comparisons.

In your file templates/payment_en_GB.csv, please delete the last comma. There are 11 fields separated by 10 commas but the last line has an extra comma.

@joe: PKG Repo update needed for this extension.

For Bank Statement Processing:
Check the bank statement checkbox below the drop down box.
Your csv must have the two amount columns. One for deposits. One for payments.
Which is typical of any bank statement.

Remember this is an adapted Bank Statement csv file that you are importing.

So the assumption is:
1. Download your bank statement from your bank in csv format.
2. Incorporate additional columns in this spreadsheet according to the template.
3. Complete your account code designations to the transactions in your bank.csv including tax codes.
4. Import the csv.

So you do the all account code and vat code designations for each line or transaction on the bank statement before you import.

The first few columns of the spreadsheet/csv must be identical to your bank statement.
The last few columns incorporate account and tax allocation.

So this spreadsheet/csv that you have created has your account and vat allocations in addition to the bank statement.

Essentially you are doing your account and vat  code designations before the import. Which I personally find much easier than any other commercial software packages available on the market. Complete all your account code designations to each transaction before you do the import.

Process the same csv twice.
Once with Deposit Processing.
Next with Payment Processing.

Hope that clears a few things up.

Hi Ross,

In modules/import_transactions/includes/import_transactions.inc replace line 634:

        $sql = "SELECT id, type, trans_no, amount FROM ".TB_PREF."bank_trans ORDER BY id DESC LIMIT 1";

with

        $sql = "SELECT id, type, trans_no, amount FROM ".TB_PREF."bank_trans WHERE `type` =".$type." ORDER BY id DESC LIMIT 1";

Although ST_BANKDEPOSIT and ST_BANKPAYMENT are stored in the same table they use separate transaction numbers. I tried to import bank deposits, unfortunately the transaction id's that were assigned were already in use.

Probably line 651 must be changed accordingly to separate the sales orders and invoices, but I haven't checked that!

Committed in my GitHub repo.
Thanks @jornrikkers and @raddison.

Have a look at the templates folder in the said module. There are 9,10,11 or 24 commas in each field in the CSV files there.

Your data set seems to be following the templates/bank_en_GB.csv file.

Which CoA are you using? The UK CoA has some errors in field order.

Check out the corrected extensions and CoAs in my unofficial GitHub Repo.

@gbw: Thankyou for your valuable feedback regarding spaces.

Since the strings in the CSV are not quoted or double quoted, spaces should be taken as a zero for numerical fields and trimmed for others in the extension.

Wiki-ed it.

Also, here is what I get when I run the trail run on my cvs file:
Skipped header. (line 1 in import file 'CSV_484074944.csv')
--------------------------------------------------------------------------------------------Line 2 ------------------------------------------------------------------------------------------
Error: Account code 5-00-000-01-01 does not exist
--------------------------------------------------------------------------------------------Line 3 ------------------------------------------------------------------------------------------
Added to table 'bank_trans': 15, 11/11/2016, JL25, 5700, 0, 0, -411.88 (line 3 in import file 'CSV_484074944.csv')
Added to table 'gl_trans' Debit: 15, 11/11/2016, 5700, 0, 0, 411.88, General Retail (line 3 in import file 'CSV_484074944.csv')
Added to table 'supp_trans' Debit: Supplier paid -411.88 (line 3 in import file 'CSV_484074944.csv')
Added to table 'gl_trans' Credit bank account: 15, 11/11/2016, 1060, 0, 0, -411.88, General Retail (line 3 in import file 'CSV_484074944.csv')
--------------------------------------------------------------------------------------------Line 4 ------------------------------------------------------------------------------------------
Added to table 'bank_trans': 16, 11/12/2016, JL26, 5100, 0, 0, -272.45 (line 4 in import file 'CSV_484074944.csv')
Added to table 'gl_trans' Debit: 16, 11/12/2016, 5100, 0, 0, 272.45, Fuel/Auto (line 4 in import file 'CSV_484074944.csv')
Added to table 'supp_trans' Debit: Supplier paid -272.45 (line 4 in import file 'CSV_484074944.csv')
Added to table 'gl_trans' Credit bank account: 16, 11/12/2016, 1060, 0, 0, -272.45, Fuel/Auto (line 4 in import file 'CSV_484074944.csv')
--------------------------------------------------------------------------------------------Line 5 ------------------------------------------------------------------------------------------
Added to table 'bank_trans': 17, 11/13/2016, JL27, 5100, 0, 0, -12.26 (line 5 in import file 'CSV_484074944.csv')
Added to table 'gl_trans' Debit: 17, 11/13/2016, 5100, 0, 0, 12.26, Fuel/Auto (line 5 in import file 'CSV_484074944.csv')
Added to table 'supp_trans' Debit: Supplier paid -12.26 (line 5 in import file 'CSV_484074944.csv')
Added to table 'gl_trans' Credit bank account: 17, 11/13/2016, 1060, 0, 0, -12.26, Fuel/Auto (line 5 in import file 'CSV_484074944.csv')
--------------------------------------------------------------------------------------------Line 6 ------------------------------------------------------------------------------------------
Added to table 'bank_trans': 18, 11/14/2016, JL28, 5100, 0, 0, -357.98 (line 6 in import file 'CSV_484074944.csv')
Added to table 'gl_trans' Debit: 18, 11/14/2016, 5100, 0, 0, 357.98, Fuel/Auto (line 6 in import file 'CSV_484074944.csv')
Added to table 'supp_trans' Debit: Supplier paid -357.98 (line 6 in import file 'CSV_484074944.csv')
Added to table 'gl_trans' Credit bank account: 18, 11/14/2016, 1060, 0, 0, -357.98, Fuel/Auto (line 6 in import file 'CSV_484074944.csv')
--------------------------------------------------------------------------------------------Line 7 ------------------------------------------------------------------------------------------
Added to table 'bank_trans': 19, 11/15/2016, JL29, 5700, 0, 0, -1000 (line 7 in import file 'CSV_484074944.csv')
Added to table 'gl_trans' Debit: 19, 11/15/2016, 5700, 0, 0, 1000, Transportation (line 7 in import file 'CSV_484074944.csv')
Added to table 'supp_trans' Debit: Supplier paid -1000 (line 7 in import file 'CSV_484074944.csv')
Added to table 'gl_trans' Credit bank account: 19, 11/15/2016, 1060, 0, 0, -1000, Transportation (line 7 in import file 'CSV_484074944.csv')
--------------------------------------------------------------------------------------------Line 8 ------------------------------------------------------------------------------------------
Added to table 'bank_trans': 20, 11/16/2016, JL30, 5100, 0, 0, -14 (line 8 in import file 'CSV_484074944.csv')
Added to table 'gl_trans' Debit: 20, 11/16/2016, 5100, 0, 0, 14, Loading/Unloading (line 8 in import file 'CSV_484074944.csv')
Added to table 'supp_trans' Debit: Supplier paid -14 (line 8 in import file 'CSV_484074944.csv')
Added to table 'gl_trans' Credit bank account: 20, 11/16/2016, 1060, 0, 0, -14, Loading/Unloading (line 8 in import file 'CSV_484074944.csv')
--------------------------------------------------------------------------------------------Line 9 ------------------------------------------------------------------------------------------
Added to table 'bank_trans': 21, 11/17/2016, JL31, 5700, 0, 0, -50 (line 9 in import file 'CSV_484074944.csv')
Added to table 'gl_trans' Debit: 21, 11/17/2016, 5700, 0, 0, 50, General Retail (line 9 in import file 'CSV_484074944.csv')
Added to table 'supp_trans' Debit: Supplier paid -50 (line 9 in import file 'CSV_484074944.csv')
Added to table 'gl_trans' Credit bank account: 21, 11/17/2016, 1060, 0, 0, -50, General Retail (line 9 in import file 'CSV_484074944.csv')
--------------------------------------------------------------------------------------------Line 10 ------------------------------------------------------------------------------------------
Added to table 'bank_trans': 22, 11/18/2016, JL32, 5020, 0, 0, -89.06 (line 10 in import file 'CSV_484074944.csv')
Added to table 'gl_trans' Debit: 22, 11/18/2016, 5020, 0, 0, 89.06, Reefer Fuel (line 10 in import file 'CSV_484074944.csv')
Added to table 'supp_trans' Debit: Supplier paid -89.06 (line 10 in import file 'CSV_484074944.csv')
Added to table 'gl_trans' Credit bank account: 22, 11/18/2016, 1060, 0, 0, -89.06, Reefer Fuel (line 10 in import file 'CSV_484074944.csv')
--------------------------------------------------------------------------------------------Line 11 ------------------------------------------------------------------------------------------
Added to table 'bank_trans': 23, 11/19/2016, JL33, 5100, 0, 0, -491.68 (line 11 in import file 'CSV_484074944.csv')
Added to table 'gl_trans' Debit: 23, 11/19/2016, 5100, 0, 0, 491.68, Fuel/Auto (line 11 in import file 'CSV_484074944.csv')
Added to table 'supp_trans' Debit: Supplier paid -491.68 (line 11 in import file 'CSV_484074944.csv')
Added to table 'gl_trans' Credit bank account: 23, 11/19/2016, 1060, 0, 0, -491.68, Fuel/Auto (line 11 in import file 'CSV_484074944.csv')
1 error(s) detected. Correct before importing.

When you Look at what it pulls up below all that, it shows the correct account number and account name.  I know FA doesn't like account numbers bigger than like 8 digits and I had to already make other modifications to the core to make my longer account numbers work in the program.