How do I properly format bank CSV files
To help automate more data entry for poorly supported banks an credit cards, Seller Ledger makes it possible to upload transaction data in comma-separated file format (CSV.) There are only a few requirements for this formatting, but failure to follow them could result in the file causing an error upon upload.
Column headers
In order for Seller Ledger to know what information is in which column, we require that the first row of the file include a name that describes the data within that column. While you can have names for many columns of data, we care primarily about 3 required ones and 1 optional one:
Required:
- Date - to be used at the top of the column that provides the date of the transaction
- Amount
- Description
Optional:
- Currency
Here's an example of what a simple, correct set of column headers might look like:
Format of data in each column
In addition to making sure that each column has the correct header name, there are some additional guidelines about the data within each column:
Date
All date values must be in mm/dd/yyyy. Yes, this is very US centric, but that's the bulk of the customer base we currently support. Please note, if you open your CSV in Excel, you may see dates shows as mm/dd/yyyy, but that may just be a display option. Click into the individual cell to make sure the year is 4 digits.
Amount
Amounts can be positive or negative, but must not contain any currency information. For example, no not include formatting using $, USD, CAD, etc. Just the number and the sign (positive or negative)
Note: while amounts can be positive or negative, it matters when each sign is used.
For checking and savings accounts, positive amounts are assumed to be deposits, while negative amounts are assumed to be withdrawals.
For credit card accounts, positive amounts are assumed to be payments made to your credit card and negative amounts are assumed to be purchases/charges.
Most banks that we have worked with follow these defaults, but we have seen (at least with credit cards,) examples where the signs on the numbers are reversed. In such a case, simply open your file in a spreadsheet, reverse the signs to match the assumptions above.