How To Map CSV 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.

For example, the file below has the Date, Amount, Payee, Memo, Check #, Type, and Category.

Click on the Source tab to see what a text CSV file looks like.

Let's switch to the spreadsheet form - it looks like a table. So, this is a simple table file where every column and transaction takes one line, and every column represents a specific transaction field. So, in this case, the Date would be the first column, followed by the Amount, Payee, Memo, and Check number. This optional column type and category optional column.

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.

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.