cancel
Showing results for 
Search instead for 
Did you mean: 
Announcements
Everything you need to know about banking in QuickBooks Online - Discover more
jayemmee
Level 2

Looking for UK sample IIF Invoice files

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
Best answer June 16, 2020

Accepted Solutions
jayemmee
Level 2

Looking for UK sample IIF Invoice files

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.

View solution in original post

27 REPLIES 27
Fiat Lux - ASIA
Level 15

Looking for UK sample IIF Invoice files

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

CustomerTransaction DateRefNumberPO NumberTermsClassTemplate NameTo Be PrintedShip DateBillTo Line1BillTo Line2BillTo Line3BillTo Line4BillTo CityBillTo StateBillTo PostalCodeBillTo CountryShipTo Line1ShipTo Line2ShipTo Line3ShipTo Line4ShipTo CityShipTo StateShipTo PostalCodeShipTo CountryPhoneFaxEmailContact NameFirst NameLast NameRepDue DateShip MethodCustomer MessageMemoCust. Tax CodeItemQuantityDescriptionPriceIs PendingItem Line ClassService DateFOBCustomer Acct NoSales Tax ItemTo Be E-MailedOtherOther1Other2Unit of MeasureAR AccountCurrencyExchange RateSales Tax Code
Anne's Bakery01/09/2020100    Y#########Anne's BakeryOne Harbor Street  AnytownNY12345USAAnne's BakeryOne Harbor Street  AnytownNY12345USA(123) 456-7890          NonCabinets10Cabinets1000              Non
Anne's Bakery01/09/2020100    Y#########Anne's BakeryOne Harbor Street  AnytownNY12345USAAnne's BakeryOne Harbor Street  AnytownNY12345USA(123) 456-7890          NonHardware10Hardware50              Non
John's Barber Shope02/09/2020101    Y#########John's Barber Shope1 Main Street  AnytownNY12345USAJohn's Barber Shope1 Main Street  AnytownNY12345USA(999) 888-8888          NonBlueprints1Blueprints500              

Non

 

 

 

Bill

VendorTransaction DateRefNumberBill DueTermsMemoAddress Line1Address Line2Address Line3Address Line4Address CityAddress StateAddress PostalCodeAddress CountryVendor Acct NoExpenses AccountExpenses AmountExpenses MemoExpenses ClassExpenses CustomerExpenses BillableItems ItemItems QtyItems DescriptionItems CostItems ClassItems CustomerItems BillableUnit of MeasureAP AccountCurrencyExchange Rate
Bell Telephone01/09/202012301/10/2020Net 30Office TelephoneBell Telephone1 Main Street  NewtonNY12345USA Telephone1000Sales Line              
Bell Telephone01/09/202012301/10/2020Net 30Office TelephoneBell Telephone1 Main Street  NewtonNY12345USA Telephone500Service Line              
Ajax Lumber01/09/202040001/10/2020Net 30RepairsAjax Lumber200 Smith Street  AnytownNY11111USA Repairs750Front Door              
jayemmee
Level 2

Looking for UK sample IIF Invoice files

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 

Fiat Lux - ASIA
Level 15

Looking for UK sample IIF Invoice files

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. 

jayemmee
Level 2

Looking for UK sample IIF Invoice files

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 NameCustomer ContactCustomer AttentionCustomer Street AddressCustomer Street Address 2Customer TownCustomer StateCustomer CountryCustomer Post CodeIDInvoice DateInvoice NetInvoice TaxTax_Code
Cust1 Jo SoapAny Street Anytown GBAB1 2BC198856-109-Oct-191200N
Cust2 Fred SoapAnother Street Anothertown GBWF6 1TA178856-209-Oct-1910020S






 

Ashleigh1
QuickBooks Team

Looking for UK sample IIF Invoice files

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. 

 

 

 

 

jayemmee
Level 2

Looking for UK sample IIF Invoice files


@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.

jayemmee
Level 2

Looking for UK sample IIF Invoice files

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.

AndyHP
Level 1

Looking for UK sample IIF Invoice files

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'?

 

Ashleigh1
QuickBooks Team

Looking for UK sample IIF Invoice files

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. 

 

 

AndyHP
Level 1

Looking for UK sample IIF Invoice files

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.

 

 

 

Ashleigh1
QuickBooks Team

Looking for UK sample IIF Invoice files

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. 

AndyHP
Level 1

Looking for UK sample IIF Invoice files

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?

 

Ashleigh1
QuickBooks Team

Looking for UK sample IIF Invoice files

Yes if you could send us a screenshot showing this that would be great thanks. 

AndyHP
Level 1

Looking for UK sample IIF Invoice files

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.

 

 

AndyHP
Level 1

Looking for UK sample IIF Invoice files

screen shot from QB showing the imported invoice with incorrect vat code assigned

 

 

AndyHP
Level 1

Looking for UK sample IIF Invoice files

Attaching a sample IIF file appears to be blocked.  Here is a screen shot of the file open in Excel showing the SalesTaxCode 'R' assigned, yet the item imports with SalesTaxCode 'S' assigned.

 

 

AndyHP
Level 1

Looking for UK sample IIF Invoice files

Hi Ashleigh, do you have any feedback on my posts yesterday?

 

Fiat Lux - ASIA
Level 15

Looking for UK sample IIF Invoice files

@AndyHP 

If you still hit the the wall, consider to utilize a 3rd party importer tool

CustomerTransaction DateRefNumberPO NumberTermsClassTemplate NameTo Be PrintedShip DateBillTo Line1BillTo Line2BillTo Line3BillTo Line4BillTo CityBillTo StateBillTo PostalCodeBillTo CountryShipTo Line1ShipTo Line2ShipTo Line3ShipTo Line4ShipTo CityShipTo StateShipTo PostalCodeShipTo CountryPhoneFaxEmailContact NameFirst NameLast NameRepDue DateShip MethodCustomer MessageMemoCust. Tax CodeItemQuantityDescriptionPriceIs PendingItem Line ClassService DateFOBCustomer Acct NoSales Tax ItemTo Be E-MailedOtherOther1Other2Unit of MeasureAR AccountCurrencyExchange RateSales Tax Code
Anne's Bakery01/09/2020100    Y15/09/2020Anne's BakeryOne Harbor Street  AnytownNY12345USAAnne's BakeryOne Harbor Street  AnytownNY12345USA(123) 456-7890          NonCabinets10Cabinets1000              Non
Anne's Bakery01/09/2020100    Y15/09/2020Anne's BakeryOne Harbor Street  AnytownNY12345USAAnne's BakeryOne Harbor Street  AnytownNY12345USA(123) 456-7890          NonHardware10Hardware50              Non
John's Barber Shope02/09/2020101    Y15/09/2020John's Barber Shope1 Main Street  AnytownNY12345USAJohn's Barber Shope1 Main Street  AnytownNY12345USA(999) 888-8888          NonBlueprints1Blueprints500              Non

 

AndyHP
Level 1

Looking for UK sample IIF Invoice files

Can you confirm how this is imported into QB Desktop Pro as the Excel Import only suggests it imports Customer, Suppliers or Product lists?

 

 

 

Fiat Lux - ASIA
Level 15

Looking for UK sample IIF Invoice files

AndyHP
Level 1

Looking for UK sample IIF Invoice files

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.

 

 

 

 

 

 

 

Fiat Lux - ASIA
Level 15

Looking for UK sample IIF Invoice files

@AndyHP 

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.

AndyHP
Level 1

Looking for UK sample IIF Invoice files

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?

 

 

 

AndyHP
Level 1

Looking for UK sample IIF Invoice files

Can I request a response to the post from last week identify a program defect?

 

Need to get in touch?

Contact us