>, Staff member functions, Tutorials>Importing invoice details and agency sales data

Importing invoice details and agency sales data

This article explains how you can import invoice details from a CSV file (Comma Separated Values). This function is also used to import sales data that you receive from affiliate agencies.

Page last updated on 25 September 2022.

Recommended reading:

Step 1. Prepare a CSV file for importing

You can create a CSV file with any text editor but it’s probably more convenient to use for example MS Excel or other office software (e.g. the free LibreOffice Calc).
The CSV file can have the following columns and column titles, the column titles are important:

    This column must have values that can be used to look up the file in your database. It can have the Infradox 8-digit numbers but it can also have other Id’s that you have stored in e.g. objectname, original file name or one of the custom fields.
    Maximum length 50 characters
    * Required column
    This is the net amount without tax et cetera. If you use multiple currencies, note that the amount is imported as your base currency. You must select a user account that has the same currency as your website’s base currency. You can however convert the imported amounts afterwards.
    Must be properly formatted and it must use a dot as a decimal separator. E.g. 50.00. The amount column may not have a currency symbol.
    * Required column
    This is the text that will be imported into the invoice detail description. E.g. Textbook interior: print and electronic.
    Maximum length 1000 characters
    Optional column
    This is the text that will be imported into the second invoice detail description.
    Maximum length 1000 characters
    Optional column
  • DATE
    Date formatted as text.
    Maximum length 25 characters
    Optional column
    The name of the photographer/supplier
    Maximum length 250 characters
    Optional column
    This is the sales region as text. E.g. United Kingdom
    Maximum length 250 characters
    Optional column
    A value that can be used to look up the contributor/supplier if a detail can’t be matched with a file in the database (32.19 or later)
    Maximum length 50 characters
    Optional column
    The royalties percentage if you want to apply such values from the imported file – the data format is 0.00 (32.19 or later)

Make sure that the column titles have no trailing or leading spaces.

If your CSV file already has the columns but the titles of the required columns are different, then simple change the titles (e.g. change Image number to FILEID) before you upload the file.

Your CSV file does not need to have any of the optional columns, and it can have more columns too. However, additional columns (other than the ones listed above) are not imported.

If your CSV file has the columns Description1, Date, Credit, Region (one or more of these) then the values are added together in the invoice detail description field.

If your CSV file has the column Description2, its value will be imported into invoice detail description 2.

If your CSV files contain many details, your invoice will become large and therefore less manageable. You can either manually break up your CSV data into multiple files and then create separate invoices, or you can have the import process create multiple invoices automatically by specifying a threshold in the import dialog. E.g. enter a value of 1000 to automatically create a new invoice after the first 1000 details. The number of invoices that will be created depends of course on the number of details in your sales data file (and the threshold value that you have entered).

Step 2. Create a new invoice

Go to Invoice management in Back office. The invoice overview page has a toolbar underneath the selection criteria panels. In this toolbar, type the name of a user account for who you are going to creating an invoice. When you type part of a name, a list with possible matches will appear. Click on the user account in this list to select it. You can now click on the New button – next to the user account name input box – to create a draft invoice. Confirm the dialog box that will appear, and the new invoice will load.

Step 3. Import your CSV file

In the toolbar at the top of the invoice page, click on Import. A dialog will appear.

Configure the upload settings for your file. You can click “Use Windows defaults” or “Use Mac defaults” to apply the default settings. If you’ve created or saved your file on a PC then use the Windows settings, if you’ve created or saved your file on a Mac then use the Mac settings.

You can either drag your CSV file to the dialog or you can click the Upload a file button to select a file. When the file is uploaded to the server it will be imported into a temporary database table. The dialog will show the number of rows that have been imported.

Step 4. View the imported rows

Click on “View imported rows” to check that the temporary table has the correct imported values.

Step 5. Configure the processing settings and process the imported rows

When you’re ready, enter the following settings for your import in the dialog:

  • Matching field
    Select a field in the dropdown. If your CSV file has 8 digit Infradox id’s without leading zeroes then select ID. If your CSV file has Infradox id’s with leading zeroes, then select the field Refcode. Or select any of the other fields if your not importing Infradox system id’s.
  • Tax
    If you want to add tax to the amounts that you are importing then select the appropriate option in the Tax dropdown.
  • Select one of the radio buttons for the supplier percentage:
    – Fixed percentage, enter a percentage in the input box. This percentage is applied regardless of supplier defaults or group settings.
    – Contributor settings, this applies the percentage that is configured for each of the contributors.
    – Contributor + royalties rules, this applies the percentage that is configured for each of the contributors unless there’s a matching royalty rule that will override the default.
    – Group import settings, this applies the percentage that you have configured as the “import percentage” for each supplier group. Edit the percentages via Supplier management first.
    – Imported percentage, this will apply the imported royalties percentage. If less than 1, then the contributor’s default percentage is used.
  • Mark as payable to supplier
    Check this box to immediately mark the invoice details as payable to the supplier(s).
  • Import rows for which a file can’t be found
    If the file can’t be found in the content database, you can still import the row – but it will be imported as a normal invoice detail, i.e. not linked to a file and supplier/photographer. You can link such invoice details to suppliers manually after the import has completed. In version 32.19 or later, your CSV file can have a column CONTRIBUTORID that can be used to automatically link invoice details to the suppliers if such details can’t be linked to a file. I.e. if there are sales records for files that are not in your database.

When you are done, click on the Process imported rows button.

The server will process the rows in the temporary table, and when it’s done it will display the results in the dialog. Click Ok to close the dialog and to reload the invoice page with all the imported details added.

If processing failed, check your settings to make sure the correct “Matching field” is selected, and click Retry to try again.

Click on the Save settings button at the bottom of the dialog to save the current import and processing settings. The next time you use the dialog the saved settings will be automatically applied.

Step 6. Assign unmatched sales/invoice details to suppliers

If there are invoice details for which a file could not be found in your database, you can attach those details to suppliers (contributors) manually. The right most column of the invoice details list will show links “Attach to supplier”. To attach a single invoice detail to a supplier, click on the link to open the dialog. If you want to attach multiple invoice details to the same supplier, then first select the details with the checkboxes and then click on any of the “Attach to supplier” links.

Step 7. Currency conversions

Amounts in your import file are imported as amounts in your own currency. If the user account for which you have created the invoice is configured for a different currency, then the amounts are automatically converted. Click the edit button on one of the imported invoice details to check the converted amounts. If however you have imported amounts in a different currency then your website currency, you can now click the Rate conversion button in the toolbar. This function allows you to enter a conversion rate and to apply that to all the invoice details. Let’s say you have imported sales data in US Dollars and you want to convert the amounts to GBP – look up the conversion rate (e.g. at www.xe.com) and enter it in the input box. If the rate of the Dollar is 0.82187 GBP, then that’s the number you enter in the input box. Next, click the Convert button. The invoice will load again after the conversion has been executed on the server.

Step 7. Making changes and finalising the invoice

You can make further changes to the invoice (e.g. delete some of the imported rows, change prices, payment percentages et cetera) as long as the invoice is not saved as final. You can also add more details, import another CSV file and so on. When you are done, check the box “Final” and click on Save or Save and Close.

Problems with importing

  • If you want to start over again, click the button Delete details in the toolbar and repeat the process.
  • If the import of the CSV file returns errors, then check your CSV file to make sure that it has the required columns, that the columns have valid characters only and that the column names have no leading or trailing spaces.