Why copy and paste costs you more than it looks
The time you see is the time spent entering, but it isn't the only time you pay for, because around it there are the re-reads, the wrong rows to track down, the corrections made the next day and the interruptions that break up the work of whoever is sitting at the computer
The typical mistakes are always the same, a row skipped, a date Excel decided to read its own way, an amount the business software reads with the wrong decimal separator, an old customer code left in the file, and none of these is serious on its own, the problem is that you find them later, when the entry is already inside the business software
Multiply four numbers together and you'll see it for yourself, the people who do the work, the hours a day each, the days a month it happens and the hourly cost to the company, then look at the total calmly
Here's a made-up example, just to show the arithmetic, say two people for two hours a day, twenty days a month, that's eighty hours a month going into an Excel file, the real numbers are yours, but the order of magnitude shows up straight away
Before automating, tidy up the file
A sheet made to be read by a person isn't ready to be read by a program, and this is the part you can sort out on your own, before you even talk to anybody
- One header row only, at the top, with no colored titles above it and no merged cells
- One row equals one record, no total rows or comment rows in the middle of the data
- Dates in a single format, decided once and kept
- Amounts as real numbers, not as text, otherwise even Excel adds them up wrong
- Codes written the way the business software wants them, including the leading zeros Excel tends to swallow
If the file comes from a supplier or from a portal and you can't touch it, that's fine too, a rule gets written to put it back in order before it's used, but it's extra work and it's better to know that first
How the automatic transfer works
The system makes the same moves a person would, only without getting tired and without getting distracted
It reads the file and recognizes the columns, checks every row against the rules you gave it, opens the business software, moves to the right screen, fills in the fields, saves and moves on to the next row, and at the end it leaves a log with what it entered and with the rows it set aside, so you always know what happened
No official integration from the business software is needed, if the program has screens and somebody knows how to fill them in, then a system can fill them in too, and when the business software does offer an import or a direct channel so much the better, that road is sturdier and it's the one to use
The checks are the part that really counts
An automatic upload with no checks is just a faster way of getting it wrong, so the rules have to be decided first, together with the person who does that work every day and already knows where the file trips up
Usually you check that the required fields are there, that the codes really exist in the customer records, that the dates fall inside a sensible range, that the amounts add up to the document total and that the row wasn't already entered yesterday, and then the rule is always the same, a row that fails a check doesn't go in, it stops before saving and ends up in a list to look at by hand
It's worth deciding as well what to do when the file is only half good, usually it's better to load the valid rows and set the others aside, but in some accounting processes it's safer to stop everything than to leave an upload half done, it depends on which one costs more to put right, and that's something you know
When the files aren't all the same
The format is rarely just one, different sheets often arrive from different suppliers, and each one names the columns its own way
You solve it by recognizing the template before reading it, from the file name, from a header, from the folder it lands in, and by giving every template its own map of the columns, so adding a new one becomes a small job
The awkward case is a different one, the supplier who changes the sheet every couple of months without saying anything, and there the most useful thing is a check that notices the headers are no longer the expected ones and stops, instead of loading data that has ended up in the wrong column
What changes for the person who used to do that work
The person doesn't disappear from the process, her job changes, before she entered every row hoping not to get any of them wrong, afterwards she looks at the list of exceptions and decides on those
It's a shorter job and a more sensible one, because she uses her head on the odd cases and not on two thousand identical rows, and it's the same reason it pays to start slowly, keeping human control over everything for a while and easing off only when the numbers add up
If you want to know whether your case is a fit
The things to look at are which program it is and how it opens, how many steps it takes to enter one row, how many operations you do in a month and how consistent the files you receive are, that's already enough to tell whether it makes sense and roughly how much work is behind it
If you like, start with a screen recording of you doing the work the way you do it today, it lasts two minutes and says more than half an hour of explaining