Similar to how the Product List, GL Code, Supplier Cards, etc. bulk upload works, users can use a CSV to quickly create all Suppliers within Lightyear.
The CSV needs to follow a specified layout, as some columns are required, and others need to follow a format. All you need to know is explained below.
*Lightyear Supplier - The name of the supplier you want to appear within Lightyear. This is the name all documents (i.e. invoices, POs, statements) will appear under
ABN/VAT/GST/Business No - Depending on the country of the supplier this may be called something else. US suppliers may not have a Business Number.
Accounting Supplier - The name of the supplier as it appears from your Supplier Cards list within Lightyear. This will be the imported list from your Accounting Software
*Country - The country of the supplier. This can only be 2 characters in length, which will be the country code e.g. Australia = AU, New Zealand = NZ, United Kingdom = GB, etc.
Registered Company Name - The alternative supplier name, usually appears on an invoice under 'Trading As...'
*Currency - The currency of the supplier. This can only be 3 characters in length, which will be the currency code e.g. AUD, NZD, GBP, EUR, etc.
*Registered for GST/VAT/Tax - Is a Yes/No field. If the supplier is registered for Tax, input
Y, if not, input
N. If this is left blank, it will default to No within Lightyear.
Default GL Code - If the supplier will always be coded to the same GL code, you can set a default at this point. From the list of GLs imported to your Lightyear account, use the GL
Code in this column.
Default Tax Code
- If the supplier will always be coded to the same Tax code, you can set a default at this point. From the list of Tax Codes imported to your Lightyear account, use the Tax
in this column. To find the tax code, head over to Accountancy > Tax Rates, and click the green pencil icon
beside the tax code. The first field will give you the Code to be input.
Approver - If the supplier will always be approved by the same User, you can set a default at this point. The user must already exist within Lightyear for this to succeed. Enter the users name as First Name followed by Last Name, exactly as it exists within Lightyear. E.g.
Number of Days Credit - The credit terms for payment on the supplier. Based on the pre-existing options in Lightyear, format the CSV as follows:
- From Bill Date - Set From End of Month to FROM_BILL_DATE and enter the number of days
- From End of Month - Set From End of Month to FROM_END_OF_MONTH and enter the number of days
- Due on Receipt - Set From End of Month to DUE_ON_RECEIPT and enter 0 as the number of days
- Use Extracted Due Date - Set From End of Month to USE_EXTRACTED_DUE_DATE and leave Number of Days Credit blank
*From End of Month - If you would like this to be enabled, enter Y. Otherwise, enter N
Account Name - If using the Bank Details Checker, this refers to the Bank Account Name of the supplier.
BSB/Sort Code/Routing No
- If using the Bank Details Checker, this refers to the BSB/Sort Code of the supplier.
- If using the Bank Details Checker, this refers to the Bank Account Number of the supplier.
- If using the Bank Details Checker, this refers to the IBAN of the supplier.
- If using the Bank Details Checker, this refers to the Swift Code of the supplier.
- If using the Bank Details Checker, this refers to the Payment Ref of the supplier.
BPAY Biller Code
- If using the Bank Details Checker, this refers to the BPAY Biller Code of the supplier. Only available for AU customers
Orders Email - If using POs, this is the email that is used when Ordering the PO.
Address Line 1 - Address Line 1 of the Supplier.
Address Line 2
- Address Line 2 of the Supplier.
- If emailing the supplier from within the Approvals Workflow/Processing/Statements, this is the email that is used. To enter multiple Email Addresses on the CSV, separate each with a semi-colon. E.g. email@example.com;firstname.lastname@example.org
Order Email - When Ordering a PO, this is the email that will default when creating a new PO. To enter multiple Email Addresses on the CSV, separate each with a semi-colon. E.g. email@example.com;firstname.lastname@example.org
- City of the Supplier.
- Contact Number of the Supplier.
- State/County of the Supplier.
- ZIP/Postcode of the Supplier.
Use Grouped Line Data - If you would like this to be enabled, enter Y. Otherwise, enter N
PO Supplier - If you would like this to be enabled, enter Y. Otherwise, enter N
Auto Export - If you would like this to be enabled, enter Y. Otherwise, enter N
Departments - The name of the Lightyear Department this Supplier is to be added to. If you want to add multiple, separate these with a semi-colon (;). Please Note: The Department name is Case Sensitive.
Columns marked with a
* are required.
Any columns that are not required, can be left blank.
Saving a Template
When completing this CSV routine, you can save a template to remember which field names match to which column. The next time you need to bulk upload or update suppliers, this template can be selected and the information auto-populated in the correct fields for you to import.
To create a template, go through the above steps to import your CSV. When you have filled out the relevant fields, go back to the top and enter a
Template Name, then click the
Create Template button at the bottom of the modal.
Once this has been Created, the next CSV Import will allow you to select the template from the
Choose Template at the top of the modal.
To make changes to the Template, select it from the
Choose Template field and make the necessary changes to the fields and click
Overwrite Template at the bottom of the modal. These changes will be saved and available to use during the next CSV Import.
To delete a Template, select from the
Choose Template dropdown, and click the red X beside the template name.
How to Upload to Suppliers
Once you have the CSV populated, head over to the Suppliers tab within Lightyear, and click the
Upload CSV File button in the top right.
You will need to select the file from your computer and click
Open. A popup will display asking if the file contains a Header Row, depending on what your CSV looks like, select
This is where you will match the columns on the CSV to what is needed within Lightyear. Beside each field, select the field that matches on the CSV. If you don't have this on the CSV, it can be left blank.
Once all fields have been matched, click
Confirm. The upload will then start.
When the upload has finished you will receive a file in return, this will mirror the CSV file uploaded, with two extra columns at the end:
Success/Fail - Whether the supplier succeeded or failed will be detailed in this column
Reason - If the supplier fails, the reason will be detailed here.
If you have a number of successes and failures within one CSV, the suppliers which were successful will update. The suppliers that fail will not.
If you already have some suppliers in Lightyear before the CSV has been uploaded, these will be overwritten with the updated details on the CSV.
Export Supplier Info from Lightyear
Users can use this CSV report to update current supplier details, then upload to Lightyear to bulk update. Or use this template of Suppliers within one account, and upload to another to quickly copy suppliers across accounts.
Head back over to you Suppliers page and click the
Export CSV File button in the top right. This CSV will provide you with the same information required for a Bulk Upload.
This CSV file format can be edit and re-uploaded to Lightyear again!
Tips and Tricks
If you have issues with the formatting of your CSV document, these tips and tricks may come in useful!
Issue: Excel is truncating numbers in certain columns which is giving an incorrect result. Example below.
Solution: Highlight the column or cell causing issues. Right click and select
Format Cells. In the Number tab, select Text and click OK. This will change the column to read the values like text. If the cells don't automatically update, click into each one, select the value and click Enter. This will re-format the cell to be read as text.
Issue: Excel is removing the format I have previously set.
Solution: Excel will automatically remove the formatting of a CSV each time it is opened. It is best to make all changes at once, then save the document. Before you upload, the CSV shouldn't be opened again, as this will edit the format once again.