[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2704
  • Last Modified:

Entering Reciepts to Import to Quickbooks

We have Quickbook Premier 2004

I have no clue on where to start with this.  I rarely use Quickbooks other than our accountants but I have given the daunting task of figuring this out.  What we would like to do is develop some sort of excel sheet that Quickbooks will import an excel sheet with data entered from reciepts.  The basic should include:

Date - Ref No. - Payee - Charge - Account - Memo

When imported into Quickbooks we would like it to be imported to a specific area of our Chart of Accounts.

Now my question would be first what are the best solution that everyone is currently using or know of.  If this could be done without any third part additions especially for one that you have to pay for then that would be first priority.  Please provide links for step by step instructions on ways to handle this.
0
MontyV
Asked:
MontyV
  • 45
  • 41
1 Solution
 
turn123Commented:
MontyV,

>Please provide links for step by step instructions on ways to handle this.
http://www.developer.intuit.com/ is the free way but I seriously doubt you'll find step by step instructions.  Do you have any programming experience?

Hope this helps,

Turn123
0
 
turn123Commented:
What your describing is the .iif method which is not recommended.

http://www.ouresolutions.com.au/integratorpro.asp has an explanation why

Please note that I DO NOT RECOMMEND THIS METHOD but it is easier and the samples can be found at http://www.quickbooks.com/support/faqs/docs/w_iiffiles2.html and instructions can be found at http://www.state.sd.us/drr2/newspaper/Revenue%20QuickBooks%20Instructions.pdf
0
 
MontyVAuthor Commented:
I have little to know experience with programming.  What programming language may help in accomplishing my goals.  I know Quickbook currently is able to import files from Excel or as a .csv file but how do I know how to situate my excel sheet to be able to import correctly into QuickBooks.  Do I simply create a sheet with these categories and Quickbooks know where to put it?  For example:

          A                    B                    C                    D                    E                    F  
        Date             Ref No.            Payee             Charge            Account            Memo
1    5/1/05              10-1                ABC              $85.00         3120-FF&E         explanation of charge
2    5/5/05              10-2                XYZ              $75.00         3120-FF&E         explanation of charge
3    5/12/05             10-3                DEF              $26.00         3120-FF&E         explanation of charge
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
turn123Commented:
MontyV,

That would be too easy ;-).  I would recommend that you use the QBSDK if you know how to store your information into variables and add to a string.  The language doesn't really matter but I use Perl myself.

If you would after downloading the SDK go to C:/Program Files/Intuit/QBSDK4.0/OSR/OnscreenRef/index-QBD.html or whatever the path on your computer is and look at select message too see what looks like your trying to do.  I'm thinking your doing a bill add but am unsure.

Turn123
0
 
MontyVAuthor Commented:
What is Quickbooks SDK?  I noticed you can download it but what exactly does it do?  I don't know the exact term but what I'm trying to do is eliminate the need for bookkeepers to collect all reciepts for the week/month and have to enter in all the reciepts manually into Quickbooks.  What we want to do is if, for example, I were to use the company credit card on misc. items througout the month I would like to enter all my reciepts into an excel sheet and have this file imported into Quickbooks.  From here all that is needed is reconcilliation.  Our accountant will simply import the file then reconcile and re-classify all entries.  Does that make sense?

-Monty
0
 
turn123Commented:
Monty,

The QuickBooks SDK is the only officially supported method that will work when you upgrade QuickBooks.  IIF files as I mentioned before might be usable depending on the version of QuickBooks your using but are pretty much guaranteed not to work in the not so distant future.  They were never gotten to a point where they are bug free.

My approach has been to set up an Excel spreadsheet for my users then import it to QuickBooks on the back end.  If you can make it a CSV file it is fairly straightforward.

What your saying is making sense and I'm still thinking your accountants are entering it as a bill.  Could you check with them and see how they enter it?  If you can post the steps they go through I can get a better understanding of your problem.

Turn123
0
 
turn123Commented:
FYI if you can get approved for a third party option you will have a much easier time of it but for a one-time project I would probably go the www.rentacoder.com route.  I think I can give you enough that you could get this done for < $20 if you can go with the CSV approach.

Are you in a situation where a CSV file won't work for you as far as your everyone entering data?

There is no easy way to do what your wanting the way your wanting for free.  bill.iif is probably the closest thing to your current request but I think it will be way too easy to mess up.  My personal preference is the SDK for reasons I won't go into here but there are third party solutions that should make your life easier.
0
 
MontyVAuthor Commented:
I will get back with you when our accountant gets back in from lunch in regards to the particular steps.  As far as a CSV file is concerned entering the data in Excel and saving it in a CSV file would be sufficient right?  When importing from Quickbooks these options are given Excel, IIF Files, Web Connect Files and Convert from Quicken.  When importing Excel does it automatically convert into a csv file?  The reason I ask is because the I'm counting the number of steps and learning curve of explaining the steps to individuals using this system.  Meaning if they have a template of what is required to enter in their reciepts and all they have to do is save and e-mail then that would be perfect but if they also have to convert to CSV file before sending then that would be an extra step.  Also if they forgot to covert then the accountant has to check and know if it is a CSV file or not.

- Monty
0
 
turn123Commented:
Monty,

I understand counting steps.  If I were you and I decided to go the IIF route distribute an IIF template.  You may have to associate the .iif extension with Excel for convince.

If your looking at going the SDK route name a file something.csv then open it with Excel.  Just email it as is an it will stay a CSV file.  Working with a .xls is also possible but is harder to do.  If your doing I would STRONGLY recommend against this route unless you just want to have some fun.  Not knowing what language your using the difficulty varies by language.

Please let me know which way your looking at going when you get the information from the accountant.  What version of QuickBooks are you using?  I'm using US so I might not be able to help you as far as I had hoped to be able too.

Turn123
0
 
MontyVAuthor Commented:
turn123

Sorry it took so long but apparently my accounting department is too busy to answer, I would assume, an easy question even though I'm trying to help them.  These are the steps they take to enter reciepts.

1.)  Open Chart of Accounts
2.)  Scroll to Credit Card Accounts (i.e. 3000)
3.)  Select Type of Credit Card (i.e. 3100 - AMX-Corp)
4.)  From here they would enter in each transaction.

Also we use Excel 2003 and Quickbooks Premier 2004 US.
0
 
turn123Commented:
MontyV,

I hear you :-).

So you are playing with multipule accounts here?  Have you thought about how you want to keep them separated in Excel?

I'm going to play around with some ideas and see if I can figure out which request you need.

Turn123
0
 
MontyVAuthor Commented:
Separate files can be maintained for the different credit cards.  Normally each credit card is property specific.  So most users will be using only one but some users have multiple credit cards maybe two or three but never exceeding.  As a company we have four credit cards but each serves its purpose and each individual is issued as needed.  

- Monty
0
 
turn123Commented:
Monty,

Ok more questions :-).

1) Is the Payee always going to be in QuickBooks already or are you going to have to add?
2) I'm almost 100% certain that the way your going to need to do this is with a bill add.  Could you try adding a bill on a copy of your company file and see if it does what you want?
3) Is there any specific reason that a bill add won't work for you?

Turn123
0
 
MontyVAuthor Commented:
Turn123

1.)  I can only imagine this may change but very infrequently depending on the person.  We can do without the payee I'm assuming considering spelling differences.  One person may put Ace Maintenance and another person may put Ace Maintenance Hardware.
2.)  I will see what I can do with the bill add.
3.)  There was no specific reason.  I know you mentioned it above but was not sure if that was the solution to the problem.  I can follow the link you provided.  Is it pretty straight forward to set up?

- Monty
0
 
turn123Commented:
Monty,

I think I've actually got something better that I can give you as I was working on importing the UPS bill as a favor for I guy I know at UPS and it covers what you need fairly closely.  I'ts not finished but should be a good start for you.

It requires Active Perl (http://www.activestate.com/Perl.plex?hdr=1 standard distribution [free download]).  After you install it you will need to go to start -> run -> ppm -> search text-csv -> install 1 (may vary choose "Text-CSV        [0.01] comma-separated values manipulation routines")

I'm going to post two scripts in my next two comments.  For each one create a new text file -> copy and past the code -> save as aUniqueName.pl -> depending on your setup you should be able to double-click it and have the script run.

For the CSV file create a new text file -> copy and paste the below text -> name it bill.csv -> open it with excel.

Date,Ref No.,Payee,Charge,Account,Memo
5/1/05,10-1,ABC,$85.00,3120-FF&E,explanation of charge
5/5/05,10-2,XYZ,$75.00,3120-FF&E,explanation of charge
5/12/05,10-3,DEF,$26.00,3120-FF&E,explanation of charge

Turn123
0
 
turn123Commented:
open BILL, "< bill.csv" || die "No bill found!\n";
use text::csv;
my $date;
my $csv = Text::CSV->new();
my $xml = "";
my $xmlFirst = "";
my $billNumber;

while (<BILL>) {
    if ($csv->parse($_)) {
        my @field = $csv->fields();
        next if $field[0] == "Date";
        $billNumber = "Credit Card purchase";
        if ($field[3] =~ /^\$(\d+)\.(\d*)$/) {
            $cost = $1;
            if ($2 > 9) {
                $cost .= '.'.$2;
            } elsif ($2 > 0) {
                $cost .= '.'.$2.'0';
            } else {
                $cost .= '00';
            }
        } else {
            print '******************************************'; print "\n";
            print "    Failed to import $field[2]    \n";
            print "    Bad payment amound $field[3]!    \n";
            print '******************************************'; print "\n";
            next;
        }
        $field[6]  =~ s/\&/\&amp\;/g;
        $xml .= '                    <ItemLineAdd>
                        <ItemRef>
                            <FullName>MiscCharge</FullName>
                        </ItemRef>
                        <Desc>Purchase made on '.$field[0].': Refference number is '.$field[1].': Paying '.$field[2].':
For '.$field[5].'</Desc>
                        <Quantity>1</Quantity>
                        <Cost>'.$cost.'</Cost>
                    </ItemLineAdd>
';

    } else {
        die "The XML input file was invalid!";
    }
}

open XML, "> Bill.xml";

$xmlFirst = '<?xml version="1.0"?>
<?qbxml version="3.0"?>
    <QBXML>
        <QBXMLMsgsRq onError="stopOnError" >
            <BillAddRq requestID="1" >
                <BillAdd>
                    <VendorRef>
                        <FullName>UPS</FullName>
                    </VendorRef>
                    <APAccountRef>
                        <FullName>Accounts Payable</FullName>
                    </APAccountRef>
                    <RefNumber>'.$billNumber.'</RefNumber>
                    <TermsRef>
                        <FullName>Net 30</FullName>
                    </TermsRef>
';

$xml .= '                </BillAdd>
            </BillAddRq>
        </QBXMLMsgsRq>
    </QBXML>';

print XML $xmlFirst;
print XML $xml;
0
 
turn123Commented:
use Win32::OLE::Const 'QBXMLRP2';
Win32::OLE->Option(Warn=>3);
my $lib=Win32::OLE::Const->Load('QBXMLRP2');

#
# Connect
#

our $qbxmlrp = Win32::OLE->new("QBXMLRP2.RequestProcessor");
my $qbfile = "";
$qbxmlrp->OpenConnection("","UPStest");
our $qbticket = $qbxmlrp->BeginSession("$qbfile", 2);
$actualFile = $qbxmlrp->GetCurrentCompanyFileName($qbticket);
print "Companyfile is $actualFile\n";
exit 1 unless ($actualFile);

#
# Read the request file and send it to QuickBooks
#

open IN, "< bill.xml" || die "Couldn't open input file";
my @req = <IN>;
my $req = join("\n", @req);
$xmlresponse = $qbxmlrp->ProcessRequest($qbticket,$req);
print $xmlresponse;

#
# Close connection, etc.
#

$qbxmlrp->EndSession($qbticket);
$qbxmlrp->CloseConnection();
undef $qbxmlrp;
0
 
MontyVAuthor Commented:
Turn123

I have no knowledge of perl but I will try out your solution over the weekend and any questions I have I'll post by Monday.  Thank you for you help thus far.

- Monty
0
 
MontyVAuthor Commented:
turn123

Sorry it took long but the last week and a half has been hell.  I'm going to follow your instructions you have laid out above then next couple of days but I have a couple of question first before I start.

1.)  I know nothing of Active Perl so I'm hoping it is pretty idiot proof to set up?
2.)  Do I install Active Perl on our server or is this something that must be installed on any computer that may be performing this function?
3.)  When I copy those scripts and rename then what does each script do specifically so that I can name them properly?
4.)  When I save those scripts does it have to be in a specific directory for them to run and if so where?

- Monty
0
 
turn123Commented:
Monty,

1) I thought so until I had someone mess up the installation somehow.  If you stick with the defaults you shouldn't have a problem.
2) It has to be set up on any computer using the script.  There are ways to compile the script so it takes longer to use but you don't have to install ActivePerl on each computer.
3) The first one takes the CSV file and generates an XML file.  The second takes the bill.xml file and imports it into QuickBooks.
4) No but I would suggest wrapping them in a batch file so you can see what their doing.

copy and paste to a new text document and make sure the extension is .bat  First pause is optional but it can help.

script1.pl
pause
script2.pl
pause

Good luck,
Turn123
0
 
MontyVAuthor Commented:
Turn123

Comments

3.)  The first script generates the XML.  Does the file name always has to be named bill.csv then?  The second script imports into Quickbooks.  Does it do this automatically?  What if we have multiple accounts and such as checking, savings, money market etc...  We also have two companies set up in our Quickbooks.

4.)  I have files saved under D:/Perl/convert.pl and D:/Perl/import.pl.  Do I create the .bat file in this same directory and is this directory ok in the first place?

- Monty
0
 
MontyVAuthor Commented:
I selected each script and I believe it converted the file ok but the import did not end of going anywhere.  I am having out accountant double check again though.

- Monty
0
 
turn123Commented:
Monty,

3) You can adjust it but you need to change line one (bill.csv) to your new file name.
4) As long as the .bat changes the working directory to this file you can put it wherever you want.  (cd\Perl);

On the not importing...

Can you post the contents of bill.xml and the output of when you tried to run the Perl script?

Turn123
0
 
MontyVAuthor Commented:
<?xml version="1.0"?>
<?qbxml version="3.0"?>
    <QBXML>
        <QBXMLMsgsRq onError="stopOnError" >
            <BillAddRq requestID="1" >
                <BillAdd>
                    <VendorRef>
                        <FullName>UPS</FullName>
                    </VendorRef>
                    <APAccountRef>
                        <FullName>Accounts Payable</FullName>
                    </APAccountRef>
                    <RefNumber></RefNumber>
                    <TermsRef>
                        <FullName>Net 30</FullName>
                    </TermsRef>
                </BillAdd>
            </BillAddRq>
        </QBXMLMsgsRq>
    </QBXML>
0
 
MontyVAuthor Commented:
The second script creates a "QBInstanceFinder" in a text file but with nothing in it.  Am I suppose to then import this file into quickbooks even though there is nothing in there.  The file is also 0kb.

When running the batch file as described this is what I get: (Also I wrote "amound" but that is what it says.  I thought it was referring to amount but I wrote exactly what the message said)

D:\Perl>convert.pl
*********************************
     Failed to import ABC
     Bad payment amound $85.00 !
*********************************
*********************************
     Failed to import XYZ
     Bad payment amound $75.00 !
*********************************
*********************************
     Failed to import DEF
     Bad payment amound $26.00
*********************************

D:\Perl>pause
Press any key to continue . . .


D:\Perl>import.pl
OLE exception from "QBXMLRP2.RequestProcessor.1":

If Quickbooks is not running, a call to the "BeginSession" method must include the name of the Quickbooks company data file.

Win32::OLE<0.1702> error 0x80040416
     in METHOD/PROPERTYGET "BeginSession" at D:\Perl\import.pl line 12

D:\Perl>pause
Press any key to continue

0
 
turn123Commented:
Monty,

amound is a typo on my part.  If you want to fix it change
Bad payment amound $field[3]!    \n";
to
Bad payment amount $field[3]!    \n";

There is a space after the amount which is throwing it for a loop.  You need to either remove the space or change
if ($field[3] =~ /^\$(\d+)\.(\d*)$/) {
to
if ($field[3] =~ /^\$(\d+)\.(\d*)\s*$/) {

Your not running this with QuickBooks open.  The first time you will need to register it by running it with QuickBooks open in the admin account.  You will get some choices.  Choose what is best for your needs.  If you want to run it with QuickBooks open you will need to allow it to always logon even when QuickBooks isn't running and give it a user account to log on with.  Then change
my $qbfile = "";
to
my $qbfile = 'c:\fullPathtoCompanyFile\companyFile.qbw';

Turn123
0
 
MontyVAuthor Commented:
I've made these changes and I will see the difference.  Quickbooks is not running when I run these scripts.  I do not want to run these scripts while quickbooks is open.  While Quickbooks is closed and I run the scripts do I import the "QBInstanceFinder" into Quickbooks?

- Monty
0
 
MontyVAuthor Commented:
I do not get the failed to import message anymore but I am still getting the error message when it is trying to start the second script (import.pl).  We use multi user in Quickbooks and I had everyone close out of Quickbooks when I ran this.

- Monty
0
 
turn123Commented:
Monty,

You need to run the import script with QuickBooks open logged in as the Administrator.  If you don't have QuickBooks open logged in as ADMIN the first time it won't work due to a security feature in QuickBooks.

QBInstanceFinder is something that QuickBooks generates and you don't need to worry about.

If you could copy the output of when you run the scripts and also D:\Program Files\Common Files\Intuit\QuickBooks\qbsdklog.txt

Turn123
0
 
MontyVAuthor Commented:
It looked good for a moment.

This is what the message says now.

D:\Perl>import.pl
Companyfile is \\Mainserver\Quickbooks Data\CorDS.QBW
<?xml version="1.0" ?>
<QBXML>
<QBXMLMsgsRs>
<BilAddRs requestID="1" statusCode=3140" statusSeverity="Error" statusMessage=
"There is an invalid reference to Quickbooks Vendor &quot;UPS&quot; in the Bill.
    QuickBoks error message: Invalid argument.  The specified record does not exist
in the list." />
</QBXMLMsgRs>
</QBXML>

D:\Perl>pause
Press any key to continue . . .
0
 
turn123Commented:
Monty,

That's better.  What it's saying is that you need to change
                        <FullName>UPS</FullName>
to
                        <FullName>TheVendorYouWantToUseThatIsAlreadyInQuickBooks</FullName>
changing the name of course ;-).

Turn123
0
 
MontyVAuthor Commented:
Will this be the same name as the "Payee already in my .csv file?"  Meaning if I were to change UPS to ABC Company and my three payee transactions are to XYZ Company, DEF Company and ABC Company does that mean it will write all payee to ABC Company rather then using the ones in the .csv file?

Also ran files again with different companies we have on file and I am still getting the same message.

- Monty
0
 
turn123Commented:
Monty,

This needs to be an vendor in your vendor file.  Use whatever they use under Account.

Payee should import as a line item.

Turn123
0
 
MontyVAuthor Commented:
I have tried multiple variations.  Can I email you the files with a snapshot of my quickbooks file in a jpeg?
0
 
turn123Commented:
Monty,

Use of email to solve a problem is prohibited by Experts-Exchange http://www.experts-exchange.com/help.jsp#hi99

You can upload the screenshot to a free file server and I'll look at it there.  If you need someone to help you figure out how to do that you can post in http:Community_Support/ and a Moderator will be happy to help.

I think your right about a screenshot being a HUGE help here.

Turn123
0
 
MontyVAuthor Commented:
Sorry I should of known.  Let me know if additional screenshots will help.

http://www.savefile.com/files/1635864 

- Monty
0
 
turn123Commented:
Monty,

It looks like your looking in your chart of accounts.  You need to pick an entry from the Vendor list.

Turn123
0
 
MontyVAuthor Commented:
I used a vendor on a list and this is the error message I get.  I did use a vendor but then I thought you meant account and that is why I tried that one instead.  I thought they were the same message but looking at it now it is a slightly different message.

D:\Perl>import.pl
Companyfile is \\Mainserver\Quickbooks Data\CorDS.QBW
<?xml version="1.0" ?>
<QBXML>
<QBXMLMsgsRs>
<BilAddRs requestID="1" statusCode=3140" statusSeverity="Error" statusMessage=
"There is an invalid reference to Quickbooks Terms &quot;Net 30&quot; in the Bill.
    QuickBooks error message: Invalid argument.  The specified record does not exist
in the list." />
</QBXMLMsgRs>
</QBXML>

D:\Perl>pause
Press any key to continue . . .

0
 
MontyVAuthor Commented:
Here is the file again with the screenshot.

http://www.savefile.com/files/6968568 

- Monty
0
 
turn123Commented:
Monty,

Thats good it means were making progress.

Change

                    <TermsRef>
                        <FullName>Net 30</FullName>
                    </TermsRef>
to a terms that you use with your Company File.

Turn123
0
 
MontyVAuthor Commented:
We do not have any terms set.  Is there a default?  I want to make sure we are on the right track so here are screenshots of the steps we take to entering the reciepts.

http://www.savefile.com/files/9205214 

- Monty
0
 
turn123Commented:
Monty,

Were going the roundabout way of doing what you want as I can't find any way to make the direct route work.  On my copy of QB it gives the end result like what you want as far as I can tell.

It really doesn't matter what terms you choose as long as they exist in QuickBooks.

Turn123
0
 
MontyVAuthor Commented:
We don't use such terms.  Where would I go about finding this so that it can be corrected?  If roundabout is what it takes then it will be perfect.

- Monty
0
 
turn123Commented:
Reports -> Lists -> Terms Listing
0
 
MontyVAuthor Commented:
This is what I have listed.  Do I use "10" or "Per Contract" or do I use one of the others?

     Term          Type          Discount%          Net due days     Discount days     Days of Month Due
       10         Standard           0.0%                     0                       0                         31
Per Contract  Standard           0.0%                     0                       0                         31

- Monty
0
 
turn123Commented:
I would say do 10 for now and if the folks your doing this for want something else let them tell you what they want.  JMO.  Lets get it working then figure out where they want to go from there.

Turn123
0
 
MontyVAuthor Commented:
Changed Terms:

                    <TermsRef>
                        <FullName>10</FullName>
                    </TermsRef>

This is the error message:

D:\Perl>import.pl
Companyfile is \\Mainserver\Quickbooks Data\CorDS.QBW
<?xml version="1.0" ?>
<QBXML>
<QBXMLMsgsRs>
<BilAddRs requestID="1" statusCode=3140" statusSeverity="Error" statusMessage=
"There is an invalid reference to Quickbooks Item &quot;MiscCharge&quot; in the Bill line.
    QuickBooks error message: Invalid argument.  The specified record does not exist
in the list." />
</QBXMLMsgRs>
</QBXML>

D:\Perl>pause
Press any key to continue . . .

- Monty

0
 
turn123Commented:
                       <ItemRef>
                            <FullName>MiscCharge</FullName>
                        </ItemRef>

Needs to be changed to an item already in QuickBooks or be added.  (I think the second option is what would be best for you).
0
 
MontyVAuthor Commented:
I think the verbage is confusing me considering I'm unfamiliar with Quickbooks.  Based on my screenshots what should i replace it with since my accountant is against me adding anything while they are not here.

- Monty
0
 
turn123Commented:
Lists -> Items list -> pick one.
0
 
turn123Commented:
Make sure that you play with a copy of the company file though :-)
0
 
MontyVAuthor Commented:
Great...It finally comes into Quickbooks.  Some questions though.  The amount entered in the CSV file is for example $85.00 and the it goes into QB as $8500.00.  Also the amount goes in as a payment but we want it to go in as a charge.  If you delete one of the entries it will delete all the entries.

- Monty
0
 
MontyVAuthor Commented:
0
 
turn123Commented:
Monty,

I would need to see the bill.xml file to tell you anything much.

Turn123
0
 
MontyVAuthor Commented:
Also what the csv file is not used?  Unless this is the only way it is going to work.  In the CSV file the date entered, payee, account and memo do not transfer to the entry.  Rather the code in the convert file is transferred see below:

open XML, "> Bill.xml";

$xmlFirst = '<?xml version="1.0"?>
<?qbxml version="3.0"?>
    <QBXML>
        <QBXMLMsgsRq onError="stopOnError" >
            <BillAddRq requestID="1" >
                <BillAdd>
                    <VendorRef>
                        <FullName>UPS</FullName>
                    </VendorRef>
                    <APAccountRef>
                        <FullName>Accounts Payable</FullName>
                    </APAccountRef>
                    <RefNumber>'.$billNumber.'</RefNumber>
                    <TermsRef>
                        <FullName>Net 30</FullName>
                    </TermsRef>
';

$xml .= '                </BillAdd>
            </BillAddRq>
        </QBXMLMsgsRq>
    </QBXML>';
0
 
MontyVAuthor Commented:
0
 
turn123Commented:
Lets smash dem stupid bugs.  $cost .= '00'; should be $cost .= '.00';

Being a credit has something to do with the Vendor, Terms, or item.  I'm not sure which.  Can you confirm the item your using is assigned to an expense account?

Because it's much easier this way.  If you need that stuff to transfer let me know and we can work on that.  As you can tell this is a fairly is nowhere near as simple as it should be.

I'll look at this again later as I need to run right now.
0
 
MontyVAuthor Commented:
turn123

You have been extremely helpful and have provided me with extremely helpful solutions.  This has thus far been the best support on EE I have ever had so far.

The credit card is assigned to the "Type - Credit Cards."  The entries are made here and then under account we assign it to a expense.  Hope this makes sense.
screenshot - http://www.savefile.com/files/5460939 

Having all the information transfer from the CSV file was the route I thought we were going in initially.  I apologize if I was not clear enough.  If done this way then every entry would have to be the same.  This is never the case because most entries are different.  For exampe Home Depot, Electronic Wherehouse, HD Supply etc...  It is not always Home Depot, Home Depot and Home Depot.  Ideally I would want entries made on the CSV file and have the "exact" contents in the CSV file to be transferred to Quickbooks.

The setup directs the entries to the correct place, whic is perfect but the entries is the only thing that needs correction.

Take your time because I too realize this is a lot more hairy then expected and I appreciate the time you have taken.

- Monty
0
 
turn123Commented:
Monty,

As your no doubt aware just about everyone you meet on Experts-Exchange are not paid so support you receive is given because the person giving it feels like it :-).

>>1.)  I can only imagine this may change but very infrequently depending on the person.  We can do without the payee I'm assuming considering spelling differences.  One person may put Ace Maintenance and another person may put Ace Maintenance Hardware.<<

This is the easiest approach as QuickBooks is very picky about the Vendor having to be in.  If you wan to have each vendor billed your going to need to run two scripts (one to add the vendor if it doesn't exist and one to add the bill).  Before going that route make sure that you *know* that this will work for you.

What type of item are you using to enter this information?

Could you replace bill.xml with the below xml and post the results?

<?xml version="1.0"?>
<?qbxml version="3.0"?>
    <QBXML>
        <QBXMLMsgsRq onError="stopOnError" >
            <AccountQueryRq>
                <FullName>Credit Cards</FullName>
            </AccountQueryRq>
        </QBXMLMsgsRq>
    </QBXML>
0
 
MontyVAuthor Commented:
So you want to replace the contents of the bill.xml file (it seems this one given is much shorter than the one I have) and run only the second script where it imports it into quickbooks?

- Monty
0
 
MontyVAuthor Commented:
I did replace the contents and ran the second script only and depending on what you were expecting, I got nothing.  Meaning there were no entries made.  I did not get any type of error though.

- Monty
0
 
turn123Commented:
Can you post what it gave you for the XML output?  It wasn't supposed to make any entries I just want to see how Credit Cards is set up.
0
 
MontyVAuthor Commented:
So you wanted me to replace the contents in the first script?  This part:

open XML, "> Bill.xml";

$xmlFirst = '<?xml version="1.0"?>
<?qbxml version="3.0"?>
    <QBXML>
        <QBXMLMsgsRq onError="stopOnError" >
            <BillAddRq requestID="1" >
                <BillAdd>
                    <VendorRef>
                        <FullName>Boiler Depot</FullName>
                    </VendorRef>
                    <APAccountRef>
                        <FullName>Accounts Payable</FullName>
                    </APAccountRef>
                    <RefNumber>'.$billNumber.'</RefNumber>
                    <TermsRef>
                        <FullName>10</FullName>
                    </TermsRef>
';

$xml .= '                </BillAdd>
            </BillAddRq>
        </QBXMLMsgsRq>
    </QBXML>';



****And you want me to only put in the part you just gave?****

<?xml version="1.0"?>
<?qbxml version="3.0"?>
    <QBXML>
        <QBXMLMsgsRq onError="stopOnError" >
            <AccountQueryRq>
                <FullName>Credit Cards</FullName>
            </AccountQueryRq>
        </QBXMLMsgsRq>
    </QBXML>
0
 
MontyVAuthor Commented:
I changed file in the portion of the first script and I got an error message:

Companyfile is \\mainserver\Quickbooks Data\CorDS.QBW
OLE exception from "QBXMLRP2.RequestProcessor.1":

Quickbooks found an error when parsing the provided XML text stream.

Win32::OLE<0.1702> error 0x80040400
     in METHOD/PROPERTYGET "ProcessRequest" at D:\Perl\import.pl line 24


In the xml this is what I get.

<?xml version="1.0"?>
<?qbxml version="3.0"?>
    <QBXML>
        <QBXMLMsgsRq onError="stopOnError" >
            <AccountQueryRq>
                <FullName>Credit Cards</FullName>
            </AccountQueryRq>
        </QBXMLMsgsRq>
    </QBXML>                    <ItemLineAdd>
                        <ItemRef>
                            <FullName>Credit Cards</FullName>
                        </ItemRef>
                        <Desc>Purchase made on 8/1/2005: Reference number is 1-Oct: Paying ABC:
For For Maintenance</Desc>
                        <Quantity>1</Quantity>
                        <Cost>85.00</Cost>
                    </ItemLineAdd>
                    <ItemLineAdd>
                        <ItemRef>
                            <FullName>Credit Cards</FullName>
                        </ItemRef>
                        <Desc>Purchase made on 8/5/2005: Reference number is 2-Oct: Paying ABC:
For For Office</Desc>
                        <Quantity>1</Quantity>
                        <Cost>75.00</Cost>
                    </ItemLineAdd>
                    <ItemLineAdd>
                        <ItemRef>
                            <FullName>Credit Cards</FullName>
                        </ItemRef>
                        <Desc>Purchase made on 8/12/2005: Reference number is 3-Oct: Paying Cahners:
For For Management</Desc>
                        <Quantity>1</Quantity>
                        <Cost>26.00</Cost>
                    </ItemLineAdd>


I hope I did it right and I hope this helps.

- Monty

0
 
turn123Commented:
Monty,

You should have a file named bill.xml.  Delete the contents of that file and past the raw XML into it.  Don't worry about the Perl script that generates it I just want some infromation from QuickBooks.

The XML file should look like

<?xml version="1.0"?>
<?qbxml version="3.0"?>
    <QBXML>
        <QBXMLMsgsRq onError="stopOnError" >
            <AccountQueryRq>
                <FullName>Credit Cards</FullName>
            </AccountQueryRq>
        </QBXMLMsgsRq>
    </QBXML>
0
 
MontyVAuthor Commented:
That is what I tried and I did not get anything not even a error message.  What I did.

1.)  Opened the Bill.xml doc and erased everything in it and replaced with new (above) xml content.
2.)  I ran the second script provided.

use Win32::OLE::Const 'QBXMLRP2';
Win32::OLE->Option(Warn=>3);
my $lib=Win32::OLE::Const->Load('QBXMLRP2');

#
# Connect
#

our $qbxmlrp = Win32::OLE->new("QBXMLRP2.RequestProcessor");
my $qbfile = "";
$qbxmlrp->OpenConnection("","UPStest");
our $qbticket = $qbxmlrp->BeginSession("$qbfile", 2);
$actualFile = $qbxmlrp->GetCurrentCompanyFileName($qbticket);
print "Companyfile is $actualFile\n";
exit 1 unless ($actualFile);

#
# Read the request file and send it to QuickBooks
#

open IN, "< bill.xml" || die "Couldn't open input file";
my @req = <IN>;
my $req = join("\n", @req);
$xmlresponse = $qbxmlrp->ProcessRequest($qbticket,$req);
print $xmlresponse;

#
# Close connection, etc.
#

$qbxmlrp->EndSession($qbticket);
$qbxmlrp->CloseConnection();
undef $qbxmlrp;

Unless you want me to import the bill.xml file and not use the second perl script to pass it through Quickbooks please let me know and how.
0
 
turn123Commented:
2) Right.  What I want is what that second script outputted (the text) or are you saying there was no output at all?

Could you call it like scripName.pl >> test.txt

and paste the contents of text.txt here?
0
 
MontyVAuthor Commented:
There was no output into Quickbooks and if you are referring ot the "QBIntanceFinder.txt" then that file also has no output, it is blank.
0
 
turn123Commented:
I'm looking for the output from QuickBooks.  There should be something where you were checking for errors.  That is what I want.
0
 
MontyVAuthor Commented:
If you are referring to the errors that I have been posting then that is from the .bat file.  I created a new .bat file with only the second script and this is the error that I got.

D:\Perl>scripname.pl
Companyfile is \\Mainserver\Quickbooks Data\CordDS.QBW
<?xml version+"1.0" ?>
<QBXML>
<QBXMLMsgsRs>
<AccountQueryRs statusCode="500" statusSeverity="Warn" statusMessage="The query request has not been fully completed.  There was required element <&quot;Credit Cards&quot;> that could not be found in QuickBooks."/>
</QBXMLMsgsRs>
</QBXML>

D:\Perl\pause
Press any key to continue . . .
0
 
turn123Commented:
Could you replace <FullName>Credit Cards</FullName> with the actual name of the account your trying to use?
0
 
MontyVAuthor Commented:
I changed the file to "CreditCard Accounts" and this is what I got.

D:\Perl>scripname.pl
Companyfile is \\Mainserver\Quickbooks Data\CorDS.QBW
<?xml version="1.0" ?>
<QBXML>
<QBXMLMsgsRs>
<AccountQueryRs statusCode="0" statusSeverity="Info" statusMessage="Status OK">
<AccountRet>
<ListID>1030000-1094051818</ListID>
<TimeCreated>2004-09-01T08:16:58-08:00</TimeCreated>
TimeModified>2005-04-04T14:34:59-08:00</TimeModified>
<EditSequence>1112650499</EditSequence>
<Name>CreditCard Accounts</Name>
<FullName>CreditCard Accounts</FullName>
<IsActive>true</IsActive>
<Sublevel>0</Sublevel>
<AccountType>CreditCard</AccountType>
<AccountNumber>3210</AccountNumber>
<Balance>0.00</Balance>
<TotalBalance>39471.92</TotalBalance>
<TaxLineInfoRet>
<TaxLineID>1577</TaxLineID>
<TaxLineName>B/S-Liabs/Eq.: Other current liabilities</TaxLineName>
</TaxLineInfoRed>
<CashFlowClassification>Operating</CashFlowClassification>
</AccountRet>
</AccountQueryRs>
</QBXMLMsgsRs>
</QBXML>

D:Perl>pause
Press any key to continue . . .
0
 
turn123Commented:
Monty,

The CreditCard type apparently gets registered as a payment not a charge :-/.

If you change the account associated with your item to an account with a type expense it should fix the payment instead of bill issue.  Is that something that you can do?

Turn123
0
 
MontyVAuthor Commented:
We want the CreditCard type to be registered as a charge not a payment though.  All entries are posted as a charge weekly and payments are posted monthly or as payments are needed.  This is what my accountants at least tell me.

Changing the associated account was not possible with this script.  If we can get the entries in the CSV to import exactly into the Credit Card Account section but have it assigned to a default expense, for example 0000-unknown, then our bookkeepers can reassign the account to the proper expense.  This would work.

- Monty
0
 
turn123Commented:
Monty,

You ever get the feeling that something’s not quite right?  Try changing the radio at the top of the bill to credit and see if that fixes it.

When you enter a charge manually have you seen the QuickBooks warning?

"You have specified this transaction's source account on a distribution line.  When creating a transaction based on an account such as your checking account, credit card, or accounts payable, you will usually want to specify an expense, asset or liability account in the distribution area to indicate what this money was used for"

Can you give me a screenshot of what your accounting guys are doing currently?

Turn123
0
 
MontyVAuthor Commented:
Turn123

"You ever get the feeling that something’s not quite right?  Try changing the radio at the top of the bill to credit and see if that fixes it."

Changing what radio?  Where is this?

"When you enter a charge manually have you seen the QuickBooks warning?"

No Warning.  Does this not sound right?  From what they explain it sounds ok.  They enter the charges then the payment which balances out the two.  They went into a more lengthy explanation but that is what I got out of it.

Screenshot - http://www.savefile.com/files/3995147

If you look at the few entries above "Boiler Depot (Test Import)" for example Ferguson, Smart & Final, HGTV etc.. you will see that under the name that an expense has been assigned to different expenses.  Ferguson 9000 series expense, Smart & Final 5000 series expense etc...  and if you look at the top of the window you will the entries are made in "Credit Card Accounts" 3210 series (not an expense account).

- Monty
0
 
turn123Commented:
http://www.savefile.com/files/8412796 shows what I'm referring to.

Whee this is so much fun (and way off the beaten path for me).
0
 
MontyVAuthor Commented:
Oh yeah I see it now.  Can it be defaulted on Credit and not the other way around?  Are we going to be able to tranfer the exact data on the CSV file rather then what is assigned in the script?

- Monty
0
 
turn123Commented:
Monty,

If changing it to a credit works we should be able to import it that way.  Please double check with the accountants that's what they want as it seems very odd to me.

>Are we going to be able to tranfer the exact data on the CSV file rather then what is assigned in the script?
We should be able to but I want to know for sure this is what you want before looking at it.

Turn123
0
 
MontyVAuthor Commented:
Though it may seem odd this is exactly what i want.  Thank you.

- Monty
0
 
turn123Commented:
Monty,

Please try this XML to confirm it's what you want.  If it is I'll start re-writing the Perl script.

<?xml version="1.0"?>
<?qbxml version="3.0"?>
    <QBXML>
        <QBXMLMsgsRq onError="stopOnError" >
            <CreditCardChargeAddRq requestID="1" >
        <CreditCardChargeAdd>
                    <AccountRef>
                        <FullName>sds</FullName>
                    </AccountRef>
                    <PayeeEntityRef>
                        <FullName>UPS</FullName>
                    </PayeeEntityRef>
                    <RefNumber>sfdesf</RefNumber>
                    <ExpenseLineAdd>
                        <AccountRef>
                            <FullName>sds</FullName>
                        </AccountRef>
                        <Amount>5.00</Amount>
                    </ExpenseLineAdd>
                 </CreditCardChargeAdd>
            </CreditCardChargeAddRq>
        </QBXMLMsgsRq>
    </QBXML>
0
 
MontyVAuthor Commented:
I have the entry entered twice.  One as a charge and the other as a payment.  Other than that the bill it is no longer one bill, which is good.  I am able to change the account which is also good.  I'm assuming I will be able to send to tranfer the memo section also right?  Other then that it looks good.

Screenshot - http://www.savefile.com/files/6786062

- Monty
0
 
MontyVAuthor Commented:
turn123

You haven't given up on me completely have you? :-[  

- Monty
0
 
turn123Commented:
Monty,

Unfortunately I've never messed with credit card entry and QuickBooks handles them in a way that defies all my reasoning.  I would pursue this further if I had enough free time but I'm out of ideas and don't really have time to try to find more :-(.

If you could handle it like a normal bill it would work fine but the CreditCard account acts VERY WIERD.  If they could go and edit the bills changing the bill to a credit you could make it work but I don't think that’s exactly what you wanted.

You might look at the IIF files and see how they work at importing the bill but I doubt that would work very well either.

~~~Turn123

0
 
MontyVAuthor Commented:
turn123

I have yet to find the exact solution that I need but you helped lead me in the right direction.  I might have some solutions that may work for us.  Thank you for taking out the time in helping.  Sorry for such a late reply.

- Monty
0
 
turn123Commented:
Goodluck Monty.

Thanks for the points and the grade and I hope you get your solution.

Turn123
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 45
  • 41
Tackle projects and never again get stuck behind a technical roadblock.
Join Now