Re: [GNC] Importing data from QuickBooks Online
ad but have this advice for >>>>> you to further diagnose. When I face a puzzle like this, I like to try >>>>> creating the simplest possible scenario in which I am still able to >>>>> reproduce the problem. In this case this would start by figuring out how >>>>> far you can pare down your 2022 csv file while still having the problem. >>>>> Can you pair it down to just a few transactions, like 5 or 10 max? Can >>>>> you then change some of that data in that pared down file and still have >>>>> the problem? By simplifying you can often more easily find the source of >>>>> the problem and also might be able to share your csv file when it reaches >>>>> the point where the data in it is no longer personal. >>>>> >>>>> Sincerely, >>>>> >>>>> Vincent Dawans >>>>> >>>>> On Mon, Jul 3, 2023 at 3:35 PM Tom Olin via gnucash-user >>>>> mailto:gnucash-user@gnucash.org> >>>>> <mailto:gnucash-user@gnucash.org <mailto:gnucash-user@gnucash.org>>> >>>>> wrote: >>>>> I would add that I’m more inclined to attribute the problem to GnuCash >>>>> logic. However, the code will require a lot of study on my part before I >>>>> can make meaningful sense of it. I just wish I could think of some >>>>> possible logic that might explain it. >>>>> >>>>> -- >>>>> Tom >>>>> >>>>> Money is created out of thin air when banks loan and when the federal >>>>> government spends. Money is destroyed into thin air when bank loans are >>>>> repaid and when federal taxes are paid. >>>>> >>>>>> On Jul 3, 2023, at 5:41 PM, Ken Pyzik >>>>> <mailto:py...@outlook.com> <mailto:py...@outlook.com >>>>>> <mailto:py...@outlook.com>>> wrote: >>>>>> >>>>>> Tom -- I believe at the beginning of 2022, QuickBooks went from being a >>>>>> desktop and online software package - to being strictly/only online. >>>>>> With that transition, I believe they also gave a one-year period where >>>>>> you could export data. While this may be a stretch, I believe that they >>>>>> may have somehow added a change flag or some other thing to the data to >>>>>> prevent you from exporting it as easy as it used to be. This could be >>>>>> the discrepancy you are experiencing. In other words, to prevent people >>>>>> from doing what you are exactly trying to do -- they may have placed a >>>>>> simple data offset or some other thing into the data to prevent easy >>>>>> export. Again, this could be a stretch -- but it would explain why one >>>>>> year comes over correctly and the next does not. Just my two cents -- >>>>>> for what it is worth ( which could be nothing at all!) >>>>>> >>>>>> Ken >>>>>> >>>>>> -Original Message- >>>>>> From: gnucash-user >>>>> <mailto:gnucash-user-bounces+pyz01=outlook@gnucash.org> >>>>>> <mailto:outlook@gnucash.org <mailto:outlook....@gnucash.org>>> On >>>>>> Behalf Of Tom Olin via gnucash-user >>>>>> Sent: Monday, July 3, 2023 1:55 PM >>>>>> To: Kalpesh Patel mailto:kalpesh.pa...@usa.net> >>>>>> <mailto:kalpesh.pa...@usa.net <mailto:kalpesh.pa...@usa.net>>> >>>>>> Cc: gnucash-user@gnucash.org <mailto:gnucash-user@gnucash.org> >>>>>> <mailto:gnucash-user@gnucash.org <mailto:gnucash-user@gnucash.org>> >>>>>> Subject: Re: [GNC] Importing data from QuickBooks Online >>>>>> >>>>>> Good shot, but no, date formats are consistent. >>>>>> >>>>>> -- >>>>>> Tom >>>>>> >>>>>> The federal government imposes a tax on you so YOU need THEIR money, not >>>>>> because they need yours. >>>>>> >>>>>>> On Jul 3, 2023, at 4:52 PM, Kalpesh Patel >>>>>> <mailto:kalpesh.pa...@usa.net> <mailto:kalpesh.pa...@usa.net >>>>>>> <mailto:kalpesh.pa...@usa.net>>> wrote: >>>>>>> >>>>&g
Re: [GNC] Importing data from QuickBooks Online
; Tom >> >> Money is created out of thin air when banks loan and when the federal >> government spends. Money is destroyed into thin air when bank loans are >> repaid and when federal taxes are paid. >> >> On Jul 3, 2023, at 5:41 PM, Ken Pyzik > mailto:py...@outlook.com >> wrote: >> >> Tom -- I believe at the beginning of 2022, QuickBooks went from being a >> desktop and online software package - to being strictly/only online. With >> that transition, I believe they also gave a one-year period where you could >> export data. While this may be a stretch, I believe that they may have >> somehow added a change flag or some other thing to the data to prevent you >> from exporting it as easy as it used to be. This could be the discrepancy >> you are experiencing. In other words, to prevent people from doing what >> you are exactly trying to do -- they may have placed a simple data offset >> or some other thing into the data to prevent easy export. Again, this >> could be a stretch -- but it would explain why one year comes over >> correctly and the next does not. Just my two cents -- for what it is worth >> ( which could be nothing at all!) >> >> Ken >> >> -Original Message- >> From: gnucash-user > mailto:outlook@gnucash.org >> On Behalf Of >> Tom Olin via gnucash-user >> Sent: Monday, July 3, 2023 1:55 PM >> To: Kalpesh Patel mailto:kalpesh.pa...@usa.net >> >> >> Cc: gnucash-user@gnucash.org <mailto:gnucash-user@gnucash.org >> > >> Subject: Re: [GNC] Importing data from QuickBooks Online >> >> Good shot, but no, date formats are consistent. >> >> -- >> Tom >> >> The federal government imposes a tax on you so YOU need THEIR money, not >> because they need yours. >> >> On Jul 3, 2023, at 4:52 PM, Kalpesh Patel > mailto:kalpesh.pa...@usa.net >> wrote: >> >> I am likely grasping straws here like David but did you verify the format >> of the date that it is consistent throughout? Like it isn’t switching from >> two digits to four digits, or replace certain digits with place holders, >> etc. QuickBooks is (was?) published by the maker of Quicken and I remember >> their exports when it came to Quicken was all over the map for the format >> of the date and ended up normalizing it with an external script when I did >> a full migration from Quicken to GNC. By far this, the date format, was >> biggest PITA. >> >> -Original Message- >> From: Tom Olin mailto:t...@tomolin.net >> >> >> >> Sent: Monday, July 03, 2023 12:14 PM >> To: Jean L mailto:rip...@gmail.com >> >> >> Cc: gnucash-user@gnucash.org <mailto:gnucash-user@gnucash.org >> > >> Subject: Re: [GNC] Importing data from QuickBooks Online >> >> Jean, >> >> Thanks for that. I did play with a few similar options but couldn’t get >> anything to work easily. Some were aimed more at Quicken or QuickBooks >> desktop instead of QuickBooks Online. >> >> As it turns out, my minimal script seems to work very well with this one >> weird anomaly. The nature of it suggests something that should be easily >> worked around - if I can just figure out what it is. >> >> -- >> Tom >> >> Federal tax dollars don’t exist. Federal spending creates dollars out of >> thin air. With federal tax payments, the opposite occurs. >> >> On Jul 3, 2023, at 12:04 PM, Jean L > mailto:rip...@gmail.com >> wrote: >> >> This may be slightly off topic, or too late to help, but... >> >> In github, there is a repository >> https://github.com/tim-rohrer/move2gnucash < >> https://github.com/tim-rohrer/move2gnucash> >> That seems pretty well setup to migrate your data from a quicken csv >> export to GC. I haven't used it, but I looked at it for a friend and it >> looked interesting. >> >> Jean >> >> On 7/3/2023 8:59 AM, Tom Olin via gnucash-user wrote: >> >> [Resending to the list. Original reply went only to Jim.] >> >> Jim, >> >> Fair questions. Answers below, and I’ve attached the script itself which >> includes documentation which addresses some of the questions. I’ve reviewed >> all documentation that I can find. >> >> From QBO, I export a journal report to XLS (only usable option in QBO). >> >> GnuCash 5.3 on macOS 11.7.8 >> >> See the script for the specific steps. >> >> The import matcher step of the import process is fully satisfied. That is >> the step labeled “Ma
Re: [GNC] Importing data from QuickBooks Online
gnucash-user >>>> mailto:gnucash-user@gnucash.org> >>>> <mailto:gnucash-user@gnucash.org <mailto:gnucash-user@gnucash.org>>> wrote: >>>> I would add that I’m more inclined to attribute the problem to GnuCash >>>> logic. However, the code will require a lot of study on my part before I >>>> can make meaningful sense of it. I just wish I could think of some >>>> possible logic that might explain it. >>>> >>>> -- >>>> Tom >>>> >>>> Money is created out of thin air when banks loan and when the federal >>>> government spends. Money is destroyed into thin air when bank loans are >>>> repaid and when federal taxes are paid. >>>> >>>>> On Jul 3, 2023, at 5:41 PM, Ken Pyzik >>>> <mailto:py...@outlook.com> <mailto:py...@outlook.com >>>>> <mailto:py...@outlook.com>>> wrote: >>>>> >>>>> Tom -- I believe at the beginning of 2022, QuickBooks went from being a >>>>> desktop and online software package - to being strictly/only online. >>>>> With that transition, I believe they also gave a one-year period where >>>>> you could export data. While this may be a stretch, I believe that they >>>>> may have somehow added a change flag or some other thing to the data to >>>>> prevent you from exporting it as easy as it used to be. This could be >>>>> the discrepancy you are experiencing. In other words, to prevent people >>>>> from doing what you are exactly trying to do -- they may have placed a >>>>> simple data offset or some other thing into the data to prevent easy >>>>> export. Again, this could be a stretch -- but it would explain why one >>>>> year comes over correctly and the next does not. Just my two cents -- >>>>> for what it is worth ( which could be nothing at all!) >>>>> >>>>> Ken >>>>> >>>>> -Original Message- >>>>> From: gnucash-user >>>> <mailto:gnucash-user-bounces+pyz01=outlook@gnucash.org> >>>>> <mailto:outlook@gnucash.org <mailto:outlook@gnucash.org>>> On >>>>> Behalf Of Tom Olin via gnucash-user >>>>> Sent: Monday, July 3, 2023 1:55 PM >>>>> To: Kalpesh Patel mailto:kalpesh.pa...@usa.net> >>>>> <mailto:kalpesh.pa...@usa.net <mailto:kalpesh.pa...@usa.net>>> >>>>> Cc: gnucash-user@gnucash.org <mailto:gnucash-user@gnucash.org> >>>>> <mailto:gnucash-user@gnucash.org <mailto:gnucash-user@gnucash.org>> >>>>> Subject: Re: [GNC] Importing data from QuickBooks Online >>>>> >>>>> Good shot, but no, date formats are consistent. >>>>> >>>>> -- >>>>> Tom >>>>> >>>>> The federal government imposes a tax on you so YOU need THEIR money, not >>>>> because they need yours. >>>>> >>>>>> On Jul 3, 2023, at 4:52 PM, Kalpesh Patel >>>>> <mailto:kalpesh.pa...@usa.net> <mailto:kalpesh.pa...@usa.net >>>>>> <mailto:kalpesh.pa...@usa.net>>> wrote: >>>>>> >>>>>> I am likely grasping straws here like David but did you verify the >>>>>> format of the date that it is consistent throughout? Like it isn’t >>>>>> switching from two digits to four digits, or replace certain digits with >>>>>> place holders, etc. QuickBooks is (was?) published by the maker of >>>>>> Quicken and I remember their exports when it came to Quicken was all >>>>>> over the map for the format of the date and ended up normalizing it with >>>>>> an external script when I did a full migration from Quicken to GNC. By >>>>>> far this, the date format, was biggest PITA. >>>>>> >>>>>> -Original Message- >>>>>> From: Tom Olin mailto:t...@tomolin.net> >>>>>> <mailto:t...@tomolin.net <mailto:t...@tomolin.net>>> >>>>>> Sent: Monday, July 03, 2023 12:14 PM >>>>>> To: Jean L mailto:rip...@gmail.com> >>>>>> <mailto:rip...@gmail.com <mailto:rip...@gmail.com>>> >>>>>> Cc: gnucash-user@gnucash.org <mailto:gnucash-user@gnucash.org> >>>>>> <ma
Re: [GNC] Importing data from QuickBooks Online
port. Again, this > could be a stretch -- but it would explain why one year comes over > correctly and the next does not. Just my two cents -- for what it is worth > ( which could be nothing at all!) > > Ken > > -Original Message- > From: gnucash-user mailto:outlook@gnucash.org >> On Behalf Of > Tom Olin via gnucash-user > Sent: Monday, July 3, 2023 1:55 PM > To: Kalpesh Patel mailto:kalpesh.pa...@usa.net > >> > Cc: gnucash-user@gnucash.org <mailto:gnucash-user@gnucash.org > > > Subject: Re: [GNC] Importing data from QuickBooks Online > > Good shot, but no, date formats are consistent. > > -- > Tom > > The federal government imposes a tax on you so YOU need THEIR money, not > because they need yours. > > On Jul 3, 2023, at 4:52 PM, Kalpesh Patel mailto:kalpesh.pa...@usa.net >> wrote: > > I am likely grasping straws here like David but did you verify the format > of the date that it is consistent throughout? Like it isn’t switching from > two digits to four digits, or replace certain digits with place holders, > etc. QuickBooks is (was?) published by the maker of Quicken and I remember > their exports when it came to Quicken was all over the map for the format > of the date and ended up normalizing it with an external script when I did > a full migration from Quicken to GNC. By far this, the date format, was > biggest PITA. > > -Original Message- > From: Tom Olin mailto:t...@tomolin.net >> > > Sent: Monday, July 03, 2023 12:14 PM > To: Jean L mailto:rip...@gmail.com >> > Cc: gnucash-user@gnucash.org <mailto:gnucash-user@gnucash.org > > > Subject: Re: [GNC] Importing data from QuickBooks Online > > Jean, > > Thanks for that. I did play with a few similar options but couldn’t get > anything to work easily. Some were aimed more at Quicken or QuickBooks > desktop instead of QuickBooks Online. > > As it turns out, my minimal script seems to work very well with this one > weird anomaly. The nature of it suggests something that should be easily > worked around - if I can just figure out what it is. > > -- > Tom > > Federal tax dollars don’t exist. Federal spending creates dollars out of > thin air. With federal tax payments, the opposite occurs. > > On Jul 3, 2023, at 12:04 PM, Jean L mailto:rip...@gmail.com >> wrote: > > This may be slightly off topic, or too late to help, but... > > In github, there is a repository > https://github.com/tim-rohrer/move2gnucash < > https://github.com/tim-rohrer/move2gnucash> > That seems pretty well setup to migrate your data from a quicken csv > export to GC. I haven't used it, but I looked at it for a friend and it > looked interesting. > > Jean > > On 7/3/2023 8:59 AM, Tom Olin via gnucash-user wrote: > > [Resending to the list. Original reply went only to Jim.] > > Jim, > > Fair questions. Answers below, and I’ve attached the script itself which > includes documentation which addresses some of the questions. I’ve reviewed > all documentation that I can find. > > From QBO, I export a journal report to XLS (only usable option in QBO). > > GnuCash 5.3 on macOS 11.7.8 > > See the script for the specific steps. > > The import matcher step of the import process is fully satisfied. That is > the step labeled “Match Import and GnuCash accounts”. > > The problem manifests at the “Match Transactions” screen where all > transactions need to be matched. I’ve attached a screenshot if it is > supported here. > > One more data point: I tried changing one of the transactions in the 2022 > import file to 2023. It still failed the same way. > > — > Tom > > On Jul 2, 2023, at 11:55 PM, Jim DeLaHunt mailto:list%2bgnuc...@jdlh.com >> wrote: > > Tom: > > On 2023-07-02 15:10, Tom Olin via gnucash-user wrote: > > I’m attempting to import data from QuickBooks Online to GnuCash. I’ve > written an awk script which appears to work well except for one major issue. > > What format is the data which you export from Quickbooks Online? CSV > (Comma Separated Values text files with tabular data)? QFX (Quicken > Financial Exchange, similar to OFX)? > > What GnuCash version are you using? On what computer OS? > > What GnuCash sequence of actions do you use to import the data? > > > The data I’m importing spans the years 2022 and 2023. All 2023 > transactions import cleanly, but all 2022 transactions import unbalanced, > meaning I have to manually match up each of them - doable but tedious. > > Let's assume you are exporting data in CSV format, and using the current > version of GnuCash (5.3), and importing using the File… Import… Import > Transactions
Re: [GNC] Importing data from QuickBooks Online
Here’s the final version (for now) of my script. I hope someone else finds it useful. Thanks to all who responded to my query with suggestions. -- Tom Federal taxes can be paid with dollars, but the dollars have to be created (spent) by the government before anyone has dollars with which to pay their taxes. > On Jul 4, 2023, at 8:00 AM, Tom Olin via gnucash-user > wrote: > > Vincent, > > Thanks so much for figuring this out! If you don’t mind, I’d like to give you > credit in my file. > > I’m going to rerun the entire dataset as a final test. I will repost the > final version of the script here for anyone else looking to do this. > > Thanks, again! > > -- > Tom > > Federal spending funds taxes. It is impossible to pay taxes until the > government has spent money into the economy. > >> On Jul 3, 2023, at 10:37 PM, Vincent Dawans wrote: >> >> Tom: >> >> I tested both files and it seems that it fails on the reconcile column for >> 2022. When I skip the reconcile column it works. 2022 has Y entries in that >> column while 2023 doesn't, and when I replace the 2022 Y entries with c it >> works. So it seems to be related to either the inability to import >> reconciled flag or the flag is different, I am not sure. But that's where >> the problem is, something to do with the Y reconcile flag. >> >> Sincerely, >> >> Vincent Dawans >> >> On Mon, Jul 3, 2023 at 4:41 PM Tom Olin > <mailto:t...@tomolin.net>> wrote: >> Vincent, >> >> Good suggestions. I’ve attached 2 files, 3 transactions each, for 2022 and >> 2023. Instructions for importing them are in the documentation of the >> script, latest version also attached. >> >> 2022 still fails, 2023 still works. Create the accounts as needed; there are >> only a few. >> >> -- >> Tom >> >> Money is created when banks loan and when the federal government spends. The >> latter increases someone’s net worth. The former does not, but the interest >> and fees transfer net worth from the borrower to the lender. >> >>> On Jul 3, 2023, at 6:44 PM, Vincent Dawans >> <mailto:dawa...@gmail.com>> wrote: >>> >>> Tom: >>> >>> I haven't read every single email in this thread but have this advice for >>> you to further diagnose. When I face a puzzle like this, I like to try >>> creating the simplest possible scenario in which I am still able to >>> reproduce the problem. In this case this would start by figuring out how >>> far you can pare down your 2022 csv file while still having the problem. >>> Can you pair it down to just a few transactions, like 5 or 10 max? Can you >>> then change some of that data in that pared down file and still have the >>> problem? By simplifying you can often more easily find the source of the >>> problem and also might be able to share your csv file when it reaches the >>> point where the data in it is no longer personal. >>> >>> Sincerely, >>> >>> Vincent Dawans >>> >>> On Mon, Jul 3, 2023 at 3:35 PM Tom Olin via gnucash-user >>> mailto:gnucash-user@gnucash.org>> wrote: >>> I would add that I’m more inclined to attribute the problem to GnuCash >>> logic. However, the code will require a lot of study on my part before I >>> can make meaningful sense of it. I just wish I could think of some possible >>> logic that might explain it. >>> >>> -- >>> Tom >>> >>> Money is created out of thin air when banks loan and when the federal >>> government spends. Money is destroyed into thin air when bank loans are >>> repaid and when federal taxes are paid. >>> >>>> On Jul 3, 2023, at 5:41 PM, Ken Pyzik >>> <mailto:py...@outlook.com>> wrote: >>>> >>>> Tom -- I believe at the beginning of 2022, QuickBooks went from being a >>>> desktop and online software package - to being strictly/only online. >>>> With that transition, I believe they also gave a one-year period where you >>>> could export data. While this may be a stretch, I believe that they may >>>> have somehow added a change flag or some other thing to the data to >>>> prevent you from exporting it as easy as it used to be. This could be the >>>> discrepancy you are experiencing. In other words, to prevent people from >>>> doing what you are exactly trying to do -- they may have placed a simple >>>> data offset
Re: [GNC] Importing data from QuickBooks Online
Vincent, Thanks so much for figuring this out! If you don’t mind, I’d like to give you credit in my file. I’m going to rerun the entire dataset as a final test. I will repost the final version of the script here for anyone else looking to do this. Thanks, again! -- Tom Federal spending funds taxes. It is impossible to pay taxes until the government has spent money into the economy. > On Jul 3, 2023, at 10:37 PM, Vincent Dawans wrote: > > Tom: > > I tested both files and it seems that it fails on the reconcile column for > 2022. When I skip the reconcile column it works. 2022 has Y entries in that > column while 2023 doesn't, and when I replace the 2022 Y entries with c it > works. So it seems to be related to either the inability to import reconciled > flag or the flag is different, I am not sure. But that's where the problem > is, something to do with the Y reconcile flag. > > Sincerely, > > Vincent Dawans > > On Mon, Jul 3, 2023 at 4:41 PM Tom Olin <mailto:t...@tomolin.net>> wrote: > Vincent, > > Good suggestions. I’ve attached 2 files, 3 transactions each, for 2022 and > 2023. Instructions for importing them are in the documentation of the script, > latest version also attached. > > 2022 still fails, 2023 still works. Create the accounts as needed; there are > only a few. > > -- > Tom > > Money is created when banks loan and when the federal government spends. The > latter increases someone’s net worth. The former does not, but the interest > and fees transfer net worth from the borrower to the lender. > >> On Jul 3, 2023, at 6:44 PM, Vincent Dawans > <mailto:dawa...@gmail.com>> wrote: >> >> Tom: >> >> I haven't read every single email in this thread but have this advice for >> you to further diagnose. When I face a puzzle like this, I like to try >> creating the simplest possible scenario in which I am still able to >> reproduce the problem. In this case this would start by figuring out how far >> you can pare down your 2022 csv file while still having the problem. Can you >> pair it down to just a few transactions, like 5 or 10 max? Can you then >> change some of that data in that pared down file and still have the problem? >> By simplifying you can often more easily find the source of the problem and >> also might be able to share your csv file when it reaches the point where >> the data in it is no longer personal. >> >> Sincerely, >> >> Vincent Dawans >> >> On Mon, Jul 3, 2023 at 3:35 PM Tom Olin via gnucash-user >> mailto:gnucash-user@gnucash.org>> wrote: >> I would add that I’m more inclined to attribute the problem to GnuCash >> logic. However, the code will require a lot of study on my part before I can >> make meaningful sense of it. I just wish I could think of some possible >> logic that might explain it. >> >> -- >> Tom >> >> Money is created out of thin air when banks loan and when the federal >> government spends. Money is destroyed into thin air when bank loans are >> repaid and when federal taxes are paid. >> >> > On Jul 3, 2023, at 5:41 PM, Ken Pyzik > > <mailto:py...@outlook.com>> wrote: >> > >> > Tom -- I believe at the beginning of 2022, QuickBooks went from being a >> > desktop and online software package - to being strictly/only online. >> > With that transition, I believe they also gave a one-year period where you >> > could export data. While this may be a stretch, I believe that they may >> > have somehow added a change flag or some other thing to the data to >> > prevent you from exporting it as easy as it used to be. This could be the >> > discrepancy you are experiencing. In other words, to prevent people from >> > doing what you are exactly trying to do -- they may have placed a simple >> > data offset or some other thing into the data to prevent easy export. >> > Again, this could be a stretch -- but it would explain why one year comes >> > over correctly and the next does not. Just my two cents -- for what it is >> > worth ( which could be nothing at all!) >> > >> > Ken >> > >> > -Original Message- >> > From: gnucash-user > > <mailto:outlook@gnucash.org>> On Behalf Of Tom Olin via gnucash-user >> > Sent: Monday, July 3, 2023 1:55 PM >> > To: Kalpesh Patel mailto:kalpesh.pa...@usa.net>> >> > Cc: gnucash-user@gnucash.org <mailto:gnucash-user@gnucash.org> >> > Subject: Re: [GNC] Importing data from QuickBooks Onl
Re: [GNC] Importing data from QuickBooks Online
Just to add that the reconcile flag is sort of a moot point right now because it seems like there is a bug with the multi-split import where the flag is ignored and set to cleared for the first account on each transaction. See 796890 – CSV import of multi-split transactions marks reconciliation status of first line as cleared (gnucash.org) <https://bugs.gnucash.org/show_bug.cgi?id=796890> On Mon, Jul 3, 2023 at 7:37 PM Vincent Dawans wrote: > Tom: > > I tested both files and it seems that it fails on the reconcile column for > 2022. When I skip the reconcile column it works. 2022 has Y entries in that > column while 2023 doesn't, and when I replace the 2022 Y entries with c it > works. So it seems to be related to either the inability to import > reconciled flag or the flag is different, I am not sure. But that's where > the problem is, something to do with the Y reconcile flag. > > Sincerely, > > Vincent Dawans > > On Mon, Jul 3, 2023 at 4:41 PM Tom Olin wrote: > >> Vincent, >> >> Good suggestions. I’ve attached 2 files, 3 transactions each, for 2022 >> and 2023. Instructions for importing them are in the documentation of the >> script, latest version also attached. >> >> 2022 still fails, 2023 still works. Create the accounts as needed; there >> are only a few. >> >> -- >> Tom >> >> Money is created when banks loan and when the federal government spends. >> The latter increases someone’s net worth. The former does not, but the >> interest and fees transfer net worth from the borrower to the lender. >> >> On Jul 3, 2023, at 6:44 PM, Vincent Dawans wrote: >> >> Tom: >> >> I haven't read every single email in this thread but have this advice for >> you to further diagnose. When I face a puzzle like this, I like to try >> creating the simplest possible scenario in which I am still able to >> reproduce the problem. In this case this would start by figuring out how >> far you can pare down your 2022 csv file while still having the problem. >> Can you pair it down to just a few transactions, like 5 or 10 max? Can you >> then change some of that data in that pared down file and still have the >> problem? By simplifying you can often more easily find the source of the >> problem and also might be able to share your csv file when it reaches the >> point where the data in it is no longer personal. >> >> Sincerely, >> >> Vincent Dawans >> >> On Mon, Jul 3, 2023 at 3:35 PM Tom Olin via gnucash-user < >> gnucash-user@gnucash.org> wrote: >> >>> I would add that I’m more inclined to attribute the problem to GnuCash >>> logic. However, the code will require a lot of study on my part before I >>> can make meaningful sense of it. I just wish I could think of some possible >>> logic that might explain it. >>> >>> -- >>> Tom >>> >>> Money is created out of thin air when banks loan and when the federal >>> government spends. Money is destroyed into thin air when bank loans are >>> repaid and when federal taxes are paid. >>> >>> > On Jul 3, 2023, at 5:41 PM, Ken Pyzik wrote: >>> > >>> > Tom -- I believe at the beginning of 2022, QuickBooks went from being >>> a desktop and online software package - to being strictly/only online. >>> With that transition, I believe they also gave a one-year period where you >>> could export data. While this may be a stretch, I believe that they may >>> have somehow added a change flag or some other thing to the data to prevent >>> you from exporting it as easy as it used to be. This could be the >>> discrepancy you are experiencing. In other words, to prevent people from >>> doing what you are exactly trying to do -- they may have placed a simple >>> data offset or some other thing into the data to prevent easy export. >>> Again, this could be a stretch -- but it would explain why one year comes >>> over correctly and the next does not. Just my two cents -- for what it is >>> worth ( which could be nothing at all!) >>> > >>> > Ken >>> > >>> > -Original Message- >>> > From: gnucash-user >>> On Behalf Of Tom Olin via gnucash-user >>> > Sent: Monday, July 3, 2023 1:55 PM >>> > To: Kalpesh Patel >>> > Cc: gnucash-user@gnucash.org >>> > Subject: Re: [GNC] Importing data from QuickBooks Online >>> > >>> > Good shot, but no, date formats are consistent. >>> > >>> > -- >>> > Tom >
Re: [GNC] Importing data from QuickBooks Online
Tom: I tested both files and it seems that it fails on the reconcile column for 2022. When I skip the reconcile column it works. 2022 has Y entries in that column while 2023 doesn't, and when I replace the 2022 Y entries with c it works. So it seems to be related to either the inability to import reconciled flag or the flag is different, I am not sure. But that's where the problem is, something to do with the Y reconcile flag. Sincerely, Vincent Dawans On Mon, Jul 3, 2023 at 4:41 PM Tom Olin wrote: > Vincent, > > Good suggestions. I’ve attached 2 files, 3 transactions each, for 2022 and > 2023. Instructions for importing them are in the documentation of the > script, latest version also attached. > > 2022 still fails, 2023 still works. Create the accounts as needed; there > are only a few. > > -- > Tom > > Money is created when banks loan and when the federal government spends. > The latter increases someone’s net worth. The former does not, but the > interest and fees transfer net worth from the borrower to the lender. > > On Jul 3, 2023, at 6:44 PM, Vincent Dawans wrote: > > Tom: > > I haven't read every single email in this thread but have this advice for > you to further diagnose. When I face a puzzle like this, I like to try > creating the simplest possible scenario in which I am still able to > reproduce the problem. In this case this would start by figuring out how > far you can pare down your 2022 csv file while still having the problem. > Can you pair it down to just a few transactions, like 5 or 10 max? Can you > then change some of that data in that pared down file and still have the > problem? By simplifying you can often more easily find the source of the > problem and also might be able to share your csv file when it reaches the > point where the data in it is no longer personal. > > Sincerely, > > Vincent Dawans > > On Mon, Jul 3, 2023 at 3:35 PM Tom Olin via gnucash-user < > gnucash-user@gnucash.org> wrote: > >> I would add that I’m more inclined to attribute the problem to GnuCash >> logic. However, the code will require a lot of study on my part before I >> can make meaningful sense of it. I just wish I could think of some possible >> logic that might explain it. >> >> -- >> Tom >> >> Money is created out of thin air when banks loan and when the federal >> government spends. Money is destroyed into thin air when bank loans are >> repaid and when federal taxes are paid. >> >> > On Jul 3, 2023, at 5:41 PM, Ken Pyzik wrote: >> > >> > Tom -- I believe at the beginning of 2022, QuickBooks went from being a >> desktop and online software package - to being strictly/only online. With >> that transition, I believe they also gave a one-year period where you could >> export data. While this may be a stretch, I believe that they may have >> somehow added a change flag or some other thing to the data to prevent you >> from exporting it as easy as it used to be. This could be the discrepancy >> you are experiencing. In other words, to prevent people from doing what >> you are exactly trying to do -- they may have placed a simple data offset >> or some other thing into the data to prevent easy export. Again, this >> could be a stretch -- but it would explain why one year comes over >> correctly and the next does not. Just my two cents -- for what it is worth >> ( which could be nothing at all!) >> > >> > Ken >> > >> > -Original Message- >> > From: gnucash-user >> On Behalf Of Tom Olin via gnucash-user >> > Sent: Monday, July 3, 2023 1:55 PM >> > To: Kalpesh Patel >> > Cc: gnucash-user@gnucash.org >> > Subject: Re: [GNC] Importing data from QuickBooks Online >> > >> > Good shot, but no, date formats are consistent. >> > >> > -- >> > Tom >> > >> > The federal government imposes a tax on you so YOU need THEIR money, >> not because they need yours. >> > >> >> On Jul 3, 2023, at 4:52 PM, Kalpesh Patel >> wrote: >> >> >> >> I am likely grasping straws here like David but did you verify the >> format of the date that it is consistent throughout? Like it isn’t >> switching from two digits to four digits, or replace certain digits with >> place holders, etc. QuickBooks is (was?) published by the maker of Quicken >> and I remember their exports when it came to Quicken was all over the map >> for the format of the date and ended up normalizing it with an external >> script when I did a full migration from Quicken to GNC. By far this, the >> date format, was bigg
Re: [GNC] Importing data from QuickBooks Online
Vincent, Good suggestions. I’ve attached 2 files, 3 transactions each, for 2022 and 2023. Instructions for importing them are in the documentation of the script, latest version also attached. 2022 still fails, 2023 still works. Create the accounts as needed; there are only a few. -- Tom Money is created when banks loan and when the federal government spends. The latter increases someone’s net worth. The former does not, but the interest and fees transfer net worth from the borrower to the lender. > On Jul 3, 2023, at 6:44 PM, Vincent Dawans wrote: > > Tom: > > I haven't read every single email in this thread but have this advice for you > to further diagnose. When I face a puzzle like this, I like to try creating > the simplest possible scenario in which I am still able to reproduce the > problem. In this case this would start by figuring out how far you can pare > down your 2022 csv file while still having the problem. Can you pair it down > to just a few transactions, like 5 or 10 max? Can you then change some of > that data in that pared down file and still have the problem? By simplifying > you can often more easily find the source of the problem and also might be > able to share your csv file when it reaches the point where the data in it is > no longer personal. > > Sincerely, > > Vincent Dawans > > On Mon, Jul 3, 2023 at 3:35 PM Tom Olin via gnucash-user > mailto:gnucash-user@gnucash.org>> wrote: > I would add that I’m more inclined to attribute the problem to GnuCash logic. > However, the code will require a lot of study on my part before I can make > meaningful sense of it. I just wish I could think of some possible logic that > might explain it. > > -- > Tom > > Money is created out of thin air when banks loan and when the federal > government spends. Money is destroyed into thin air when bank loans are > repaid and when federal taxes are paid. > > > On Jul 3, 2023, at 5:41 PM, Ken Pyzik > <mailto:py...@outlook.com>> wrote: > > > > Tom -- I believe at the beginning of 2022, QuickBooks went from being a > > desktop and online software package - to being strictly/only online. With > > that transition, I believe they also gave a one-year period where you could > > export data. While this may be a stretch, I believe that they may have > > somehow added a change flag or some other thing to the data to prevent you > > from exporting it as easy as it used to be. This could be the discrepancy > > you are experiencing. In other words, to prevent people from doing what > > you are exactly trying to do -- they may have placed a simple data offset > > or some other thing into the data to prevent easy export. Again, this > > could be a stretch -- but it would explain why one year comes over > > correctly and the next does not. Just my two cents -- for what it is worth > > ( which could be nothing at all!) > > > > Ken > > > > -Original Message- > > From: gnucash-user > <mailto:outlook....@gnucash.org>> On Behalf Of Tom Olin via gnucash-user > > Sent: Monday, July 3, 2023 1:55 PM > > To: Kalpesh Patel mailto:kalpesh.pa...@usa.net>> > > Cc: gnucash-user@gnucash.org <mailto:gnucash-user@gnucash.org> > > Subject: Re: [GNC] Importing data from QuickBooks Online > > > > Good shot, but no, date formats are consistent. > > > > -- > > Tom > > > > The federal government imposes a tax on you so YOU need THEIR money, not > > because they need yours. > > > >> On Jul 3, 2023, at 4:52 PM, Kalpesh Patel >> <mailto:kalpesh.pa...@usa.net>> wrote: > >> > >> I am likely grasping straws here like David but did you verify the format > >> of the date that it is consistent throughout? Like it isn’t switching from > >> two digits to four digits, or replace certain digits with place holders, > >> etc. QuickBooks is (was?) published by the maker of Quicken and I remember > >> their exports when it came to Quicken was all over the map for the format > >> of the date and ended up normalizing it with an external script when I did > >> a full migration from Quicken to GNC. By far this, the date format, was > >> biggest PITA. > >> > >> -Original Message- > >> From: Tom Olin mailto:t...@tomolin.net>> > >> Sent: Monday, July 03, 2023 12:14 PM > >> To: Jean L mailto:rip...@gmail.com>> > >> Cc: gnucash-user@gnucash.org <mailto:gnucash-user@gnucash.org> > >> Subject: Re: [GNC] Importing data from QuickBooks Online > >> > >>
Re: [GNC] Importing data from QuickBooks Online
Tom: I haven't read every single email in this thread but have this advice for you to further diagnose. When I face a puzzle like this, I like to try creating the simplest possible scenario in which I am still able to reproduce the problem. In this case this would start by figuring out how far you can pare down your 2022 csv file while still having the problem. Can you pair it down to just a few transactions, like 5 or 10 max? Can you then change some of that data in that pared down file and still have the problem? By simplifying you can often more easily find the source of the problem and also might be able to share your csv file when it reaches the point where the data in it is no longer personal. Sincerely, Vincent Dawans On Mon, Jul 3, 2023 at 3:35 PM Tom Olin via gnucash-user < gnucash-user@gnucash.org> wrote: > I would add that I’m more inclined to attribute the problem to GnuCash > logic. However, the code will require a lot of study on my part before I > can make meaningful sense of it. I just wish I could think of some possible > logic that might explain it. > > -- > Tom > > Money is created out of thin air when banks loan and when the federal > government spends. Money is destroyed into thin air when bank loans are > repaid and when federal taxes are paid. > > > On Jul 3, 2023, at 5:41 PM, Ken Pyzik wrote: > > > > Tom -- I believe at the beginning of 2022, QuickBooks went from being a > desktop and online software package - to being strictly/only online. With > that transition, I believe they also gave a one-year period where you could > export data. While this may be a stretch, I believe that they may have > somehow added a change flag or some other thing to the data to prevent you > from exporting it as easy as it used to be. This could be the discrepancy > you are experiencing. In other words, to prevent people from doing what > you are exactly trying to do -- they may have placed a simple data offset > or some other thing into the data to prevent easy export. Again, this > could be a stretch -- but it would explain why one year comes over > correctly and the next does not. Just my two cents -- for what it is worth > ( which could be nothing at all!) > > > > Ken > > > > -Original Message- > > From: gnucash-user > On Behalf Of Tom Olin via gnucash-user > > Sent: Monday, July 3, 2023 1:55 PM > > To: Kalpesh Patel > > Cc: gnucash-user@gnucash.org > > Subject: Re: [GNC] Importing data from QuickBooks Online > > > > Good shot, but no, date formats are consistent. > > > > -- > > Tom > > > > The federal government imposes a tax on you so YOU need THEIR money, not > because they need yours. > > > >> On Jul 3, 2023, at 4:52 PM, Kalpesh Patel > wrote: > >> > >> I am likely grasping straws here like David but did you verify the > format of the date that it is consistent throughout? Like it isn’t > switching from two digits to four digits, or replace certain digits with > place holders, etc. QuickBooks is (was?) published by the maker of Quicken > and I remember their exports when it came to Quicken was all over the map > for the format of the date and ended up normalizing it with an external > script when I did a full migration from Quicken to GNC. By far this, the > date format, was biggest PITA. > >> > >> -Original Message- > >> From: Tom Olin > >> Sent: Monday, July 03, 2023 12:14 PM > >> To: Jean L > >> Cc: gnucash-user@gnucash.org > >> Subject: Re: [GNC] Importing data from QuickBooks Online > >> > >> Jean, > >> > >> Thanks for that. I did play with a few similar options but couldn’t get > anything to work easily. Some were aimed more at Quicken or QuickBooks > desktop instead of QuickBooks Online. > >> > >> As it turns out, my minimal script seems to work very well with this > one weird anomaly. The nature of it suggests something that should be > easily worked around - if I can just figure out what it is. > >> > >> -- > >> Tom > >> > >> Federal tax dollars don’t exist. Federal spending creates dollars out > of thin air. With federal tax payments, the opposite occurs. > >> > >>> On Jul 3, 2023, at 12:04 PM, Jean L wrote: > >>> > >>> This may be slightly off topic, or too late to help, but... > >>> > >>> In github, there is a repository > >>> https://github.com/tim-rohrer/move2gnucash > >>> That seems pretty well setup to migrate your data from a quicken csv > export to GC. I haven't used it, but I looked at it for a friend and it > looked i
Re: [GNC] Importing data from QuickBooks Online
I would add that I’m more inclined to attribute the problem to GnuCash logic. However, the code will require a lot of study on my part before I can make meaningful sense of it. I just wish I could think of some possible logic that might explain it. -- Tom Money is created out of thin air when banks loan and when the federal government spends. Money is destroyed into thin air when bank loans are repaid and when federal taxes are paid. > On Jul 3, 2023, at 5:41 PM, Ken Pyzik wrote: > > Tom -- I believe at the beginning of 2022, QuickBooks went from being a > desktop and online software package - to being strictly/only online. With > that transition, I believe they also gave a one-year period where you could > export data. While this may be a stretch, I believe that they may have > somehow added a change flag or some other thing to the data to prevent you > from exporting it as easy as it used to be. This could be the discrepancy > you are experiencing. In other words, to prevent people from doing what you > are exactly trying to do -- they may have placed a simple data offset or some > other thing into the data to prevent easy export. Again, this could be a > stretch -- but it would explain why one year comes over correctly and the > next does not. Just my two cents -- for what it is worth ( which could be > nothing at all!) > > Ken > > -Original Message- > From: gnucash-user On > Behalf Of Tom Olin via gnucash-user > Sent: Monday, July 3, 2023 1:55 PM > To: Kalpesh Patel > Cc: gnucash-user@gnucash.org > Subject: Re: [GNC] Importing data from QuickBooks Online > > Good shot, but no, date formats are consistent. > > -- > Tom > > The federal government imposes a tax on you so YOU need THEIR money, not > because they need yours. > >> On Jul 3, 2023, at 4:52 PM, Kalpesh Patel wrote: >> >> I am likely grasping straws here like David but did you verify the format of >> the date that it is consistent throughout? Like it isn’t switching from two >> digits to four digits, or replace certain digits with place holders, etc. >> QuickBooks is (was?) published by the maker of Quicken and I remember their >> exports when it came to Quicken was all over the map for the format of the >> date and ended up normalizing it with an external script when I did a full >> migration from Quicken to GNC. By far this, the date format, was biggest >> PITA. >> >> -Original Message- >> From: Tom Olin >> Sent: Monday, July 03, 2023 12:14 PM >> To: Jean L >> Cc: gnucash-user@gnucash.org >> Subject: Re: [GNC] Importing data from QuickBooks Online >> >> Jean, >> >> Thanks for that. I did play with a few similar options but couldn’t get >> anything to work easily. Some were aimed more at Quicken or QuickBooks >> desktop instead of QuickBooks Online. >> >> As it turns out, my minimal script seems to work very well with this one >> weird anomaly. The nature of it suggests something that should be easily >> worked around - if I can just figure out what it is. >> >> -- >> Tom >> >> Federal tax dollars don’t exist. Federal spending creates dollars out of >> thin air. With federal tax payments, the opposite occurs. >> >>> On Jul 3, 2023, at 12:04 PM, Jean L wrote: >>> >>> This may be slightly off topic, or too late to help, but... >>> >>> In github, there is a repository >>> https://github.com/tim-rohrer/move2gnucash >>> That seems pretty well setup to migrate your data from a quicken csv export >>> to GC. I haven't used it, but I looked at it for a friend and it looked >>> interesting. >>> >>> Jean >>> >>> On 7/3/2023 8:59 AM, Tom Olin via gnucash-user wrote: >>>> [Resending to the list. Original reply went only to Jim.] >>>> >>>> Jim, >>>> >>>> Fair questions. Answers below, and I’ve attached the script itself which >>>> includes documentation which addresses some of the questions. I’ve >>>> reviewed all documentation that I can find. >>>> >>>> From QBO, I export a journal report to XLS (only usable option in QBO). >>>> >>>> GnuCash 5.3 on macOS 11.7.8 >>>> >>>> See the script for the specific steps. >>>> >>>> The import matcher step of the import process is fully satisfied. That is >>>> the step labeled “Match Import and GnuCash accounts”. >>>> >>>> The problem manifests at the “Match Transactions” screen where all >
Re: [GNC] Importing data from QuickBooks Online
Ken, That’s a better theory than anything I’ve come up with so far. However, that would seem to get it backwards. Presumably, they might allow export of 2022 but not 2023. In my case, it was 2022 that failed. If they managed to do something like that, how might they do it? The only idea I come up with would be some invisible characters in the text. But analysis shows no such characters. Thanks for the suggestion. -- Tom A forest has no "value" to capitalism until it is cut down. > On Jul 3, 2023, at 5:41 PM, Ken Pyzik wrote: > > Tom -- I believe at the beginning of 2022, QuickBooks went from being a > desktop and online software package - to being strictly/only online. With > that transition, I believe they also gave a one-year period where you could > export data. While this may be a stretch, I believe that they may have > somehow added a change flag or some other thing to the data to prevent you > from exporting it as easy as it used to be. This could be the discrepancy > you are experiencing. In other words, to prevent people from doing what you > are exactly trying to do -- they may have placed a simple data offset or some > other thing into the data to prevent easy export. Again, this could be a > stretch -- but it would explain why one year comes over correctly and the > next does not. Just my two cents -- for what it is worth ( which could be > nothing at all!) > > Ken > > -Original Message- > From: gnucash-user On > Behalf Of Tom Olin via gnucash-user > Sent: Monday, July 3, 2023 1:55 PM > To: Kalpesh Patel > Cc: gnucash-user@gnucash.org > Subject: Re: [GNC] Importing data from QuickBooks Online > > Good shot, but no, date formats are consistent. > > -- > Tom > > The federal government imposes a tax on you so YOU need THEIR money, not > because they need yours. > >> On Jul 3, 2023, at 4:52 PM, Kalpesh Patel wrote: >> >> I am likely grasping straws here like David but did you verify the format of >> the date that it is consistent throughout? Like it isn’t switching from two >> digits to four digits, or replace certain digits with place holders, etc. >> QuickBooks is (was?) published by the maker of Quicken and I remember their >> exports when it came to Quicken was all over the map for the format of the >> date and ended up normalizing it with an external script when I did a full >> migration from Quicken to GNC. By far this, the date format, was biggest >> PITA. >> >> -Original Message- >> From: Tom Olin >> Sent: Monday, July 03, 2023 12:14 PM >> To: Jean L >> Cc: gnucash-user@gnucash.org >> Subject: Re: [GNC] Importing data from QuickBooks Online >> >> Jean, >> >> Thanks for that. I did play with a few similar options but couldn’t get >> anything to work easily. Some were aimed more at Quicken or QuickBooks >> desktop instead of QuickBooks Online. >> >> As it turns out, my minimal script seems to work very well with this one >> weird anomaly. The nature of it suggests something that should be easily >> worked around - if I can just figure out what it is. >> >> -- >> Tom >> >> Federal tax dollars don’t exist. Federal spending creates dollars out of >> thin air. With federal tax payments, the opposite occurs. >> >>> On Jul 3, 2023, at 12:04 PM, Jean L wrote: >>> >>> This may be slightly off topic, or too late to help, but... >>> >>> In github, there is a repository >>> https://github.com/tim-rohrer/move2gnucash >>> That seems pretty well setup to migrate your data from a quicken csv export >>> to GC. I haven't used it, but I looked at it for a friend and it looked >>> interesting. >>> >>> Jean >>> >>> On 7/3/2023 8:59 AM, Tom Olin via gnucash-user wrote: >>>> [Resending to the list. Original reply went only to Jim.] >>>> >>>> Jim, >>>> >>>> Fair questions. Answers below, and I’ve attached the script itself which >>>> includes documentation which addresses some of the questions. I’ve >>>> reviewed all documentation that I can find. >>>> >>>> From QBO, I export a journal report to XLS (only usable option in QBO). >>>> >>>> GnuCash 5.3 on macOS 11.7.8 >>>> >>>> See the script for the specific steps. >>>> >>>> The import matcher step of the import process is fully satisfied. That is >>>> the step labeled “Match Import and GnuCash accounts”. >>>> >>>> The problem mani
Re: [GNC] Importing data from QuickBooks Online
Tom -- I believe at the beginning of 2022, QuickBooks went from being a desktop and online software package - to being strictly/only online. With that transition, I believe they also gave a one-year period where you could export data. While this may be a stretch, I believe that they may have somehow added a change flag or some other thing to the data to prevent you from exporting it as easy as it used to be. This could be the discrepancy you are experiencing. In other words, to prevent people from doing what you are exactly trying to do -- they may have placed a simple data offset or some other thing into the data to prevent easy export. Again, this could be a stretch -- but it would explain why one year comes over correctly and the next does not. Just my two cents -- for what it is worth ( which could be nothing at all!) Ken -Original Message- From: gnucash-user On Behalf Of Tom Olin via gnucash-user Sent: Monday, July 3, 2023 1:55 PM To: Kalpesh Patel Cc: gnucash-user@gnucash.org Subject: Re: [GNC] Importing data from QuickBooks Online Good shot, but no, date formats are consistent. -- Tom The federal government imposes a tax on you so YOU need THEIR money, not because they need yours. > On Jul 3, 2023, at 4:52 PM, Kalpesh Patel wrote: > > I am likely grasping straws here like David but did you verify the format of > the date that it is consistent throughout? Like it isn’t switching from two > digits to four digits, or replace certain digits with place holders, etc. > QuickBooks is (was?) published by the maker of Quicken and I remember their > exports when it came to Quicken was all over the map for the format of the > date and ended up normalizing it with an external script when I did a full > migration from Quicken to GNC. By far this, the date format, was biggest PITA. > > -Original Message- > From: Tom Olin > Sent: Monday, July 03, 2023 12:14 PM > To: Jean L > Cc: gnucash-user@gnucash.org > Subject: Re: [GNC] Importing data from QuickBooks Online > > Jean, > > Thanks for that. I did play with a few similar options but couldn’t get > anything to work easily. Some were aimed more at Quicken or QuickBooks > desktop instead of QuickBooks Online. > > As it turns out, my minimal script seems to work very well with this one > weird anomaly. The nature of it suggests something that should be easily > worked around - if I can just figure out what it is. > > -- > Tom > > Federal tax dollars don’t exist. Federal spending creates dollars out of thin > air. With federal tax payments, the opposite occurs. > >> On Jul 3, 2023, at 12:04 PM, Jean L wrote: >> >> This may be slightly off topic, or too late to help, but... >> >> In github, there is a repository >> https://github.com/tim-rohrer/move2gnucash >> That seems pretty well setup to migrate your data from a quicken csv export >> to GC. I haven't used it, but I looked at it for a friend and it looked >> interesting. >> >> Jean >> >> On 7/3/2023 8:59 AM, Tom Olin via gnucash-user wrote: >>> [Resending to the list. Original reply went only to Jim.] >>> >>> Jim, >>> >>> Fair questions. Answers below, and I’ve attached the script itself which >>> includes documentation which addresses some of the questions. I’ve reviewed >>> all documentation that I can find. >>> >>> From QBO, I export a journal report to XLS (only usable option in QBO). >>> >>> GnuCash 5.3 on macOS 11.7.8 >>> >>> See the script for the specific steps. >>> >>> The import matcher step of the import process is fully satisfied. That is >>> the step labeled “Match Import and GnuCash accounts”. >>> >>> The problem manifests at the “Match Transactions” screen where all >>> transactions need to be matched. I’ve attached a screenshot if it is >>> supported here. >>> >>> One more data point: I tried changing one of the transactions in the 2022 >>> import file to 2023. It still failed the same way. >>> >>> — >>> Tom >>> >>>> On Jul 2, 2023, at 11:55 PM, Jim DeLaHunt wrote: >>>> >>>> Tom: >>>> >>>> On 2023-07-02 15:10, Tom Olin via gnucash-user wrote: >>>>> I’m attempting to import data from QuickBooks Online to GnuCash. I’ve >>>>> written an awk script which appears to work well except for one major >>>>> issue. >>>> What format is the data which you export from Quickbooks Online? CSV >>>> (Comma Separated Values text files with tabular data)? QFX (Quicken >>>&g
Re: [GNC] Importing data from QuickBooks Online
Good shot, but no, date formats are consistent. -- Tom The federal government imposes a tax on you so YOU need THEIR money, not because they need yours. > On Jul 3, 2023, at 4:52 PM, Kalpesh Patel wrote: > > I am likely grasping straws here like David but did you verify the format of > the date that it is consistent throughout? Like it isn’t switching from two > digits to four digits, or replace certain digits with place holders, etc. > QuickBooks is (was?) published by the maker of Quicken and I remember their > exports when it came to Quicken was all over the map for the format of the > date and ended up normalizing it with an external script when I did a full > migration from Quicken to GNC. By far this, the date format, was biggest PITA. > > -Original Message- > From: Tom Olin > Sent: Monday, July 03, 2023 12:14 PM > To: Jean L > Cc: gnucash-user@gnucash.org > Subject: Re: [GNC] Importing data from QuickBooks Online > > Jean, > > Thanks for that. I did play with a few similar options but couldn’t get > anything to work easily. Some were aimed more at Quicken or QuickBooks > desktop instead of QuickBooks Online. > > As it turns out, my minimal script seems to work very well with this one > weird anomaly. The nature of it suggests something that should be easily > worked around - if I can just figure out what it is. > > -- > Tom > > Federal tax dollars don’t exist. Federal spending creates dollars out of thin > air. With federal tax payments, the opposite occurs. > >> On Jul 3, 2023, at 12:04 PM, Jean L wrote: >> >> This may be slightly off topic, or too late to help, but... >> >> In github, there is a repository >> https://github.com/tim-rohrer/move2gnucash >> That seems pretty well setup to migrate your data from a quicken csv export >> to GC. I haven't used it, but I looked at it for a friend and it looked >> interesting. >> >> Jean >> >> On 7/3/2023 8:59 AM, Tom Olin via gnucash-user wrote: >>> [Resending to the list. Original reply went only to Jim.] >>> >>> Jim, >>> >>> Fair questions. Answers below, and I’ve attached the script itself which >>> includes documentation which addresses some of the questions. I’ve reviewed >>> all documentation that I can find. >>> >>> From QBO, I export a journal report to XLS (only usable option in QBO). >>> >>> GnuCash 5.3 on macOS 11.7.8 >>> >>> See the script for the specific steps. >>> >>> The import matcher step of the import process is fully satisfied. That is >>> the step labeled “Match Import and GnuCash accounts”. >>> >>> The problem manifests at the “Match Transactions” screen where all >>> transactions need to be matched. I’ve attached a screenshot if it is >>> supported here. >>> >>> One more data point: I tried changing one of the transactions in the 2022 >>> import file to 2023. It still failed the same way. >>> >>> — >>> Tom >>> >>>> On Jul 2, 2023, at 11:55 PM, Jim DeLaHunt wrote: >>>> >>>> Tom: >>>> >>>> On 2023-07-02 15:10, Tom Olin via gnucash-user wrote: >>>>> I’m attempting to import data from QuickBooks Online to GnuCash. I’ve >>>>> written an awk script which appears to work well except for one major >>>>> issue. >>>> What format is the data which you export from Quickbooks Online? CSV >>>> (Comma Separated Values text files with tabular data)? QFX (Quicken >>>> Financial Exchange, similar to OFX)? >>>> >>>> What GnuCash version are you using? On what computer OS? >>>> >>>> What GnuCash sequence of actions do you use to import the data? >>>> >>>> >>>>> The data I’m importing spans the years 2022 and 2023. All 2023 >>>>> transactions import cleanly, but all 2022 transactions import unbalanced, >>>>> meaning I have to manually match up each of them - doable but tedious. >>>> Let's assume you are exporting data in CSV format, and using the current >>>> version of GnuCash (5.3), and importing using the File… Import… Import >>>> Transactions from CSV menu item. You should be directed through an import >>>> matcher. This is the place where GnuCash should assign accounts to balance >>>> each transaction. Is each transaction assigned to an account in the import >>>> matcher? >>>> >>>> Have you read the section
Re: [GNC] Importing data from QuickBooks Online
I am likely grasping straws here like David but did you verify the format of the date that it is consistent throughout? Like it isn’t switching from two digits to four digits, or replace certain digits with place holders, etc. QuickBooks is (was?) published by the maker of Quicken and I remember their exports when it came to Quicken was all over the map for the format of the date and ended up normalizing it with an external script when I did a full migration from Quicken to GNC. By far this, the date format, was biggest PITA. -Original Message- From: Tom Olin Sent: Monday, July 03, 2023 12:14 PM To: Jean L Cc: gnucash-user@gnucash.org Subject: Re: [GNC] Importing data from QuickBooks Online Jean, Thanks for that. I did play with a few similar options but couldn’t get anything to work easily. Some were aimed more at Quicken or QuickBooks desktop instead of QuickBooks Online. As it turns out, my minimal script seems to work very well with this one weird anomaly. The nature of it suggests something that should be easily worked around - if I can just figure out what it is. -- Tom Federal tax dollars don’t exist. Federal spending creates dollars out of thin air. With federal tax payments, the opposite occurs. > On Jul 3, 2023, at 12:04 PM, Jean L wrote: > > This may be slightly off topic, or too late to help, but... > > In github, there is a repository > https://github.com/tim-rohrer/move2gnucash > That seems pretty well setup to migrate your data from a quicken csv export > to GC. I haven't used it, but I looked at it for a friend and it looked > interesting. > > Jean > > On 7/3/2023 8:59 AM, Tom Olin via gnucash-user wrote: >> [Resending to the list. Original reply went only to Jim.] >> >> Jim, >> >> Fair questions. Answers below, and I’ve attached the script itself which >> includes documentation which addresses some of the questions. I’ve reviewed >> all documentation that I can find. >> >> From QBO, I export a journal report to XLS (only usable option in QBO). >> >> GnuCash 5.3 on macOS 11.7.8 >> >> See the script for the specific steps. >> >> The import matcher step of the import process is fully satisfied. That is >> the step labeled “Match Import and GnuCash accounts”. >> >> The problem manifests at the “Match Transactions” screen where all >> transactions need to be matched. I’ve attached a screenshot if it is >> supported here. >> >> One more data point: I tried changing one of the transactions in the 2022 >> import file to 2023. It still failed the same way. >> >> — >> Tom >> >>> On Jul 2, 2023, at 11:55 PM, Jim DeLaHunt wrote: >>> >>> Tom: >>> >>> On 2023-07-02 15:10, Tom Olin via gnucash-user wrote: >>>> I’m attempting to import data from QuickBooks Online to GnuCash. I’ve >>>> written an awk script which appears to work well except for one major >>>> issue. >>> What format is the data which you export from Quickbooks Online? CSV (Comma >>> Separated Values text files with tabular data)? QFX (Quicken Financial >>> Exchange, similar to OFX)? >>> >>> What GnuCash version are you using? On what computer OS? >>> >>> What GnuCash sequence of actions do you use to import the data? >>> >>> >>>> The data I’m importing spans the years 2022 and 2023. All 2023 >>>> transactions import cleanly, but all 2022 transactions import unbalanced, >>>> meaning I have to manually match up each of them - doable but tedious. >>> Let's assume you are exporting data in CSV format, and using the current >>> version of GnuCash (5.3), and importing using the File… Import… Import >>> Transactions from CSV menu item. You should be directed through an import >>> matcher. This is the place where GnuCash should assign accounts to balance >>> each transaction. Is each transaction assigned to an account in the import >>> matcher? >>> >>> Have you read the section of the documentation explaining how to import >>> data? >>> >>>> Can anyone think of anything that would cause this behavior? I’ve ruled >>>> out Accounting Period. I’ve imported each year separately. I’ve exported >>>> each year separately from QBO. The behavior persists. >>>> >>>> I’m stumped. Any ideas? >>> I hope these questions help get enough information on the table to give >>> someone ideas. >>> >>> Best regards, >>>—Jim DeLaHunt >>> >>> >>> _
Re: [GNC] Importing data from QuickBooks Online
All records came from the same account tree at the same time. We need more straws!!! :-) -- Tom How many dollars would you own if the federal government had balanced its budget every year since the founding of our country? (Hint: It’s an exact number.) > On Jul 3, 2023, at 2:32 PM, David Reiser wrote: > > Really grasping at straws now, but invisible characters/extra spaces in the > 2022 account names? > -- > Dave Reiser > dbrei...@icloud.com > > > > > >> On Jul 3, 2023, at 12:29 PM, Tom Olin wrote: >> >> David, >> >> Both import files were generated from the same 2-year QBO export, so they >> have identical structure. The process was: >> >> QBO export -> convert to CVS -> my script -> unified import file (CVS) >> >> The problem occurred identically whether I imported the unified file or just >> the 2022 data portion. >> >> I tried changing a 2023 transaction to 2022 and it imported correctly. >> >> -- >> Tom >> >> For the government, which came first? (a) first $ taxed; (b) first $ >> borrowed; (c) first $ spent. (Hint: state or federal?) >> >>> On Jul 3, 2023, at 12:11 PM, David Reiser wrote: >>> >>> So, the year isn’t the sticking point. If you look at the .xls files — one >>> transaction from 2022 and one from 2023 — do you see a difference? If you >>> move a 2023 transaction to 2022, does it import correctly, or fail? I’m >>> guessing there’s a column missing in the 2022 xls file. >>> >>> -- >>> Dave Reiser >>> dbrei...@icloud.com >>> >>> >>> >>> On Jul 3, 2023, at 11:59 AM, Tom Olin via gnucash-user wrote: [Resending to the list. Original reply went only to Jim.] Jim, Fair questions. Answers below, and I’ve attached the script itself which includes documentation which addresses some of the questions. I’ve reviewed all documentation that I can find. From QBO, I export a journal report to XLS (only usable option in QBO). GnuCash 5.3 on macOS 11.7.8 See the script for the specific steps. The import matcher step of the import process is fully satisfied. That is the step labeled “Match Import and GnuCash accounts”. The problem manifests at the “Match Transactions” screen where all transactions need to be matched. I’ve attached a screenshot if it is supported here. One more data point: I tried changing one of the transactions in the 2022 import file to 2023. It still failed the same way. — Tom > On Jul 2, 2023, at 11:55 PM, Jim DeLaHunt wrote: > > Tom: > > On 2023-07-02 15:10, Tom Olin via gnucash-user wrote: >> I’m attempting to import data from QuickBooks Online to GnuCash. I’ve >> written an awk script which appears to work well except for one major >> issue. > > What format is the data which you export from Quickbooks Online? CSV > (Comma Separated Values text files with tabular data)? QFX (Quicken > Financial Exchange, similar to OFX)? > > What GnuCash version are you using? On what computer OS? > > What GnuCash sequence of actions do you use to import the data? > > >> The data I’m importing spans the years 2022 and 2023. All 2023 >> transactions import cleanly, but all 2022 transactions import >> unbalanced, meaning I have to manually match up each of them - doable >> but tedious. > > Let's assume you are exporting data in CSV format, and using the current > version of GnuCash (5.3), and importing using the File… Import… Import > Transactions from CSV menu item. You should be directed through an import > matcher. This is the place where GnuCash should assign accounts to > balance each transaction. Is each transaction assigned to an account in > the import matcher? > > Have you read the section of the documentation explaining how to import > data? > >> Can anyone think of anything that would cause this behavior? I’ve ruled >> out Accounting Period. I’ve imported each year separately. I’ve exported >> each year separately from QBO. The behavior persists. >> >> I’m stumped. Any ideas? > > I hope these questions help get enough information on the table to give > someone ideas. > > Best regards, > —Jim DeLaHunt > >>> >> > ___ gnucash-user mailing list gnucash-user@gnucash.org To update your subscription preferences or to unsubscribe: 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.
Re: [GNC] Importing data from QuickBooks Online
Really grasping at straws now, but invisible characters/extra spaces in the 2022 account names? -- Dave Reiser dbrei...@icloud.com > On Jul 3, 2023, at 12:29 PM, Tom Olin wrote: > > David, > > Both import files were generated from the same 2-year QBO export, so they > have identical structure. The process was: > > QBO export -> convert to CVS -> my script -> unified import file (CVS) > > The problem occurred identically whether I imported the unified file or just > the 2022 data portion. > > I tried changing a 2023 transaction to 2022 and it imported correctly. > > -- > Tom > > For the government, which came first? (a) first $ taxed; (b) first $ > borrowed; (c) first $ spent. (Hint: state or federal?) > >> On Jul 3, 2023, at 12:11 PM, David Reiser wrote: >> >> So, the year isn’t the sticking point. If you look at the .xls files — one >> transaction from 2022 and one from 2023 — do you see a difference? If you >> move a 2023 transaction to 2022, does it import correctly, or fail? I’m >> guessing there’s a column missing in the 2022 xls file. >> >> -- >> Dave Reiser >> dbrei...@icloud.com >> >> >> >> >>> On Jul 3, 2023, at 11:59 AM, Tom Olin via gnucash-user >>> wrote: >>> >>> [Resending to the list. Original reply went only to Jim.] >>> >>> Jim, >>> >>> Fair questions. Answers below, and I’ve attached the script itself which >>> includes documentation which addresses some of the questions. I’ve reviewed >>> all documentation that I can find. >>> >>> From QBO, I export a journal report to XLS (only usable option in QBO). >>> >>> GnuCash 5.3 on macOS 11.7.8 >>> >>> See the script for the specific steps. >>> >>> The import matcher step of the import process is fully satisfied. That is >>> the step labeled “Match Import and GnuCash accounts”. >>> >>> The problem manifests at the “Match Transactions” screen where all >>> transactions need to be matched. I’ve attached a screenshot if it is >>> supported here. >>> >>> One more data point: I tried changing one of the transactions in the 2022 >>> import file to 2023. It still failed the same way. >>> >>> — >>> Tom >>> On Jul 2, 2023, at 11:55 PM, Jim DeLaHunt wrote: Tom: On 2023-07-02 15:10, Tom Olin via gnucash-user wrote: > I’m attempting to import data from QuickBooks Online to GnuCash. I’ve > written an awk script which appears to work well except for one major > issue. What format is the data which you export from Quickbooks Online? CSV (Comma Separated Values text files with tabular data)? QFX (Quicken Financial Exchange, similar to OFX)? What GnuCash version are you using? On what computer OS? What GnuCash sequence of actions do you use to import the data? > The data I’m importing spans the years 2022 and 2023. All 2023 > transactions import cleanly, but all 2022 transactions import unbalanced, > meaning I have to manually match up each of them - doable but tedious. Let's assume you are exporting data in CSV format, and using the current version of GnuCash (5.3), and importing using the File… Import… Import Transactions from CSV menu item. You should be directed through an import matcher. This is the place where GnuCash should assign accounts to balance each transaction. Is each transaction assigned to an account in the import matcher? Have you read the section of the documentation explaining how to import data? > Can anyone think of anything that would cause this behavior? I’ve ruled > out Accounting Period. I’ve imported each year separately. I’ve exported > each year separately from QBO. The behavior persists. > > I’m stumped. Any ideas? I hope these questions help get enough information on the table to give someone ideas. Best regards, —Jim DeLaHunt >> > ___ gnucash-user mailing list gnucash-user@gnucash.org To update your subscription preferences or to unsubscribe: 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.
Re: [GNC] Importing data from QuickBooks Online
David, Both import files were generated from the same 2-year QBO export, so they have identical structure. The process was: QBO export -> convert to CVS -> my script -> unified import file (CVS) The problem occurred identically whether I imported the unified file or just the 2022 data portion. I tried changing a 2023 transaction to 2022 and it imported correctly. -- Tom For the government, which came first? (a) first $ taxed; (b) first $ borrowed; (c) first $ spent. (Hint: state or federal?) > On Jul 3, 2023, at 12:11 PM, David Reiser wrote: > > So, the year isn’t the sticking point. If you look at the .xls files — one > transaction from 2022 and one from 2023 — do you see a difference? If you > move a 2023 transaction to 2022, does it import correctly, or fail? I’m > guessing there’s a column missing in the 2022 xls file. > > -- > Dave Reiser > dbrei...@icloud.com > > > > >> On Jul 3, 2023, at 11:59 AM, Tom Olin via gnucash-user >> wrote: >> >> [Resending to the list. Original reply went only to Jim.] >> >> Jim, >> >> Fair questions. Answers below, and I’ve attached the script itself which >> includes documentation which addresses some of the questions. I’ve reviewed >> all documentation that I can find. >> >> From QBO, I export a journal report to XLS (only usable option in QBO). >> >> GnuCash 5.3 on macOS 11.7.8 >> >> See the script for the specific steps. >> >> The import matcher step of the import process is fully satisfied. That is >> the step labeled “Match Import and GnuCash accounts”. >> >> The problem manifests at the “Match Transactions” screen where all >> transactions need to be matched. I’ve attached a screenshot if it is >> supported here. >> >> One more data point: I tried changing one of the transactions in the 2022 >> import file to 2023. It still failed the same way. >> >> — >> Tom >> >>> On Jul 2, 2023, at 11:55 PM, Jim DeLaHunt wrote: >>> >>> Tom: >>> >>> On 2023-07-02 15:10, Tom Olin via gnucash-user wrote: I’m attempting to import data from QuickBooks Online to GnuCash. I’ve written an awk script which appears to work well except for one major issue. >>> >>> What format is the data which you export from Quickbooks Online? CSV (Comma >>> Separated Values text files with tabular data)? QFX (Quicken Financial >>> Exchange, similar to OFX)? >>> >>> What GnuCash version are you using? On what computer OS? >>> >>> What GnuCash sequence of actions do you use to import the data? >>> >>> The data I’m importing spans the years 2022 and 2023. All 2023 transactions import cleanly, but all 2022 transactions import unbalanced, meaning I have to manually match up each of them - doable but tedious. >>> >>> Let's assume you are exporting data in CSV format, and using the current >>> version of GnuCash (5.3), and importing using the File… Import… Import >>> Transactions from CSV menu item. You should be directed through an import >>> matcher. This is the place where GnuCash should assign accounts to balance >>> each transaction. Is each transaction assigned to an account in the import >>> matcher? >>> >>> Have you read the section of the documentation explaining how to import >>> data? >>> Can anyone think of anything that would cause this behavior? I’ve ruled out Accounting Period. I’ve imported each year separately. I’ve exported each year separately from QBO. The behavior persists. I’m stumped. Any ideas? >>> >>> I hope these questions help get enough information on the table to give >>> someone ideas. >>> >>> Best regards, >>> —Jim DeLaHunt >>> > ___ gnucash-user mailing list gnucash-user@gnucash.org To update your subscription preferences or to unsubscribe: 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.
Re: [GNC] Importing data from QuickBooks Online
Jean, Thanks for that. I did play with a few similar options but couldn’t get anything to work easily. Some were aimed more at Quicken or QuickBooks desktop instead of QuickBooks Online. As it turns out, my minimal script seems to work very well with this one weird anomaly. The nature of it suggests something that should be easily worked around - if I can just figure out what it is. -- Tom Federal tax dollars don’t exist. Federal spending creates dollars out of thin air. With federal tax payments, the opposite occurs. > On Jul 3, 2023, at 12:04 PM, Jean L wrote: > > This may be slightly off topic, or too late to help, but... > > In github, there is a repository > https://github.com/tim-rohrer/move2gnucash > That seems pretty well setup to migrate your data from a quicken csv export > to GC. I haven't used it, but I looked at it for a friend and it looked > interesting. > > Jean > > On 7/3/2023 8:59 AM, Tom Olin via gnucash-user wrote: >> [Resending to the list. Original reply went only to Jim.] >> >> Jim, >> >> Fair questions. Answers below, and I’ve attached the script itself which >> includes documentation which addresses some of the questions. I’ve reviewed >> all documentation that I can find. >> >> From QBO, I export a journal report to XLS (only usable option in QBO). >> >> GnuCash 5.3 on macOS 11.7.8 >> >> See the script for the specific steps. >> >> The import matcher step of the import process is fully satisfied. That is >> the step labeled “Match Import and GnuCash accounts”. >> >> The problem manifests at the “Match Transactions” screen where all >> transactions need to be matched. I’ve attached a screenshot if it is >> supported here. >> >> One more data point: I tried changing one of the transactions in the 2022 >> import file to 2023. It still failed the same way. >> >> — >> Tom >> >>> On Jul 2, 2023, at 11:55 PM, Jim DeLaHunt wrote: >>> >>> Tom: >>> >>> On 2023-07-02 15:10, Tom Olin via gnucash-user wrote: I’m attempting to import data from QuickBooks Online to GnuCash. I’ve written an awk script which appears to work well except for one major issue. >>> What format is the data which you export from Quickbooks Online? CSV (Comma >>> Separated Values text files with tabular data)? QFX (Quicken Financial >>> Exchange, similar to OFX)? >>> >>> What GnuCash version are you using? On what computer OS? >>> >>> What GnuCash sequence of actions do you use to import the data? >>> >>> The data I’m importing spans the years 2022 and 2023. All 2023 transactions import cleanly, but all 2022 transactions import unbalanced, meaning I have to manually match up each of them - doable but tedious. >>> Let's assume you are exporting data in CSV format, and using the current >>> version of GnuCash (5.3), and importing using the File… Import… Import >>> Transactions from CSV menu item. You should be directed through an import >>> matcher. This is the place where GnuCash should assign accounts to balance >>> each transaction. Is each transaction assigned to an account in the import >>> matcher? >>> >>> Have you read the section of the documentation explaining how to import >>> data? >>> Can anyone think of anything that would cause this behavior? I’ve ruled out Accounting Period. I’ve imported each year separately. I’ve exported each year separately from QBO. The behavior persists. I’m stumped. Any ideas? >>> I hope these questions help get enough information on the table to give >>> someone ideas. >>> >>> Best regards, >>>—Jim DeLaHunt >>> >>> >>> ___ >>> gnucash-user mailing list >>> gnucash-user@gnucash.org >>> To update your subscription preferences or to unsubscribe: >>> 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-user@gnucash.org >> To update your subscription preferences or to unsubscribe: >> 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-user@gnucash.org > To update your subscription preferences or to unsubscribe: > 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-user@gnucash.org To update your subscription preferences or to unsubscribe: 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
Re: [GNC] Importing data from QuickBooks Online
So, the year isn’t the sticking point. If you look at the .xls files — one transaction from 2022 and one from 2023 — do you see a difference? If you move a 2023 transaction to 2022, does it import correctly, or fail? I’m guessing there’s a column missing in the 2022 xls file. -- Dave Reiser dbrei...@icloud.com > On Jul 3, 2023, at 11:59 AM, Tom Olin via gnucash-user > wrote: > > [Resending to the list. Original reply went only to Jim.] > > Jim, > > Fair questions. Answers below, and I’ve attached the script itself which > includes documentation which addresses some of the questions. I’ve reviewed > all documentation that I can find. > > From QBO, I export a journal report to XLS (only usable option in QBO). > > GnuCash 5.3 on macOS 11.7.8 > > See the script for the specific steps. > > The import matcher step of the import process is fully satisfied. That is the > step labeled “Match Import and GnuCash accounts”. > > The problem manifests at the “Match Transactions” screen where all > transactions need to be matched. I’ve attached a screenshot if it is > supported here. > > One more data point: I tried changing one of the transactions in the 2022 > import file to 2023. It still failed the same way. > > — > Tom > >> On Jul 2, 2023, at 11:55 PM, Jim DeLaHunt wrote: >> >> Tom: >> >> On 2023-07-02 15:10, Tom Olin via gnucash-user wrote: >>> I’m attempting to import data from QuickBooks Online to GnuCash. I’ve >>> written an awk script which appears to work well except for one major issue. >> >> What format is the data which you export from Quickbooks Online? CSV (Comma >> Separated Values text files with tabular data)? QFX (Quicken Financial >> Exchange, similar to OFX)? >> >> What GnuCash version are you using? On what computer OS? >> >> What GnuCash sequence of actions do you use to import the data? >> >> >>> The data I’m importing spans the years 2022 and 2023. All 2023 transactions >>> import cleanly, but all 2022 transactions import unbalanced, meaning I have >>> to manually match up each of them - doable but tedious. >> >> Let's assume you are exporting data in CSV format, and using the current >> version of GnuCash (5.3), and importing using the File… Import… Import >> Transactions from CSV menu item. You should be directed through an import >> matcher. This is the place where GnuCash should assign accounts to balance >> each transaction. Is each transaction assigned to an account in the import >> matcher? >> >> Have you read the section of the documentation explaining how to import data? >> >>> Can anyone think of anything that would cause this behavior? I’ve ruled out >>> Accounting Period. I’ve imported each year separately. I’ve exported each >>> year separately from QBO. The behavior persists. >>> >>> I’m stumped. Any ideas? >> >> I hope these questions help get enough information on the table to give >> someone ideas. >> >> Best regards, >> —Jim DeLaHunt >> ___ gnucash-user mailing list gnucash-user@gnucash.org To update your subscription preferences or to unsubscribe: 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.
Re: [GNC] Importing data from QuickBooks Online
This may be slightly off topic, or too late to help, but... In github, there is a repository https://github.com/tim-rohrer/move2gnucash That seems pretty well setup to migrate your data from a quicken csv export to GC. I haven't used it, but I looked at it for a friend and it looked interesting. Jean On 7/3/2023 8:59 AM, Tom Olin via gnucash-user wrote: [Resending to the list. Original reply went only to Jim.] Jim, Fair questions. Answers below, and I’ve attached the script itself which includes documentation which addresses some of the questions. I’ve reviewed all documentation that I can find. From QBO, I export a journal report to XLS (only usable option in QBO). GnuCash 5.3 on macOS 11.7.8 See the script for the specific steps. The import matcher step of the import process is fully satisfied. That is the step labeled “Match Import and GnuCash accounts”. The problem manifests at the “Match Transactions” screen where all transactions need to be matched. I’ve attached a screenshot if it is supported here. One more data point: I tried changing one of the transactions in the 2022 import file to 2023. It still failed the same way. — Tom On Jul 2, 2023, at 11:55 PM, Jim DeLaHunt wrote: Tom: On 2023-07-02 15:10, Tom Olin via gnucash-user wrote: I’m attempting to import data from QuickBooks Online to GnuCash. I’ve written an awk script which appears to work well except for one major issue. What format is the data which you export from Quickbooks Online? CSV (Comma Separated Values text files with tabular data)? QFX (Quicken Financial Exchange, similar to OFX)? What GnuCash version are you using? On what computer OS? What GnuCash sequence of actions do you use to import the data? The data I’m importing spans the years 2022 and 2023. All 2023 transactions import cleanly, but all 2022 transactions import unbalanced, meaning I have to manually match up each of them - doable but tedious. Let's assume you are exporting data in CSV format, and using the current version of GnuCash (5.3), and importing using the File… Import… Import Transactions from CSV menu item. You should be directed through an import matcher. This is the place where GnuCash should assign accounts to balance each transaction. Is each transaction assigned to an account in the import matcher? Have you read the section of the documentation explaining how to import data? Can anyone think of anything that would cause this behavior? I’ve ruled out Accounting Period. I’ve imported each year separately. I’ve exported each year separately from QBO. The behavior persists. I’m stumped. Any ideas? I hope these questions help get enough information on the table to give someone ideas. Best regards, —Jim DeLaHunt ___ gnucash-user mailing list gnucash-user@gnucash.org To update your subscription preferences or to unsubscribe: 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-user@gnucash.org To update your subscription preferences or to unsubscribe: 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-user@gnucash.org To update your subscription preferences or to unsubscribe: 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.
Re: [GNC] Importing data from QuickBooks Online
Tom: On 2023-07-02 15:10, Tom Olin via gnucash-user wrote: I’m attempting to import data from QuickBooks Online to GnuCash. I’ve written an awk script which appears to work well except for one major issue. What format is the data which you export from Quickbooks Online? CSV (Comma Separated Values text files with tabular data)? QFX (Quicken Financial Exchange, similar to OFX)? What GnuCash version are you using? On what computer OS? What GnuCash sequence of actions do you use to import the data? The data I’m importing spans the years 2022 and 2023. All 2023 transactions import cleanly, but all 2022 transactions import unbalanced, meaning I have to manually match up each of them - doable but tedious. Let's assume you are exporting data in CSV format, and using the current version of GnuCash (5.3), and importing using the File… Import… Import Transactions from CSV menu item. You should be directed through an import matcher. This is the place where GnuCash should assign accounts to balance each transaction. Is each transaction assigned to an account in the import matcher? Have you read the section of the documentation explaining how to import data? Can anyone think of anything that would cause this behavior? I’ve ruled out Accounting Period. I’ve imported each year separately. I’ve exported each year separately from QBO. The behavior persists. I’m stumped. Any ideas? I hope these questions help get enough information on the table to give someone ideas. Best regards, —Jim DeLaHunt ___ gnucash-user mailing list gnucash-user@gnucash.org To update your subscription preferences or to unsubscribe: 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.