HOWTO: Modify the payment card report file format in order to upload it to Zetadocs Expenses by following the standard transaction mappings
This Zetadocs technical note applies to:
- Zetadocs Expenses Essentials
Zetadocs Expenses users may encounter an issue when trying to upload their credit card statement to Zetadocs Expenses due to a formatting error similar to the example as below:
The reason why the import is failing is because of the format of the statement file from the user’s bank did not match the standard format for direct comma-separated values (csv) imports for the card type selected.
Usually, the users will be able to download the file with the transactions from their bank and import it into the Zetadocs Expenses instantly without altering anything as the correct format is already in place.
However, on rare occasions some banks e.g. Lloyds Bank have developed their own methods in which the users can get transactions from them since this functionality was implemented in Zetadocs Expenses Essentials edition version as above. Therefore, the users can pre-select which specific data to download for multiple categories rather than receive a standard file with typical data. For example, for the category “Expense Report” the user can choose from:
This means that the users may need to perform a manual change in this version of Zetadocs Expenses in order to transform the data into a report format which can be successfully read and imported to Expenses. The steps below present how this can be achieved by configuring the .csv statement file as easiest as possible.
Here is the screenshot from our sample* statement with transaction mappings showing how we get the data for a csv import using the Lloyds Bank card type:
The columns names are specifying what data would be in which column e.g. PostingDate. Please also note these rules for the successful import:
- The order of columns have to be in the same positions
- The names are case sensitive and have to match exactly including white spaces
Everything highlighted green in our sample* statement spreadsheet requires data by Zetadocs Expenses software AND needs to be in this column. Thus, e.g. the transaction date must be in column 1, the posting date must be in column 2, etc.:
Please pay attention to and do not skip the column position 37.
Additionally, in order to import successfully, the variables names need to be in the second row as these column headers are part of the import. Therefore, summarizing it in the table:
The main part of the workaround is that for example with Lloyds Bank, the users can preselect which data to download as well as define at the same time which data would be in which column e.g. last name column 3, etc. by reordering the selected fields (Alt + up or down arrow).
This is where our recommended step is for this manual workaround which would be to set random data into columns that our software does not read. So for example, in column 5 you can put a random category. All other columns would be skipped by our software. However, we recommend to have something in these columns, so in this way you can copy the data from your downloaded statement and paste it directly = the correct data would be in the right columns and then you can import straight away from there to Zetadocs Expenses. The manual process in this way would be the quickest: cut from the old statement and paste into the new statement without any further formatting needed and so it is ready for uploading.
*The sample template spreadsheet file with the mappings where our software needs data is freely available by contacting Equisys Support.
Last updated: 16th October 2020 (AL)
Keywords: Expenses Essentials, Import file format