Bulk Supplier Upload

Bulk Supplier Upload

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.

CSV Layout

*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  Code 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. Joe Bloggs.
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:
  1. From Bill Date - Set From End of Month to FROM_BILL_DATE and enter the number of days 
  2. From End of Month - Set From End of Month to FROM_END_OF_MONTH and enter the number of days 
  3. Due on Receipt - Set From End of Month to DUE_ON_RECEIPT and enter 0 as the number of days
  4. Use Extracted Due Date - Set From End of Month to USE_EXTRACTED_DUE_DATE and leave Number of Days Credit blank
  5. Last Working Day of Month - Set From End of Month to LAST_WORKING_DAY_OF_MONTH and leave Number of Days Credit blank
  6. Last Day of Month - Set From End of Month to LAST_DAY_OF_MONTH 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.
Account Number  - If using the Bank Details Checker, this refers to the Bank Account Number of the supplier.
IBAN  - If using the Bank Details Checker, this refers to the IBAN of the supplier.
Swift/BIC Code  - If using the Bank Details Checker, this refers to the Swift Code of the supplier.
Payment Ref  - 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.
Accounts Email - 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. johndoe@gmail.com;janedoe@gmail.com
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. johndoe@gmail.com;janedoe@gmail.com
Remittance Email - To enter multiple Email Addresses on the CSV, separate each with a semi-colon. E.g. johndoe@gmail.com;janedoe@gmail.com
Town/City  - City of the Supplier.
Contact Number  - Contact Number of the Supplier.
State/County  - State/County of the Supplier.
ZIP/Postcode  - 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 Yes or No.

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 and Reason.
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.

Supplier Upload Template

Finally, click below to download an excel file to collate your Supplier List in. Once done, simply export to a CSV file and that's you 90% closer to automating your AP.


      

    Check out our
    Product Blog Series
    to stay up to date

      • Related Articles

      • Bulk upload lists for set up

        Bulk Upload is a simple and fast way of uploading information to your Lightyear account. You can make use of Bulk Upload in the following areas of Lightyear: Accounting Software (For Adept, Attache, MYOB Desktop, Pronto, Sage and Sun Systems) ...

      • Setting Supplier Card Country Codes

        In Supplier Cards, Lightyear can import the Country Codes from your Accountancy Software. There are instances where some Accountancy Software don't store the Country Code, or the Country Code they store is different from Lightyear, e.g. GBR vs GB, ...

      • Uploading Products - Products Catalog and Price Checker

        This is a step-by-step guide to uploading Products in Lightyear. There's a multitude of benefits in doing so. You can use this as part of the initial supplier set up to automate line item coding, create your Product Catalogue in PO's so items can ...

      • Step 8: Tell your Suppliers what to do

        FOR YOUR TEAM Once your staff have been brought up to speed to the changes re Lightyear, you should now start asking your suppliers to email your bills to your xxxxx@lightyear.cloud account. No-one particularly likes change, but from the very outset ...

      • Step 4: Approve and Export your bills

        The time has come! Here we'll take you through approving and exporting bills. Your approval workflow is going to depend on how you customise your account settings which is actually our next step. The guide for Step 5 will let you know which user ...