Home > Managing Banking Information > Using Electronic Bank Statements > CSV Formatted Files > File Selection and Field Mapping

File Selection and Field Mapping


The first step in the bank file import wizard is to select the file, associate to a bank account and map the fields in the file to the corresponding database fields.


File Selection

To select the file, you can type in the full path of click the Browse button to open the File Explorer window and navigate to its location. By default, Bookkeeper will look here for bank import files: Documents\Bookkeeper\Electronic Download Files\Bank Account\. But you can store them anywhere you'd like. But know that you will have to navigate the file explorer to the folder you are storing the files.


* Make sure the file is not open in another program


Once the file has been selected, enter the values for the Data Starts At Row, the Column Header Row and indicate whether this file has no header. 


    • Data Starts At Row - this is the row in the file where the transaction data starts. This does not include the column headers but where the actual data begins.
    • Column Header Row - this is the row that contains the column headings (if they exist). Sometimes export files may contain extraneous information in the first few rows (e.g. date of export, account number, etc.)  The import process is not interested in that data but really needs to know the column headers in order to map to the database fields.
    • File Has No Column Header - In some cases, export files do not contain any column headers and just has data. If this is the case, check this box. Then when the Get Fields button is clicked, generic column names will appear in the import file field name list. You'll need to know which column has which data to map to.


Now click the Get Fields button in the upper right. This will read the file and attempt to grab the column header names (if they exist) and populate the list on the left side.


Select the bank account that this import file is associated with. If you want to retain these mappings in the future, enter a name in the Import Template field. (If you already have a template assigned and you want to remove it, just blank out the Import Template field,)


The template will be saved when you click on the Next button to go onto the next step in the import wizard. After the template has been saved, the next time you import a file and select the bank account, the template name will appear automatically. You can then click the Map button to re-apply the mappings. (Note: When importing always make sure to click the Get Fields button to populate the File column headers even if the template has been saved.)

Mapping

With the file column name list populated, you can now map the file columns to the corresponding database columns. Click the column header on the left side (file columns) then click the related database column on the left side and then click the Assign button. The field mapping will then appear in the bottom section.


If you need to unassign a mapping, click in the Current Field Assignments list to highlight the mapping and click the Unassign button. You can also remove all mappings by first clicking Select All and then Unassign.


For mapping transaction amounts, there are two ways this information may be presented in the import file:

- amounts in the file that contain both debits and credits in a single column (e.g.  17.88  &  -21.45)

       Map to the 'Amount'  field in the list of database fields

- individual columns for debits and credits

       Map the Debit and Credit fields accordingly to the Debit Amount / Credit Amount database fields


Note that is possible to assign the same field in the import file to different fields in the database. For example, you could assign a Description field in the Import File to the Description database field as well as the Line Item Memo field in the database.


Data Types

When columns are mapped, it's important to make sure the types of data in each of the columns in the file match to the type of data the database is expecting (e.g. date fields are in a standard type format like mm/dd/yyyy and dollar amounts are just numbers/decimals like 25.09 and not $25.09.  If you need to adjust these, you can open the import file in Excel first, make the changes and then re-save the file (make sure any changes saved still retain the file format as .csv).