Issues with Importing Invoices

Nelson Handcock nelson.handcock at gmail.com
Fri May 26 21:10:31 EDT 2017


Thanks for the update. Good to see another happy gnucash camper!

On 27 May 2017 10:04 a.m., "Bruce Danielson" <DanielsonB at logroom.com> wrote:

> Nelson – on a whim, today at work I decided to import my 34 June 1
> invoices, opening date today but invoice date and due date June 1, with
> posting fields filled in – worked like a charm.  So I have to plug in
> invoice numbers, but that’s ok.
>
>
>
> You’re right about the header row forcing commas as without the header row
> and without invoice numbers, Excel deletes the blank column A when saving.
> I opened the file in a text editor and placed a leading comma in both rows,
> but then got the same error as if I omitted invoice numbers with a header
> row (ID Not Set).
>
>
>
> To tell the truth, I could not import customers from my work PC, but it
> worked the first try on my home PC.  At work all the fields to the right of
> Ship Name got shifted 1 field to the right, like if there were one too many
> commas in the file – but at home it worked perfect using the same csv
> file.  Weird.
>
>
>
> SO – I’m importing and posting, all one step.  Thanks Nelson, all is good.
>
> I appreciate your help & suggestions.
>
> Bruce
>
>
>
>
>
> *From:* Nelson Handcock [mailto:nelson.handcock at gmail.com]
> *Sent:* Thursday, May 25, 2017 8:03 PM
> *To:* Bruce Danielson
> *Cc:* gnucash-user at gnucash.org
> *Subject:* Re: Issues with Importing Invoices
>
>
>
> 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