Import Excel data into Sage 50 invoice

Question

Hi there, 
 
 First time poster here. Glad to have found this community 
 I have a situation where, for a specific client, I need to create quite voluminous sales invoices within Sage 50. Each of those invoices consist of several hundred items, and each of those items are in turn made of several different informations (item description, quantity, price, sales taxes) 
 The data I need to populate those invoices is located in a .csv document, which when imported in Excel, looks something like that : 
 
 And so on for several hundred (or even thousand) rows. 
 In my current workflow, I manually copy-paste the data from the Excel document into my invoice in Sage 50. 
 My question : is there a way I could import this data directly into a Sage 50 invoice, having each of the rows creating a different item and each of the columns populating a different information field for those items ? 
 Thanks in advance for your precious help. 
 Best regards, Etienne

Richard S. Ridings · Answer

I did the same thing for a grocery store about 15 years ago. The entries were roughly 500-700 lines and I believed they were using Simply 2004. 
 I created a small program that took their cash register export of all items sold during a day and manipulated it into the final format to be imported based on the SDK Import documentation. 
 The invoices we imported took forever to look up in Simply (about an 1 or more hours - go to lunch then review one invoice), so I gave them the option of importing as a Sales order because SO lookup was faster. Then once they reviewed it and it was validated, they deleted that SO and imported as an invoice. They also managed inventory in Simply. 
 The documentation is in the import.pdf in the SDK for Sage 50 Canadian. Search for it on the Knowledgebase or open internet and you should find an article or page for downloads. Just make sure you get the Canadian version. Sage's website links tend to push you toward the Sage 50 US version which is not compatible.

Richard S. Ridings · Answer

I"m afraid that is the step-by-step guide and it's been the same since around version 9. 
 You can search this forum for samples as there are several. 
 Page 7 is the setup for the header of <SalInvoice> (you might want to just search this forum for SalInvoice and you will see sample templates). 
 The tax information is the pickiest part but if you piece out the setup of your tax codes in Sage 50 you can mimic them in the setup. Be aware that the tax info on page 7 as part of the header transaction options is only for the freight. The rest of the tax information for line items is at the bottom of page 7 under Sales Invoice Detail Lines. Use the template on page 8 to fill in the correct field information. 
 The last page has a good example layout. 
 Your original post here shows three columns in Excel. They relate to the Item Number, Quantity and Amount fields in the Sales Invoice Detail Lines template on page 7. I've never imported without a unit Price so you may need to calculate that. I would copy it into a spreadsheet you wish to use as your template creator. Then set up formulas on a different sheet to format the data. Then export to txt file with an imp extension. 
 There are options on the customer ledger in Sage 50 for importing. You will want to set them so you are not asked for item codes each time. You will likely have to play with the import on a copy of your original Sage 50 file before you try it on your live file. I would work with a few line items first, then build up.

Agate · Answer

Hi Etienne: 
 We can assist you through one of our work associates by taking your .csv file and importing it into Sage 50. It would mean purchasing a 3rd party Sage developer software and using it to import the information. Please contact us for further information.

Sage 50 Canada

Import Excel data into Sage 50 invoice

Top Replies

COMMUNITY

COMPANY

PARTNERS

SUPPORT & TRAINING