<sect1 id="details.impexp.csv">
<sect1info>
   <author> &Allan.Anderson; &Allan.Anderson.mail; </author>
</sect1info>
<title>CSV Importer Plugin</title>

<sect2>
<title>Reasons for importing CSV Files</title>

<para>
  In general, it is preferable to import OFX.  However, not all institutions
  provide data in that format.  CSV (comma separated value) files are almost
  always available, sometimes described as Excel or spreadsheet files.  Also,
  they can often be created fairly easily by capturing the data you want to
  import, such as in a text file, and manually editing it.
</para>

<para>
  The primary focus of the plugin is on importing data from bank statements, but
  there is also a capability to import some investment statements.  This plugin
  was initially created, before becoming a CSV importer, to produce QIF files
  from CSV, which could then be imported.  This functionality is still present,
  but is likely to be removed, as the plugin now focuses on directly importing
  CSV files.  Also, &kmymoney; has the native ability to <link
  linkend="details.impexp.qifexp">export QIF files</link>, so there is no real
  reason to produce a QIF file from a CSV file.
</para>
</sect2>

<sect2>
<title>Getting the plugin</title>

<para>
  &kmymoney; will import CSV files.  This functionality is provided as a plugin,
  and it is now built into the core program, both in distro packages and in the
  source files.  Once the distro package is installed, or the source files are
  compiled and installed, the menu choice to import CSV files will automatically
  show up under the
  <menuchoice><guimenu>File</guimenu><guimenuitem>Import</guimenuitem></menuchoice>
  menu.
</para>

<para>
  The CSV importer plugin is much newer than the OFX plugin, but most
  distributions are now built with the CSV importer already included or
  available as a separate package.  Ensure that it is enabled within &kmymoney;.
  Check the <menuchoice><guimenu>Settings</guimenu><guimenuitem>Configure
  KMyMoney</guimenuitem><guimenuitem>Plugins</guimenuitem></menuchoice> menu.
  If the CSV importer does not seem to be installed in your version, the first
  place to check is in the same place you got your base &kmymoney; package. See
  if a later version is available, or if the importer is available as a separate
  package.
</para>

<para> 
  If you have installed from RPM or Deb, the CSV Importer Plugin should be
  contained within the &kmymoney; package.  If you have built from source, there
  should be no additional requirements.  The &kmymoney; build process should
  detect the plugin source and compile the plugin.
</para>
</sect2>

<sect2>
<title>Importing a CSV file</title>

<para>
  To import a CSV file, choose the importer from the menu bar:
  <menuchoice><guimenu>File</guimenu><guimenuitem>Import</guimenuitem><guimenuitem>CSV...</guimenuitem></menuchoice>.
  If CSV does not show up under Import, you do not have the CSV Importer Plugin
  installed correctly.  Please see the previous section.
</para>

<para>
  The CSV Importer is in the form of a wizard, with a separate page for each
  individual step of the process.  There may be some minor differences between
  the text in this handbook, the labels in the screenshots, and the labels you
  actually see in the wizard.  In such cases, the handbook describes what the
  wizard will look like in the next release.
</para>

<sect3>
<title>CSV Import Wizard: Start</title>

<para>
  When started, the CSV Importer displays the <guilabel>Start</guilabel>
  page.  The upper area, where data will be displayed, is initially empty.
  Below that, on the left, is a list of the steps of the import process, with
  the current one highlighted.  To the right of that are some brief instructions
  and two radio buttons, allowing the choice of either
  <guilabel>Banking</guilabel> or <guilabel>Investment</guilabel>.  Next there is
  a profile selector box, which is enabled once one of the radio buttons has
  been selected.  At the bottom of the display are buttons to move on to the
  next step of the wizard, go <guibutton>Back</guibutton> to the previous step,
  or <guibutton>Cancel</guibutton> the import.  At the initial step, there is
  also a button <guilabel>Select File</guilabel> to initially select the file to
  import.
</para>
<!-- want to use inlinemediaobject to avoid lines above and below. -->
  <screenshot>
  <mediaobject>
  <imageobject>
  <imagedata fileref="csvImporter_1.png" format="PNG" />
  </imageobject>
  </mediaobject>
  </screenshot>

<para>
  Also, note the <guilabel>Skip setup</guilabel> checkbox next to the profile
  selector.  Initially, you should not select this check-box.  Once you have set
  up a profile and finished the wizard, those parameters are saved in the
  resource file.  Next time you use that same profile, the parameters will be
  loaded into the UI (User Interface).  The wizard would then plod through the
  following pages of parameters that you won't need to change.  So, instead,
  once you are happy with a profile, it may be helpful to check this box.  The
  wizard will then move directly to the final page, and, assuming no problems
  are found, you just have to click <guilabel>Import</guilabel>.
</para>

<para>
  First select either <guilabel>Banking</guilabel> or
  <guilabel>Investment</guilabel>, then click in the selector box, which displays
  "Add New Profile."  If you have previously created any profiles, you can
  select one of them, otherwise enter a new profile name, possibly the name of
  the account into which you wish to import.  If you enter a new profile name,
  hit &Enter; to create it.  Then, click on <guilabel>Select File</guilabel>,
  and a standard file selector box will open, from which you should select the
  CSV file you wish to import.
</para>
</sect3>

<sect3>
<title>CSV Import Wizard: Separators</title>
<para>
  The wizard will now have advanced to the <guilabel>Separators</guilabel> page,
  and you should now see your data.
</para>

  <screenshot>
  <mediaobject>
  <imageobject>
  <imagedata fileref="csvImporter_2.png" format="PNG" />
  </imageobject>
  </mediaobject>
  </screenshot>

  <warning>
    <para>
      It may appear that the displayed entries in the upper section of the
      plugin window may be edited, and in fact they may, but any edits are not
      kept.  The table is purely for display, not for editing.  The input file
      is never altered by the plugin, and the data actually imported comes from
      the input file, not from the display.  The one exception to this is
      covered in <link linkend="details.impexp.csv.secsym">Securities and
      Symbols</link> below.
    </para> 
  </warning>

<para>
  The plugin should have detected the appropriate <guilabel>Field
  Separator</guilabel> to use, and it is not usually possible to select a
  different one.  In fact, attempting to do so will reset any field choices you
  may already have made.  There is also a selector for the <guilabel>Text
  Delimiter</guilabel>, but generally the quote (") is correct.  Now click on
  the <guilabel>Next</guilabel> button.  Depending upon the earlier selection
  you made, you will now be on either the Banking page or the Investment page.
</para>
</sect3>

<sect3>
<title>CSV Import Wizard: Banking</title>
<para>
  On this page, you select the column numbers from which to import the relevant fields.
</para>

  <screenshot>
  <mediaobject>
  <imageobject>
  <imagedata fileref="csvImporter_3.png" format="PNG" />
  </imageobject>
  </mediaobject>
  </screenshot>

<para>
  For most fields, you just need to use the appropriate dropdown to select the
  appropriate column.  However, there are a few special considerations.
</para>

<itemizedlist>
<listitem>
<para>
  In the center are two radio buttons. If your file has a single column for all
  values, select <guilabel>Amount col</guilabel>. However, if there are separate
  columns for debits and credits, select <guilabel>Debit/credit col</guilabel>.
  This will enable either the <guilabel>Amount column</guilabel> selector or the
  <guilabel>Debit column</guilabel> and <guilabel>Credit column</guilabel>
  selectors.
</para>
</listitem>

<listitem>
<para>
  It is possible to select more than one column for the Memo field, by
  consecutive selections.  Memo columns already selected are marked in the
  drop-down with an asterisk (*).  If you select multiple columns in this way,
  their contents will be concatenated in the Memo field.
</para>
</listitem>

<listitem>
<para>
  If you attempt to choose the same column number for two fields, the plugin
  will alert you and clear both selections.  However, it is possible, if
  desired, to use the same column in both the
  <guilabel>Payee/Description</guilabel> and <guilabel>Memo</guilabel> fields.
  If you select a column for the <guilabel>Payee/Description</guilabel> field,
  and then select the same field for the <guilabel>Memo</guilabel> field, you
  will receive a warning that duplicate columns have been selected, but asking
  if you wish to proceed.  If you do, click <guibutton>Yes</guibutton>.
</para>
</listitem>

<listitem>
<para>
  One particular reason to also capture the Payee/Descriptor field in the Memo
  field is that the incoming Payee/Description field might get completely
  changed on import when &kmymoney; does transaction matching.  Selecting that
  field also as Memo will preserve that data, which would otherwise get lost.
</para>
</listitem>
</itemizedlist>

<para>
  If you notice you have made an incorrect choice, just change that selection.
  If several changes need to be made, click the
  <guilabel>Clear</guilabel> button. 
</para>

<para>
  Once columns have been chosen for all the necessary fields, the
  <guilabel>Next</guilabel> button will be enabled, and clicking it will advance
  the wizard.
</para>
</sect3>

<sect3>
<title>CSV Import Wizard: Investment</title>
<para>
  This page is similar to the <guilabel>Banking</guilabel> page, although it
  is somewhat more complex.  Most selections are fairly obvious, but there are
  some items which can seem confusing until you have completed the process
  once or twice.

  <screenshot>
  <mediaobject>
  <imageobject>
  <imagedata fileref="csvImporter_4.png" format="PNG" />
  </imageobject>
  </mediaobject>
  </screenshot>
</para>

<para>
<itemizedlist>
<listitem>
<para>
  As on the <guilabel>Banking</guilabel> page, you may select more than one
  column for the Memo field
</para>
</listitem>

<listitem>
<para>
  The <guilabel>Type/Action</guilabel> selector is to identify the column which
  contains the action type, such as Buy, Sell, Dividend, etc.
</para>
</listitem>

<listitem>
<para>
  The <guilabel>Price Fraction</guilabel> selector is to indicate the
  fraction/multiplier for compatibility between imported and stored prices.  For
  instance, if the import file price is in cents, but your &kmymoney; account is
  priced in dollars, select 0.01.  Or, if your &kmymoney; data file pricing is
  in dollars, and so is the CSV file being imported, then set <guilabel>Price
  Fraction</guilabel> to 1.0.
</para>
</listitem>

<listitem>
<para>
  Use the <guilabel>Fee Column</guilabel> selector if your file has a distinct
  column for fees.  Beware, though, that the fee might have been taken into
  account in the price.  If there is a fee, and it is a percentage figure,
  rather than a value, click the <guilabel>Fee is percentage</guilabel> check
  box.  Note that on this page, this is the only field to explicitly include
  "column" in the label, to emphasize that it is for the fee column, not for any
  actual fee.
</para>
</listitem>

<listitem>
<para>
  Below the column selectors are two areas for the security identity.  Depending
  upon your broker or financial institution, your file may contain entries for
  only one or for several securities.

  <itemizedlist>
  <listitem>
  <para>
  If the file contains transactions for just a single security, with the name
  possibly in a header row, the name should be entered into the
  <guilabel>Security Name</guilabel> box. The name you enter will be added to
  the drop-down list for future use. You may subsequently wish to remove that
  name from the list.  If so, select it, then click the <guilabel>Hide
  security</guilabel> button.  This removes it only from this list, and has no
  effect on your main &kmymoney; file.
  </para>
  </listitem>

  <listitem>
  <para>
  If the file includes transactions for several securities, each will be
  identified by its ticker symbol in a column with further detail in another
  column. Select those columns in the <guilabel>Symbol</guilabel> and
  <guilabel>Detail</guilabel> selectors.  It may be that a security has no
  official symbol, and in this case a pseudo-symbol may be invented; this is not
  a problem, as long as it uniquely identifies that security in the import file.
  Sometimes the actual activity type is embedded in the detail column, possibly
  prefixed by a standard text. For instance, if the field contains <quote>type:
  dividend</quote>, enter into the <guilabel>Filter text</guilabel> box
  <quote>type: </quote> including the trailing space.
  </para>
  </listitem>
  </itemizedlist>
</para>
</listitem>
</itemizedlist>
</para>

<para>
  When all required fields are selected,
  the <guilabel>Next</guilabel> button will be enabled, and clicking it will
  advance the wizard.
</para>
</sect3>

<sect3>
<title>CSV Import Wizard: Lines</title>
<para>
  On this page, you indicate if any lines should be ignored at the beginning or
  end of the file.  You also indicate the format of any date column.

  <screenshot>
  <mediaobject>
  <imageobject>
  <imagedata fileref="csvImporter_5.png" format="PNG" />
  </imageobject>
  </mediaobject>
  </screenshot>
</para>

<formalpara><title>Start line</title>
<para>
  Set this so the importer skips any header lines in the file.  Your choice will
  be saved in this profile for future use. The start and end lines interact, and
  the start line may not be higher than the end line. If the <guilabel>Start
  line</guilabel> selector does not respond, check the end line setting.
</para>
</formalpara>

<formalpara><title>End line</title>
<para>
  The importer will automatically set this to the last line in the file, or to
  the setting last saved.  You will only need to adjust it if there are footer
  lines in the file the importer should ignore.  Otherwise, you are likely to
  get a data error warning when the plugin attempts to parse incorrect
  data.  Again, if the <guilabel>End line</guilabel> selector does not respond,
  check the <guilabel>Start line</guilabel> setting.
</para>
</formalpara>

<formalpara><title>Date format</title>
<para>
  This needs to be set according to the order of year, month, and day in the
  dates in the file.  If the plugin finds data incompatible with this setting,
  it will complain when you try to import.  However, if the setting is wrong,
  but does not produce invalid results (such as data with no days higher than 12,
  so month and day could be switched) you will simply get incorrect data,
  because the plugin cannot know you made a mistake.  In this case, the error will be
  obvious in the ledger after import.
</para>
</formalpara>

<para>
  Once ready, the <guilabel>Next</guilabel> button will be enabled, and clicking
  it will advance the wizard.
</para>
</sect3>

<sect3 id="details.impexp.csv.secsym">
<title>CSV Import Wizard: Securities and Symbols</title>

<para>
  For an Investment file, after the <guilabel>Lines</guilabel> page has been
  accepted, you need to assure that each security in the file is matched to the
  correct security in your &kmymoney; file, before import can proceed.  At this
  point, another window will open, showing the securities and symbols contained
  in the import file.  Note that unlike the data display in the main wizard
  windows, the changes you make on this page <emphasis>are</emphasis> imported.
</para>

<para>
  Completing this page is straightforward, if you consider these items:
  <itemizedlist>
    <listitem>
    <para>
      Each row represents one transaction, and so it may appear there are
      duplicate rows.  This is OK.
    </para>
    </listitem>

    <listitem>
    <para> 
      Each security name must match exactly the existing security as specified
      in &kmymoney;.  If it does not match, it will be created as a new
      security, which you probably do not want, unless it represents the
      purchase of a new security.
    </para>
    </listitem>

    <listitem>
    <para>
      A symbol must be shown for each security.
    </para>
    </listitem>

    <listitem>
    <para>
      The <emphasis>only</emphasis> information on this page should be the
      security symbol and name.  Any other information initially shown (such as
      date or activity type) is still in the actual import file, but should not
      be shown here.
    </para>
    </listitem>
  </itemizedlist>
</para>

<para>
  You can edit a symbol or security name by double clicking the cell.  For
  each security, if necessary, edit the name in one of its rows, If the correct
  security name appears in the imported file, double click on it to select it,
  then copy and paste/edit, taking care if you have used a variation or
  abbreviation within &kmymoney;.  If you edit a security name, that edit will
  be applied to all rows with the same symbol.
</para>

<para>
  Any line without a symbol will be treated as a brokerage-type checking
  item. If any transaction involves another account, &eg;, a checking or
  brokerage account for a received dividend or for making a payment, a message
  box will pop up for the account name to be entered for the transfer.  This
  will generally be the Brokerage account you chose or created when you created
  the Investment account.  Similarly enter the column number containing the
  payee, if requested. If a mistake is made when entering the account name, the
  import will go ahead, but &kmymoney; will not recognize it, and will flag
  those transactions as missing a category assignment.  If the required account
  name is rather long, just enter a few characters.  The import will proceed but
  the transactions will be flagged by &kmymoney; as missing a category
  assignment, and you will need to select the correct transfer account after the
  import. Click <guilabel>OK</guilabel> when done. The import process then gets
  handed over to
  &kmymoney;
</para>

<para>
  If you have more that one transaction referring to the same security, you can
  edit all of them at once, using multi-select.  For instance, to add a symbol for
  several lines, press and hold the <keysym>CTL</keysym> key, and in the symbol column,
  select each transaction.  While still holding the <keysym>CTL</keysym> key,
  all those symbol cells should still be selected, so click on one and enter the
  symbol.  Click inside the window but outside that column, or hit
  <keysym>Enter</keysym> (not <guilabel>OK</guilabel>).  Now that those
  transactions all have the same symbol, double click one detail entry and edit
  the security name as you wish.  Click elsewhere on the window (or
  <keysym>Enter</keysym>) to accept the edit, which will then change all
  those entries.  The remaining entries will show the symbols picked up from the
  transactions in the import file.
</para>

<para>
  Now click <guilabel>OK</guilabel>, then <guilabel>Import</guilabel>.  In the
  <guilabel>Enter Account</guilabel> box, enter the name of a Brokerage/checking
  account for funds.  If you enter a valid name that account will be used.  If
  you can't be bothered entering a correct but long name, enter a few
  characters.  The import will accept that but the transactions in the ledger
  after import will need a proper account to be selected.  For the
  <guilabel>Brokerage Item</guilabel> box, enter the number of the column
  containing that detail. Now, on the <guilabel>Invalid transaction</guilabel>
  box you may get a few entries because the activity type does not match the
  qty/price/amount combination.  On each message, click <guilabel>Select
  Transaction Type</guilabel>, and a drop down will appear indicating valid
  activity types for that combination of values.
</para>

<para>
  Now the import has occurred and you're into KMM to select the investment
  account to use.  Then the checking account, if there were any brokerage type
  transactions.
</para>
</sect3>

<sect3>
<title>CSV Import Wizard: Finish</title>
<para>
  On reaching the Final page, the plugin automatically validates the values.  If
  the numeric value column/s is/are highlighted in green, then the validation
  was successful and all that is necessary is to click <guilabel>Import
  CSV</guilabel> and control then passes to the main &kmymoney; program.
  However, if the start and/or end lines are incorrectly set, or if the wrong
  columns were selected, the highlighting will be in red, and an error message
  will appear indicating where the error lies.  The user will then need to click
  <guilabel>Back</guilabel> to get to the relevant page to correct the error.
</para>

<para>
  It might also be that if debit and credit columns are in use, one of those
  columns may legitimately contain no entries.  This would mean that that column
  has no decimal symbol present, and this would result in a warning.  If you see
  that this is the case, you may click either of the <guilabel>Accept</guilabel>
  buttons (<guilabel>Accept this</guilabel> or <guilabel>Accept all</guilabel>.)
</para>
 
<formalpara><title>Decimal Symbol</title>
<para>
  Another possible problem might be that the selected decimal symbol is
  incorrect. Selecting the symbol to match the data should clear that error.
  Normally, you should not need to change this selection.  Note that the
  <guilabel>Decimal Symbol</guilabel> must be set to match your file, not your
  locale.  If your locale setting has a different value, conversion will be seen
  to take place.  The display of the file in the upper part of the window will
  show numeric fields highlighted in green if the current setting produces valid
  results, otherwise in red.  The highlighting also reflects the <guilabel>Start
  line</guilabel> and <guilabel>End line</guilabel> settings.  There could be
  warnings if any of the selected cells appear not to contain the selected
  symbol.
</para>
</formalpara>

<formalpara><title>Thousands Symbol</title>
<para>
  This does not need to be selected, as it is set automatically based on the
  <guilabel>Decimal Symbol</guilabel>. It is provided purely as a guide.  In
  addition, the selector will be inactive if none of the values to be imported
  is greater or equal to 1000.
</para>
</formalpara>

<formalpara><title>Import CSV</title>
<para>
  Clicking this button tells the plugin to actually import the data from the
  file, based on the choices you have made above.  &kmymoney; will prompt you
  for the correct account into which to import the data.
</para>
</formalpara>
</sect3>

<sect3>
<title>Make QIF File</title>
<para>
  This button gives you the ability, after the import has been completed, to
  save the data from the csv file as a qif file, should you require one for any
  reason. This was actually the original functionality that drove the creation
  of this plugin.  However, as &kmymoney; itself is now able to export a qif
  file, the capability is now probably of little use and is likely to be removed
  eventually.
</para>
</sect3>

<sect3>
<title>Finishing up</title>
<para>
  For a <guilabel>Banking</guilabel> import, the plugin has finished, and
  &kmymoney; will prompt you, as stated above, for the correct account into
  which to import the data. For an <guilabel>Investment</guilabel> import,
  however, a little more may be required.  If, during import of a transaction,
  the plugin finds no valid transaction type, it will display the offending
  transaction, and the user may select a valid type to substitute, depending on
  the combination of quantity, price, and amount values.  For every transaction,
  the plugin will validate the column contents to ensure they match the action
  type.  For instance, if a quantity appears but no price or amount, it is
  assumed that the transaction can be only an Add or Remove Shares.  Or, if
  there is an amount but no quantity or price, then a Dividend is assumed, etc..
</para>

<para>
  If you wish to save your settings, remember to click the
  <guilabel>Finish</guilabel> button, and the plugin will then close.
</para>
</sect3>

<sect3>
<title>Adding Investment Activity Types</title>
<para>
  If you find that your investment statements keep including activity types that
  are not recognized, just add them to the section in the resource file.  (See
  <link linkend="details.impexp.csv.config">below</link> for more details on
  this file.)  For instance, in the [InvestmentSettings] section of the file,
  the BuyParam field includes entries for Purchase, Buy, New Inv, and Switch In.
  If you find a different one, add it to the correct list and restart the
  plugin.  You may notice that there are similarities in the entries in
  different fields, and you may find that the wrong activity type is being
  selected.  The plugin checks these lists in the following order: Shrsin, DivX,
  Reinvdiv, Brokerage, Buy, Sell, Remove.  Re-ordering the lists to suit this
  does not work as might be expected, since the entries in the resource file get
  sorted into alphabetical order. If the offending parameter is one you don't
  need, just delete it from the file.  If that is not possible, you may need to
  edit your file before input.
</para>
</sect3>

<sect3 id="details.impexp.csv.config">
<title>Configuration of CSV importer plugin</title>

<para>
 A well-known drawback of QIF format is that it is a fairly loose format.
 With CSV files, there is this same problem, only more so, in that there  is
 no agreed standard at all.  With investment files, in particular, there is
 much more scope for variation in specifying the different types of activities
 represented in the data.  The plugin handles this by listing these activity
 types in a resource file, called csvimporterrc. The location of this file
 depends on your distribution.  It will usually be located in ~/.kde4/share/config/,
 or in ~/.kde/... instead. Using this resource file allows the user to add an
 activity type that the developer had not encountered.  If the file does not
 exist when the importer first runs, the plugin will create a default version,
 containing a few of the more obvious descriptions.
</para>

<para>
 A number of sample csv files are provided (in the kmymoney/contrib/csvimporter/
 folder in the source tree) in the hope that they may help.  For example, in the
 investment sample, an activity type is "ReInvestorContract Buy : ReInvested Units".
 In the validation process, the first successful match is on the ReInv in
 ReInvestorContract Buy, so the transaction therefore gets classed as Reinvdiv,
 even though Buy also is mentioned. Another example which has been observed is an
 activity type of Reinvest even though the transaction included neither price nor
 amount, but only a quantity, so that needed to be treated as Add Shares, or Shrsin.
</para>

<para>
 When this plugin was created, only a few investment formats had been seen as
 examples, and it may well be that you will encounter one which cannot be
 handled correctly.  If you find such a file, please send a suitable example
 (edited to remove or replace personal information) to the &kmymoney; user list
 &userlist; or developer list &devlist;, the developer will do his best to
 modify the plugin to handle it.
</para>
</sect3>
</sect2>
</sect1>