How to work with CSV/Excel files (extended tutorial)

This is an extended tutorial about CSV/Excel mapping. The tutorial uses a recently retired app, but applicable to the current app. See it as a video at https://youtu.be/YdjEWB3n2Vw

Once you load a CSV file inside the application, you should see some transactions already as the converter understands the most CSV transaction formats correctly.

The first thing to do is to set the output format so you do need to set the output format as it defines which columns you're working with. So if you need to convert to a QIF file set on the right sidebar, that you are converting to QIF.

You can click on the 'Source' tab to see your CSV file or Excel file, the contents. You can see that this CSV file has the first line as column names: Date, Amount, Payee, Memo, Check #, Type, Category. If your CSV file does not have those column names - a recommendation to add them to be as common as Date, Amount. If you have several Date columns then mark clearly the data columns you would like to be picked up and other date columns just live as some other name. Each row in the CSV file after column names is expected to be the whole transaction. So one line - is one transaction.

As you can see this file was on auto mapping, which means that the converter will try to figure out itself how to map this file it's and it says there's a Date column, Amount column, Payee column, Memo column and it's quite easily can map them for the conversion.

Some CSV files are not clear, they may have a lot of columns, and columns may have different meanings. You may need to do the manual mapping. After you load the CSV file or Excel file, click 'Review Mapping'.

And this mapping window has two parts: the left part is to do mapping and the right part is to look at data. The set of these fields is defined by the Output format. For the QIF you would have all these items.

And for OFX, or QFX, or QBO you would have a different set.

There are few dropdowns: for the date - Date field, Amount field, Deposit, Withdrawals. This is how it will be used by the application to create output files. These are your input column names in your CSV file or Excel file. You can see the converter map them more or less correctly.

What else you can do, as part of mapping? If you know, that this is a Credit Card file and the amounts are reversed, you can say to the converter: 'please reverse them' by setting the 'Reverse +/-' checkbox. Then it will reverse it for you as part of mapping.

Then you can set 'Output account Type' to be used here and depending on the format if it's OFX or QIF this could be different things, but you can set them, like Account ID, Bank ID, Currency, and you can set them and save as part of mapping. Then when you select this mapping all these settings will be set as soon as you select mapping.

Once you save the Mapping it will be available here to quickly select and apply.

This panel will show up when you load a CSV file or Excel file.

And you can set here like Input Dates, which order. You see there is no like date or year, like four digits or two digits. There is just the order - how dates are presented in your CSV file. You would say: 'my dates are month/day/year, or day/months/year, or year/month/date'. In 90 plus cases - the converter will figure out itself - what the date format is, but in some cases, let's say: it's January 1st, January 2nd, like up to 13, there's not quite, you cannot for sure say what are those dates: are those January dates or day just first day of each month days, which could be both cases. And in this case, you can tell the converter: my dates are this way.

If your file could have some non-writing characters you may need to play with Input Encoding, depending on how your CSV file is supplied usually after mapping does quite a few things to figure out the encoding of your CSV file. UTF8 is a very specific option, you can try one of those things if you know they are in those encodings.

In this file, we have a comma, as a field separator.

It could be another thing like 'Tab' or 'Semicolon' for some other countries than the US. The converter will try to detect and figure out by itself what the field separator is and if it is confused you can tell it which one to use. And once you change any of those settings (Input Dates, Input Encoding, Field separator), you can click 'Apply' to reload the file.

You can 'Copy', 'Paste' from Excel. So instead of saving the file you could go to Excel and then you would select similar to this table, you could select area in Excel and just press 'Ctrl + 'C' or 'Common' + 'C' and then you would click 'Paste' and paste with paste data instead of loading the file. So that's another way, you could work in Excel, edit your transactions, then select them, copy them to clipboard and then paste them in the converter.