Thanks for the update. Good to see another happy gnucash camper! On 27 May 2017 10:04 a.m., "Bruce Danielson" <daniels...@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.handc...@gmail.com] > *Sent:* Thursday, May 25, 2017 8:03 PM > *To:* Bruce Danielson > *Cc:* gnucash-user@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 <daniels...@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.handc...@gmail.com] > *Sent:* Thursday, May 25, 2017 5:57 PM > > > *To:* Bruce Danielson > *Cc:* gnucash-user@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 <daniels...@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.handc...@gmail.com] > *Sent:* Wednesday, May 24, 2017 12:54 PM > *To:* Bruce Danielson > *Cc:* gnucash-user@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 <daniels...@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@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. > > > > > > > _______________________________________________ gnucash-user mailing list gnucash-user@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.
