Mapping the CSV files - ProperSoft

Mapping the CSV files

This video is to show how to work with a CSV file.

This video is applicable to all Bank2 converters, which are Bank2QBO, Bank2CSV, Bank2OFX, Bank2QIF, Bank2QFX, Bank2IIF. And also this video is applicable 100% to CSV2 converters, all of them: CSV2CSV, CSV2QBO, CSV2IIF, CSV2OFX, CSV2QFX, CSV2IIF.

Mapping CSV files Step 1: main window

What is a CSV file? CSV file is a comma-separated value and usually, this is a simple table format. And usually, if you look at text editor it would look like every line, there is value separated by a comma, and could be a tab character or semicolon, but mostly it's the comma. And if your CSV file has the first line, as column names, this is a huge help for the converter to figure out 'what your data is about'. And if your CSV file does not have that first line, as column names, depending on the complexity of the file, the converter may figure out 'what columns are' or you need to help it by doing the mapping. If you do have column names - 99% of cases the converter would know 'what each column is about'. So, this is a sample.csv file. It has Date, Amount, Payee, Memo, Check #, Type, Category.

Mapping CSV files Step 2: table format

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

Mapping CSV files Step 3: spreadsheet form

In this case, the converter already mapped it automatically, move the column, figure out that 'where is a Date column', 'where is the Amount column' or 'Name column', 'Memo', 'Check # column', because of those column names the converter mapped the file correctly. If you want to change 'how the Data is mapped', you can click 'Review mapping' and then start to remapping your file. How does the Mapper work? On the left part, you have the column assignments or field assignments and on the right part, you have the actual file shown to allow you easily map the file. So you can see this is 'Date' column, column name 'Date' is Date, column name 'Amount' is Amount, and so on. So, for example, you could say, that the name column would be 'Memo' column and 'Memo' would be the 'Category' column. You can do that. So, if you have more than one text column, the converter may be confused about which column to use.

Mapping CSV files Step 4: review mapping memo

Let's say you have column name 'Description' and column name 'Memo', column name 'Reference', column name 'Detail', it would be hard for the converter, because all those names are okay for the 'Memo', to be qualified for the 'Memo' column. So, you have to decide which column to use. 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 CSV files Step 5: review mapping name memo

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.

Mapping CSV files Step 6: review mapping amount

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.

Mapping CSV files Step 7: review mapping withdrawal

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'.

Mapping CSV files Step 8: review mapping type

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.

Mapping CSV files Step 9: review mapping balance

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. 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 Checking card transaction, then you would use this assignment to specify the Account type column.

Mapping CSV files Step 10: review mapping account number

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 CSV files Step 11: review mapping output

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.

Mapping CSV files Step 12: review mapping split

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

Mapping CSV files Step 13: save mapping name

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.

Mapping CSV files Step 14: auto mapping

Related articles