Reply to topic

CSV import: struggling with it and some ideas

User avatar JesusM
Registered Member
Posts
135
Karma
0
OS
Hi,

I am using an "umbrella" credit card (maybe some of you know it, Curve), and it can export all transactions to a CSV file, so it theoretically should be easy to import to Skrooge, but I am having some problems:

1. The import plugin is called "CSV" but it doesn't work when the separator is a comma, it needs to be semi-colon, or I am doing something wrong?

2. Curve export the expenses as positive values and refunds as negative, is there any way I can tell so Skrooge, so it can import the right value? I saw an answer from 2012 saying that this is not possible, but a lot of things have changed since then, so worth of confirming.

3. Categories. Would be possible to create a "map" of categories so when importing Skrooge could use my categories instead the ones in the CSV file? A workaround is not as simple as search/replace, because the CSV file doesn't understand sub-categories.

4. Accounts. Something similar than for categories. Would be possible to map my "Skrooge" accounts with "Curve" accounts, so I don't need to make sure the name is the same in both software and I don't need to search/replace the CSV file?

Thanks!
//JM
User avatar smankowski
Moderator
Posts
970
Karma
7
OS
JesusM wrote:Hi,

I am using an "umbrella" credit card (maybe some of you know it, Curve), and it can export all transactions to a CSV file, so it theoretically should be easy to import to Skrooge, but I am having some problems:

1. The import plugin is called "CSV" but it doesn't work when the separator is a comma, it needs to be semi-colon, or I am doing something wrong?

This should work with comma. I have some unit test working well and importing CSV file with comma.
Could you provide me a sample file by email to reproduce the error?
JesusM wrote:2. Curve export the expenses as positive values and refunds as negative, is there any way I can tell so Skrooge, so it can import the right value? I saw an answer from 2012 saying that this is not possible, but a lot of things have changed since then, so worth of confirming.

I will try to implement something. Could you provide me a sample file?
JesusM wrote:3. Categories. Would be possible to create a "map" of categories so when importing Skrooge could use my categories instead the ones in the CSV file? A workaround is not as simple as search/replace, because the CSV file doesn't understand sub-categories.

No, this is not possible. "Search & Process" is done for that.
JesusM wrote:4. Accounts. Something similar than for categories. Would be possible to map my "Skrooge" accounts with "Curve" accounts, so I don't need to make sure the name is the same in both software and I don't need to search/replace the CSV file?

No, this is not possible. "Search & Process" is done for that.
JesusM wrote:Thanks!
//JM


Skrooge, a personal finances manager powered by KDE
Image
User avatar JesusM
Registered Member
Posts
135
Karma
0
OS
Hi again,

for #1 and #2, files sent to your email address.

For #3 is not that easy, because some of the "curve categories" use the same name than my "personal categories", so using "search & process" would be incredible difficult to make sure it is finding and updating only the "new imported transactions" so don't modify the "old, right ones". So the "mapping" has to be done *before* importing (search&replace with some text editor, inconvenient but possible), or *at importing time* with some category map configured in the import tool (ideal solution in my opinion, but you say -and you are the expert ;-) - that it is not possible).

For #4, not exactly, but similar to #3. Using "search and process" in Skrooge here I think it is more complex than "search&replace" in a text editor before importing, because you will need to create "fake" accounts from the importation and then empty them moving the transactions to the real accounts, and either you have those fake account always there and introduce noise to dashboards and reports, or delete them every time, that is inconvenient. So in this case the "search&replace" in a text editor is not ideal but doable.

Once again, thanks for your work and response.

//JM
User avatar smankowski
Moderator
Posts
970
Karma
7
OS
JesusM wrote:Hi again,

for #1 and #2, files sent to your email address.

Thank you. I will analyze.
JesusM wrote:For #3 is not that easy, because some of the "curve categories" use the same name than my "personal categories", so using "search & process" would be incredible difficult to make sure it is finding and updating only the "new imported transactions" so don't modify the "old, right ones". So the "mapping" has to be done *before* importing (search&replace with some text editor, inconvenient but possible), or *at importing time* with some category map configured in the import tool (ideal solution in my opinion, but you say -and you are the expert ;-) - that it is not possible).

When "Search & process" is used after import, this is done only on currently imported operations.
JesusM wrote:For #4, not exactly, but similar to #3. Using "search and process" in Skrooge here I think it is more complex than "search&replace" in a text editor before importing, because you will need to create "fake" accounts from the importation and then empty them moving the transactions to the real accounts, and either you have those fake account always there and introduce noise to dashboards and reports, or delete them every time, that is inconvenient. So in this case the "search&replace" in a text editor is not ideal but doable.

I don't undestand. If the objective is to assign the right account of each imported operations, this can be done with "Search & Process".
Operations will be imported in a "Default" account and moved by "Search & Process" in the account you want.
JesusM wrote:Once again, thanks for your work and response.

//JM


Skrooge, a personal finances manager powered by KDE
Image
User avatar JesusM
Registered Member
Posts
135
Karma
0
OS
smankowski wrote:I don't undestand. If the objective is to assign the right account of each imported operations, this can be done with "Search & Process".
Operations will be imported in a "Default" account and moved by "Search & Process" in the account you want.


Yes. The thing is the import task will create/use the "wrong" accounts, learned from the CSV file. Then "Search&Process" move transactions to the right accounts. What happen with the wrong accounts? Maybe I am wrong, but I understand that nothing happen to them by default, i.e. they remain empty in Skrooge file. So either I keep them there (ugly) or I manually remove them. This is why I think that it is easier a "search&replace" operation in a text editor before the import operation, so set the right accounts from the beginning.

//JM
User avatar smankowski
Moderator
Posts
970
Karma
7
OS
Hi,

I commited a correction for the import CSV with comma :
https://cgit.kde.org/skrooge.git/commit ... ab4a2d1ada


Skrooge, a personal finances manager powered by KDE
Image
User avatar JesusM
Registered Member
Posts
135
Karma
0
OS
smankowski wrote:Hi,

I commited a correction for the import CSV with comma :
https://cgit.kde.org/skrooge.git/commit ... ab4a2d1ada


Tested, and apparently it has a problem ... Skrooge has created the accounts again (it created them when I imported using semicolon). And how is it possible to create the same account again: adding quotes to the name:

Image

The file is exactly the same, simply before replacing all commas by semi-colon. And not only the account name is quoted, the payee is quoted as well.

Image

//JM
User avatar smankowski
Moderator
Posts
970
Karma
7
OS
Hi,

I took the file you sent me by email and I replaced all "," by ";".
I have exactly the same result when I import the original file or the modified one.

During the import, the character " is not ignored because this character is not on all columns.
You can remove all " in the imported file to have the expected result.
Regards.


Skrooge, a personal finances manager powered by KDE
Image
User avatar JesusM
Registered Member
Posts
135
Karma
0
OS
It is weird because I used these two same files and the one with semicolons created the accounts without quotes and the one with commas didn't removed it. I will delete the accounts and transaction completely and will try again.

//JM
User avatar JesusM
Registered Member
Posts
135
Karma
0
OS
I found the difference, and it has nothing to do with the comma or semicolon: when the fields are separated by <comma or semicolon> + <blank space>, the import operation keeps the quotes. When there is not a <blank space> after the separator, Skrooge removes the quotes.
User avatar smankowski
Moderator
Posts
970
Karma
7
OS
JesusM wrote:I found the difference, and it has nothing to do with the comma or semicolon: when the fields are separated by <comma or semicolon> + <blank space>, the import operation keeps the quotes. When there is not a <blank space> after the separator, Skrooge removes the quotes.

This is normal.


Skrooge, a personal finances manager powered by KDE
Image
User avatar JesusM
Registered Member
Posts
135
Karma
0
OS
smankowski wrote:This is normal.


What is normal? If there is no <blank space> Skrooge removed the quotes but off the is a <blank space> the quotes remain? I don't understand it.
User avatar smankowski
Moderator
Posts
970
Karma
7
OS
Hi,
I committed a new correction. Could you test it?


Skrooge, a personal finances manager powered by KDE
Image
User avatar JesusM
Registered Member
Posts
135
Karma
0
OS
Apologies for the late answer.

Yes, it works in the same way now with comma or semi-colon, and with or without blank spaces.

The only thing is that importing the same content twice, first from a file with commas and then from a file with semi-colon, for example, Skrooge creates all transaction duplicated. I know that you don't usually perform this double importation, but I wonder if this shouldn't be controlled by the same rules and import only the non-existing transactions.

Anyway, from the point of view of the comma/semi-colon and with/without blank spaces, this is working fine now.

Thanks
User avatar smankowski
Moderator
Posts
970
Karma
7
OS
Hi,

The "Import ID" of the transaction is based on the CSV line corresponding.
So, because the lines (with comma, with semi-colon, blank spaces or without, with " or without) are different the ID are different.
I can not change this without changing the behavior for one type this CSV file. This will cause trouble for people using this format.
So, I prefer to not change this.
Regards.


Skrooge, a personal finances manager powered by KDE
Image

 
Reply to topic

Bookmarks



Who is online

Registered users: Baidu [Spider], Bing [Bot], claydoh, Dibo, Google [Bot], hsnnsnc, Majestic-12 [Bot], nocruoro