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.

Recommended reading: Invoicing FAQ

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:

  • FILEID
    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 or one of the custom fields.
    Maximum length 50 characters
    * Required column
  • AMOUNTNET
    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
    * Required column
  • DESCRIPTION1
    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
  • DESCRIPTION2
    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
  • CREDIT
    The name of the photographer/supplier
    Maximum length 250 characters
    Optional column
  • REGION
    This is the sales region as text. E.g. United Kingdom
    Maximum length 250 characters
    Optional column

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.

Note that your CSV files should not contain too many details or your invoice may become very large and slow to load. You can of course break up your data into multiple files and then create separate invoices.

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. 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. Enter settings and process imported rows

You can now 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.
    – Supplier settings, this applies the percentage that is configured for each of the suppliers.
    – 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.
  • 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.

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.

Step 5. 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 6. 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 and that the columns have valid characters only.