How To Map CSV/Excel Files

This tutorial shows how to map CSV, Excel, or (copy/paste from a spreadsheet application). It uses a recently retired application, but the technique applies to the current app.

What is a CSV file?

A CSV file is a comma-separated value and usually is in a simple table format. Typically, when you open a CSV file using a text editor, it appears as if there are values separated by a comma, semicolon, or a tab (aligned blank space) character on each line.

If your CSV file has the first line as a column name, this is a huge help for the converter in figuring out what your data is about.

If your CSV file does not have that first line as a column header, the converter may figure out what data each column contains, and you may need to help the app by doing a custom mapping.

If you do have column names - 99% of cases the converter should find and map the main columns automatically.

Automatic Mapping

When you click the Review Mapping button, you see that the converter already mapped some columns automatically using provided header with the column names:

• Date
• Amount
• Name
• Memo
• Check number

If you want to change how the Data is mapped, you can click the Review mapping button and then start remapping your file.

Date format

On your CSV/Exce/TXT files, the transaction dates can come in varoius date formats. You can tell the converter how dates (M/D/Y vs D/M/Y vs Y/M/D) are supplied on your CSV file (check the source tab to confirm).

The Transactions tab shows dates in your computer system date format to avoid confusion (this is how the converter understands your dates and present to your in expected date format). How the extracted dates are shown is controlled by the "Show dates as" setting. Change the expected date format if the date format was not picked up correctly.

How to Map the Amount Columns

This tutorial explains CSV mapping for amounts. It uses an obsolete app, but applicable to the current converter.

CSV/Excel files may have several layouts for the amounts.

What is 'Layout'? Let's say, in this case, we have a simple layout, where the Amount is presented in a single column and the Deposits are positive, Withdrawals are negative.

So, that Amount sign plus the column itself, create complete details for the converter, it knows, which transactions are Withdrawals, which transactions are Deposits. So, you would go to CSV Mapper (click 'Review Mapping').

And then you would just say - this is my Amount column, I don't use Deposits and I don't use Withdrawals.

Then, let's say, you have different layouts, you have Debit and Credit columns and they are both positive, or the Debit column is all negative, and the Credit column is positive, or you have Debit/Credit column and they also may have different Amount signs. This is all working, but you have to specify Deposit and Withdrawal columns, you have to say - Amount 'Do not use' and the Deposit specifies Deposit column, and Withdrawal specifies Withdrawal column here.

And, the last case, for the Amount layout kind - is when you have one Amount column, but all numbers are positive, and then you have another Type column, which has words like Debit/Credit, DR/CR, and other variants.

Then you would say here - Debit/Credit column - this is my column, that provides the transaction Type.

And this is my Amount column. Then the converter will use this information to figure out, which one is Withdrawal, which one is Deposit.

Deposit/withdrawal columns in CSV/Excel files

CSV/Excel files may have transaction amounts of a transaction supplied in various ways:

• Layout #1: one amount column with positive and negative amounts (could be reversed for credit card files, see an explanation below)
• Layout #2: separate debit (withdrawal) and credit (deposit) columns (the amount sign does not matter for this layout)
• Layout #3one amount column and another column (Record type) with words like DEBIT/CREDIT/DR/CR (amounts are usually positive 

Bank account (checking, savings) and credit card transactions may have a different meaning in CSV files. Some credit card companies provide CSV files with positive amounts for purchases and negative amounts for credit card payments.

Some credit card companies provide CSV files with positive amounts for purchases and negative amounts for credit card payments.

Bank account files usually have negative amounts for withdrawals and positive amounts for deposits.

To avoid confusion, accounting software like Quickbooks, Quicken, MS Money and others as well as financial format specifications like OFX, QFX, QBO, QIF have one way to for transaction amounts regardless if it is credit card or bank account:

• if a transaction takes money from an account (bank account withdrawal or credit card purchase), it must be negative (minus)
• if a transaction puts money into an account (bank account deposit or credit card payment), if must be positive (plus)

For the layout #1 above, all CSV converters follow this rule treating negative amounts as taking money from an account and positive amounts as putting money into an account. All CSV converters have "Change +/-" button to reverse the sign for all amounts for the whole file as one click action.

For the layout #2 above, if a CSV column is mapped as Debit (withdrawal, credit card purchase), the sign of amounts is not considered as the whole column is declared as negative.

For the layout #3 above, if a transaction Record Type is Debit (withdrawal, credit card purchase), the sign of amount is not considered as the transaction is declared as negative.

Custom/Manual Mapping

How does the mapper work? On the left part, you have the field assignments for column names and on the right part, you have the actual file shown to help you with mapping. So you can see there is the date column, column name 'Date' assigned to the Date field, column name 'Amount' assigned to the Amount field, and so on.

You may reassign columns to different fields or use the same column for more than one field.

For example, you have the following columns:

• Description
• Memo
• Reference
• Detail

All these columns could be used for the Memo field. It would be hard for the converter to assign a specific column correctly, so you have to decide which column to use.

Multiple Name and Memo Columns

The Mapper allows you to map up to two columns for the Name, and up to two columns for the 'Memo'. Sometimes you have data spread mode to more than one column and you want to combine it, and you don't want to edit the CSV file, so the CSV Mapper allows you to do that: combine those two columns and there is another option: if you name your columns as 'Memo 1', 'Memo 2', 'Memo 3', 'Memo 5' up to 'Memo 99999', as long as number keep increasing the converter will see those numbers and will combine all those Memo columns into one Memo column. But here, in Memo, you've just to specify 'Memo 1' and the rest of the columns will be combined.

Mapping Amounts

So, another thing is about Amounts. CSV files may have different layouts for Amounts. What are the different layouts? For example, you would have one Amount column with positive and negative numbers, and this is the easiest one. Then you would say - this is the 'Amount' column and that's it.

But let's say you have Debit and Credit columns, you don't have Amount column, you have one Debit column, one Credit column. In this case, you would say - Amount do not use, and for the Deposits select the Deposit column or Credit column, and for Withdrawal - select Debit column or Withdrawal column.

And the last layout: let's say you have one Amount column, like this, and then you have the Type column, which says Debit, Credit, or DR, CR, things like that. Then you would say: my Amount column is the 'Amount' and my Debit/Credit column is the 'Type'.

Other Columns (Balance, Check number, Category)

What about other fields, like the Balance column? If you have the balance, you can assign it, and then the balance from the 'Balance' column will be used for the balance in the QBO file if the balance is applicable for that format.

What else is the Mapper allows you to do? If you have multiple accounts in the same CSV file, but you have a column, that indicates - which account is which, then you would use the Account number column again. This is not an account to be used for the QBO file, but the account to indicate multiple accounts specified in a CSV file. The same applies to Account type if you have several accounts listed in the same CSV file, and there is a column, that says - this is a Credit card transaction, this column says - this is a Checking card transaction, then you would use this assignment to specify the Account type column.

The next group - these controls do not have a list of columns from your CSV file, but they also can be part of your mapping. So, let's say, you want to set the Account type for this mapping - you once set the Account type to be 'Checking', so, when you use this mapping - the Account type here will be also set to 'Checking' or to 'Credit card'. You would have a mapping for the 'Bank 1', mapping for the 'Bank 2', or mapping for the Credit card, so then you would select that mapping, and then it will also set all these additional attributes, as part of mapping, like Output Account ID, Output Bank ID, Output Branch ID, Output Currency. So, that's how it works.

Mapping Split Amounts

And the last thing. How splits are working? Splits are expected to be as an additional column. So, you have the 'Split 1' column, 'Split 2' column, 'Split Category 1' column, 'Split Category 2' column. Let's say, you have a PayPal file, you would have a gross, fee, net, if you have something, for example, like payment processor file, like stripers, square, you would have collected fees the positive. So, then you would say here, that I have two Splits, and then you would specify which column is 'Split 1'', which column is 'Split 2' - and these are Amounts columns. So, if you have one column for the Amount, and another column for the Category, and so for the one Split and the second Split, you would have one column for the Amount and another column for the Category. Then you have to specify all those columns, you may have, let's say, as PayPal file you don't have a category, but you would have the column name, as you as expected, so in this case, would be fee column, or cross column or net column. So, if you don't specify a 'Split category', then the name of the 'Split 1' column will be used as the Category name, and the 'Split 2' name of that column, in this case, would be a fee, would be used for the category. The CSV mapper provides flexible options for the splits.

Make sure to enter the Mapping name to be something descriptive, and then you save the mapping.

You see the mapping is listed here and when you select this mapping - it will be applied to the loaded CSV file or the next CSV file, you load. And all those parameters will be set, as well, for this part, and if you want to switch to after mapping - it means, that the Parser will try to figure out itself first and this is useful when you have one mapping applied, and you load in some file, that has a completely different structure, and you see, that data doesn't look right, so, the first thing to do is to switch back to after mapping, and then map the file again, that new file to the new layout.

Convert Transaction files with splits

Some transactions cover several categories (splits), and converting transaction files with splits could be challenging, depending on the output format.

The ProperConvert supports splits in transactions.

What is a transaction with multiple splits? When each transaction can have more than one category assigned.

Some formats support splits and the converter can convert transactions using splits. When supported, splits are passed during conversion and adapted when the output format does not.

Formats that support splits

• CSV (can be created with multiple columns or multiple lines)
• QIF format (for Quicken)
• IIF format (for QuickBooks)

Formats that do not support splits

• OFX, QFX, QBO
• MT940
• PDF

How to process splits converting to non-supporting formats

The OFX, QFX, QBO, and MT940 formats do not support splits. For example, a PayPal file has Gross, Fee, and Net columns, resulting in two splits and the amount. Since creating a transaction with multiple splits is not possible, the converter offers two ways to pass splits into the output file:

• create separate transactions
• collapse splits

Creating separate transactions will result in multiple transactions, but you can still work within your accounting software.

Collapsing splits will lose the category details but keep a transaction as a whole, which could be edited later in the accounting software.

Step 1: Create CSV splits as columns

For example, a CSV file with splits below has the Amount column, 'Split1', 'Split2', 'SplitCategory1', and 'SplitCategory2'.

If there are no split category columns, the name of this split column will be used as the category name for all transactions.

Step 2: Name split columns to support automapping

If possible to adjust column names in a CSV file, rename the columns before converting:

• Name amount columns as Split1, Split2, Split3 and so on
• Name category columns such as SplitCategory1, SplitCategory2, SplitCategory3, etc.

Step 3: Apply a custom mapping for split columns

Click 'Review Mapping' to use a custom mapping feature (provided for CSV/Excel files only).

The next step is to set the number of splits.

Set columns, which have split amounts.

In the same way, set columns for split categories.

Once the mapping is saved, verify that all splits are parsed correctly in the splits column at the end of the transaction grid.