[ic] Need Quickbooks Import Help Pls
jonc at webmaint.com
Mon Jul 28 11:33:11 EDT 2003
> An update on what I've found so far, some of this
> might help others using the quickbooks module. And
> thanks to Jon for the link to the newsgroups, google
> groups is perfect for getting through the info there.
> >First, it seems that when you have a quotation mark
> >the item description, it messes up the import into
> > Say 57" Windsock. I tried escaping it with 57\"
> >Windsock and that didn't work. In the import I get
> >the SPLID in the quantity field as a negative
> >and the date in the sku field when I have something
> >like this. If I take the " out, then it works fine.
> >Any ideas?
> It seems this is a bug with QB, at least with the
> version I'm using which is a bit older (2000). QB
> considers the " mark to be a field delimiter. My
> solution, in case anyone else needs it, is going to be
> a cron job to run just after midnight each day which
> simply does a replace on the .iif file from the
> previous day to replace all " with '' (the '' looks
> just like a " when it prints in their invoices and I
> do want that to appear). Not elegant, but certainly
> functional and won't require me to figure out any
> place the " might show up, including places customers
> might accidentally enter it like in company name and
> address and such.
We came up against this recently. Even up to and including QB2003 (UK and
US) there is still a problem.
>From what we can tell, QB uses double quotation marks (") as text delimiters
when there are certain characters in the field. You can see this by creating
an item in QB with a comma in the description and then running an item
For imports, you will break the import if you have double quotes within your
text - this will result in some data importing into the wrong fields. We
could find no way of escaping a " to get it back into QB. Interestingly
enough, it does use \n for a newline though..
Also, when QB exports a " it turns it into a ' which is annoying in itself
(when you have a catalog of thousands of products with inches in the
descriptions!). The only workaround we could find was replacing the double
with double single quotes. This looks fine in QB which uses a proportionally
Webmaint.com - Building Clever Websites http://www.webmaint.com/
Webmaint.net - Business Web Hosting http://www.webmaint.net/
> >Second, I'm exporting the affiliate id and the type
> >payment used (ie Credit Card (visa)) to go into terms
> >and rep, respectively.
> >The payment type seems to get entered in the customer
> >record under the additional info tab, but it's not
> >showing up on the invoice in quickbooks under terms.
> >And in some instances Credit Card (discover) has the
> >last two characters cut off.
> >The affiliate sorta shows up in the customer record,
> >actually only the first character of the affiliate
> >code shows up there (initials), and it too doesn't
> >show up on the invoice, so I can't report on sales by
> The problem with these two is that it's only being
> recorded by trans_quickbooks in one place when it
> should be recorded in two. Here's the solution in
> case anyone else wants it. There is a line around
> line 109 in my file that begins like this
> !TRNS TRNSTYPE DATE ACCNT
> at the end of the line you need to add two more fields
> TERMS REP
> (those should all be tabs separating the info)
> and then down about 24 lines is this line
> [scratch SADDR5][/filter]
> change that to
> [scratch SADDR5]
> [value mv_payment]
> [data session source][/filter]
> and that will record the affiliate and credit card
> used in rep and terms respectively on the invoice part
> of the transaction. The first place it was used
> recorded it only in the customer record, not the
> invoice part.
> The only problem I have left is that it's only
> recording the first letter of the affiliate code, like
> an initial, so that's more of a QB issue I think now.
> >Also I'm using [data session id] to record the
> >id into the memo field, but for some reason it's
> >cutting off the domain part so instead of something
> >like mF24dZxb:yahoo.com it's just showing mF24dZxb
> >And finally, is there any way in quickbooks to work
> >with partial shipments like there is in IC, I'm not
> >finding it anywhere I search.
> Havent figured out the last two yet.
> >If anyone has solved these issues or can point me in
> >the right direction I'd be most appreciative. And if
> >there are any good quickbooks community sites where I
> >can ask about stuff more specific to quickbooks,
> >please let me know. Thx.
More information about the interchange-users