importing invoices from a non-QB source


J

Jay Pennington

I am not a QuickBooks expert but I'm working with someone who uses
QuickBooks daily.
I have a MySQL database that has invoices that we would like to import into
my associate's QuickBooks database.

From my research it looks like QuickBooks can import a file of invoices.
(...commonly done when moving QuickBooks from one QuickBooks PC to another
QuickBook's PC.)

I can write a program to pull invoice data from my MySQL database and create
a flat file to be used to import into QuickBooks.....I need some advice on
what the data format needs to look like. Any documents/tutorial out there
on this type of effort. Anyone done something similar?

A secondary question - when importing a file of invoices - if the invoice
number already exists in the QuickBooks system will it ignore that incoming
record or overwrite it?

Thanks! I really need some advice on this one! Serious project.
Jay P.
 
Ad

Advertisements

B

Brad Thomas

Hi Jay

Jay Pennington said:
I am not a QuickBooks expert but I'm working with someone who uses
QuickBooks daily.
I have a MySQL database that has invoices that we would like to import into
my associate's QuickBooks database.

From my research it looks like QuickBooks can import a file of invoices.
(...commonly done when moving QuickBooks from one QuickBooks PC to another
QuickBook's PC.)

I can write a program to pull invoice data from my MySQL database and create
a flat file to be used to import into QuickBooks.....I need some advice on
what the data format needs to look like. Any documents/tutorial out there
on this type of effort. Anyone done something similar?

A secondary question - when importing a file of invoices - if the invoice
number already exists in the QuickBooks system will it ignore that incoming
record or overwrite it?

Thanks! I really need some advice on this one! Serious project.
Jay P.
The usual method is to use the IIF file format (the format is in the help
files), but apparently, a recent "technical update" (or service pack) broke
the IIF import routine (well, on the Australian version anyway)

The best and most accurate method is to use the Quickbooks SDK.
You'll need to create XML of your invoice details and use some programming
language (lots of examples for Visual Basic which is what I use are on
Intuit's developers webpages) to actually write them into QB.

Secondly, if you're going to create a piece of "middleware" to do this, you
can query Quickbooks to return the details of an invoice - so you'd do a
query using your invoice number, and if anything is returned with that
invoice number, you can take steps to avoid adding a duplicate. (An invoice
number, or Reference, is only a string, not a number so thats a good thing
IMHO)

There are 3 steps to get data from Quickbooks:
1. Open a connection
2. Start a session
3. Build your request XML
4. Request Quickbooks to process that XML
5. Parse the resulting XML

A Visual Basic sample looks like this:

' open a connection
Dim qbXMLRP as QBXMLRPLib.RequestProcessor
Set qbXMLRP = New QBXMLRPLib.RequestProcessor
qbXMLRP.OpenConnection "", "Example Application Name"

' begin a session to the QB datafile
Dim strTicket as String
' the "" in the next line means use the currently open data file
strTicket = qbXMLRP.BeginSession("",QBXMLRPLib.qbFileOpenDoNotCare)

' build the XML request - you need to end up with something like:

<?xml version="1.0"?>
<?qbxml version="2.0"?>
<QBXML>
<QBXMLMsgsRq onError="stopOnError">
<InvoiceQueryRq requestID="1">
<RefNumber>12345</RefNumber>
</InvoiceQueryRq>
</QBXMLMsgsRq>
</QBXML>


' request QB to process the XML
Dim strXMLResponse as String
strXMLResponse = qbXMLRP.ProcessRequest(strTicket, strXMLRequest)

' assuming your XML was well formed (the SDK has a validator) you'll get a
response like this:
' this is actually a payment response from a production application ;-)

<?xml version="1.0" ?>
<QBXML>
<QBXMLMsgsRs>
<ReceivePaymentAddRs requestID="1" statusCode="0" statusSeverity="Info"
statusMessage="Status OK">
<ReceivePaymentRet>
<TxnID>1A0E-1089620563</TxnID>
<TimeCreated>2004-07-12T18:22:43+10:00</TimeCreated>
<TimeModified>2004-07-12T18:22:43+10:00</TimeModified>
<EditSequence>1089620563</EditSequence>
<TxnNumber>1562</TxnNumber>
<CustomerRef>
<ListID>2D80000-1089616803</ListID>
<FullName>PAUL WOLFF:881631454933</FullName>
</CustomerRef>
<ARAccountRef>
<ListID>420000-1088389360</ListID>
<FullName>Accounts Receivable</FullName>
</ARAccountRef>
<TxnDate>2004-07-12</TxnDate>
<RefNumber>007692</RefNumber>
<TotalAmount>40.00</TotalAmount>
[snip]
</ReceivePaymentRet>
</ReceivePaymentAddRs>
</QBXMLMsgsRs>
</QBXML>

So you need to parse this XML and at least check the
<ReceivePaymentAddRs requestID="1" statusCode="0" statusSeverity="Info"
statusMessage="Status OK">
element for a status code of "0" - then check the rest if you need to. ie:
your invoice will exist if there is a value in the RefNumber element.

You could always add a Memo element in your InvoiceAdd request to say that
this invoice was imported from an external source (Memos appear in reports,
but not on the invoice)

Um - I hope this gets you started somewhat..... ;-)

Doing it the SDK way is the preferred method - stay right away from the IIF
file format.


Hope this helps

Kind Regards

Brad


Bradley D Thomas
Director
Phoenix Software International
Brisbane, Australia
http://phoenixsoftware.dyndns.biz/
 
J

Jay Pennington

Brad,
Thank you! I'm going to need to study through this....it may be my
solution!
Thank you so very much for spending some serious time putting this together
& explaining it!

I really do appreciate it!
Jay


Brad Thomas said:
Hi Jay

Jay Pennington said:
I am not a QuickBooks expert but I'm working with someone who uses
QuickBooks daily.
I have a MySQL database that has invoices that we would like to import into
my associate's QuickBooks database.

From my research it looks like QuickBooks can import a file of invoices.
(...commonly done when moving QuickBooks from one QuickBooks PC to another
QuickBook's PC.)

I can write a program to pull invoice data from my MySQL database and create
a flat file to be used to import into QuickBooks.....I need some advice on
what the data format needs to look like. Any documents/tutorial out there
on this type of effort. Anyone done something similar?

A secondary question - when importing a file of invoices - if the invoice
number already exists in the QuickBooks system will it ignore that incoming
record or overwrite it?

Thanks! I really need some advice on this one! Serious project.
Jay P.
The usual method is to use the IIF file format (the format is in the help
files), but apparently, a recent "technical update" (or service pack) broke
the IIF import routine (well, on the Australian version anyway)

The best and most accurate method is to use the Quickbooks SDK.
You'll need to create XML of your invoice details and use some programming
language (lots of examples for Visual Basic which is what I use are on
Intuit's developers webpages) to actually write them into QB.

Secondly, if you're going to create a piece of "middleware" to do this, you
can query Quickbooks to return the details of an invoice - so you'd do a
query using your invoice number, and if anything is returned with that
invoice number, you can take steps to avoid adding a duplicate. (An invoice
number, or Reference, is only a string, not a number so thats a good thing
IMHO)

There are 3 steps to get data from Quickbooks:
1. Open a connection
2. Start a session
3. Build your request XML
4. Request Quickbooks to process that XML
5. Parse the resulting XML

A Visual Basic sample looks like this:

' open a connection
Dim qbXMLRP as QBXMLRPLib.RequestProcessor
Set qbXMLRP = New QBXMLRPLib.RequestProcessor
qbXMLRP.OpenConnection "", "Example Application Name"

' begin a session to the QB datafile
Dim strTicket as String
' the "" in the next line means use the currently open data file
strTicket = qbXMLRP.BeginSession("",QBXMLRPLib.qbFileOpenDoNotCare)

' build the XML request - you need to end up with something like:

<?xml version="1.0"?>
<?qbxml version="2.0"?>
<QBXML>
<QBXMLMsgsRq onError="stopOnError">
<InvoiceQueryRq requestID="1">
<RefNumber>12345</RefNumber>
</InvoiceQueryRq>
</QBXMLMsgsRq>
</QBXML>


' request QB to process the XML
Dim strXMLResponse as String
strXMLResponse = qbXMLRP.ProcessRequest(strTicket, strXMLRequest)

' assuming your XML was well formed (the SDK has a validator) you'll get a
response like this:
' this is actually a payment response from a production application ;-)

<?xml version="1.0" ?>
<QBXML>
<QBXMLMsgsRs>
<ReceivePaymentAddRs requestID="1" statusCode="0" statusSeverity="Info"
statusMessage="Status OK">
<ReceivePaymentRet>
<TxnID>1A0E-1089620563</TxnID>
<TimeCreated>2004-07-12T18:22:43+10:00</TimeCreated>
<TimeModified>2004-07-12T18:22:43+10:00</TimeModified>
<EditSequence>1089620563</EditSequence>
<TxnNumber>1562</TxnNumber>
<CustomerRef>
<ListID>2D80000-1089616803</ListID>
<FullName>PAUL WOLFF:881631454933</FullName>
</CustomerRef>
<ARAccountRef>
<ListID>420000-1088389360</ListID>
<FullName>Accounts Receivable</FullName>
</ARAccountRef>
<TxnDate>2004-07-12</TxnDate>
<RefNumber>007692</RefNumber>
<TotalAmount>40.00</TotalAmount>
[snip]
</ReceivePaymentRet>
</ReceivePaymentAddRs>
</QBXMLMsgsRs>
</QBXML>

So you need to parse this XML and at least check the
<ReceivePaymentAddRs requestID="1" statusCode="0" statusSeverity="Info"
statusMessage="Status OK">
element for a status code of "0" - then check the rest if you need to. ie:
your invoice will exist if there is a value in the RefNumber element.

You could always add a Memo element in your InvoiceAdd request to say that
this invoice was imported from an external source (Memos appear in reports,
but not on the invoice)

Um - I hope this gets you started somewhat..... ;-)

Doing it the SDK way is the preferred method - stay right away from the IIF
file format.


Hope this helps

Kind Regards

Brad


Bradley D Thomas
Director
Phoenix Software International
Brisbane, Australia
http://phoenixsoftware.dyndns.biz/
 
B

Brad Thomas

Hi Jay

No worries! Have a look at my other post to you re getting a list of
invoices in IIF format.
You'll need the SDK to do that.

Regards

Brad

Jay Pennington said:
Brad,
Thank you! I'm going to need to study through this....it may be my
solution!
Thank you so very much for spending some serious time putting this together
& explaining it!

I really do appreciate it!
Jay


Brad Thomas said:
advice
on
what the data format needs to look like. Any documents/tutorial out there
on this type of effort. Anyone done something similar?

A secondary question - when importing a file of invoices - if the invoice
number already exists in the QuickBooks system will it ignore that incoming
record or overwrite it?

Thanks! I really need some advice on this one! Serious project.
Jay P.
The usual method is to use the IIF file format (the format is in the help
files), but apparently, a recent "technical update" (or service pack) broke
the IIF import routine (well, on the Australian version anyway)

The best and most accurate method is to use the Quickbooks SDK.
You'll need to create XML of your invoice details and use some programming
language (lots of examples for Visual Basic which is what I use are on
Intuit's developers webpages) to actually write them into QB.

Secondly, if you're going to create a piece of "middleware" to do this, you
can query Quickbooks to return the details of an invoice - so you'd do a
query using your invoice number, and if anything is returned with that
invoice number, you can take steps to avoid adding a duplicate. (An invoice
number, or Reference, is only a string, not a number so thats a good thing
IMHO)

There are 3 steps to get data from Quickbooks:
1. Open a connection
2. Start a session
3. Build your request XML
4. Request Quickbooks to process that XML
5. Parse the resulting XML

A Visual Basic sample looks like this:

' open a connection
Dim qbXMLRP as QBXMLRPLib.RequestProcessor
Set qbXMLRP = New QBXMLRPLib.RequestProcessor
qbXMLRP.OpenConnection "", "Example Application Name"

' begin a session to the QB datafile
Dim strTicket as String
' the "" in the next line means use the currently open data file
strTicket = qbXMLRP.BeginSession("",QBXMLRPLib.qbFileOpenDoNotCare)

' build the XML request - you need to end up with something like:

<?xml version="1.0"?>
<?qbxml version="2.0"?>
<QBXML>
<QBXMLMsgsRq onError="stopOnError">
<InvoiceQueryRq requestID="1">
<RefNumber>12345</RefNumber>
</InvoiceQueryRq>
</QBXMLMsgsRq>
</QBXML>


' request QB to process the XML
Dim strXMLResponse as String
strXMLResponse = qbXMLRP.ProcessRequest(strTicket, strXMLRequest)

' assuming your XML was well formed (the SDK has a validator) you'll get a
response like this:
' this is actually a payment response from a production application ;-)

<?xml version="1.0" ?>
<QBXML>
<QBXMLMsgsRs>
<ReceivePaymentAddRs requestID="1" statusCode="0" statusSeverity="Info"
statusMessage="Status OK">
<ReceivePaymentRet>
<TxnID>1A0E-1089620563</TxnID>
<TimeCreated>2004-07-12T18:22:43+10:00</TimeCreated>
<TimeModified>2004-07-12T18:22:43+10:00</TimeModified>
<EditSequence>1089620563</EditSequence>
<TxnNumber>1562</TxnNumber>
<CustomerRef>
<ListID>2D80000-1089616803</ListID>
<FullName>PAUL WOLFF:881631454933</FullName>
</CustomerRef>
<ARAccountRef>
<ListID>420000-1088389360</ListID>
<FullName>Accounts Receivable</FullName>
</ARAccountRef>
<TxnDate>2004-07-12</TxnDate>
<RefNumber>007692</RefNumber>
<TotalAmount>40.00</TotalAmount>
[snip]
</ReceivePaymentRet>
</ReceivePaymentAddRs>
</QBXMLMsgsRs>
</QBXML>

So you need to parse this XML and at least check the
<ReceivePaymentAddRs requestID="1" statusCode="0" statusSeverity="Info"
statusMessage="Status OK">
element for a status code of "0" - then check the rest if you need to. ie:
your invoice will exist if there is a value in the RefNumber element.

You could always add a Memo element in your InvoiceAdd request to say that
this invoice was imported from an external source (Memos appear in reports,
but not on the invoice)

Um - I hope this gets you started somewhat..... ;-)

Doing it the SDK way is the preferred method - stay right away from the IIF
file format.


Hope this helps

Kind Regards

Brad


Bradley D Thomas
Director
Phoenix Software International
Brisbane, Australia
http://phoenixsoftware.dyndns.biz/
 
C

Charlie

Jay,

As a stop-gap for my need in this area, I created a process for exporting
the info I need for invoices into an IIF file that can be imported into QB
by using the Timer Import function. In other words, I have an export
feature from another DB that exports the data into the IIF format used by QB
to import QB Timer data; this allows me to import what I would call the
"line items" for an invoice into an IIF, then import it into QB using File >
Timer > Import Activities. It's certainly got its limitations, but for my
needs it's perfect and didn't require learning the SDK. (Note: this is for
importing invoice line items only and allowing QB to create the invoice
from the data, which is what I am doing/wanted, so I don't know if you want
an entire invoice [including invoice #, tax info, etc.] already done prior
to importing...)

Feel free to email me at Charlie/at/pipeline.com if you want to know more...


Charlie


Jay Pennington said:
Brad,
Thank you! I'm going to need to study through this....it may be my
solution!
Thank you so very much for spending some serious time putting this together
& explaining it!

I really do appreciate it!
Jay


Brad Thomas said:
advice
on
what the data format needs to look like. Any documents/tutorial out there
on this type of effort. Anyone done something similar?

A secondary question - when importing a file of invoices - if the invoice
number already exists in the QuickBooks system will it ignore that incoming
record or overwrite it?

Thanks! I really need some advice on this one! Serious project.
Jay P.
The usual method is to use the IIF file format (the format is in the help
files), but apparently, a recent "technical update" (or service pack) broke
the IIF import routine (well, on the Australian version anyway)

The best and most accurate method is to use the Quickbooks SDK.
You'll need to create XML of your invoice details and use some programming
language (lots of examples for Visual Basic which is what I use are on
Intuit's developers webpages) to actually write them into QB.

Secondly, if you're going to create a piece of "middleware" to do this, you
can query Quickbooks to return the details of an invoice - so you'd do a
query using your invoice number, and if anything is returned with that
invoice number, you can take steps to avoid adding a duplicate. (An invoice
number, or Reference, is only a string, not a number so thats a good thing
IMHO)

There are 3 steps to get data from Quickbooks:
1. Open a connection
2. Start a session
3. Build your request XML
4. Request Quickbooks to process that XML
5. Parse the resulting XML

A Visual Basic sample looks like this:

' open a connection
Dim qbXMLRP as QBXMLRPLib.RequestProcessor
Set qbXMLRP = New QBXMLRPLib.RequestProcessor
qbXMLRP.OpenConnection "", "Example Application Name"

' begin a session to the QB datafile
Dim strTicket as String
' the "" in the next line means use the currently open data file
strTicket = qbXMLRP.BeginSession("",QBXMLRPLib.qbFileOpenDoNotCare)

' build the XML request - you need to end up with something like:

<?xml version="1.0"?>
<?qbxml version="2.0"?>
<QBXML>
<QBXMLMsgsRq onError="stopOnError">
<InvoiceQueryRq requestID="1">
<RefNumber>12345</RefNumber>
</InvoiceQueryRq>
</QBXMLMsgsRq>
</QBXML>


' request QB to process the XML
Dim strXMLResponse as String
strXMLResponse = qbXMLRP.ProcessRequest(strTicket, strXMLRequest)

' assuming your XML was well formed (the SDK has a validator) you'll get a
response like this:
' this is actually a payment response from a production application ;-)

<?xml version="1.0" ?>
<QBXML>
<QBXMLMsgsRs>
<ReceivePaymentAddRs requestID="1" statusCode="0" statusSeverity="Info"
statusMessage="Status OK">
<ReceivePaymentRet>
<TxnID>1A0E-1089620563</TxnID>
<TimeCreated>2004-07-12T18:22:43+10:00</TimeCreated>
<TimeModified>2004-07-12T18:22:43+10:00</TimeModified>
<EditSequence>1089620563</EditSequence>
<TxnNumber>1562</TxnNumber>
<CustomerRef>
<ListID>2D80000-1089616803</ListID>
<FullName>PAUL WOLFF:881631454933</FullName>
</CustomerRef>
<ARAccountRef>
<ListID>420000-1088389360</ListID>
<FullName>Accounts Receivable</FullName>
</ARAccountRef>
<TxnDate>2004-07-12</TxnDate>
<RefNumber>007692</RefNumber>
<TotalAmount>40.00</TotalAmount>
[snip]
</ReceivePaymentRet>
</ReceivePaymentAddRs>
</QBXMLMsgsRs>
</QBXML>

So you need to parse this XML and at least check the
<ReceivePaymentAddRs requestID="1" statusCode="0" statusSeverity="Info"
statusMessage="Status OK">
element for a status code of "0" - then check the rest if you need to. ie:
your invoice will exist if there is a value in the RefNumber element.

You could always add a Memo element in your InvoiceAdd request to say that
this invoice was imported from an external source (Memos appear in reports,
but not on the invoice)

Um - I hope this gets you started somewhat..... ;-)

Doing it the SDK way is the preferred method - stay right away from the IIF
file format.


Hope this helps

Kind Regards

Brad


Bradley D Thomas
Director
Phoenix Software International
Brisbane, Australia
http://phoenixsoftware.dyndns.biz/
 
G

Garry Martin

Hi Charlie

I have already sent you an email but thought I would ask here as well.
I too need to import Invoice data into Quickbooks and would be grateful if
you could email me the code you used to create the iif file.

Many Thanks

Garry

Charlie said:
Jay,

As a stop-gap for my need in this area, I created a process for exporting
the info I need for invoices into an IIF file that can be imported into QB
by using the Timer Import function. In other words, I have an export
feature from another DB that exports the data into the IIF format used by QB
to import QB Timer data; this allows me to import what I would call the
"line items" for an invoice into an IIF, then import it into QB using File

Timer > Import Activities. It's certainly got its limitations, but for my
needs it's perfect and didn't require learning the SDK. (Note: this is for
importing invoice line items only and allowing QB to create the invoice
from the data, which is what I am doing/wanted, so I don't know if you want
an entire invoice [including invoice #, tax info, etc.] already done prior
to importing...)

Feel free to email me at Charlie/at/pipeline.com if you want to know more...


Charlie


Jay Pennington said:
Brad,
Thank you! I'm going to need to study through this....it may be my
solution!
Thank you so very much for spending some serious time putting this together
& explaining it!

I really do appreciate it!
Jay


advice this,
you
get
a
response like this:
' this is actually a payment response from a production application ;-)

<?xml version="1.0" ?>
<QBXML>
<QBXMLMsgsRs>
<ReceivePaymentAddRs requestID="1" statusCode="0" statusSeverity="Info"
statusMessage="Status OK">
<ReceivePaymentRet>
<TxnID>1A0E-1089620563</TxnID>
<TimeCreated>2004-07-12T18:22:43+10:00</TimeCreated>
<TimeModified>2004-07-12T18:22:43+10:00</TimeModified>
<EditSequence>1089620563</EditSequence>
<TxnNumber>1562</TxnNumber>
<CustomerRef>
<ListID>2D80000-1089616803</ListID>
<FullName>PAUL WOLFF:881631454933</FullName>
</CustomerRef>
<ARAccountRef>
<ListID>420000-1088389360</ListID>
<FullName>Accounts Receivable</FullName>
</ARAccountRef>
<TxnDate>2004-07-12</TxnDate>
<RefNumber>007692</RefNumber>
<TotalAmount>40.00</TotalAmount>
[snip]
</ReceivePaymentRet>
</ReceivePaymentAddRs>
</QBXMLMsgsRs>
</QBXML>

So you need to parse this XML and at least check the
<ReceivePaymentAddRs requestID="1" statusCode="0" statusSeverity="Info"
statusMessage="Status OK">
element for a status code of "0" - then check the rest if you need to. ie:
your invoice will exist if there is a value in the RefNumber element.

You could always add a Memo element in your InvoiceAdd request to say that
this invoice was imported from an external source (Memos appear in reports,
but not on the invoice)

Um - I hope this gets you started somewhat..... ;-)

Doing it the SDK way is the preferred method - stay right away from
the
IIF
file format.


Hope this helps

Kind Regards

Brad


Bradley D Thomas
Director
Phoenix Software International
Brisbane, Australia
http://phoenixsoftware.dyndns.biz/
 
Ad

Advertisements

Joined
Sep 30, 2013
Messages
1
Reaction score
0
Quickbooks Invoice Import

I hope this thread is still checked. I have invoices being created in a third party solution. I wrote a routine that brings the invoices into quickbooks. Everything works. If a user goes into QuickBooks to enter a misc. invoice, QuickBooks doesn't see that an invoice was already created using the same invoice number (imported from third party). So if the last invoice imported batch was 100 to 200 QuickBooks still things 100 is the next number. Is there a way through the SDK or a setting that will allow QuickBooks to see the next invoice number should be 201?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top