Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for
I am using QB Desktop Pro 2020 and am trying to import Invoices (and Bills) from spreadsheets.
I suspect it will have to be an IIF file so I downloaded the IIF_Import_Kit zip file but these example files are all dated 2005/6 and contain sales tax not VAT
Can anyone please point me to a sample IIF file for UK invoices and bills with VAT
Solved! Go to Solution.
In the absence of any UK samples being provided - I used the age old method of trial and error and managed to simplify the sample IIF file in the kit enough to get it working for importing UK invoices with or without VAT.
If you are not familiar with IIF, utilize the trial period of these importer tools
https://transactionpro.grsm.io/qbd
https://partners.saasant.com/affiliate/idevaffiliate.php?id=5051_2
The template should be like this:
Invoice
Customer | Transaction Date | RefNumber | PO Number | Terms | Class | Template Name | To Be Printed | Ship Date | BillTo Line1 | BillTo Line2 | BillTo Line3 | BillTo Line4 | BillTo City | BillTo State | BillTo PostalCode | BillTo Country | ShipTo Line1 | ShipTo Line2 | ShipTo Line3 | ShipTo Line4 | ShipTo City | ShipTo State | ShipTo PostalCode | ShipTo Country | Phone | Fax | Contact Name | First Name | Last Name | Rep | Due Date | Ship Method | Customer Message | Memo | Cust. Tax Code | Item | Quantity | Description | Price | Is Pending | Item Line Class | Service Date | FOB | Customer Acct No | Sales Tax Item | To Be E-Mailed | Other | Other1 | Other2 | Unit of Measure | AR Account | Currency | Exchange Rate | Sales Tax Code | |
Anne's Bakery | 01/09/2020 | 100 | Y | ######### | Anne's Bakery | One Harbor Street | Anytown | NY | 12345 | USA | Anne's Bakery | One Harbor Street | Anytown | NY | 12345 | USA | (123) 456-7890 | Non | Cabinets | 10 | Cabinets | 1000 | Non | ||||||||||||||||||||||||||||||||
Anne's Bakery | 01/09/2020 | 100 | Y | ######### | Anne's Bakery | One Harbor Street | Anytown | NY | 12345 | USA | Anne's Bakery | One Harbor Street | Anytown | NY | 12345 | USA | (123) 456-7890 | Non | Hardware | 10 | Hardware | 50 | Non | ||||||||||||||||||||||||||||||||
John's Barber Shope | 02/09/2020 | 101 | Y | ######### | John's Barber Shope | 1 Main Street | Anytown | NY | 12345 | USA | John's Barber Shope | 1 Main Street | Anytown | NY | 12345 | USA | (999) 888-8888 | Non | Blueprints | 1 | Blueprints | 500 | Non
|
Bill
Vendor | Transaction Date | RefNumber | Bill Due | Terms | Memo | Address Line1 | Address Line2 | Address Line3 | Address Line4 | Address City | Address State | Address PostalCode | Address Country | Vendor Acct No | Expenses Account | Expenses Amount | Expenses Memo | Expenses Class | Expenses Customer | Expenses Billable | Items Item | Items Qty | Items Description | Items Cost | Items Class | Items Customer | Items Billable | Unit of Measure | AP Account | Currency | Exchange Rate |
Bell Telephone | 01/09/2020 | 123 | 01/10/2020 | Net 30 | Office Telephone | Bell Telephone | 1 Main Street | Newton | NY | 12345 | USA | Telephone | 1000 | Sales Line | |||||||||||||||||
Bell Telephone | 01/09/2020 | 123 | 01/10/2020 | Net 30 | Office Telephone | Bell Telephone | 1 Main Street | Newton | NY | 12345 | USA | Telephone | 500 | Service Line | |||||||||||||||||
Ajax Lumber | 01/09/2020 | 400 | 01/10/2020 | Net 30 | Repairs | Ajax Lumber | 200 Smith Street | Anytown | NY | 11111 | USA | Repairs | 750 | Front Door |
Thanks for your prompt reply, unfortunately you appear to have given me examples of spreadsheets (not IIF files) for the US version and not UK with VAT.
The the two products
https://transactionpro.grsm.io/qbd
https://partners.saasant.com/affiliate/idevaffiliate.php?id=5051_2
also seem to work with US version QB as again using Sales Tax and not VAT
The products support QB Desktop US, UK, CA, and older AU version. The template is not in IIF format, but in XLS. As of 2008, company dropped support for setting Tax1Total and Tax2Total via the QBSDK. Users that have been using these fields to set GST/PST (Canada) or VAT (UK) will not longer have access to these fields once they upgrade to QuickBooks 2008 or later. The workaround is using the following fields:
* Sales Tax Code (set to a valid tax or VAT code) on each line
* Is Tax Included - optional flag to include the tax in the amount on all transaction lines
You should explore the trial period of those products.
Hi again @Fiat Lux - ASIA
So if I was to use your XLS templates - how would I import them into QuickBooks Desktop Pro 2020 - do I have to use one of those products.
I would prefer to use IIF, but I need a suitable sample IIF file that is not complicated - all invoices are for the same single service, which I can add as columns .
This is a copy of what I have at the moment...
Customer Name | Customer Contact | Customer Attention | Customer Street Address | Customer Street Address 2 | Customer Town | Customer State | Customer Country | Customer Post Code | ID | Invoice Date | Invoice Net | Invoice Tax | Tax_Code |
Cust1 | Jo Soap | Any Street | Anytown | GB | AB1 2BC | 198856-1 | 09-Oct-19 | 120 | 0 | N | |||
Cust2 | Fred Soap | Another Street | Anothertown | GB | WF6 1TA | 178856-2 | 09-Oct-19 | 100 | 20 | S |
Hello jayemmee,
Welcome to the Community page,
This is not something that is supported anymore by Quickbooks and you cant convert a .xls file to a .iff file either.
@Ashleigh1 wrote:Hello jayemmee,
Welcome to the Community page,
This is not something that is supported anymore by Quickbooks and you cant convert a .xls file to a .iff file either.
Hi @Ashleigh1
My QB still allows me to import IIF files and if I remember correctly (I used them a long time ago - I first started using QB back in 1998 when I lived in the USA), an IIF file is just a tab-separated text file with a very specific format
Excel can save files as tab-separated so I should be able to edit IIF files in Excel, if I had the right file structure.
In the absence of any UK samples being provided - I used the age old method of trial and error and managed to simplify the sample IIF file in the kit enough to get it working for importing UK invoices with or without VAT.
I have also played with the IIF format and have imported the IIF file with the assigned SalesTaxCode. Unfortunately although the SalesTaxCode if verieied on import it is then completly ignored and the Standard Tax code is assigned to the invoice line item.
QB support is ignoring this fact and providing links to US sample files which is useless fro the UK, particularly as we have 3-4 differing tax codes for any given invoice.
Has anybody managed to get the Invoiceline items to import from an IIF with their correctly assigned 'SalesTaxCode'?
Hello AndyHP,
Thanks for commenting on this thread,
The IIF files aren't supported in Quickbooks and there is no program that will open it.
Sorry, what do you mean IIF files are not supported?
QuickBooks Desktop Pro allows you to import IIF files under File Utilities etc.
We are importing the an invoice IIF and this is working, but there appear ot be a program defect as it is not importingthe SalesTaxCode assigned, instead it is assigning the Standard Tax Code (20%) in the UK which is wrong.
Hello AndyHP,
You can change it manually by going into the transaction and changing the VAT code.
We can't say if it is a program defect or if it is a problem with the IIF file as cant open them.
Yes you can most certainly change it in QB once it is imported, however this is prone to human error and relies on the operator knowning the correct VAT codes.
The invoice we are importing has 20-30 line items some with the new reduced rate VAT of 5% some at standard 20% and some at Zero rate.
IIF files open in Excel as TAB seperate files so you can inspect the contents. Would it help if I attach a sample IIF with one invoice line item to demonstrate the issue using the QB demo company?
Yes if you could send us a screenshot showing this that would be great thanks.
Attached is a simple sample invoice.IIF file which will import to the QB demo company.
The file has one line item with a reducted rate tax code assigned
When this imports, view the invoice in QB and you will see the line item has been imported with a standard 20% tax code and the VAT is calculated incorrectly.
Hi Ashleigh, do you have any feedback on my posts yesterday?
If you still hit the the wall, consider to utilize a 3rd party importer tool
Customer | Transaction Date | RefNumber | PO Number | Terms | Class | Template Name | To Be Printed | Ship Date | BillTo Line1 | BillTo Line2 | BillTo Line3 | BillTo Line4 | BillTo City | BillTo State | BillTo PostalCode | BillTo Country | ShipTo Line1 | ShipTo Line2 | ShipTo Line3 | ShipTo Line4 | ShipTo City | ShipTo State | ShipTo PostalCode | ShipTo Country | Phone | Fax | Contact Name | First Name | Last Name | Rep | Due Date | Ship Method | Customer Message | Memo | Cust. Tax Code | Item | Quantity | Description | Price | Is Pending | Item Line Class | Service Date | FOB | Customer Acct No | Sales Tax Item | To Be E-Mailed | Other | Other1 | Other2 | Unit of Measure | AR Account | Currency | Exchange Rate | Sales Tax Code | |
Anne's Bakery | 01/09/2020 | 100 | Y | 15/09/2020 | Anne's Bakery | One Harbor Street | Anytown | NY | 12345 | USA | Anne's Bakery | One Harbor Street | Anytown | NY | 12345 | USA | (123) 456-7890 | Non | Cabinets | 10 | Cabinets | 1000 | Non | ||||||||||||||||||||||||||||||||
Anne's Bakery | 01/09/2020 | 100 | Y | 15/09/2020 | Anne's Bakery | One Harbor Street | Anytown | NY | 12345 | USA | Anne's Bakery | One Harbor Street | Anytown | NY | 12345 | USA | (123) 456-7890 | Non | Hardware | 10 | Hardware | 50 | Non | ||||||||||||||||||||||||||||||||
John's Barber Shope | 02/09/2020 | 101 | Y | 15/09/2020 | John's Barber Shope | 1 Main Street | Anytown | NY | 12345 | USA | John's Barber Shope | 1 Main Street | Anytown | NY | 12345 | USA | (999) 888-8888 | Non | Blueprints | 1 | Blueprints | 500 | Non |
You need a 3rd party importer tool to do so.
https://transactionpro.grsm.io/qbd
https://partners.saasant.com/affiliate/idevaffiliate.php?id=5051_2
These links were posted earlier in the thread and aimed at US QuickBooks customers. Support is only available in ET (Eastern Time) for instance.
We need a solution for the UK that handles mutiple VAT rates on a single invoice. Can I ask that the program defect I have identified with the IIF file to be esclated to your product team and a fix timeline communicated for the UK market.
The products support QB Desktop US, UK, CA, and older AU version. The template is not in IIF format, but in XLS. As of 2008, company dropped support for setting Tax1Total and Tax2Total via the QBSDK. Users that have been using these fields to set GST/PST (Canada) or VAT (UK) will not longer have access to these fields once they upgrade to QuickBooks 2008 or later. The workaround is using the following fields:
* Sales Tax Code (set to a valid tax or VAT code) on each line
* Is Tax Included - optional flag to include the tax in the amount on all transaction lines
You should explore the trial period of those products.
Yes this is what I understand, I am using the SalesTaxCode & IsTaxIncluded fields in the IIF.
They are being validated by the import process and the import is successful other than the fact that the SalesTaxCode is ignored and the Standard Tax Code (S) applied to all line items which is incorrect.
We need the SalesTaxCode to be assigned correctly by QuickBooks Desktop 2020 Pro when it imports the IIF file.
This is a program defect so please can this be fixed in the UK release as a priority?
Can I request a response to the post from last week identify a program defect?
You have clicked a link to a site outside of the QuickBooks or ProFile Communities. By clicking "Continue", you will leave the community and be taken to that site instead.