• Registered: 11/18/2011
  • Posts: 5

Topic: Batch Import of Sales Invoices from CSV file

We have to use a bespoke system to generate our sales invoices because there are some things unique to our business, but at the moment we are manually keying these into FrontAccounting.

As far as I can see a Direct Invoice in Front Accounting inserts/updates 10 tables, and I was rather hoping someone might have already done something for importing a large batch of Sales Invoices into FrontAccounting.

Any assistance gratefully received

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,160

Re: Batch Import of Sales Invoices from CSV file

To stay within FA, use Quick Entries or use the TextCart Extension. Otherwise:

1. Take a backup of FA
2. Post a typical entry thru the FA web interface
3. take another backup of FA
4. Compare the backups using tools like WinMerge
5. Skip the normal audit trail and logging entries and focus on the meat of the insert / updates
6. Make a template out of the insert / update sqls making the timestamp entries NOW() where appropriate (and likewise for current date)
7. Export the record variables from your manual invoice system
8. Mailmerge with the sql template created in 6
9 upload the mailmerged sql statements in a MySQL client or append them to the backup and then restore it.
10. Make sure no entries are made / changed in FA in the meanwhile.

apmuthu's Website

  • Registered: 11/18/2011
  • Posts: 5

Re: Batch Import of Sales Invoices from CSV file

I was really looking for something simpler, but thanks

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,160

Re: Batch Import of Sales Invoices from CSV file

A trigger on the bespoke database to populate records in the FA db might be another way out.

apmuthu's Website

  • Registered: 12/25/2007
  • Posts: 2,448

Re: Batch Import of Sales Invoices from CSV file

There is no ready solution fulfilling your requirements, but you can try developing another extension for FA, starting form code used in  another FA module with similar functionality like Items Import or Import Multi Journal Entries.
Janusz

  • From: UK, Scotland
  • Registered: 11/30/2014
  • Posts: 21

Re: Batch Import of Sales Invoices from CSV file

Hi,
I have updated the extension Import Transactions to incorporate Sales Orders / Invoices.  A spreadsheet template is available for both credit invoices and cash invoices.  Invoices may be multiline or singleline. The templates use a combination of service and product items.  Each invoice / order is defined by the reference number used.

Regards
Ross

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,160

Re: Batch Import of Sales Invoices from CSV file

The last line in templates/payment_en_GB.csv has an extra comma at the very end that needs to be removed.

REF2,16/12/14,Paid Creditor Control account 2100,220,2100,0,,,1,1,,

should be

REF2,16/12/14,Paid Creditor Control account 2100,220,2100,0,,,1,1,

apmuthu's Website

  • From: Mauritius, Zimbabwe
  • Registered: 03/03/2013
  • Posts: 107

Re: Batch Import of Sales Invoices from CSV file

Hi
where do I find the temlate.
Mark

Hi,
I have updated the extension Import Transactions to incorporate Sales Orders / Invoices.  A spreadsheet template is available for both credit invoices and cash invoices.  Invoices may be multiline or singleline. The templates use a combination of service and product items.  Each invoice / order is defined by the reference number used.
Regards
Ross

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,160

Re: Batch Import of Sales Invoices from CSV file

Refer this post.

apmuthu's Website