Hi,

I don't know of anywhere that a template is available, what I usually do is set up an invoice *like* the ones I want to import, then email it to get the .IMP output file. I then open a copy of the formatted .IMP file in Excel as a CSV.

There is at least one commercial import utility available that I know of. There may be at least two if I ever finish the one I'm working on.

There's an SDK if you're good with c# code and plan to do this more than once.

If you need to do something more complex than the .IMP format allows for, such as fields that aren't available in the import utility, I've had good success using AutoHotKey to key data from a formatted excel-generated text file. (It's a kind of robot key-board and mouse utility)

I hope that helps, please post back!

Problem with importing a large number of invoices without the IMP method is there is a lot of tables you must update so will not be easy. You could consider importing as a sales order and then converting to invoices after import. Far fewer tables involved. Is this a one shot import or is this a regularly scheduled import?

Hello, Thanks for replying. This is a one time import. I am establishing Sage for a new Company and they had a very busy year. I have a spreadsheet with 3500 individual invoices and I have been adding them one at a time. The invoices are pay later as I need to record payments against individual invoices. I am open to ideas here because I have another 4 das at the pace I am going. I would be open to importing as a Sales Order and Then Converting if that is an easy option. My spreadsheet is structured to have 1 row for each invoice.

I sent you a friend request. I can send you a template. But this method is for Adv. Excel users

I would really appreciate that. I am very savvy with excel and Databases and can create and ODBC Connection if it links. Is there a utility required for Sage 50 - 2017 Canadian Version? I do not have the SDK yet. The online form is not working to get it from the forum...

Hello again.

My email address is:

[email protected]

Feel free to send a template to this email. This is a big help. It would probably take me a week to enter these invoices full time... If you are Ok with contacting me via my company email, I am always plugged in to my company.

Regards,
John

Hi John,

Sage 50 Canadian comes with ODBC.  If the software is installed, the ODBC link should be created each time you log into the software.  If it isn't, the software won't work.

The data dictionary datadict.pdf is included with the software, in a folder in the program directory.

The SDK has samples for c# (extensive example including data import, uncomment the modules to try them), and some simple examples in VB, and C++.  Use of the Examples is not documented, and the Examples are not overmuch commented.

Documentation in the SDK also includes information on the data relationships (datarel.pdf), and on the API.

I would not recommend using ODBC to drop the information into Sage 50.  There is no referential integrity enforced between the modules, so the input data would have to be complete and loaded into every required table perfectly.

john Strain said:

The online form is not working to get it from the forum...

It worked just now for me.  Using IE in Windows 10. 

If you can't make it work, clear the cache or try another browser.

Hi Randy,

I've been working with Sage ERP for many years and now I'm back to working at the Sage 50 level. In Sage ERP, it is super easy to export any transaction batch and get the formatting to copy and create a custom import.

I've been trying several work arounds trying to get the same from Sage 50. I just came across your comment here about creating a sales invoice, emailing, and getting an .IMP formatted file from the email. When I try this, I'm only getting a pdf attachment. Would you expand on how to get the emailed invoice to arrive in the correct format?

Thanks a million!
Gwen

Hi Gwen,

The .IMP formatted file is generated for some types of document, as long as the customer or vendor is set up in the Import / Export tab for the customer record as:
[ x ] This customer (or Vendor) uses Sage 50 and can import invoices and estimates
[ x ] This customer (or Vendor) uses my item numbers on orders

If emailing to a Customer:
Sales Quote will have an attachment 'PURQUOTE_xxxxx.IMP' and a PDF
Sales Order Quote will have a PDF attachment.
Sales Invoice will have an attachment 'PURINVCE_xxxxx.IMP' and a PDF

If emailing to a vendor:
Estimate will send an email 'Your quote has been received' with no attachments
Purchase Order will have an attachment 'SALORDER_xxxxx.IMP' and a PDF
Purchase Invoice will send an email 'your invoice has been received' with no attachments

There will be some formatting differences as far as number of fields, but the formats are more or less the same, exc. that line 4 will have a bracketed tag for the document type.

I hope that helps, please post back!

Randy Wester

Hi Randy,

That's it! I was missing the import/export setting in the customer record! Thank you SO much!

Gwen :)