Sample QIF Import and Export
simplsol at aol.com
simplsol at aol.com
Fri Jan 13 11:00:56 EST 2006
Scott,
Here are a set of handlers I wrote a very long time ago in HyperTalk.
This is entirely reverse engineered (trial and error) and it was
written for import and export to the checkbook part of my business
system, so it may not handle all of the data you are dealing with.
Looking at it today, I can see lots of improvements from porting it to
Transcript (30,000 character limit in fields?!?). Don't forget to quote
all literals - HC could handle a variable called Date, Rev requires
"Date".
This should get you started:
on exportQIF
ask file "Enter a name for exported text:" with "Export to QIF"
if the result is "Cancel" then exit exportQIF
put it into fileName
ask "Start date:" with fld Date of cd 1
if the result is "Cancel" then exit exportQIF
put it into startDate
ask "End date:" with the short date
if the result is "Cancel" then exit exportQIF
put it into endDate
convert startDate to seconds
convert endDate to seconds
put "Reviewed: 0 Exported: 0"
put "!Type:Bank" into line 1 of temp
repeat with i = 1 to the number of cds
put i into word 2 of msg
get fld "Date" of cd i
convert it to seconds
if (it ? startDate) and (it ? endDate)
then
add 1 to last word of msg
put return after temp
put "D"& fld "Date" of cd i after temp
put return after temp
put "P"& fld "Payee" of cd i after temp
put return after temp
put "M"& fld "Memo" of cd i after temp
put return after temp
if fld "Check/Deposit" of cd i is "Deposit"
then put "T"& fld "Deposit" of cd i after temp
else put "T-"& fld "$" of cd i after temp
put return after temp
if fld "Check/Deposit" of cd i is "Check"
then
if the number of lines of fld "Mailing Address" > 0
then
repeat with n = 1 to the number of lines of fld "Mailing
Address"
put "A" & line n of fld "Mailing Address" of cd i after temp
put return after temp
end repeat
end if
put "N"& fld "Check Number" of cd i after temp
put return after temp
end if
if fld "Cleared"of cd i is not empty
then
put "C*" after temp
put return after temp
end if
put "L" & fld "Category" of cd i after temp
put return after temp
put "^" after temp
end if
end repeat
get the number of chars of temp
if it>30000
then
answer "The export contains "&it&" characters."&¬
" Only 30,000 fit in the text file. Try a smaller range of dates."
clearMsg
end if
open file fileName
write temp to file fileName
close file fileName
clearMsg
end exportQIF
on importQIF
answer file "Import from which QIF text file?" of type text
if the result is "Cancel" then exit importQIF
put it into fileToImport
if it is empty then exit importQIF
open file fileToImport
read from file fileToImport until eof
go to last cd of this stack
send "doMenu new card" to HyperCard
put 0 into addressLine
put "Transactions imported: 0"
repeat with i = 2 to the number of lines of it
--if char 2 of line i of it is empty then next repeat--to skip
empty lines,didn't work
if char 1 of line i of it is D
then
delete char 1 of line i of it
put line i of it into fld "Date"
next repeat
end if
if char 1 of line i of it is P
then
delete char 1 of line i of it
put line i of it into fld "Payee"
next repeat
end if
if char 1 of line i of it is M
then
delete char 1 of line i of it
put line i of it into fld "Memo"
next repeat
end if
if char 1 of line i of it is T
then
delete char 1 of line i of it
if the number of chars of line i of it > 5
then
repeat with x = 1 to (the number of chars of line i of it - 6)
if char x of line i of it is ","
then delete char x of line i of it
end repeat
end if
if char 1 of line i of it is "-"
then
delete char 1 of line i of it
put line i of it into fld "$"
put "Cash Pmt." into fld "Check/Deposit"
else
put line i of it into fld "Deposit"
put "Deposit" into fld "Check/Deposit"
end if
next repeat
end if
if char 1 of line i of it is "A"
then
add 1 to addressLine
delete char 1 of line i of it
put line i of it into line addressLine of fld "Mailing Address"
next repeat
end if
if char 1 of line i of it is N
then
delete char 1 of line i of it
put line i of it into fld "Check Number"
put line i of it into fld "MaxNumber"
put "Check" into fld "Check/Deposit"
writeOutTheAmount
next repeat
end if
if char 1 of line i of it is C
then
delete char 1 of line i of it
--X=reconciled, *=cleared
if line i of it is X
then put "?" into fld "Cleared"
else put "?" into fld "Cleared"
next repeat
end if
if char 1 of line i of it is L
then
delete char 1 of line i of it
put line i of it into fld "Category"
next repeat
end if
--S=Category in split
--E=Memo in split
--$=Dollar amt in split
if char 1 of line i of it is "^"
then
put 0 into addressLine
send "doMenu new card" to HyperCard
set numberFormat to "#."
add 1 to last word of msg
end if
end repeat
close file fileToImport
go to last cd of this stack
set lockMessages to true
send "doMenu delete card" to HyperCard
updateRunningTtl
go to previous card
put "Updating totals."
set numberFormat to "#."
listRecentTransactions
--answer "File "&fileToImport&" imported successfully."
end importQIF
Paul Looney
-----Original Message-----
From: Scott Kane <scott at proherp.com>
To: 'How to use Revolution' <use-revolution at lists.runrev.com>
Sent: Fri, 13 Jan 2006 15:29:31 +1100
Subject: RE: Qif Files
Hi Paul,
> QIF is an old-style data format, it does not use tabs or commas as
> delimiters. Instead it allocates aa specific number of characters per
> field - and the fields have to be padded if they contain less
> than the
> allocated characters.
> About 10 years ago I wrote a QIF import/export routine in HyperCard.
> Let me know if you need it and I'll try to find it.
As I'm new to Rev's parsing routines it'd be great
if you could find it. I'd really appreciate it,
but don't let it consume to much time for you...
Thanks,
Scott
_______________________________________________
use-revolution mailing list
use-revolution at lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your
subscription
preferences:
http://lists.runrev.com/mailman/listinfo/use-revolution
More information about the use-livecode
mailing list