Open the spreadsheet you wish import and map Delete the rows of information not required for the import (see below screenshot, the area highlighted in grey), for example, report date, invoice number, account number, etc.. Save the file as a CSV file. You may be prompted that “Some features in your workbook may be lost if you save it as a CSV (MS-DOS). Do you want to keep using that format?” Click Yes. Go to Poolcar > Sysadmin> Fuel Card Formats, found under the System Data heading. For the 'name' field, type in (for example) Flintstone Fuel Solutions Now we start mapping the import fields to the columns in your spreadsheet. Open your spreadsheet and keep it handy as we will be referring back to it often. Go to the first row of the table which says 'Purchase Date'. Flip back to your spreadsheet and look for the column which has the purchase date and note the column number. Column A in Excel would be column number 1, B is 2, C is 3, and so on. You might find it helpful to insert a new row and put the column numbers in for reference. Let's say that in our spreadsheet, purchase date is column E (column number 5). Flip back to PoolCar and select '5' for the column number. Rinse and repeat for the remaining data fields. If your spreadsheet does not have a value, such as Driver name for example, select 'Skip' from the column number list. Skip means the field will not be imported. The last row asks you for the price format. Some vendors express the pump price in cents, such as 149.0 for one-dollar-forty-nine, and other vendors may express the pump price in dollar terms, such as 1.49 - select the format that your spreadsheet uses. Once you've mapped your spreadsheet columns to data fields, click the 'Save' button. Note you can come back and make changes (edit) if there is a problem discovered after your first import.
Navigate to Sysadmin Main Menu > Fuel Card File (under the Uploaders heading) and try your file. |
A note about purchase date - some vendors combine the purchase date and time in the one column, and other vendors may have the date in one column and the time of purchase in another column. The latter is easy - just select column X for date and column Y for time. For the latter, where the purchase date and time is combined in the one field, select the same column number for both data fields purchase date and purchase time. |
We have a column called 'Divide Value By'. This is to accommodate fuel files where the values you receive from the fuel company are not in decimal notation. For example, you may have the pump price to four decimal places but represented as a whole number: "15645" which is 1.5645 when you divide by 10,000. To get this price imported in a consistent format, that is say decimal notation, you then specify the corresponding divide-by number. |
