QIF import file construction....
Derek Atkins
warlord at MIT.EDU
Sun Jan 11 13:26:33 EST 2015
Allan,
Please be sure to CC all your replies back to the list using your
mailer's Reply-To-List or Reply-All functionality. This allows other
people to see your questions/followups and also help out potentially
faster.
Allan <allanhasmail at gmail.com> writes:
> Thank you Derek,
>
> I figured that out by trial and error. Will the "Type:Bank" line work for any
> account type? i.e. an adjustment of Payroll Liability/Payroll expense, not
> involving a bank check?
No. You should use the correct "Type" based on the actual account
type. For example, !Type:CCard for a credit card.
> Also, the signs I had are all wrong. I reversed them.
>
> But GnuCash isn't matching anything and just creates all new accounts for the
> accounts in the splits! --it can't find anything by name at a lower level in
> the higherarchy! Oh no... What's it going to do with 2000 transactions? Will
> it group names to match or deal with each separately?
You have two options here:
1) Modify the categories in your QIF to be the full GnuCash name. E.g.:
SExpenses:Shareholder Wages
2) During QIF Import one page will will ask you to map QIF Categories to
GnuCash Accounts; at this point you can manually map
e.g. "Shareholder Wages" to "Expenses:Shareholder Wages", or wherever
it is in your hierarchy.
It sounds like you're doing neither of these, which is why it puts it at
the top-level.
-derek
> I now have this, which works correctly to allocate everything:
>
> !Type:Bank
> MPaycheck
> D9/1/2012
> N1074
> PAllan Anderson
> NTCF
> T-1556.00
> U-2153.00
> SShareholder Wages
> EAllan Anderson
> $2000.00
> SFederal Withholding
> EAllan Anderson
> $-201.00
> SEmployer FICA Expense
> EAllan Anderson
> $124.00
> SCompany FICA
> EAllan Anderson
> $-124.00
> SEmployee FICA
> EAllan Anderson
> $-124.00
> SEmployer FICA Expense
> EAllan Anderson
> $29.00
> SCompany Medicare
> EAllan Anderson
> $-29.00
> SEmployee Medicare
> EAllan Anderson
> $-29.00
> SState Withholding MN
> EAllan Anderson
> $-90.00
> ^
>
> On 01/10/2015 04:13 PM, Derek Atkins wrote:
>
> You are missing the !Type:Bank ..
>
> -derek
>
> Sent on my mobile. Please forgive any typos.
>
> ----- Reply message -----
> From: "Allan" <allanhasmail at gmail.com>
> To: <gnucash-user at gnucash.org>
> Subject: QIF import file construction....
> Date: Sat, Jan 10, 2015 4:51 PM
>
> I have about 2000 transactions from QuickBooks in a csv like
> format/general journal report I am trying to convert to QIF format for
> import into GC.
>
> I'm searching for converters, etc. to do this. Or just what the QIF
> format would be at least. Or, idk, advice to give up? lol
>
> I'm not seeing how to handle transactions with lots of splits, which are
> not unusual. Is all this QIF import just for importing a bank statement
> or some such simple case?
>
> ie. from my CSV file, here's a paycheck with splits for all the payroll
> taxes, deductions, etc:
>
> "Trans #","Type","Date","Num","Name","Memo","Account","Debit","Credit"
> "1951","Paycheck","9/1/2012","1074","Allan Anderson",,"1002 · TCF
> Bank","",1556.00
> ,,,,"Allan Anderson",,"6010 · Shareholder Wages",2000.00,""
> ,,,,"Allan Anderson",,"7015 · Automobile Expense","",""
> ,,,,"Allan Anderson",,"2130 · Federal Withholding","",201.00
> ,,,,"Allan Anderson",,"6040 · Employer FICA Expense",124.00,""
> ,,,,"Allan Anderson",,"2105 · Company FICA","",124.00
> ,,,,"Allan Anderson",,"2110 · Employee FICA","",124.00
> ,,,,"Allan Anderson",,"6040 · Employer FICA Expense",29.00,""
> ,,,,"Allan Anderson",,"2115 · Company Medicare","",29.00
> ,,,,"Allan Anderson",,"2120 · Employee Medicare","",29.00
> ,,,,"Allan Anderson",,"6050 · FUTA Expense",0.00,""
> ,,,,"Allan Anderson",,"2160 · FUTA Payable",0.00,""
> ,,,,"Allan Anderson",,"2140 · State Withholding MN","",90.00
> ,,,,,,,2153.00,2153.00
>
> As for the destination QIF, From this:
> https://github.com/Gnucash/gnucash/blob/master/src/import-export/qif-imp/file-format.txt
>
> I see:
> Non-investment transaction format
> ---------------------------------
> Letter Definition
> D Date
> T Amount
> U Transaction amount (higher possible value than T)
> C Cleared status
> N Number (check or reference number)
> P Payee/description
> M Memo
> A Address (up to 5 lines; 6th line is an optional message)
> L Category (category/class or transfer/class)
>
> S Category in split (category/class or transfer/class)
> E Memo in split
> $ Dollar amount of split
> % Percentage of split if percentages are used
> F Reimbursable business expense flag
> X Small Business extensions
> ^ End of entry
>
> Note that S,E and $ lines are repeated as needed for splits.
>
> So it seems this would convert to something like this, except, nothing
> happens when I import it. There are no matching accounts (QIF to GC
> accounts), no changes to any account balances:
>
> !Account
> MPaycheck
> D9/1/2012
> N1074
> PAllan Anderson
> NTCF
> T1556.00
> U2153.00
> SShareholder Wages
> EAllan Anderson
> $-2000.00
> SFederal Withholding
> EAllan Anderson
> $201.00
> SEmployer FICA Expense
> EAllan Anderson
> $-124.00
> SCompany FICA
> EAllan Anderson
> $124.00
> SEmployee FICA
> EAllan Anderson
> $124.00
> SEmployer FICA Expense
> EAllan Anderson
> $-29.00
> SCompany Medicare
> EAllan Anderson
> $29.00
> SEmployee Medicare
> EAllan Anderson
> $29.00
> SState Withholding MN
> EAllan Anderson
> $90.00
> ^
>
> _______________________________________________
> gnucash-user mailing list
> gnucash-user at gnucash.org
> https://lists.gnucash.org/mailman/listinfo/gnucash-user
> -----
> Please remember to CC this list on all your replies.
> You can do this by using Reply-To-List or Reply-All.
>
--
Derek Atkins, SB '93 MIT EE, SM '95 MIT Media Laboratory
Member, MIT Student Information Processing Board (SIPB)
URL: http://web.mit.edu/warlord/ PP-ASEL-IA N1NWH
warlord at MIT.EDU PGP key available
More information about the gnucash-user
mailing list