CSV import in Credit/Debit mode ignores the credit and writes 0.0 into the Deposit column. The Payments values are correct. Reproducible: Always Steps to Reproduce: 1. Import a CSV with seperate Credit and Debit values Notice that all the credits are 0.0 Actual Results: all credits are 0.0 Expected Results: credits value taken from the CSV file
This doesn't happen for me. Are you able to provide a simple file that demonstrates the problem? You can send it to me if you wish. Allan
On 20/05/14 20:03, Barry Scott wrote: > Allen, > > here is an example of what I am trying to import. > > Date,Reference,Transaction Type,Money In, Money Out > 25/02/2014,,Cheque Deposit,115.00,0.00 > 11/02/2014,,Cheque Deposit,15.94,0.00 > 25/10/2013,,Cheque Deposit,28.99,0.00 > 25/10/2013,,Cheque Deposit,175.23,0.00 > 28/06/2013,BARRY SCOTT,Inter-Account Transfer,0.00,5.12 > 18/05/2013,Coffee on us,Bank Welcome,3.00,0.00 > 17/05/2013,BARRY SCOTT,Inter-Account Transfer,1000.00,0.00 > > I should raise another bug that the CSV dialog does not remember the "." decimal seperator. > > > Barry > Hi Barry Well, over the last several years I've seen I don't know how many different file formats, but this is the first one I've seen that actually uses both the debit and credit fields for each transaction. Needless to say, that is the cause of your problem. Harking back to QIF format, each entry will have either a debit or a credit, but not both. I think OFX is probably the same. However, after I replied to your first post, I did wonder if this could be the cause. I could disregard any zero debit or credit entry, but what if both have a value? Which do I disregard? They could both have similar or identical values. I think the safest might be to output a message indicating an invalid format, and asking what the user wishes to do, perhaps? So far as the decimal symbol is concerned, the previous value is saved in that profile and does get picked up and used. I'll need to do some more checking. You don't need to raise another bug. If there is a problem, we can edit the heading so that both issues can be picked up in a search. I had actually had in mind to determine the setting from the imported file, to save the user having to possibly make a choice. It's a bit more complicated than that, as clicking that choice triggers a validity scan of the entries, and highlights the column/s. Allan
Summary: You have an assumption that that in Credit/Debit mode only one of the two cells will have a value, the other will be blank. Change that assumption to be only on of the cells can have a non-zero value, treat 0 the same as blank. If both Credit and Debit are non blank/0.00 report a format error. Refactor if anyone can come up with this ambiguous format. --- decimal defaults --- Choosing Import/CSV gives me a dialog that has the Decimal and Thousands drop down both blank. I cannot see a setting in the profile I found: $ cat ./Library/Preferences/KDE/share/config/csvimporterrc [Columns] AmountCol=3 CreditCol=-1 DateCol=0 DebitCol=-1 NumberCol=-1 PayeeCol=1 [MainWindow] Height=595 [Profile] CsvDirectory=~/KMyMoney/new-transactions/ CurrentUI=Banking DateFormat=2 Encoding=0 FieldDelimiter=0 StartLine=1 TextDelimiter=0 -- automatic defaulting -- I can see how this would work. 1234.56 - "." is decimal no thousands 1234,56 - "," is decimal no thousands 1,234.56 - "." is decimal "," is thousands 1.234,56 - "," is decimal "." is thousands 123456 - assume pennies/cents? Ask user? Barry
On 21/05/14 20:44, Barry Scott wrote: > https://bugs.kde.org/show_bug.cgi?id=334995 > > --- Comment #3 from Barry Scott <barry@barrys-emacs.org> --- > Summary: > > You have an assumption that that in Credit/Debit mode only one of the two cells > will have a value, the other will be blank. > > Change that assumption to be only on of the cells can have a non-zero value, > treat 0 the same as blank. > > If both Credit and Debit are non blank/0.00 report a format error. > Refactor if anyone can come up with this ambiguous format. I have this more or less working now, apart from polishing up. I output a message if either debit or credit is empty or zero, or if both fields are non-zero. I give the content of both fields and ask the user which to use, or to cancel. > --- decimal defaults --- > > Choosing Import/CSV gives me a dialog that has the Decimal and Thousands > drop down both blank. I cannot see a setting in the profile I found: Ah, but then you are looking at 4.6.4, whereas I'm working on the development branch, and there is now a "DecimalSymbol" parameter in csvimporterrc. So, your wish will be granted, in the upcoming next stable release. I'm surprising myself about how much has changed since 4.6.4. > -- automatic defaulting -- > > I can see how this would work. > > 1234.56 - "." is decimal no thousands > 1234,56 - "," is decimal no thousands > 1,234.56 - "." is decimal "," is thousands > 1.234,56 - "," is decimal "." is thousands Yes, that sort of thing. I automated the field separator setting already. > 123456 - assume pennies/cents? Ask user? At the moment (development branch), when the user chooses a setting, the monetary fields are scanned for any errors. A missing decimal is flagged (highlighted red) and the user's locale setting is added. The highlighting draws the user's attention to the change, hopefully. I would probably leave the ui combobox in, to allow the user to override, although I would hope that that wouldn't be necessary. Allan > Barry >
> I output a message if either debit or credit is empty or zero, Did you mean debit and credit not "or"?
On 23/05/14 19:49, Barry Scott wrote: > https://bugs.kde.org/show_bug.cgi?id=334995 > > --- Comment #5 from Barry Scott <barry@barrys-emacs.org> --- >> I output a message if either debit or credit is empty or zero, > > Did you mean debit and credit not "or"? > No, I did mean 'or', but I over-simplified that and should have added 'and the other field is non-zero' If either is zero, and the other non-zero it almost certainly needs to be examined. The only valid reason for a zero value is for a statement of interest, which happens to be zero. There's just one other point I'm wondering about. Where did your file originate? I mean is it one you constructed, or one you received? The reason I ask is that if you continue to receive similar, and they contain more than a few lines, you are going to have to answer a lot of queries, which might be tiresome. Allan
> No, I did mean 'or', but I over-simplified that and should have added 'and the other field is non-zero' Why is that a problem? If one is zero and the other is non-zero, then the non-zero is the relevant field for that line. If both are zero then you can use either one - isn't a zero deposit equivalent to a zero withdrawal? Isn't the only error if both are non zero?
On 23/05/14 23:37, Jack wrote: > https://bugs.kde.org/show_bug.cgi?id=334995 > > --- Comment #7 from Jack <ostroffjh@users.sourceforge.net> --- >> No, I did mean 'or', but I over-simplified that and should have added 'and the other field is non-zero' > Why is that a problem? Sorry, why is what a problem? Here, the original problem was that the OP's file's transactions had a zero field and a non-zero field in every transaction. The plugin did not test for that and gave erroneous results. I had not encountered that situation previously. With CSV, there are no rules or official formats, at least so far as I am aware. It may be valid when importing into a spreadsheet, but not, in my opinion, when importing into the likes of KMM. Initially, I am issuing a warning to the user, and did not want to take responsibility for deleting/ignoring user's data without comment, but rather than warning for every single line, it might be better to warn that the zero fields are going to be dropped and giving the option to bail out. That apart, the only other problem is as you mention below, if both are non zero. That definitely is an error. If one is zero and the other is non-zero, then the > non-zero is the relevant field for that line. If both are zero then you can > use either one - isn't a zero deposit equivalent to a zero withdrawal? Isn't > the only error if both are non zero? > Allan
I think this may just be a difference of degree. I agree with the importance of data integrity, but given the assumption (I think we agree) that each row represents either a debit or a credit but not both at once, then if it uses separate columns for debits and credits, it seems that having a non-zero debit OR credit with the other zero is exactly what SHOULD be expected. Perhaps missing or blank would be better, but I don't see this as ignoring the zero, if it can't be any non-zero value without being an error. I certainly agree with a warning the first time this happens, strongly suggesting that the user carefully confirm that the results of the import are what are expected. Giving the option to bail out is good, but so is the option to continue, especially if this is what the user actually expects, I guess the problem is that we are overly sensitive to the potential bad things that can happen when we make any assumptions that might not match the user's reality. Notices and warnings are good, as long as they don't prevent the user from doing what he/she wants to do.
If I can add my 2c. Allan you are correct in stating there are no rules around csv. just guidelines, this means if you are going to use a column then it needs to be validated. you cannot even trust delimiters. at work we have one piece of software that outputs tildes ~ as the delimiter. It also means you cannot make assumptions about why there is something unexpected. Both credit and debit fields can be blank/zero. My bank uses such as advice lines. They should be ignored or entered in as a zero balance transaction. In my opinion a blank numeric entry field should be interpreted as 0. I personally would ignore lines without valid dates, but would advise the user this is happening and ask if they want to make it the default for that template. so they aren't asked again. Personally I would like to see the all imported transactions listed at the end of the import with the ability to edit individual transactions or cancel the import if it didn't go to plan. But I guess that is a wishlist item.
On 26/05/14 02:23, Chris wrote: > https://bugs.kde.org/show_bug.cgi?id=334995 > > Chris <developerchris@rebel.com.au> changed: > > What |Removed |Added > ---------------------------------------------------------------------------- > CC| |developerchris@rebel.com.au > > --- Comment #10 from Chris <developerchris@rebel.com.au> --- > If I can add my 2c. > > Allan you are correct in stating there are no rules around csv. just > guidelines, this means if you are going to use a column then it needs to be > validated. you cannot even trust delimiters. at work we have one piece of > software that outputs tildes ~ as the delimiter. > > It also means you cannot make assumptions about why there is something > unexpected. > > Both credit and debit fields can be blank/zero. My bank uses such as advice > lines. They should be ignored or entered in as a zero balance transaction. > > In my opinion a blank numeric entry field should be interpreted as 0. > > I personally would ignore lines without valid dates, but would advise the user > this is happening and ask if they want to make it the default for that > template. so they aren't asked again. > > Personally I would like to see the all imported transactions listed at the end > of the import with the ability to edit individual transactions or cancel the > import if it didn't go to plan. > > But I guess that is a wishlist item. > Hi Chris Thanks for the input. I'm still wrestling with the blank/zero issue. It's much more complicated than I expected. So far as separators are concerned, presently I allow for the four, although I don't recollect having encountered the colon, yet. I actually determine which of those is the relevant one, and that hasn't yet failed. In the event of failure or ambiguity, I could then allow the user to choose, or to input a new one. Such a choice could be added to that profile. With dates, if the importer can't make sense of one, it hands the issue over to KMM, where the user can opt to use the current date. Generally, though, an invalid date is usually caused by the user failing to remove a trailer line, which is unlikely to contain a date. This is more than a wish-list item, it's a wish-list! Do you think you could submit it as a wish-list item, so I don't overlook it. Thanks Allan
The file is from a bank in the U.K. It is the only export format they support. I expect the CSV import to process this format, which is unambiguous, without reporting any warnings or errors. It is reasonable to consider any line with credit!=0 and debit!=0 as a problem.
(In reply to comment #12) > The file is from a bank in the U.K. It is the only export format they > support. > > I expect the CSV import to process this format, which is unambiguous, > without reporting any warnings or errors. As KMM transaction has only a single, Amount, field, then your zeros need to be dropped without notification to you, and in effect treated as an empty field. > It is reasonable to consider any line with credit!=0 and debit!=0 as a > problem. As there could be other information in the other fields, such a transaction will be imported with no associated value, again with no warning.
Created attachment 86965 [details] patch to fix problem
I'm attaching a patch to fix this problem. Apart from the fix, I've also made a slight change to the decimal symbol selection. The UI is now preloaded with a saved value, and the validity check is now automatic, instead of upon the user selecting a changed value. This highlights any errors, and immediately warns if the saved decimal value differs from the input file value. There are also several white space changes, for neatness - aligning comment fields. I plan to commit this in a week or so, assuming no adverse comment. Oh, I have actually decided to include a warning message after all, on detection of a debit or credit field containing zero. The user may opt to suppress further messages. Also, if it is detected that both fields contain entries, there is a message asking the user which to accept. Both messages also allow for the user to abort.
Git commit 970533e429467fef5cd27503e64eacd6c45ad3a5 by Allan Anderson. Committed on 02/06/2014 at 10:59. Pushed by allananderson into branch 'master'. M +152 -38 kmymoney/plugins/csvimport/csvdialog.cpp M +22 -1 kmymoney/plugins/csvimport/csvdialog.h http://commits.kde.org/kmymoney/970533e429467fef5cd27503e64eacd6c45ad3a5
Git commit 707ff712236275f7fab77c006386afbf0f32ca83 by Allan Anderson. Committed on 02/06/2014 at 10:59. Pushed by allananderson into branch 'master'. M +3 -0 kmymoney/plugins/csvimport/csvdialog.cpp http://commits.kde.org/kmymoney/707ff712236275f7fab77c006386afbf0f32ca83
This last commit was a small fix for a problem where the saved decimal symbol did not match the imported file. The mismatch was flagged, but selecting the correct symbol was not straight forward, and now is.