Issues with Importing Invoices

Nelson Handcock nelson.handcock at gmail.com
Thu May 25 23:03:02 EDT 2017


Glad to hear you got it working!

If you google "gnucash counters" or look in the online tutorial guide you
might find some hints that may help you get that aspect working.

I guess having a header row for the invoice import forces all the optional
fields as having blank values and also forces the required number of commas
into each row, thereby making sure the file is in the correct format for
the import function. If you open the file in wordpad/notepad then you'll
see all the comma's in the data rows - I'm guessing these might not have
been there in your original file.

The customer import may not have so many optional fields and therefore what
you had in your file was valid right from the start.

It's up to you if you post your invoices as a separate activity to
importing them - but if you fill the "posting" fields in file as well then
it's all done for you when you run the import.


Cheers, Nelson



On Fri, May 26, 2017 at 12:47 PM, Bruce Danielson <DanielsonB at logroom.com>
wrote:

> Nelson – OK, I just copied your exact header row and tried importing but
> with no ID’s (Invoice Number blank) – I got errors that No ID Set (or
> something like that).
>
> So, I plugged in Invoice Numbers – and it worked.  2 invoices to 2 users,
> perfect, just as you would expect.  I then posted both and all is well.
>
>
>
> So – it won’t self-generate invoice numbers, and it needs the header row,
> I can live with all that.
>
>
>
> BTW - When I imported 32 customers, it self-generated customer numbers,
> and I did not have a header row.  But hey, who cares?
>
>
>
> Thanks Nelson, this has been a learning experience.  We can consider this
> issue closed.
>
> Bruce
>
>
>
>
>
> Thx Nelson – Let me try filling in the header row & see if it helps with
> the customer number at all.  That is the central issue right now.
>
> When I post invoices on June 1, I don’t want all 34 to go to C001 (or
> whoever is first on the list).  So I’m trying just these two customers to
> see if I can get this to work.
>
>
>
> I have the latest version 2.6.16 loaded on both PC’s.  I updated both when
> I had the idea to try this on the water club.
>
>
>
> If you think of any reason I get both invoices going to customer C033,
> please let me know.  Any idea would help.
>
> And I’ll let you know if & when I get some success.
>
>
>
> Thx Again Nelson,
>
> Bruce
>
>
>
>
>
> *From:* Nelson Handcock [mailto:nelson.handcock at gmail.com]
> *Sent:* Thursday, May 25, 2017 5:57 PM
>
> *To:* Bruce Danielson
> *Cc:* gnucash-user at gnucash.org
> *Subject:* Re: Issues with Importing Invoices
>
>
>
> Hi Bruce,
>
>
>
> In no particular order....
>
>
>
> Please check your version of gnucash - most recent version is 2.6.16. It's
> best to keep up to date.
>
>
>
> Tou can't delete an invoice - but if it's left as not posted then it won't
> show in any of the accounts. It's a bit annoying but it's always been that
> way AFAIK.
>
>
>
>
>
> I suggest you firstly complete the remaining columns out to 'V', and then
> try again If GST is not relevant for you then leave columns O, P and Q
> blank. You might want to change your invoice numbers to something else
> first - to ensure Gnucash doesn't get confused with existing invoices.
>
>
>
> Due Date can be set to the same as Date Posted - I add 7 days to mine but
> you don't have to if you don't want to.
>
>
>
> Yes - if you keep the first column blank then that could well fix your
> auto-generate invoice numbers. Another way is to open the csv file using
> Wordpad, and adding a comma in the first character position to each line
> (if you don't want to add the header row)
>
>
>
>
>
> It's very strange that you are getting the second invoice with the C033
> customer - that has not been my experience.
>
>
>
> Hope this helps....
>
>
>
>
> Thanks & Regards,
>
>
> Nelson
>
>
>
> On Fri, May 26, 2017 at 10:37 AM, Bruce Danielson <DanielsonB at logroom.com>
> wrote:
>
> Hey Nelson – I totally get all that, and I should have thought to attach a
> copy of my short import file for you to take a look.  Plz see attached.
>
> One thing that did not occur to me is to keep the header row, which I
> should have realized would get ignored during import.  Thx for that tip.
>
>
>
> In my file, both lines are virtually the same except for the Customer ID –
> which are in order (not sure why that should make a difference).
>
> I end up with two invoices, as one would expect, BUT - both are for Cust
> ID C033 (with that person’s name, etc), just as if I’d used C033 for both
> lines.
>
>
>
> The first couple of times I tried this, I had all the posting info
> included (columns R and up) – but then I had to keep unposting them, so I
> got rid of that.  I figured if I could get this to work, I could manually
> post later (just 2 invoices).
>
>
>
> Also – so you know, I’ve tried this on more than one PC – first at work,
> then at home.  Both use Win-7 & Office-10.  Same result.
>
>
>
> My import file was made with MS Excel and saved in CSV (Comma delimited)
> (*.csv) file type.  MS Excel is not perfect at CSV files as if I don’t put
> in invoice numbers, Excel deletes the first column (which is blank) when it
> saves the file.  HEY - Light bulb coming on – maybe if I keep the header
> row, Excel won’t delete the first column, and GnuCash might generate the
> invoice numbers.  I gotta give that a try.  Thx.
>
>
>
> One related thing – is there a way to DELETE the bad invoices I’ve created
> by trying to import?  I could not see how to delete unposted invoices that
> I would like to get rid of.
>
>
>
> Thanks Nelson – I appreciate your help.  Please check out my import file
> and see if you spot anything amiss.
>
> Bruce
>
>
>
> *From:* Nelson Handcock [mailto:nelson.handcock at gmail.com]
> *Sent:* Wednesday, May 24, 2017 12:54 PM
> *To:* Bruce Danielson
> *Cc:* gnucash-user at gnucash.org
> *Subject:* Re: Issues with Importing Invoices
>
>
>
> Bruce,
>
>
>
> I've attached a sample of an invoice import file that I recently prepared
> and successfully loaded up.
>
> If you change the "memberidn" to match your customer ID's and also change
> the account names then it should work for you.
>
>
>
> The key thing for me is sorting my source data by Member ID and then by
> Date, before setting the ID column with the invoice number. Note how you
> can have two lines with the same ID number one after the other - this
> results in a multi-line invoice being imported.
>
>
>
> I usually leave the header row in my source data - gnucash just spits it
> out anyway. It only ever rejects data for me if the memberID does not match
> an existing customer.
>
>
>
> I'm pretty sure I've imported data like this without setting the ID column
> at all, and Gnucash sets the ID number based on the number range set up in
> the settings - Menu path File - Properties - Counters.
>
>
>
>
>
> Hope this helps,..
>
>
>
>
> Thanks & Regards,
>
>
> Nelson
>
>
>
> On Wed, May 24, 2017 at 12:58 PM, Bruce Danielson <DanielsonB at logroom.com>
> wrote:
>
> Greetings - I am a new user, and I am trying out GnuCash on a rather odd
> application - I manage a couple of employee water clubs at work (for
> Sparkletts bottled water - since our employer won't spring for the water -
> pardon the pun).  I have it setup as a business type of accounting.  I'm
> trying this on the smaller & newer of the clubs I manage.  Weird I know,
> but
> I want to make this work.  I am also treasurer of a non-profit, I wish I'd
> known about GnuCash before I went & bought QB.  But OK, for the water club
> -
>
>
>
> I was able to import a bunch of customers (club members) via a csv file I
> made from an Excel roster.  So far, so good.  I have an asset account
> called
> Cash On Hand which monthly dues will pay into, and water bills will be paid
> out of.  And I have only one Vendor (Sparkletts) and one applicable expense
> account (bottled water).
>
>
>
> Since I don't really want to produce 36 separate invoices each month for
> member dues, one at a time, I am hoping to find a way to sort of batch
> generate invoices.  I have tried numerous times making a small 2 line csv
> file from an excel table to import, and have run into several issues.  It
> is
> only 2 lines now because I have 2 customers that are late paying for May,
> so
> I thought I'd try this out on them.
>
>
>
> One issue is that if I leave the ID field blank, "import" does not self
> generate invoice ID's - but instead just ignores the lines.  OK - no
> worries, I can plug in invoice numbers in the excel table, that's not too
> much work.  The more serious issue is this, although the specific customers
> are C033 and C034 - when I import, both invoices generated are applied to
> one of the customers only (C033) and none to the other customer (C034).  I
> generate 2 invoices, but both for the same customer (C033).
>
>
>
> Import Invoices is not reading the customer (Owner ID) in the table and
> applying to the correct customer.  This I do not know how to work around.
> I'm looking for ideas or anything I may be doing wrong.  I have upgraded to
> the latest version 2.6.16 (rev 509ce16+) BTW.
>
>
>
> Really, if I cannot batch-make invoices, I can't make use of GnuCash.
> Trying to generate 36 (or so) individual invoices on the 1st of each month
> is not going to work for me.
>
>
>
> I know this is probably a better application for just using Excel, but I
> wanted to give this a try.  Actually if I could figure a way to batch email
> my batch prepared invoices, this would be totally awesome.
>
>
>
> Any feedback is appreciated.  I will be away from this email address for a
> couple of days, so no big emergency.
>
> Thanks,
>
>
>
> Buster
>
>
>
>
>
> _______________________________________________
> gnucash-user mailing list
> gnucash-user at 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.
>
>
>
>
>


More information about the gnucash-user mailing list