Importing accounts/data as CSV

[Rich Shepard]

Upgraded from 2.6.13 to 2.6.15. Got hung up because I built and installed
2.6.13 while 2.6.12 was installed, but 2.6.15 would not build until I
removed the 2.6.13 package. Strange. Anyway, ...

Table 4.1 in the gnucash-help doc notes that to import accounts and data
they need to be in the same format as gnucash exports have. My searches in
gnucash-help and gnucash-guide find no information on the export format.
Please point me to a source for this information.

When I know the fields and their sequence I can write a one-line awk
script to reformat the .csv files that I've exported from the previous
software.

TIA,

Rich
_______________________________________________
gnucash-user mailing list
gnucash-***@gnucash.org
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.

[John Ralls]

Post by Rich Shepard
Upgraded from 2.6.13 to 2.6.15. Got hung up because I built and installed
2.6.13 while 2.6.12 was installed, but 2.6.15 would not build until I
removed the 2.6.13 package. Strange. Anyway, ...
Table 4.1 in the gnucash-help doc notes that to import accounts and data
they need to be in the same format as gnucash exports have. My searches in
gnucash-help and gnucash-guide find no information on the export format.
Please point me to a source for this information.
When I know the fields and their sequence I can write a one-line awk
script to reformat the .csv files that I've exported from the previous
software.

Table 4.1 doesn't say that. It says that to import Accounts from CSV they must be in the same format as that exported by GnuCash, meaning GnuCash's Export>Accounts Tree to CSV function.

Importing transactions is a lot more flexible and includes a wizard lifted from GOffice wherein you can assign the transaction and split fields to the appropriate column in your CSV file. It is limited in that it cannot handle transactions with more than two splits. Because the GnuCash CSV exporter *does* support more than two splits per transaction it has multiple lines per transaction and so can't round-trip back into GnuCash.

There exist some 3rd-party tools that can convert CSV to QIF or OFX. Some of those might handle more complicated situations than GnuCash's CSV importer can.

Regards,
John Ralls


_______________________________________________
gnucash-user mailing list
gnucash-***@gnucash.org
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.

[Rich Shepard]

Post by John Ralls
Table 4.1 doesn't say that. It says that to import Accounts from CSV they
must be in the same format as that exported by GnuCash, meaning GnuCash's
Export>Accounts Tree to CSV function.

John,

True. I thought it also applied to transaction data.

Post by John Ralls
Importing transactions is a lot more flexible and includes a wizard lifted
from GOffice wherein you can assign the transaction and split fields to
the appropriate column in your CSV file. It is limited in that it cannot
handle transactions with more than two splits. Because the GnuCash CSV
exporter *does* support more than two splits per transaction it has
multiple lines per transaction and so can't round-trip back into GnuCash.

This is good to know and I have only two splits on some transactions.

Thanks very much,

Rich


_______________________________________________
gnucash-user mailing list
gnucash-***@gnucash.org
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.

[Rich Shepard]

Post by John Ralls
Importing transactions is a lot more flexible and includes a wizard lifted
from GOffice wherein you can assign the transaction and split fields to
the appropriate column in your CSV file. It is limited in that it cannot
handle transactions with more than two splits.

John,

I'll look into 3rd-party tools because my mortgage is the only payment
with more than 2 splits: principle, interest, and escrow.

Post by John Ralls
There exist some 3rd-party tools that can convert CSV to QIF or OFX. Some
of those might handle more complicated situations than GnuCash's CSV
importer can.

I assume that QFX would be preferable to QIF. Is this a correct
assumption?

Thanks,

Rich
_______________________________________________
gnucash-user mailing list
gnucash-***@gnucash.org
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.

[John Ralls]

Post by Rich Shepard

Post by John Ralls
Importing transactions is a lot more flexible and includes a wizard lifted
from GOffice wherein you can assign the transaction and split fields to
the appropriate column in your CSV file. It is limited in that it cannot
handle transactions with more than two splits.

John,
I'll look into 3rd-party tools because my mortgage is the only payment
with more than 2 splits: principle, interest, and escrow.

Post by John Ralls
There exist some 3rd-party tools that can convert CSV to QIF or OFX. Some
of those might handle more complicated situations than GnuCash's CSV
importer can.

I assume that QFX would be preferable to QIF. Is this a correct
assumption?

No, not really. For your purposes there's no useful difference, so pick whichever CSV conversion tool works better for you.

More generally they each have strengths and weaknesses, but more generally one is stuck with whatever the source one wants to use exports.

Regards,
John Ralls



_______________________________________________
gnucash-user mailing list
gnucash-***@gnucash.org
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.

[Rich Shepard]

Post by John Ralls
No, not really. For your purposes there's no useful difference, so pick
whichever CSV conversion tool works better for you.

John,

There are very few I found that I'd consider. Since the file format for
QIF is well documented on wikipedia and the OFX spec seems more complex and
not immediately seen (I did not follow the web page's link to the specs),
I'll write an awk or python program to convert the CSV to QIF.

Post by John Ralls
More generally they each have strengths and weaknesses, but more generally
one is stuck with whatever the source one wants to use exports.

Jgnash exports only CSV, one account at a time.

Thanks very much,

Rich
_______________________________________________
gnucash-user mailing list
gnucash-***@gnucash.org
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.

[David T. via gnucash-user]

Rich,

How many mortgage transactions are you talking about?

Many users set up scheduled transactions for a single mortgage (I do not). My own method for handling my single mortgage is to enter transactions manually and adjust each transaction to match the loan statement. GnuCash’s autofill feature makes this easy, since it puts all the splits in place. I simply adjust the amounts. The greatest amount of time really is logging on to the mortgage website. Of course, you’re doing that anyway in order to download the transactions.

David

Post by Rich Shepard

Post by John Ralls
Importing transactions is a lot more flexible and includes a wizard lifted
from GOffice wherein you can assign the transaction and split fields to
the appropriate column in your CSV file. It is limited in that it cannot
handle transactions with more than two splits.

John,
I'll look into 3rd-party tools because my mortgage is the only payment
with more than 2 splits: principle, interest, and escrow.

Post by John Ralls
There exist some 3rd-party tools that can convert CSV to QIF or OFX. Some
of those might handle more complicated situations than GnuCash's CSV
importer can.

I assume that QFX would be preferable to QIF. Is this a correct
assumption?
Thanks,
Rich
_______________________________________________
gnucash-user mailing list
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.

_______________________________________________
gnucash-user mailing list
gnucash-***@gnucash.org
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can do

[David T. via gnucash-user]

Rich,

I’m sorry. I mis-interpreted the tenor of your comment about transactions, and I assumed you were talking about importing mortgage transactions. I went back just now and re-read the thread.

However, in looking through the thread, I noticed that your original message began with your description of upgrading from 2.6.13 to 2.6.15. Then you asked specfic questions about the CSV import format, which John discussed.

However, perhaps you don’t need the CSV importer. Since you have already been using GnuCash, you should be able simply to open the GnuCash file from 2.6.13 in 2.6.15. There is no need to export and importyour data; GnuCash2.6.15 will read your 2.6.13 file without trouble.

Or did I miss something else in the thread?

;)

David

Post by David T. via gnucash-user
Rich,
How many mortgage transactions are you talking about?
Many users set up scheduled transactions for a single mortgage (I do not). My own method for handling my single mortgage is to enter transactions manually and adjust each transaction to match the loan statement. GnuCash’s autofill feature makes this easy, since it puts all the splits in place. I simply adjust the amounts. The greatest amount of time really is logging on to the mortgage website. Of course, you’re doing that anyway in order to download the transactions.
David

Post by Rich Shepard

Post by John Ralls
Importing transactions is a lot more flexible and includes a wizard lifted
from GOffice wherein you can assign the transaction and split fields to
the appropriate column in your CSV file. It is limited in that it cannot
handle transactions with more than two splits.

John,
I'll look into 3rd-party tools because my mortgage is the only payment
with more than 2 splits: principle, interest, and escrow.

Post by John Ralls
There exist some 3rd-party tools that can convert CSV to QIF or OFX. Some
of those might handle more complicated situations than GnuCash's CSV
importer can.

I assume that QFX would be preferable to QIF. Is this a correct
assumption?
Thanks,
Rich
_______________________________________________
gnucash-user mailing list
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.

_______________________________________________
gnucash-user mailing list
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.

_______________________________________________
gnucash-user mailing list
gnucash-***@gnucash.org
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can do this by us

[Rich Shepard]

Post by David T. via gnucash-user
How many mortgage transactions are you talking about?

David,

One per month going back 7 years.

Post by David T. via gnucash-user
Many users set up scheduled transactions for a single mortgage (I do not).
My own method for handling my single mortgage is to enter transactions
manually and adjust each transaction to match the loan statement.
GnuCash’s autofill feature makes this easy, since it puts all the splits
in place. I simply adjust the amounts. The greatest amount of time really
is logging on to the mortgage website. Of course, you’re doing that anyway
in order to download the transactions.

I enter the data from the monthly statement when I write the check. I'll
look at autofill, but now I need to convert the exported .cxv files for each
account, convert them to .qif, and import them to gnucash.

Thanks,

Rich
_______________________________________________
gnucash-user mailing list
gnucash-***@gnucash.org
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can d

[Geert Janssens]

Post by Rich Shepard

Post by David T. via gnucash-user
How many mortgage transactions are you talking about?

David,
One per month going back 7 years.

Post by David T. via gnucash-user
Many users set up scheduled transactions for a single mortgage (I do not).
My own method for handling my single mortgage is to enter transactions
manually and adjust each transaction to match the loan statement.
GnuCash’s autofill feature makes this easy, since it puts all the splits
in place. I simply adjust the amounts. The greatest amount of time really
is logging on to the mortgage website. Of course, you’re doing that anyway
in order to download the transactions.

I enter the data from the monthly statement when I write the check. I'll
look at autofill, but now I need to convert the exported .cxv files for each
account, convert them to .qif, and import them to gnucash.
Thanks,
Rich

If you're adventurous and have some development capability (that is, you are
able to build gnucash from scratch) you may be interested in beta testing the
improved csv export and import functionality in the current development tree.
The new importer is able to directly import csv files exported from gnucash,
including multi split transactions. It's even multi-currency capable with
restrictions.

If this interests you, just ask for more information.

Regards,

Geert

_______________________________________________
gnucash-user mailing list
gnucash-***@gnucash.org
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replie

[Rich Shepard]

Post by Geert Janssens
If you're adventurous and have some development capability (that is, you
are able to build gnucash from scratch) you may be interested in beta
testing the improved csv export and import functionality in the current
development tree. The new importer is able to directly import csv files
exported from gnucash, including multi split transactions. It's even
multi-currency capable with restrictions.

Geert,

Most of the applications I use are built here, primarily using
SlackBuilds.org scripts. GnuCash is one such tool.

While I would not mind testing the latest development source, but the data
I need to import was not exported from GnuCash. It comes from jgnash, so the
direct import of .csv files will not help with my data.

Thanks for the information,

Rich
_______________________________________________
gnucash-user mailing list
gnucash-***@gnucash.org
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.

[Geert Janssens]

Post by Rich Shepard

Post by Geert Janssens
If you're adventurous and have some development capability (that is, you
are able to build gnucash from scratch) you may be interested in beta
testing the improved csv export and import functionality in the current
development tree. The new importer is able to directly import csv files
exported from gnucash, including multi split transactions. It's even
multi-currency capable with restrictions.

Geert,
Most of the applications I use are built here, primarily using
SlackBuilds.org scripts. GnuCash is one such tool.
While I would not mind testing the latest development source, but the
data I need to import was not exported from GnuCash. It comes from jgnash,
so the direct import of .csv files will not help with my data.

Ok.

I see my proposal was a bit misleading. While exporting and re-importing in
gnucash is the most straightforward method, because it comes with a preset for
this, you can also import other multi-split csv data. In that case you need to
set the columns in the importer gui manually to match the exact format of your
data, but the flexibility is there now.

Geert
_______________________________________________
gnucash-user mailing list
gnucash-***@gnucash.org
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.

[Rich Shepard]

Post by Geert Janssens
I see my proposal was a bit misleading. While exporting and re-importing
in gnucash is the most straightforward method, because it comes with a
preset for this, you can also import other multi-split csv data. In that
case you need to set the columns in the importer gui manually to match the
exact format of your data, but the flexibility is there now.

Geert,

That makes more sense. As soon as I can make time I'll get the development
code, build it, upgrade versions, and test it.

Thanks for clarifying,

Rich
_______________________________________________
gnucash-user mailing list
gnucash-***@gnucash.org
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.

[Geert Janssens]

Post by Rich Shepard

Post by Geert Janssens
I see my proposal was a bit misleading. While exporting and re-importing
in gnucash is the most straightforward method, because it comes with a
preset for this, you can also import other multi-split csv data. In that
case you need to set the columns in the importer gui manually to match the
exact format of your data, but the flexibility is there now.

Geert,
That makes more sense. As soon as I can make time I'll get the
development code, build it, upgrade versions, and test it.
Thanks for clarifying,

Richard,

Great!

Note my work is not merged in the gnucash repository yet, but it's available
in my own github repository:
https://github.com/gjanssens/gnucash
You'll need to check out the cpp branch there.

And please test on a backup copy of your code. The code probably contains bugs
still :)

Regards,

Geert
_______________________________________________
gnucash-user mailing list
gnucash-***@gnucash.org
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.

[Rich Shepard]

Post by Rich Shepard
look at autofill, but now I need to convert the exported .cxv files for each

Oops! Should be .csv, not .cxv.

Rich
_______________________________________________
gnucash-user mailing list
gnucash-***@gnucash.org
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.

[Rich Shepard]

Post by John Ralls
It is limited in that it cannot handle transactions with more than two
splits. Because the GnuCash CSV exporter *does* support more than two
splits per transaction it has multiple lines per transaction and so can't
round-trip back into GnuCash.

John,

I'm confused by the above and would like you to clarify it for me. I have
only one monthly transaction with more than two splits: my mortgage payment.
It has three splits: principal, interest, and escrow.

If GnuCash CSV exporter supports more than two splits per transactions why
can it import only two splits (i.e., multiple lines) per transaction?

Regards,

Rich



_______________________________________________
gnucash-user mailing list
gnucash-***@gnucash.org
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.

[Geert Janssens]

Post by Rich Shepard

Post by John Ralls
It is limited in that it cannot handle transactions with more than two
splits. Because the GnuCash CSV exporter *does* support more than two
splits per transaction it has multiple lines per transaction and so can't
round-trip back into GnuCash.

John,
I'm confused by the above and would like you to clarify it for me. I have
only one monthly transaction with more than two splits: my mortgage
payment. It has three splits: principal, interest, and escrow.
If GnuCash CSV exporter supports more than two splits per transactions
why can it import only two splits (i.e., multiple lines) per transaction?

Rich,

That is solely for historical reasons. The csv importer was written first and
has had this limitation from the beginning. Later someone contributed an
export to csv option which can write multi-split transactions (meaning more
than 2 splits) to a csv file. Such a csv file however can't be imported in
gnucash again because the csv importer (which is older) can't handle it.

In gnucash 2.8, this will change. The csv importer has been rewritten and one
of the new capabilities is it will now be able to handle multi-split
transactions.

Regards,

Geert
_______________________________________________
gnucash-user mailing list
gnucash-***@gnucash.org
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.

[Rich Shepard]

Post by Geert Janssens
That is solely for historical reasons. The csv importer was written first
and has had this limitation from the beginning. Later someone contributed
an export to csv option which can write multi-split transactions (meaning
more than 2 splits) to a csv file.

Geert,

Ok, this makes sense. However, ...

Post by Geert Janssens
In gnucash 2.8, this will change. The csv importer has been rewritten and
one of the new capabilities is it will now be able to handle multi-split
transactions.

Why so far out? I don't know the times when the importer and exporter were
written, but I suspect that transactions with more than two splits are found
in more transactions than mortgage payments.

Can't the new importer be back-ported to the current 2.6 series?
Obviously, I've not looked at gnucash source code, but it seems to be a
useful tool for folks migrating from non-QIF/OXF bookkeeping software.

Thanks for explaining,

Rich

_______________________________________________
gnucash-user mailing list
gnucash-***@gnucash.org
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.

[Geert Janssens]

Post by Rich Shepard

Post by Geert Janssens
That is solely for historical reasons. The csv importer was written first
and has had this limitation from the beginning. Later someone contributed
an export to csv option which can write multi-split transactions (meaning
more than 2 splits) to a csv file.

Geert,
Ok, this makes sense. However, ...

Post by Geert Janssens
In gnucash 2.8, this will change. The csv importer has been rewritten and
one of the new capabilities is it will now be able to handle multi-split
transactions.

Why so far out? I don't know the times when the importer and exporter
were written, but I suspect that transactions with more than two splits are
found in more transactions than mortgage payments.

Absolutely. multi-split transactions are fairly common in several situations.

Post by Rich Shepard
Can't the new importer be back-ported to the current 2.6 series?

Unfortunately not. The new importer is part of the ongoing c++ rewrite. We
can't backport c++ code to the stable series.

Post by Rich Shepard
Obviously, I've not looked at gnucash source code, but it seems to be a
useful tool for folks migrating from non-QIF/OXF bookkeeping software.

Indeed. That's partly why I decided to improve it. But the changes required
were too intrusive to do on the stable series.

Post by Rich Shepard
Thanks for explaining,

You're welcome.

Geert
_______________________________________________
gnucash-user mailing list
gnucash-***@gnucash.org
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.

[Rich Shepard]

Post by Rich Shepard
When I know the fields and their sequence I can write a one-line awk
script to reformat the .csv files that I've exported from the previous
software.

After looking at the potential time involved, and discussing record
keeping needs with my accountant, we determined the best approach is to open
GnuCash personal and business files with the opening balance as of
2017-01-01 and leave earlier years' data and software until they're past
their keep-until dates. Then I'll remove applications and data.

Now I'm reading up on how to set up my business records.

Thanks all!

Rich
_______________________________________________
gnucash-user mailing list
gnucash-***@gnucash.org
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.