CSV-import Workflow Data Formatting

Google Sheets
, ,
1

I’m attempting to import transactions from my unsupported Bank (CIBC).

CIBC’s specific dialect of csv has a few challenges to overcome:

  • The date format is DD/MM/YYYY
  • negative and positive transactions are in separate columns

So, I’m currently running through several manual steps to bash the data into the correct dialect for Tiller import like this:
(In Excel)

  1. Select all in date column
  2. Format cells for date column to USA locale specific date format
  3. Add ‘-1’ to a blank cell, copy it
  4. Select all in negative transactions column
  5. Goto Special, Constants(to select only cells with values in)
  6. Paste Special (Multiply) (To turn them all negative)
  7. New column with =C1&D1 to merge positive and negative columns together

It is time consuming and fiddly, and I haven’t figured out a way to turn it into a simple formula, but I’m sure it’s possible. In addition it’d be nice if this could all be done in GSheets instead, to remove a whole dependency link in the chain, but some researching I did seemed to show some of the UI functions I’m using in Excel like Goto Special (Constants) and Paste Special (Multiply) have no equivalents in GSheets (again, a formula would be better!).

Macros could be an option, sure. I could possibly write a Python script to do it, but I would ultimately like this to be something that my wife can use also.

I welcome any advice from the experienced folk here. The ultimate goal would be to paste raw bank CSV in, press 1 button, save it out again, CSV-Import (and then repeat for 10 other accounts)

Tangentially, this also reminded me of other i18n tooling I’ve worked on in the past, which has centered upon YYYYMMDD as the most widely accepted standard for dates, perhaps that is a way forward ultimately for storing the date values, and have GSheets/Excel format that into locale-friendly versions for readability.

2

Actually I’m mistaken on the date front, it’s stored in the csv as YYYY-DD-MM and the locale formatting is happening when I copy/paste it initially.

EDIT: I have a simple python script to do it now. Does GSheets have a built-in Python interpreter? I’ll keep looking for other ways to do it that reduce the steps.

EDIT 2: I think I figured out the formula:
=(IF(C1="", "",-C1)&D1)
C1 is the column with negative values
D1 is the column with postive values
This goes in a new column.

3

Have you looked at the CSV Spreadsheet Importer Multitool workflow, @seffy?

With a little fussing you can build formula-driven derived values in addition to directly remapping data. (There are some examples in the prefab templates.) You should even be able to deal with that non-US date format using the derived data approach.

4

That looks really cool, thanks for building and sharing it! I shall play with it tonight.

1 Like
5

I personally like this one the best, but it’s a bit more advanced/complicated to set up and I haven’t tried the multi-tool yet.

6

Good memory, @heather. I forgot about that one!

7

On merging the Debit and Credit columns, you can replace the above with the following formula:

=sum(-C1,D1)

As long as the empty columns are truly empty (to be interpreted as zero) the result is the value you are looking for. This works in Google Sheets and should work in Excel as well. Copy that result column then paste it back on itself as “values only” and you can then remove the old columns.

8

Or simply: =D1-C1

There are some helpful examples in the templates with ARRAYFORMULA() if you’re feeling brave. :muscle: