INTUIT CORPORATION ____ QUICKBOOKS VERSION 1.0 RELEASE 9 ____ DECEMBER 05, 1992 ############################################### # # # Using # # # # IMPORT/EXPORT FEATURE IN QUICKBOOKS # # # ############################################### If you have technical programming skills or if you are a software developer, you can use the import/export feature of QuickBooks to move data between QuickBooks and other software programs. This feature is NOT designed to move data from one QuickBooks company datafile to another QuickBooks company datafile. The data to be imported must be in a special format called QIF, which stands for QuickBooks Interchange Format. The QuickBooks Interchange Format is an extension of the Quicken Interchange Format. When QuickBooks exports data, it puts the data into QIF. This functionality has some specific limitations. You need to know these limitations, especially if you have been using Quicken, because you cannot use QuickBooks Import and Export in the same ways that you may have used during Quicken Import and Export. This feature is not currently suitable for direct use by QuickBooks users who do not have programming skills. In time, this feature will lead to new additional tools for all users because they will enable program development by anyone who wishes to serve our users. TABLE OF CONTENTS ################# Introduction : General Information about IMPORT and EXPORT Section 1 : Guidelines for EXPORTING QuickBooks data Section 2 : Guidelines for IMPORTING data into QuickBooks Section 3 : Details of the QIF format Section 4 : Example of a QIF datafile INTRODUCTION : General Information about IMPORT and EXPORT ########################################### The information in this file is meant to answer questions and help those people who may wish to use programming techniques in conjunction with this feature. Inutit Technical Support will not have any more information than what is included in this file. To access this feature from the QuickBooks Main Menu choose #7 Set Up/Customize, #2 Back Up/Restore/Copy Company Files, then #5 Export Company Data or #6 Import Company Data. To import data from other programs, you must edit it first to put it into QIF format. This editing involves 1) adding codes to identify the data and 2) ensuring that the data is in the order that QuickBooks expects when it imports data. Except for very long lists, you will find it easier to use the ordinary QuickBooks methods for adding to lists. LIMITATIONS: You MUST be aware of the limitations of the import feature. This feature is not designed to move data between mulitple QuickBooks files or to move data from QuickBooks to Quicken. If you export QuickBooks data and try to import it back into another QuickBooks file you will lose valuable information! You can import only "base" transactions; these are transactions that you ordinarily enter DIRECTLY into QuickBooks, such as invoices, bills and checks. You CANNOT import checks linked to bills, payments linked to invoices, and deposits linked to payments. You will lose these non-base transactions and you will lose all of the links between transactions when you import the data. Also, when importing a transaction with multiple split lines, each line of the split will become its own transaction in QuickBooks. If you are familar with Quicken's import/export feature, please note that this feature in QuickBooks is very different and cannot be used in the same manner. SECTION 1 : Guidelines for EXPORTING Quickbooks data ######################################## As with earlier versions of the QIF format, QuickBooks QIF data files are plain ASCII text files, with each line containing a single data element. The QuickBooks QIF data file format is an extension of the Quicken for DOS Version 5 and Version 6 QIF data file format. 1: A header line like this one is exported first: Intuit's QIF format exported by QuickBooks Version 1.0 R9 11/25/92 2:40pm 2. All lists are exported first, followed by transactions in each account register. OVERVIEW of QIF data file layout: Definition of Lists: Chart of Accounts list . Customer List . Other lists . Transactions in Account Registers: Account A Header Transaction A1 Transaction A2 . Account B Header Transaction B1 . 3. A single blank line is exported after each list and each register. 4. Each line in each record starts with a field-identifying code character. 5. Parent/Child relationships, defined below, are exported along with each transaction. This ensures that if & when the same transactions are re-imported, the child transaction will be ignored on import, thus ensuring data consistency by counting simple transactions once only. Parent/child transaction marking is defined as follows: a. An A/R transaction always ..................is defined as a parent. b. An A/P transaction having no transfers from A/R ..........is defined as a parent. having transfers from A/R ..........is defined as a child. c. A Bank transaction having many splits ....................is defined as a parent. having single to single transfers to A/R or A/P, is defined as a child. having single to single transfers not to A/R nor to A/P, if it reduces the net worth, it....is defined as a parent, (this allows exporting of addresses on checks) otherwise, it .....................is defined as a child. SECTION 2 : Guidelines for IMPORTING data into Quickbooks ############################################# 1. The importer expects the first line in QIF file to be a header line. If a header line is not present, a message warns you that the QIF file was not created by QuickBooks. The importer then continues processing the rest of the QIF file. 2. The importer ignores all blank lines during importing. 3. "!Account" and "!Type:" are valid record-identifying code characters. For each type of record, there is a valid set of field-identifying code characters. See "Details of the QIF format" section for this valid set for each type of record. The importer stops and warns you when a line with an unrecognized record-identifying code character is encountered. A line with an unrecognized field-identifying code character is ignored. 4. As explained in the previous section, normally the importer ignores all transactions that are marked as "-Child" transactions. However, if the switch "!option:SpecialXfr" is set, the importer ignores the parent/child transaction markings and imports all transactions. The same is true if you answer "yes" to the special handling question on the import screen. 5. The importer also handles these two switches: !Option:AutoSwitch All account definitions that follow this switch will simply be created, if not present, in the company you are importing into. !Clear:AutoSwitch Any account after this switch will be made the current account. 6. When transactions are exported, the "!Account" information is added before the set of transactions. So when you import the account, the importer loads the account before importing the transactions. If you want to import transactions into a different account, simply change the account header information in the QIF file. Also, make sure that the "T" identifier in the account header is the same as the "!Type" identifier in each transaction. example: !Account NWells Fargo Checking Account # 123456789 TChecking <----+ ^ +---These must be same. !Type:Checking <----+ SECTION 3 : Details of the QIF format ######################### QUICKBOOKS LISTS : ------------------ BALANCE-SHEET ACCOUNT Record A "balance sheet account" in QuickBooks --------------------- is the same as an "account" in Quicken. QIF Line Comments ============= =================================================== !Account * A balance-sheet account definition follows. N * Required field T * Specific account type mnemonic which is same as !Type field used to identify each transaction in the account. Type:Checking Cred Card Cur Asset Fxd Asset Cur Liab Oth A Oth Liab Net Worth (same as type "Equity" account) Bank CCard Cash Oth L Oth Asset A/R A/P D Description of the account. L Credit Limit; applies only to CreditCard accounts $ Statement Balance. / Statement balance date. V Vendor name for credit card accounts. A Account notes; except A/R & A/P ^ * End of record: EOF w/o this marker generates error. * Lines marked w/ an asterisk are REQUIRED fields. INCOME/EXPENSE ACCOUNT Record An "income/expense account" in QuickBooks ----------------------------- is the same as a "category" in Quicken. QIF Line Comments ============= =================================================== !Type:Cat * An "income/expense account" i.e. a Quicken "category" definition follows. N * Required field. D Description. B Up to 12 per account. I * Income account. Either specify "Income" or "Expense". E * Expense account.Either specify "Income" or "Expense". ^ * End of record: EOF w/o this marker generates error. * Lines marked w/ an asterisk are REQUIRED fields. CUSTOMER TYPE Record -------------------- QIF Line Comments ==================== ================================================ !Type:Customer Types * Record Type Identifier header line N * Customer type. ^ * End of record: EOF w/o this marker causes error. * Lines marked w/ an asterisk are REQUIRED fields. CUSTOMER Record --------------- QIF Line Comments =============== =================================================== !Type:Customers * Record Type Identifier header line N * Required A Up to 4 lines of address up to 30 characters each. J Up to 5 lines of address up to 30 characters each. O Must be in Customer type list; Add/Select if not C First contact person. P (nnn) nnn-nnnn {ext. nnnn} (extension not required) K Second contact. H (nnn) nnn-nnnn {ext. nnnn} (extension not required) E Representative or Salesperson. Must be in Employee list; Add/Select if not U Must be in list; Add/Select if not L Dollar amount only; cents ignored M Notes, up to 15 lines of 58 characters each. ^ * End of record: EOF w/o this marker generates error. * Lines marked w/ an asterisk are REQUIRED fields. VENDOR TYPE Record ------------------ QIF Line Comments ================== ================================================== !Type:Vendor Types * Record Type Identifier header line N * Vendor Type. ^ * End of record: EOF w/o this marker generates error * Lines marked w/ an asterisk are REQUIRED fields. VENDOR Record ------------- QIF Line Comments ============== =================================================== !Type:Vendors * Record Type Identifier header line N * Required A
Up to 4 lines of address up to 30 characters each. O Must be in list; Add/Select if not # Account Number. T Tax ID number. C Contact person. P (nnn) nnn-nnnn {ext. nnnn} (extension not required) M Notes, up to 15 lines of 58 characters each. ^ * End of record: EOF w/o this marker generates error. * Lines marked w/ an asterisk are REQUIRED fields. EMPLOYEE Record --------------- QIF Line Comments ============== =================================================== !Type:Employees * Record Type Identifier header line E * Required N * Required A
Up to 4 lines up to 30 characters each. ^ * End of record: EOF w/o this marker generates error. * Lines marked w/ an asterisk are REQUIRED fields. LINE ITEM TYPE Record --------------------- QIF Line Comments ================ =================================================== !Type:Items * Record Type Identifier header line * Line Item Type & User-Defined-Code (A Required line). Use a letter-code identifying the type of line-item, followed by the code-name, provided by you, identifying the sub-type of the line-item. P e.g. "PHardware" or "PSoftware" S "SConsulting" or "SDrafting" O D T "TSales Tax" or "TVAT" R L A C Must be in list; Add/Select if not $ Percentage is indicated by trailing '%' V * Vendor name for tax lines only. M Payment method for Payment lines. E Up to 3 lines ^ * End of record: EOF w/o this marker generates error. * Lines marked w/ an asterisk are REQUIRED fields. PROJECT Record -------------- QIF Line Comments ============== =================================================== !Type:Projects * Record Type Identifier header line N * Project. D Project Description. ^ * End of record: EOF w/o this marker generates error. * Lines marked w/ an asterisk are REQUIRED fields. PAYMENT TERMS Record -------------------- QIF Line Comments ==================== ================================================ !Type:Payment Terms * Record Type Identifier header line T * Payment Terms. N Net Days; Blank == 0 % Discount; Blank == 0 D Discount Days; Blank == 0 ^ * End of record: EOF w/o this marker causes error. * Lines marked w/ an asterisk are REQUIRED fields. SHIPPING METHOD Record ---------------------- QIF Line Comments ====================== ============================================== !Type:Shipping Methods * Record Type Identifier header line N * Shipping method name. ^ * End of record: EOF w/o this marker causes error * Lines marked w/ an asterisk are REQUIRED fields PAYMENT METHOD Record --------------------- QIF Line Comments ===================== ============================================== !Type:Payment Methods * Record Type Identifier header line N * Payment method. ^ * End of record: EOF w/o this marker causes error. * Lines marked w/ an asterisk are REQUIRED fields INVOICE MEMO Record ------------------- QIF Line Comments ============= =================================================== !Type:Memos * Record Type Identifier header line N * Up to 2 memo lines, each upto 60 characters. ^ * End of record: EOF w/o this marker generates error. * Lines marked w/ an asterisk are REQUIRED fields. BANK TYPE ACCOUNTS TRANSACTION Record ------------------------------------- All non-receivables and non-payables balance-sheet accounts are defined as "bank" type of balance-sheet accounts. IMPORT RESTRICTIONS: Transfers to A/P or A/R accounts CANNOT be imported. QIF Line Comments ============== =================================================== !Type: * Identifies the transaction account type. , the valid types for Bank Type Accounts are: Checking Cred Card Cur Asset Fxd Asset Cur Liab Oth A Oth Liab Net Worth is Equity Bank CCard Cash Oth L Oth Asset +Parent If the transaction is a parent. -Child If the transaction is a child. D * Must correspond to MM/DD/YY setting N Check #, blank (default), '*****'. P Will not link to Payee List, even if present T Sign determines outcome: debit='+', credit='-' C Blank (default), '*', or 'X' M Main memo line. L Account #1; will be overwritten if split1 has account B Project #1; will be overwritten if split1 has project A
Address; up to 6 lines; line 6 == extra message line ---splits--- Up to 31 sets, in groups as follows S Repetition of any of these split fields causes the Q split counter to advance. E Split description. $ Must be last; forces split counter to advance ---end of split--- ^ * End of record: EOF w/o this marker generates error. * Lines marked w/ an asterisk are REQUIRED fields. ACCOUNTS PAYABLE TRANSACTION Records ------------------------------------ Bill ---- IMPORT RESTRICTIONS: Transfers to A/P or A/R accounts CANNOT be imported. QIF Line Comments ============= =================================================== !Type:A/P * Identifier:Header line identifies the acct type of Tx #Bill * A/P Bill Identifier, must say "#Bill". +Parent If the transaction is a parent. -Child If the transaction is a child. D * Must conform to internal MM/DD/YY setting N Blank (default), or invoice/check number P * Must be in list; Add/Select if not W Use today's (DOS system) date if unspecified or wrong T Sign ignored - always negative (credit) M Memo line. L Account #1; will be overwritten if split1 has account B Project #1; will be overwritten if split1 has project ---splits--- Up to 31 sets, in groups as follows S Repetition of any of these split fields causes the Q split counter to advance. E Split description. $ Must be last; forces split counter to advance ---end of split--- ^ * End of record: EOF w/o this marker generates error. * Lines marked w/ an asterisk are REQUIRED fields. Payment ------- IMPORT RESTRICTIONS: A/P Payments CANNOT be imported. QIF Line Comments ============= =================================================== !Type:A/P * Identifier: Header line identifies the acct of Tx. #Payment * A/P Payment Identifier. +Parent If the transaction is a parent. -Child If the transaction is a child. D * Will conform to internal MM/DD/YY setting N Invoice number. P * Vendor name add/select if not in the list. T Always positive (debit) M Memo Line. L The checking account used to make payment B Project, if any; must be in list; Add/Select if not ^ * End of record: EOF w/o this marker generates error. * Lines marked w/ an asterisk are REQUIRED fields. ACCOUNTS RECEIVABLE TRANSACTION Records --------------------------------------- Invoice -------- QIF Line Comments ============= =================================================== !Type: A/R * Identifier: Header line identifies the acct type of Tx #Invoice * A/R subtype identifier, must say "#Invoice". +Parent An invoice transaction is a parent. D * Must conform to internal MM/DD/YY setting N Blank (default), number, or '*******' P * Must be in list; Add/Select if not A Up to 4 lines of address J Up to 5 lines of address O P.O. Number in the invoice. U Must be in list; Add/Select if not K Must be in list; Add/Select if not W Defaults to Inv Date if blank G Must be in list; Add/Select if not F Uses company default if blank B Must be in list; Add/Select if not M Up to 60 characters M Up to 60 characters T Total of all line items, INCLUDING APP-DISCs & PWIs! ---line items---up to 30 sets, as follows. Q Repeating a field causes line item counter to advance X Must be blank or in list; Add/Select if not. E Up to 60 chars; truncated according to Prod/Svc/Prof S Income/Expense/Balance sheet account name. @ Price Each or Amount exported. Importer requires both. $ All $s MUST add up to (Original Amount - PWI&SubTotals). ---end of line item ---- ^ * End of record: EOF w/o this marker generates error. * Lines marked w/ an asterisk are REQUIRED fields. RESTRICTIONS: 1. Import and Export status of the available line item types: Type Export Import Line Item Type Export Import ========= ====== ====== ==================== ====== ====== Part Y Y Other Charge Y Y Service Y Y Description-only Y Y Refund Y Y PWI: Payment w/ Inv. Y Y as non-deposited! Sales Tax Y Y Discount Line Item Y Y SubTotal Y Y Applied Discount Y Y **see note 3 2. Sales Tax and Subtotal lines have a quantity of zero. Description-only lines have a quantity of 0 and an amount of 0. Percentage 'Price Each' values are indicated by a trailing '%'. 3. Applied Discount "Line Item" will be exported as following three QIF lines: X-APP-DISC for Applied Discounts, S-Inc/Exp account, $-discount amount. Applied Discount can be imported as a Discount Line Item type. Payment -------- IMPORT RESTRICTIONS: A/R Payments CANNOT be imported. QIF Line Comments ============= =================================================== !Type:A/R * Identifier: Header line identifies the acct type of Tx. #Payment * A/R subtype identifier, must say "#Payment". +Parent An A/R "Payment" transaction is a parent. D * Conforms to internal MM/DD/YY setting. P * From list. T Total amount. N Payment Number i.e. Customer's check number. ^ * End of record: EOF w/o this marker generates error. * Lines marked w/ an asterisk are REQUIRED fields. Deposit ------- IMPORT RESTRICTIONS: A/R Deposist CANNOT be imported. A/R Deposit is a subset of the A/R Invoice. QIF Line Comments ============= =================================================== !Type:A/R * Identifier: Header line identifies account type of Tx. #Deposit * A/R subtype identifier, must say "#Deposit". +Parent A "Deposit" transaction is a parent. D * Conforms to internal MM/DD/YY setting T Total amount L * Transfer account for deposit ^ * End of record: EOF w/o this marker generates error. * Lines marked w/ an asterisk are REQUIRED fields. SECTION 4 : Example of a QIF datafile ######################### Intuit's QIF format exported by QuickBooks version 1.0 R9 11/25/92 04:41pm !Option:AutoSwitch !Account NWF Checking DWells Fargo Checking TChecking $4,211.19 A111-222-333 ^ NReceivables DCustomers & Invoices TA/R $0.00 ^ NPayables DBills & Vendors TA/P $0.00 ^ NSales Tax DBoard of Equalisation TA/P $-410.44 ^ NOpen Bal Equity DOpening Bal Equity TEquity $0.00 ^ !Clear:AutoSwitch !Type:Cat NSales I ^ NSales:Designs I ^ NSales:Disount I ^ NSales:Shipping I ^ Ndiscount Ddiscount E ^ NUtil DUtilities E ^ NUtil:Elect & Gas DElectricity & Gas E ^ !Type:Vendor Types NSupplies ^ NTax collector ^ !Type:Vendors NBay Gas And Eletric OSupplies AP. O. Box 16498 ASan Jose, CA 95123 MSent November pmt. CJones Smith P(408) 555-7878 ext 1234 #012345 T555-22-1234 ^ NState Board of Equalization OTax collector AP.O. Box 1196 ASan Tomas, CA 94389-1196 ^ !Type:Employees NEva Kwan EEK ^ NJohn P. Flying Ejpf A155C Willow St. AMenlo Park, CA 94026 ^ !Type:Customer Types NDistributor ^ NRetail ^ NTravel ^ !Type:Memos MPlease remit. Past due! ^ MThank you for your business. ^ !Type:Payment Methods NCash ^ NCheck ^ NMasterCard ^ NVISA ^ !Type:Projects NABC proj DABC project ^ NABC proj:mug Dcustom mug ^ NABC proj:Pen DBallpoint Pens ^ !Type:Payment Terms TDue on recpt N0 %0 D0 ^ TNet 10 N10 %0 D0 ^ TNet 30 N30 %0 D0 ^ !Type:Shipment Methods NFed Ex ^ Ntruck ^ NUS Mail ^ !Type:Items Lsub ESubtotal ^ Pmug CSales $45.00 ECustom Mug ^ Ppen CSales $9.50 EBall Point Pen E(Blue) ^ SDes1 CSales:Designs $300.00 EDesign Hours ^ Obal forwd COpen Bal Equity EOutstanding balance on start date ^ Oship Chrg CSales:Shipping EShipping Charge ^ Ddisc CSales:Disount $-5.000% EDisc 5% ^ Apayv MVISA EPayment by VISA ^ Rrefund CWF Checking $100.000% ERefund 10% ^ TTax VState Board of Equalization $8.250% ETax 8.25% EState Board of Equalization ^ !Type:Customers NABC Book Store A300 B-Royal Ave. ABayshore, CA 94352 ORetail MNote to ABC Book Store. CLinda Hafezi P(415) 555-2222 ext 3209 H( ) - ext UNet 10 L2,500.00 ^ NFly Away Travel Ejpf A120 Main Street AMiddlefield, CA 94261 OTravel CJohn Flying, Jr. P(415) 555-7777 ext 3209 H( ) - ext UNet 30 L5,000.00 ^ !Account NReceivables DCustomers & Invoices TA/R $0.00 ^ !Type:A/R #Invoice +Parent D11/18/92 W11/18/92 N1001 O864 PABC Book Store A300 B-Royal Ave. ABayshore, CA 94352 JABC Book Store J600 B-Royal Ave. JBayshore, CA 94352 T5,286.94 Gtruck FSan Francisco UNet 10 BABC proj KEK Q1000 Xmug SSales ECustom Mug @4.500 $4,500.00 Q500 Xpen SSales EBall Point Pen @0.950 $475.00 Q0 E(Blue) @0.000 $0.00 Q0 Xsub ESubtotal $4,975.00 @4,975.00 Q0 XTax S[Sales Tax] ETax 8.25% @8.250% $410.44 Q1 Xdisc SSales:Disount EDisc 5% @-5.000% $-248.75 Q2.5 XDes1 SSales:Designs EDesign Hours @30.000 $75.00 Q1 Xship Chrg SSales:Shipping EShipping Charge @75.250 $75.25 Q0 Xpayv EPayment by VISA @-500.000 $-500.00 Q1 XAPP-DISC Sdiscount EApplied from discount $-700.00 @-700.00 ^ #Payment +Parent D11/19/92 PABC Book Store T-1,000.00 N1 MCheck ^ #Payment +Parent D11/25/92 PABC Book Store T-3,086.94 N2 MCheck ^ !Account NPayables DBills & Vendors TA/P $0.00 ^ !Type:A/P #Bill +Parent D11/18/92 N2001 PBay Gas And Eletric W11/28/92 T-150.75 MNovember Pmt LUtil:Elect & Gas ^ #Payment +Parent D11/25/92 PBay Gas And Eletric T150.75 L[WF Checking] ^ !Account NSales Tax DBoard of Equalisation TA/P $-410.44 ^ !Type:A/P #Bill -Child D11/18/92 PState Board of Equalization W11/30/92 T-410.44 L[Receivables] BABC proj S[Receivables] QABC proj EABC Book Store $-410.44 ^ !Account NWF Checking DWells Fargo Checking TChecking $4,211.19 A111-222-333 ^ !Type:Checking -Child D11/25/92 N501 PBay Gas And Eletric T-150.75 M012345 L[Payables] ABay Gas And Eletric AP. O. Box 16498 ASan Jose, CA 95123 S[Payables] EInvoice #2001, 11/18/92 $-150.75 ^ ------------------------- end of ABOUTQIF.TXT file ---------------------------- This informative file is meant to answer questions and help those people who wish to use programming techniques in conjunction with this feature. Technical Support will not have any more information than what is included in this file. To print this "AboutQIF.txt" file: Step 1 : Check that your printer is connected and on-line. Step 2 : For example, if it is connected to DOS device LPT1:, type the following at DOS prompt: type "type AboutQIF.txt> LPT1:" and press "Enter" key. INTUIT CORPORATION ____ QUICKBOOKS VERSION 1.0 RELEASE 9 ____ DECEMBER 05, 1992